1 / 21

JDBC

JDBC. Java Database Connection. Notes on JDBC - Java Database Connection. Class Library: java.sql.* Literature/sources: SunSoft: http://java.sun.com/products/jdbc JDBC Specification jdk1.3/docs/guide/jdbc/ JDBC Guide: Getting Started

bedros
Download Presentation

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. JDBC Java Database Connection

  2. Notes on JDBC - Java Database Connection • Class Library: java.sql.* • Literature/sources: • SunSoft: http://java.sun.com/products/jdbc JDBC Specification • jdk1.3/docs/guide/jdbc/ JDBC Guide: Getting Started • Seth White & al:JDBCTM API Tutorial and Reference, 2nd ed • Horstmann & Cornell: Core JAVA Volume II Chapter 4 • Orfali & Harkey: Client/Server Programming with JAVA and CORBA • Siple: The Complete Guide to JAVA Database Programming, McGraw-Hill • SOLID JDBC: sj23win.zip SOLID JDBC Driver Programmer’s Guide • Melton & Eisenberg: Understanding SQL and Java Together

  3. JDBC 1.0 API • Designed by JavaSoft • based on ISO SQL/CLI and Microsoft ODBC API • provided in java.sql package • 4 types of JDBC Driver implementation

  4. Types of JDBC Implementations - Melton & Eisenberg Type 2 Type 3 Type 4 Type 1 Java appl Java appl Java appl Java appl JDBC-ODBC bridge JDBC driver JDBC driver JDBC driver ODBC driver Native db-library DBMS- independent protocol DBMS- specific protocol Proprietary protocol Proprietary protocol JDBC server gateway DBMS DBMS DBMS DBMS - Oracle Thin JDBC - Sybase jConnect - Solid - Oracle JDBC/OCI

  5. SQL and Java data types SQL data type: INT[EGER] SMALLINT NUMERIC (m, n) DECIMAL (m, n) DEC (m, n) FLOAT (n) REAL DOUBLE CHAR[ACTER] (n) VARCHAR (n) DATE TIME TIMESTAMP Java data type: int short java.sql.BigDecimal java.sql.BigDecimal java.sql.BigDecimal double float double String String java.sql.Date java.sql.Time java.sql.Timestamp

  6. Java.sql - Interfaces / Methods DataSource DatabaseMetaData getTables(…) … getConnection (url, user, psw) Connection ResultSet ResultSetMetaData Statement getMetaData() setAutoCommit(b) setTransaction Isolation(level) createStatement() prepareStatement(sql) prepareCall(sql) commit() rollback() close() getMetaData() findColumn(name) next() getInt(col) getShort(col) getNumeric(col) getDouble(col) getFloat(col) getString(col) getDate(col) getTime(col) getTimestamp(col) wasNull() setText(s) append(s) close() getColumnCount() getColumnName(i) getColumnLabel(i) getColumnDisplaySize(i) setCursorName(s) executeQuery(sql) executeUpdate(sql) cancel() close() DriverManager Class getConnection (url, user, psw) PreparedStatement … setXxxx(n, hvar) clearParameters() Driver ... CallableStatement SQLException registerOutputParameter execute() ... getSQLState() getErrorCode() getNextExcetion()

  7. SQL Query String s; float n; ... String query = "SELECT COF_NAME, PRICE FROM COFFEES"; ResultSet rs = stmt.executeQuery(query); while (rs.next()) { s = rs.getString("COF_NAME"); n = rs.getFloat("PRICE"); System.out.println(s + " " + n); } rs.close; rs.next() COF_NAME PRICE rs.getString() rs.getFloat() s n

  8. SQLQuery Sequence Diagram adapted from Orfali & Harkey Client DriverManager getConnection Connection createStatement Statement executeQuery ResultSet next getString getInt ... { [ Until next returns false ] } close close close

  9. Invoking a Stored Procedure adapted from Orfali & Harkey Client DriverManager getConnection Connection prepareCall Callable Statement registerOutputParameter parameters marked in the procedures call by ? placeholders are identified by the corresponding order numbers 1, 2, .. of the placeholders ... execute getString getInt ... close close

  10. JDBC Escape Syntax call {call proc (arg1, …) } ?=call {?= call proc (arg1, …) } d {d ‘yyyy-mm-dd’} escape {escape ‘%’} fn {fn function (arg1, …) } oj {oj outer-join } t {t ‘hh:mm:ss’} ts {ts ‘yyyy-mm-dd hh:mm:ss.fffff’}

  11. Transactions Default: AutoCommit Isolation Levels: 0 TRANSACTION_NONE 1 TRANSACTION_READ_UNCOMMITTED 2 TRANSACTION_READ_COMMITTED 3 TRANSACTION_REAPEATABLE_READ 4 TRANSACTION_SERIALIZABLE Methods: con.setAutoCommit(false); level = con.getTransactionIsolation(); con.setTransactionIsolation(level); con.commit(); con.rollback();

  12. Exception handling - adapted from Core JAVA Vol II ch 4 p 206 try { jdbc method call ... } catch (SQLException ex) { System.out.println (”\nSQLException:"); while (ex != null) { System.out.println (”SQLState: "+ex.getSQLState()); System.out.println (”Message: "+ ex.getMessage()); System.out.println (”Vendor: "+ ex.getErrorCode()); ex = ex.getNextException(); } } catch (java.lang.Exception ex) { System.out.println("Exception: " + ex); ex.printStackTrace (); }

  13. JDBC 2.0 API • JDBC 2.0 Core API (java.sql) • Scrollable ResultSet • Updating by ResultSet • Batch Updates • New SQL-99 datatypes • JDBC 2.0 Standard Extension API (javax.sql)

  14. Scrollable ResultSet • Resultset types • TYPE_FORWARD_ONLY (~JDBC 1.0) • TYPE_SCROLL_INSENSITIVE • TYPE_SCROLL_SENSITIVE • Methods • beforeFirst() (initially) • first() • next() (JDBC 1.0) • previous() • last() • afterLast() • absolute (n | -n) • relative (n | -n) • getRow() • isFirst() , isLast() , isBeforeFirst() , isAfterLast() • moveToInsertRow(), moveToCurrentRow()

  15. Updatable ResultSet • Updatable • CONCUR_READ_ONLY (~JDBC 1.0) • CONCUR_UPDATABLE • Methods • updateXXX(column, value) • … • updateRow() or cancelRowUpdates()

  16. Inserting a new row • InsertRow processing: • moveToInsertRow() • updateXXX( , ) …. • insertRow() • moveToCurrentRow() ResultSet: “Current row” moveToCurrentRow() updateable row moveToInsertRow() InsertRow() “InsertRow buffer”

  17. Deleting a Row • Positioning in the ResultSet and deleting: • <move method> • deleteRow() • Note: • drivers handle deletions differently

  18. Refreshing the row • Applies only to Cursor type: • TYPE_SCROLL_SENSITIVE • method • refreshRow()

  19. Batch Updates • Methods • addBatch(“….”) • … • executeBatch(); • BatchUpdateException

  20. SQL-1999 Datatypes • BLOB - binary large objects • CLOB - character large objects • SQL Array - of any SQL scalar datatype • SQL structured type - User Defined Type UDT • SQL REF - identifier

  21. JDBC 2.0 Standard Extension API • JDBC 2.0 Standard Extension API i.e. Optional Package API • in javax.sql • JavaBeans: Rowsets • JNDI for naming and directory interface • Connection Pooling • Distributed Transactions: 2PC by Java Transaction API (JTA)

More Related