1 / 30

CS6320 - JDBC

CS6320 - JDBC. 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

nassor
Download Presentation

CS6320 - JDBC

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related