CS6320 - JDBC

1. CS6320 - JDBC

2. Introducing JDBC • JDBC: is an API that provides “universal data access for the Java2 platform”Allows you to connect to a known data source using common OO semantics using Java • Allows you to issue standard SQL commands on that data source • Provides you with classes to facilitate access to and manipulation of: • returned data and, • generated exceptions

3. JDBC Driver Types • Type 1 (JDBC-ODBC Bridge Technology) • Type 2 (JNI drivers for native connection libraries) • Type 3 (Socket-level Middleware Translator) • Type 4 (Pure Java-DBMS driver)

4. Driver Types Type 2 Type 1 Type 3 Type 4

5. Type 1 Drivers: JDBC-ODBC Bridges • JDBC driver translates call into ODBC and redirects ODBC call to an ODBC driver on the DBMS • ODBC binary code must exist on every client • Translation layer compromises execution speed to small degree

6. Type 2 Drivers: Native-API + Java Driver • Java driver makes JNI calls on the client API (usually written in C or C++) • eg: Sybase dblib or ctlib • eg: Oracle Call Interface libs (OCI) • Requires client-side code to be installed • Often the fastest solution available • Native drivers are usually delivered by DBMS vendor • Bug in driver can crash JVMs

7. Type 3 Drivers:JDBC-Middleware Pure Java Driver • JDBC driver translates JDBC calls into a DBMS-independent protocol • Then, communicates over a socket with a middleware server that translates Java code into native API DBMS calls • No client code need be installed • Single driver provides access to multiple DBMSs, eg. WebLogic, Tengah drivers • Type 3 drivers auto-download for applets. • Communication is indirect via a middleware server

8. Type 4 Drivers:Pure Java Drivers • Java drivers talk directly to the DBMS using Java sockets • No Middleware layer needed, access is direct. • Simplest solution available. • No client code need be installed. • Type 4 drivers auto-download for applets

9. JDBC Drivers • JDBC drivers exist for every major database including: Oracle, SQL Server, Sybase, and MySQL.

10. Six Steps to Using JDBC (simple program…no connection pooling/middleware) • Load the JDBC Driver • Establish the Database Connection • Create a Statement Object • Execute a Query • Process the Results • Close the Connection

11. A standard simple JDBC application // Load the JDBC driver Class.forName("oracle.jdbc.OracleDriver").newInstance(); // Connect to the database Connection conn = DriverManager.getConnection (connect-string,user, pass); // Create a statement Statement stmt = conn.createStatement (); // Execute the statement: select data from the emp table boolean results = stmt.execute("select * from emp"); ResultSet rset = null; if (results) rset = stmt.getResultSet(); // Process results: walk through the result set while (rset.next ()) { System.out.println (rset.getString (1) + rset.getString(2)); …. }

12. Overview: Connecting to a Database // Load the Oracle JDBC driver Class.forName("oracle.jdbc.OracleDriver").newInstance(); // Connect to the database Connection conn = DriverManager.getConnection (connect-string,user, pass); // Create a statement Statement stmt = conn.createStatement (); // Select data from the emp table boolean results = stmt.execute("select * from emp"); ResultSet rset = null; if (results) rset = stmt.getResultSet(); // Walk through the result set while (rset.next ()) { System.out.println (rset.getString (1) + rset.getString(2)); …. }

13. The JDBC Driver Manager • Management layer of JDBC, interfaces between the client and the driver. • Keeps a list of available drivers • Manages driver login time limits and printing of log and tracing messages • Secure because manager will only allow drivers that come from local file system or the same initial class loader requesting a connection • Most popular use: • Connection getConnection(url, id, passwd);

14. Create a Connection to the database • Call the getConnection method on the DriverManager. Connection conn = DriverManager.getConnection(url, login, password) • URLs: • “jdbc:sybase:Tds:skunk:4100/myDB” • "jdbc:oracle:thin:@limani.cs.uchicago.edu:1521:cs51024"; • Only one requirement: the relevant Drivers must be able to recognize their own URL

15. Overview: Statements // Load the Oracle JDBC driver Class.forName("oracle.jdbc.OracleDriver").newInstance(); // Connect to the database Connection conn = DriverManager.getConnection (connect-string,user, pass); // Create a statement Statement stmt = conn.createStatement (); // Select data from the emp table boolean results = stmt.execute("select * from emp"); ResultSet rset = null; if (results) rset = stmt.getResultSet(); // Walk through the result set while (rset.next ()) { System.out.println (rset.getString (1) + rset.getString(2)); …. }

16. SQL Statements Types of statements: • Class Statement • Represents a basic SQL statement • Statement stmt = conn.createStatement(); • Class PreparedStatement • A precompiled SQL statement, which can offer improved performance, especially for large/complex SQL statements • Class CallableStatement • Allows JDBC programs access to stored procedures Can be used for both DDL and DML commands

17. Execute an SQL Statement • executeQuery(): execute a query and get a ResultSet back • executeUpdate(): execute an update and get back an int specifying number of rows acted on • UPDATE [table] set [column_name] = value where […] • DELETE from [table] where [column_name] = 5 • execute(): exec. unknown SQL, returns true if a resultSet is available: Statement genericStmt = conn.createStatement(); if( genericStmt.execute(SQLString)) { ResultSet rs = genericStmt.getResultSet(); process(); } else { int updated = genericStmt.getUpdateCount(); processCount(); } …

