JDBC and SQLJ

1. JDBC and SQLJ CIS 612 Spring 2009

2. JDBC • JDBC is an API that enables database access from Java programs • JDBC for DB access provides • Portability across database servers • Portability across hardware architectures • JDBC drivers have been implemented for a number of DB servers (Oracle, SQL Server, DB2, Access, etc.)

3. JDBC • Java Database Connectivity (JDBC) has been part of the standard Java standard edition since JDK 1.1 • Current version is JDBC 3.0 (as of J2SE 1.4) • JDBC classes are contained in the Java package java.sql • A JDBC-ODBC bridge is included so that any ODBC data source available in the JVM host environment can be accessed

4. JDBC Drivers • There are 4 types of JDBC drivers • Type 1 JDBC-ODBC Bridge • Type 2 Native API Driver • Type 3 Network Protocol Driver • Type 4 Native Protocol Driver • Types 1 and 2, use platform specific code • Types 3 and 4 are all-Java

5. Type 1

6. Type 2

7. Type 3

8. Type 4

9. JDBC • Steps in JDBC applications • Load JDBC classes (java.sql.*) • Load the JDBC drivers • Class.forName(“oracle.jdbc.driver.OracleDriver”) • Connect to the database • Connection conn = DriverManager.getConnection(url,userid,passwd) • jdbc:oracle:drivertype@database • Interact with DB using JDBC • Disconnect from DB

10. JDBC Example /**********************************************/ /* A Simple JDBC Program (Section 5.2) */ /* Chapter 5; Oracle Programming -- A Primer */ /* by R. Sunderraman */ /**********************************************/ import java.sql.*; import java.io.*; class simple { public static void main (String args []) throws SQLException, IOException { try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e { System.out.println ("Could not load the driver"); } String user, pass; user = readEntry("userid : "); pass = readEntry("password: ");

11. JDBC Example Connection conn = DriverManager.getConnection( "jdbc:oracle:oci8:"+user+"/"+pass); Statement stmt = conn.createStatement (); ResultSet rset = stmt.executeQuery ("select distinct eno,ename,zip,hdate from employees"); while (rset.next ()) { System.out.println(rset.getInt(1) + " " + rset.getString(2) + " " + rset.getInt(3) + " " + rset.getDate(4)); } stmt.close(); conn.close(); }

12. JDBC Example //readEntry function -- to read input string static String readEntry(String prompt) { try { StringBuffer buffer = new StringBuffer(); System.out.print(prompt); System.out.flush(); int c = System.in.read(); while(c != '\n' && c != -1){ buffer.append((char)c); c = System.in.read(); } return buffer.toString().trim(); } catch (IOException e) { return ""; } } }

13. The Connection Object • A Connection represents a session with a particular database • All SQL statements are executed and results returned in the context of a Connection • A Java program can have multiple Connections to one or more DBs • Default behavior is autocommit • Connection object is used to send statements to the DB server

14. JDBC SQL Statement Classes • There are three classes for sending SQL to the DB server • Statement. Used for SQL statements with no parameters. • PreparedStatement. Used when the same statement, possibly with different parameters is to be called multiple times. • CallableStatement. Used for calling stored procedures.

15. Other Connection Class Methods • public abstract void close() throws SQLException • Immediately release the Connection’s database and JDBC resources • public abstract void setAutoCommit (boolean autoCommit) throws SQLException • Pass true to enable autocommit, false to disable it • public abstract void rollback() throws SQLException • Useful when autocommit is false • public abstract DatabaseMetaData getMetaData() throws SQLException • Get metadata from the DB

16. PreparedStatement Object • PreparedStatement is compiled, can be reused, may contain parameters • Example: PreparedStatement stmt = conn.prepareStatement(“insert into students values (?, ?, ?, ?)” ); … stmt.setString(1, id); stmt.setString(2, fn); stmt.setString(3, ln); stmt.setString(4, mi); stmt.executeUpdate(); /* executeQuery for Select */

17. ResultSet Class • The result of an executeQuery is a single ResultSet object • This class provides access to the table resulting from the query • A cursor points to the current row (initially positioned before the first row. • Use the next method to fetch the next row • The current row’s column values are retrieved using the getXXX methods, where XXX is a Java type. • Pass either an index or the column name to specify which column

18. ResultSet Class • Use the wasNull method to check whether the previously read column value was Null. • Use getMetaData to obtain info like number, types and properties of the columns of a ResultSet

19. Errors and Warnings • The SQLException class provides information about errors while accessing the db • Contains the error message as a String object • Retrieve it using the getMessage method • SQLState string identifies the exception according to the X/Open SQL spec • Retrieve it using getSQLState method • Link to the next exception • Retrieve it using getNextException method

20. Errors and Warnings try { some JDBC statement to access the DB; } catch (SQLException e) { System.out.println(“SQL Exception caught!”); while (e!= null) { System.out.println(“Error Message = “ + e.getMessage()); System.out.println(“SQL State = “ + e.getSQLState()); System.out.println(“Error Code = “ + e.getErrorCode()); e = e.getNextException(); } }

21. Errors and Warnings • The SQLWarning class provides info about warnings generated during DB access • Use getMessage to get the warning string • Use getSQLState to get warning according to X/Open SQL Spec • Use getErrorCode for vendor specific code • Use getNextWarning for next warning generated

22. Scrollable ResultSet • Since JDBC 2.0, ResultSet objects are now updatable and scrollable • The Statement object must be created using the following Connection class method • Statement createStatement(int resultSetType, • Int resultSetConcurrency) • Where resultSetType is one of: TYPE_FORWARD_ONLY (default – old behavior), TYPE_SCROLL_INSENSITIVE (can scroll forwards and back, don’t see changes to ResultSet object in the DB), TYPE_SCROLL_SENSITIVE (changes are visible)

23. Scrollable ResultSet • The resultSetConcurrency parameter takes one of the following values: • CONCUR_READ_ONLY (default – old behavior) data is read only • CONCUR_UPDATABLE – the Java program can make changes to the database based on the new methods and positioning of the cursor • Create the scrollable ResultSet using the usual executeQuery method of the Statement object

24. Scrollable ResultSet • Methods for cursor positioning include • absolute(int row) • relative(int row) • first() • last() • previous() • next() • beforeFirst() • afterLast()

25. More on Statements • The Statement interface provides three different methods for executing SQL statements • We have already seen executeQuery for statements that produce a result set • The method executeUpdate is used to execute INSERT, UPDATE, DELETE or DDL statements • The return value is an integer indicating the number of rows affected by an update (0 for a DDL statement)

26. More on Statements • The execute method should be used only when it is possible that a statement may return more than one ResultSet object, more than one update count, or a combination of these • Possibly through executing a stored procedure call, or through executing a SQL statement which is unknown at compile time (e.g. use JDBC to implement SQL*Plus) • Returns true if first result is a result set, false if it is an update count

27. CallableStatement • A CallableStatement object provides a way to call stored procedures in a standard way for all vendor DBs • Written using “escape syntax” (syntax the driver must translate for a particular DB) with one of two forms • Without result parameter – {call proc_name[(?,?, …)]} • With result parameter – {? = call proc_name[(?,?, …)]}

28. CallableStatement • Passing IN parameters is done using the setXXX methods inherited from PreparedStatement • pstmt.setLong(1, 12345); pstmt.setLong(2, 345); • If OUT parameters are used, the JDBC type of each OUT parameter must be registered before execution • cstmt.registerOutParameter(1, java.sql.Types.TINYINT); • Then use getXXX methods to retrieve OUT parameter values • byte x = cstmt.getByte(1);