JDBC

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
  Programmers 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 1
Type 2
Type 3
Type 4
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
JDBC server gateway
Proprietary protocol
Proprietary protocol
DBMS
DBMS
DBMS
DBMS
- Oracle JDBC/OCI
- Oracle Thin JDBC - Sybase jConnect - Solid
5
SQL and Java data types
SQL data type INTEGER SMALLINT NUMERIC (m,
n) DECIMAL (m, n) DEC (m, n) FLOAT
(n) REAL DOUBLE CHARACTER (n) VARCHAR
(n) DATE TIME TIMESTAMP
Java data type int short java.sql.BigDecimal jav
a.sql.BigDecimal java.sql.BigDecimal
double float double String String java.sql.Date j
ava.sql.Time java.sql.Timestamp
6
Java.sql - Interfaces / Methods
DatabaseMetaData
Driver
getTables()
...
Connection
ResultSet
ResultSetMetaData
Statement
getMetaData() setAutoCommit(b) setTransaction
Isolation(level) createStatement() prepareStateme
nt(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) appen
d(s) close()
getColumnCount() getColumnName(i) getColumnLabel(i
) getColumnDisplaySize(i)
setCursorName(s) executeQuery(sql) executeUpdate(s
ql) cancel() close()
DriverManager Class
getConnection (url, user, psw)
PreparedStatement
setXxxx(n, hvar) clearParameters()
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 hhmmss
• ts ts yyyy-mm-dd hhmmss.fffff

11
Transactions
Default AutoCommit Isolation
Levels 0 TRANSACTION_NONE 1 TRANSACTION_READ_UNCO
MMITTED 2 TRANSACTION_READ_COMMITTED 3 TRANSACTION
_REAPEATABLE_READ 4 TRANSACTION_SERIALIZABLE Meth
ods con.setAutoCommit(false) level
con.getTransactionIsolation() con.setTransactionI
solation(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
• ltmove methodgt
• 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)