Database Programming with JDBC

1. Database Programming with JDBC Keith Vander Linden Calvin College

2. Overview • Introduction • An Example • Exercise 1 – a simple JDBC connection • JDBC Classes • Summary • Exercise 2 – recursive SQL with Java/JDBC

3. Database Programming • The world of information technology revolves around databases. • While interactive interfaces to database systems are useful, most database work is done though database programs. • Approaches to database programming: • Embedding commands in a programming language (e.g., SQLJ) • Using a database API (e.g., JDBC, ODBC, ADO.net) • Designing a database programming language (e.g., PL/SQL, T-SQL)

4. Impedance Mismatch • The problem is to bind: • relational fields, records and tables • native 4GL variables, arrays and classes • Relational databases • fields • records • tables • General-purpose programming languages • standard data types • classes

5. JDBC • Sun Microsystem’s database API for Java. • cf. ODBC/ADO.net • Supports Sun’s mantra: “Write once, run anywhere” • JDBC supports portability across DBMS vendors. • Java supports portability across hardware platforms. • The typical JDBC interaction sequence: 1. Establish a connection to a database 2. Interact with the database 3. Close the connection

6. A JDBC Example import java.sql.*; class SimpleJDBC { public static void main (String args[]) throws SQLException { DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@mydb", “login", “pswd"); Statement stmt = conn.createStatement(); try { ResultSet rset = stmt.executeQuery ("SELECT firstName, lastName FROM Student"); while (rset.next ()) System.out.println (rset.getString(1) + " " + rset.getString(2)); rset.close(); } catch (SQLException se) { System.out.println("oops! can't query the Student table. Error:"); se.printStackTrace(); } stmt.close(); conn.close(); } }

7. The Output % /usr/java/j2sdk1.4.2/bin/javac -classpath /opt/oracle/product/9ir2/jdbc/lib/ojdbc14.jar:. SimpleJDBC.java % /usr/java/j2sdk1.4.2/bin/java -classpath /opt/oracle/product/9ir2/jdbc/lib/ojdbc14.jar:. SimpleJDBC Adam Adams Bert Benson Cathy Cahill Derrick De Vries Ernest Evers Francetta Franks Gabby Gonzales Harold Hornby Inez Inglewood Jack Jansma Kevin Kline Laurance Lancaster Valery Vander Meiden

8. A JDBC Example import java.sql.*; class SimpleJDBC { public static void main (String args[]) throws SQLException { DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@mydb", “login", “pswd"); Statement stmt = conn.createStatement(); try { ResultSet rset = stmt.executeQuery ("SELECT firstName, lastName FROM Student"); while (rset.next ()) System.out.println (rset.getString(1) + " " + rset.getString(2)); rset.close(); } catch (SQLException se) { System.out.println("oops! can't query the Student table. Error:"); se.printStackTrace(); } stmt.close(); conn.close(); } } Import the JDBC API definition.

9. A JDBC Example Create a “standard” Java application. import java.sql.*; class SimpleJDBC { public static void main (String args[]) throws SQLException { DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@mydb", “login", “pswd"); Statement stmt = conn.createStatement(); try { ResultSet rset = stmt.executeQuery ("SELECT firstName, lastName FROM Student"); while (rset.next ()) System.out.println (rset.getString(1) + " " + rset.getString(2)); rset.close(); } catch (SQLException se) { System.out.println("oops! can't query the Student table. Error:"); se.printStackTrace(); } stmt.close(); conn.close(); } }

10. A JDBC Example import java.sql.*; class SimpleJDBC { public static void main (String args[]) throws SQLException { DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@mydb", “login", “pswd"); Statement stmt = conn.createStatement(); try { ResultSet rset = stmt.executeQuery ("SELECT firstName, lastName FROM Student"); while (rset.next ()) System.out.println (rset.getString(1) + " " + rset.getString(2)); rset.close(); } catch (SQLException se) { System.out.println("oops! can't query the Student table. Error:"); se.printStackTrace(); } stmt.close(); conn.close(); } } Load the JDBC driver for your DBMS and establish a connection to that DBMS.

11. A JDBC Example Create an SQL statement and pass it to the DBMS. Handle any error that might occur. import java.sql.*; class SimpleJDBC { public static void main (String args[]) throws SQLException { DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@mydb", “login", “pswd"); Statement stmt = conn.createStatement(); try { ResultSet rset = stmt.executeQuery ("SELECT firstName, lastName FROM Student"); while (rset.next ()) System.out.println (rset.getString(1) + " " + rset.getString(2)); rset.close(); } catch (SQLException se) { System.out.println("oops! can't query the Student table. Error:"); se.printStackTrace(); } stmt.close(); conn.close(); } }

12. A JDBC Example import java.sql.*; class SimpleJDBC { public static void main (String args[]) throws SQLException { DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@mydb", “login", “pswd"); Statement stmt = conn.createStatement (); try { ResultSet rset = stmt.executeQuery ("SELECT firstName, lastName FROM Student"); while (rset.next ()) System.out.println (rset.getString(1) + " " + rset.getString(2)); rset.close(); } catch (SQLException se) { System.out.println("oops! can't query the Student table. Error:"); se.printStackTrace(); } stmt.close(); conn.close(); } } Process the results of the SQL query.

13. A JDBC Example import java.sql.*; class SimpleJDBC { public static void main (String args[]) throws SQLException { DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@mydb", “login", “pswd"); Statement stmt = conn.createStatement(); try { ResultSet rset = stmt.executeQuery ("SELECT firstName, lastName FROM Student"); while (rset.next ()) System.out.println (rset.getString(1) + " " + rset.getString(2)); rset.close(); } catch (SQLException se) { System.out.println("oops! can't query the Student table. Error:"); se.printStackTrace(); } stmt.close(); conn.close(); } } Close the record set, the statement, and the connection.

14. Exercise 1 • Create a simple application with Java and JDBC to print out the contents of a relational table. • The stub code in exercise1 assumes the existence of a MS Access database called lab10.mdb.

15. JDBC Connections • Connection conn = • DriverManager.getConnection("jdbc:oracle:oci8:@mydb", “login", “pswd"); • The URL string for the connection contains: • the protocol (jdbc) • the vendor (oracle) • the driver (oci8, oci7, thin) • the server (mydb) • The Connection class is an interface, so you cannot create Connection objects directly. • All interactions between the java program and the database will be done through this object.

16. JDBC Statements • There are three classes for sending SQL statements: • Statement – for SQL statements without parameters • PreparedStatement – for precompiled/parameterized statements • CallableStatement – for executing stored procedures • These “statements” are Java classes, not individual SQL statements. They can be reused. • JDBC Statements are executed with: • executeUpdate() – executes SQL data definition statements • executeQuery() – returns the results of the SQL query • JDBC Statements support dynamic SQL statements.

17. JDBC Updates • The executeUpdate() statement performs updates: • Statement stmt = conn.createStatement(); • stmt.executeUpdate("CREATE TABLE Part (" • + " id INTEGER PRIMARY KEY, " • + " name VARCHAR2(10), " • + " cost REAL )"); • stmt.executeUpdate("INSERT INTO Part VALUES (1, 'simple', 10)"); • When used for DDL statements, executeUpdate() returns 0. • When used to modify data, it returns the number of rows affected by the update.

18. JDBC ResultSets • The executeQuery() command returns a ResultSet. • ResultSet rset = • stmt.executeQuery ("SELECT firstName, lastName FROM Student"); • while (rset.next()) • System.out.println(rset.getString(1) + " " + rset.getString(2)); • The ResultSet provides: • a cursor pointing just before the first result row • next(), to get the next row, returning true if successful • getXXX() to retrieve column values of Java type XXXThe argument to getXXX() may either be an index number, getInt(1), or a field name, getDouble(“cost”). • ResultSet cursors can be: • Forward-only or scrollable • Read-only or read-write

19. JDBC PreparedStatements • Sometimes it is more convenient or efficient to work with precompiled statements. • PreparedStatement pstmt = conn.prepareStatement( • “SELECT FROM Student WHERE lastName LIKE ?"); • The statement can then be configured and run. • pstmt.setString(1, ‘Vander%’); • ResultSet rset = pstmt.executeQuery() ; • The PerparedStatement provides: • setXXX() functions to set the statement values for Java type XXXAs with getXXX(), setXXX() arguments may be an index number or a field name.

20. JDBC Transactions • JDBC Connections can support transactions. • try { • conn.setAutoCommit(false); • stmt.executeUpdate(“CREATE TABLE t (id INTEGER, name VARCHAR2(10))"); • stmt.executeUpdate(“INSERT INTO t VALUES (10, ‘some name’)”); • conn.commit(); • conn.setAutoCommit(true); • } catch (SQLException se) { • System.out.println(“SQL Exception:“ + se.getMessage()); • conn.rollback(); • connsetAutoCommit(true); • } • This code will rollback() for any form of exception thrown by the code in the try block.

21. Summary • This lecture covered the basic concepts for accessing databases from 4th generation programming languages. • It illustrated the concepts using JDBC: • Connections • Statements • ResultSets • It also discussed JDBC support for transactions. • References (http://java.sun.com/products/jdbc/ )

22. Exercise 2 • Use the remaining time to work through the following JDBC exercise. • SQL is not recursive, so it can’t be used to compute the cost of a part and all its sub-parts (and their sub-parts and so forth). • Java is recursive, so it can be used with JDBC to perform this computation. • Start with the code given in exercise2 and then write: • code to load the appropriate database values into the tables. • a recursive Java function that uses JDBC to compute the total cost for potentially complex part. You’ll find the algorithm in the code.

23. Exercise 2: The Database • The parts database has the following relational table design: • Here, a part has its own cost and can have potentially many sub-parts. The count indicates the number of sub-parts required by the super-part. ID m SubPart Part name count m cost

24. Exercise 2: Sample Data Part ID name price 1 simple 10.00 2 complex 5.00 3 sub1 4.00 4 sub2 3.00 5 subsub1 2.00 6 subsub2 1.00 SubPart PID CID count 2 3 1 2 4 2 4 5 1 4 6 2 • Here we see that part 1 is simple in that it has no sub-parts mentioned in the SubPart table. • Part 2 is complex in that it has two sub-parts (3 & 4) one of which (4) has its own sub-parts (5 & 6)

25. Exercise 2: Hints • You may assume that the database contains no “cycles”, e.g., part 1 is made up of part 2’s, which are each made up of part 1’s. • Get the trivial case, i.e., part 1, working first. It has no sub-parts. • Print out appropriate error message if the given part doesn’t exist.