jdbc overview autumn 2001 lecturer: c. dejong. relational databases widespread use used via sql...
Post on 21-Dec-2015
216 views
TRANSCRIPT
Relational Databases
• widespread use
• used via SQL (Structured Query Language)
• freely available
• powerful
• text-based
What is JDBC?
• An API for Java database connectivity
• A collection of Java classes from Sun
• A set of interfaces for database programming
• java.sql.*
Steps for setting up JDBC
• install Java and JDBC (available from java.sun.com)
• install a driver
• install a relational database – MySQL, PostgreSQL (free!)– Oracle, Sybase, IBM’s DB2 (commercial)
Loading the driver
• driver should be provided with database
• one line of code:Class.forName(<driver class name>);
• ex: Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Make the connection
• Returns a Connection object
• Syntax:Connection con = DriverManager.getConnection(
url, "myLogin",
"myPassword");
URL for connecting
• should be in documentation with JDBC driver
• starts with “jdbc:”
• can connect across a network
Using statements
• creating a tablestmt.executeUpdate(
"CREATE TABLE COFFEES "
+ "(COF_NAME VARCHAR(32), SUP_ID INTEGER, PRICE FLOAT, "
+ "SALES INTEGER, TOTAL INTEGER)");
Using statements
• inserting datastmt.executeUpdate(
"INSERT INTO COFFEES "
+ "VALUES ('Colombian', 101, 7.99, 0, 0)");
Query result
COF_NAME PRICE
------------------ -----
Colombian 7.99
French_Roast 8.99
Espresso 9.99
Colombian_Decaf 8.99
French_Roast_Decaf 9.99
Retrieving data with ResultSet
• Statements can run a query on a database
• This returns an object.
• This object is a ResultSet.
Running the query in Java
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(
"SELECT COF_NAME, PRICE FROM COFFEES");
Inspecting the ResultSet
while (rs.next()) {
String s =
rs.getString("COF_NAME");
float n = rs.getFloat("PRICE");
System.out.println(s + " " + n);
}
Output
• The output will look something like this:
Colombian 7.99
French_Roast 8.99
Espresso 9.99
Colombian_Decaf 8.99
French_Roast_Decaf 9.99
alternative syntax
while (rs.next()) {
String s =
rs.getString(1);
float n = rs.getFloat(2);
System.out.println(s + " " + n);
}
• note: starts at 1, not 0!
some ResultSet retrieval methods
• getString()
• getInt()
• getFloat()
• getObject()
• … many others
Closing
• When finished, call close() on– ResultSet objects– Statement objects– Connection objects
• … or you may have a memory leak!