JDBC Java Database Connection Helia Martti Laiho 1998
JDBC Java Database Connection Helia / Martti Laiho, 1998 -2000
Notes on JDBC - Java Database Connection Class Library: java. sql. * Literature/sources: • Sun. Soft: http: //java. sun. com/products/jdbc JDBC Specification • jdk 1. 3/docs/guide/jdbc/ JDBC Guide: Getting Started • Seth White & al: JDBCTM API Tutorial and Reference, 2 nd 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, Mc. Graw-Hill • SOLID JDBC: sj 23 win. zip SOLID JDBC Driver Programmer’s Guide • Melton & Eisenberg: Understanding SQL and Java Together Helia / Martti Laiho, 1998 -2000
JDBC 1. 0 API • • Designed by Java. Soft based on ISO SQL/CLI and Microsoft ODBC API provided in java. sql package 4 types of JDBC Driver implementation Helia / Martti Laiho, 1998 -2000
Types of JDBC Implementations - Melton & Eisenberg Type 1 Type 2 Type 3 Java appl JDBC-ODBC bridge JDBC driver ODBC driver Native db-library Proprietary protocol DBMS - Oracle JDBC/OCI DBMS- independent protocol Type 4 DBMS- specific protocol JDBC server gateway DBMS - Oracle Thin JDBC - Sybase j. Connect - Solid Helia / Martti Laiho, 1998 -2000
SQL and Java data types SQL data type: Java data type: INT[EGER] SMALLINT NUMERIC (m, n) DECIMAL (m, n) DEC (m, n) FLOAT (n) REAL DOUBLE CHAR[ACTER] (n) VARCHAR (n) DATE TIMESTAMP int short java. sql. Big. Decimal double float double String java. sql. Date java. sql. Timestamp Helia / Martti Laiho, 1998 -2000
Java. sql - Interfaces / Methods Database. Meta. Data. Source get. Tables(…) … get. Connection (url, user, psw) Driver. Manager Class get. Connection (url, user, psw) Driver Connection Statement get. Meta. Data() set. Auto. Commit(b) set. Transaction Isolation(level) create. Statement() prepare. Statement(sql) prepare. Call(sql) commit() rollback() close() set. Cursor. Name(s) execute. Query(sql) execute. Update(sql) cancel() close() . . . Prepared. Statement … set. Xxxx(n, hvar) clear. Parameters() SQLException Callable. Statement get. SQLState() get. Error. Code() get. Next. Excetion() register. Output. Parameter execute(). . . Result. Set get. Meta. Data() find. Column(name) next() get. Int(col) get. Short(col) get. Numeric(col) get. Double(col) get. Float(col) get. String(col) get. Date(col) get. Timestamp(col) was. Null() set. Text(s) append(s) close() Result. Set. Meta. Data get. Column. Count() get. Column. Name(i) get. Column. Label(i) get. Column. Display. Size(i) Helia / Martti Laiho, 1998 -2000
SQL Query String s; float n; . . . String query = "SELECT COF_NAME, PRICE FROM COFFEES"; Result. Set rs = stmt. execute. Query(query); while (rs. next()) { s = rs. get. String("COF_NAME"); rs. next() COF_NAME PRICE n = rs. get. Float("PRICE"); System. out. println(s + " " + n); } rs. get. String() rs. close; s n rs. get. Float() Helia / Martti Laiho, 1998 -2000
SQLQuery Sequence Diagram Client adapted from Orfali & Harkey Driver. Manager get. Connection create. Statement execute. Query Result. Set next get. String get. Int. . . { [ Until next returns false ] } close Helia / Martti Laiho, 1998 -2000
Invoking a Stored Procedure Client adapted from Orfali & Harkey Driver. Manager get. Connection prepare. Callable Statement register. Output. Parameter. . . execute parameters marked in the procedures call by ? placeholders are identified by the corresponding order numbers 1, 2, . . of the placeholders get. String get. Int. . . close Helia / Martti Laiho, 1998 -2000
JDBC Escape Syntax call ? =call d escape fn oj t ts {call proc (arg 1, …) } {? = call proc (arg 1, …) } {d ‘yyyy-mm-dd’} {escape ‘%’} {fn function (arg 1, …) } {oj outer-join } {t ‘hh: mm: ss’} {ts ‘yyyy-mm-dd hh: mm: ss. fffff’} Helia / Martti Laiho, 1998 -2000
Transactions Default: Auto. Commit Isolation Levels: 0 TRANSACTION_NONE 1 TRANSACTION_READ_UNCOMMITTED 2 TRANSACTION_READ_COMMITTED 3 TRANSACTION_REAPEATABLE_READ 4 TRANSACTION_SERIALIZABLE Methods: con. set. Auto. Commit(false); level = con. get. Transaction. Isolation(); con. set. Transaction. Isolation(level); con. commit(); con. rollback(); Helia / Martti Laiho, 1998 -2000
Exception handling - adapted from Core JAVA Vol II ch 4 p 206 try { jdbc method call. . . } catch (SQLException ex) { System. out. println (”n. SQLException: "); while (ex != null) { System. out. println (”SQLState: "+ex. get. SQLState()); System. out. println (”Message: "+ ex. get. Message()); System. out. println (”Vendor: "+ ex. get. Error. Code()); ex = ex. get. Next. Exception(); } } catch (java. lang. Exception ex) { System. out. println("Exception: " + ex); ex. print. Stack. Trace (); } Helia / Martti Laiho, 1998 -2000
JDBC 2. 0 API • JDBC 2. 0 Core API (java. sql) – – Scrollable Result. Set Updating by Result. Set Batch Updates New SQL-99 datatypes • JDBC 2. 0 Standard Extension API (javax. sql) Helia / Martti Laiho, 1998 -2000
Scrollable Result. Set • Resultset types – TYPE_FORWARD_ONLY – TYPE_SCROLL_INSENSITIVE – TYPE_SCROLL_SENSITIVE (~JDBC 1. 0) • Methods – – – before. First() (initially) first() next() (JDBC 1. 0) previous() last() after. Last() absolute (n | -n) relative (n | -n) get. Row() is. First() , is. Last() , is. Before. First() , is. After. Last() move. To. Insert. Row(), move. To. Current. Row() Helia / Martti Laiho, 1998 -2000
Updatable Result. Set • Updatable – CONCUR_READ_ONLY (~JDBC 1. 0) – CONCUR_UPDATABLE • Methods – update. XXX(column, value) – … – update. Row() or cancel. Row. Updates() Helia / Martti Laiho, 1998 -2000
Inserting a new row • Insert. Row processing: – – Result. Set: move. To. Insert. Row() update. XXX( , ) …. insert. Row() move. To. Current. Row() “Current row” move. To. Current. Row() updateable row move. To. Insert. Row() “Insert. Row buffer” Helia / Martti Laiho, 1998 -2000
Deleting a Row • Positioning in the Result. Set and deleting: – <move method> – delete. Row() • Note: – drivers handle deletions differently Helia / Martti Laiho, 1998 -2000
Refreshing the row • Applies only to Cursor type: – TYPE_SCROLL_SENSITIVE • method – refresh. Row() Helia / Martti Laiho, 1998 -2000
Batch Updates • Methods – add. Batch(“…. ”) – … – execute. Batch(); • Batch. Update. Exception Helia / Martti Laiho, 1998 -2000
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 Helia / Martti Laiho, 1998 -2000
JDBC 2. 0 Standard Extension API • JDBC 2. 0 Standard Extension API i. e. Optional Package API – in javax. sql – – Java. Beans: Rowsets JNDI for naming and directory interface Connection Pooling Distributed Transactions: 2 PC by Java Transaction API (JTA) Helia / Martti Laiho, 1998 -2000
- Slides: 21