18. Prepared Statements • Use for complex queries or repeated queries • Features: • precompiled at database (statement usually sent to database immediately on creation for compilation) • supply with new variables each time you call it • Example: • PreparedStatement ps = conn.prepareStatement(“update table set sales = ? Where custName = ?”); • Set with values (use setXXX() methods on PreparedStatement: • ps.setInt(1, 400000); • ps.setString(2, “United Airlines”); • Then execute: • int count = ps.executeUpdate();

19. Overview: ResultSets and Cursors // Load the Oracle JDBC driver Class.forName("oracle.jdbc.OracleDriver").newInstance(); // Connect to the database Connection conn = DriverManager.getConnection (connect-string,user, pass); // Create a statement Statement stmt = conn.createStatement (); // Select data from the emp table boolean results = stmt.execute("select * from emp"); ResultSet rset = null; if (results) rset = stmt.getResultSet(); // Walk through the result set while (rset.next ()) { System.out.println (rset.getString (1) + rset.getString(2)); …. }

20. Result Sets and Cursors • Result Sets are returned from queries. • Possible number of rows: zero, one, or more • Cursors are ‘iterators’ that can be user to ‘walk’ through a result set • JDBC 2.0 allows for backward as well as forward cursors, including the ability to go to a specific row or a relative row

21. Result Sets • Iterate over all rows: • ResultSet rs = stmt.executeQuery(“select id, price from inventory”); • rs.next(), rs.previous(), rs.first(), … • call once to access first row: while(rs.next()) {} • Extract data from the ResultSet • getXXX(columnName/indexVal) • getInt() • getDouble() • getString() (highly versatile, inclusive of others; automatic conversion to String for most types) • getObject() (returns a generic Java Object) • rs.wasNull() - returns true if last get was Null

22. Example :Inserting Data via JDBC

23. import java.sql.*; public class InsertCoffees { public static void main(String args[]) throws SQLException { System.out.println ("Adding Coffee Data"); ResultSet rs = null; PreparedStatement ps = null; String url = "jdbc:mysql://localhost/cerami"; Connection con; Statement stmt; try { Class.forName("org.gjt.mm.mysql.Driver"); } catch(java.lang.ClassNotFoundException e) { System.err.print("ClassNotFoundException: "); System.err.println(e.getMessage()); } 1

24. try { con = DriverManager.getConnection(url); stmt = con.createStatement(); stmt.executeUpdate ("INSERT INTO COFFEES " + "VALUES('Amaretto', 49, 9.99, 0, 0)"); stmt.executeUpdate ("INSERT INTO COFFEES " + "VALUES('Hazelnut', 49, 9.99, 0, 0)"); stmt.executeUpdate ("INSERT INTO COFFEES " + "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)"); stmt.executeUpdate ("INSERT INTO COFFEES " + "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)"); stmt.close(); con.close(); System.out.println ("Done"); } catch(SQLException ex) { System.err.println("-----SQLException-----"); System.err.println("SQLState: " + ex.getSQLState()); System.err.println("Message: " + ex.getMessage()); System.err.println("Vendor: " + ex.getErrorCode()); } } } 2 3 4 6

25. Example :Querying Data via JDBC

26. import java.sql.*; public class SelectCoffees { public static void main(String args[]) throws SQLException { ResultSet rs = null; PreparedStatement ps = null; String url = "jdbc:mysql://localhost/cerami"; Connection con; Statement stmt; try { Class.forName("org.gjt.mm.mysql.Driver"); } catch(java.lang.ClassNotFoundException e) { System.err.print("ClassNotFoundException: "); System.err.println(e.getMessage()); } try { con = DriverManager.getConnection(url); stmt = con.createStatement(); 1 2 3

27. 4 ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES"); System.out.println("Table COFFEES:"); while (uprs.next()) { String name = uprs.getString("COF_NAME"); int id = uprs.getInt("SUP_ID"); float price = uprs.getFloat("PRICE"); int sales = uprs.getInt("SALES"); int total = uprs.getInt("TOTAL"); System.out.print(name + " " + id + " " + price); System.out.println(" " + sales + " " + total); } uprs.close(); stmt.close(); con.close(); } catch(SQLException ex) { System.err.println("-----SQLException-----"); System.err.println("SQLState: " + ex.getSQLState()); System.err.println("Message: " + ex.getMessage()); System.err.println("Vendor: " + ex.getErrorCode()); } } } 5 6

28. Exception Handling • SQL Exceptions • Nearly every JDBC method can throw a SQLException in response to a data access error • If more than one error occurs, they are chained together • SQL exceptions contain: • Description of the error, getMessage • The SQLState (Open Group SQL specification) identifying the exception, getSQLState • A vendor-specific integer, error code, getErrorCode • A chain to the next SQLException, getNextException

29. SQL Exception Example try { ... // JDBC statement. } catch (SQLException sqle) { while (sqle != null) { System.out.println("Message: " + sqle.getMessage()); System.out.println("SQLState: " + sqle.getSQLState()); System.out.println("Vendor Error: " + sqle.getErrorCode()); sqle.printStrackTrace(System.out); sqle = sqle.getNextException(); } }

30. Using the JDBC MetaData Interface • ResultSet: ResultSetMetaData m = rs.getMetaData() • ResultSetMetaData provides information about the types and properties of the DDL properties of a ResultSet object • ResultSetMetaData provides various methods for finding out information about the structure of a ResultSet: • getColumnClassName(int col): gets fully-qualified Java class name to which a column value will be mapped; eg. Java.lang.Integer, etc. • getColumnCount(): gets the number of columns in the ResultSet • getColumnName(int col): gets the name of column • int getColumnType(int col): gets the JDBC type (java.sql.Types) for the value stored in col; eg. Value 12 = JDBC VARCHAR, etc. • getPrecision(int col): for numbers, gets the mantissa length, for others, gets the number of bytes for column