From JavaProgramming
	* JDBC is a standard Java class library to query and modify relational data
	* JDBC is modelled after ODBC (OpenDatabaseConnectivity), but is fully Object-Oriented

The JDBC API makes it possible to do three things:
	* Establish a connection with a database or access any tabular data source
	* Send SQL statements
	* Process the results

Links
* http://java.sun.com/products/jdbc/
* http://java-source.net/open-source/database-engines

----
'''JDBC Architecture'''

''From http://java.sun.com/products/jdbc/overview.html''

The JDBC API contains two major sets of interfaces: the first is the JDBC API for application writers, and the second is the lower-level JDBC driver API for driver writers. JDBC technology drivers fit into one of four categories. Applications and applets can access databases via the JDBC API using pure Java JDBC technology-based drivers, as shown in the following figure:

http://java.sun.com/products/jdbc/images/jdbc_ds_applet.gif

Type 4 (left). Direct-to-Database Pure Java Driver: This style of driver converts JDBC calls into the network protocol used directly by DBMSs, allowing a direct call from the client machine to the DBMS server and providing a practical solution for intranet access.

Type 3 (right).	Pure Java Driver for Database Middleware: This style of driver translates JDBC calls into the middleware vendor's protocol, which is then translated to a DBMS protocol by a middleware server. The middleware provides connectivity to many different databases.

The graphic below illustrates JDBC connectivity using ODBC drivers and existing database client libraries.

http://java.sun.com/products/jdbc/images/jdbc_ds_application.gif

Type 1 (left). JDBC-ODBC Bridge plus ODBC Driver: This combination provides JDBC access via ODBC drivers. ODBC binary code - and in many cases, database client code - must be loaded on each client machine that uses a JDBC-ODBC Bridge. Sun provides a JDBC-ODBC Bridge driver, which is appropriate for experimental use and for situations in which no other driver is available.

Type 2 (right). A native-API partly Java technology-enabled driver: This type of driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.
----
'''JDBC Classes, Interfaces and Methods'''

 D''''''riverManager
  |
 Connection::getConnection()	->	D''''''atabaseMetaData::getMetaData()
  |
 Statement::createStatement()
  |
 R''''''esultSet::executeQuery()	->	R''''''esultSetMetaData::getMetaData()
  |
 Relational DBMS
'''Registering a JDBC driver'''
	* JDBC drivers must register themselves with the DriverManager
	* Drivers register themselves automatically when they are loaded
	* Use Class::forName(<package>.<driver class>) to load the JDBC driver:

 Class.forName("sun.jdbc.odbc.Jdbc''''''Odbc''''''Driver");

'''Connecting to a database'''
	* Use Driver''''''Manager to open a connection to database
	* An URL specifies JDBC driver and database:

 Connection con = Driver''''''Manager.getConnection("jdbc:odbc:databasename", "username", "password");
 try {
   ...
 } finally {
   try { con.close(); } catch (Throwable ignore) [{}
 }
'''Statements'''

Three SQL statement interfaces:
	* Statement - Execute a SQL statement
	* PreparedStatement - Execute pre-compiled statements
	* CallableStatement - Call a stored procedure

 Statement stmt = con.createStatement();
 try {
   Result''''''Set res = stmt.executeQuery("select * from sometable");
   try {   
     ...
   } finally {
     try { res.close(); } catch (Throwable ignore) [{}
   }
 } finally {
   try { stmt.close(); } catch (Throwable ignore) [{}
 }
'''Process query results'''
	* ResultSet holds a table of result data returned by an SQL query
	* Support for cursors, use Result''''''Set::next() to move to next record
	* Retrieve data using Result''''''Set::getXXX() methods

 Result''''''Set res = stmt.executeQuery("select name, salary from employee");
 try {
   while(res.next()) {
     String name = res.getString(1);
     Big''''''Decimal salary = res.getBigDecimal(2, 2);
     ...
   } 
 } finally {
   try { res.close(); } catch (Throwable ignore) [{}
 }
'''Transactions'''
	* Set Connection::setAutoCommit(false) to take transaction control
	* Otherwise a transaction for each SQL statement

 boolean autoCommitDefault = con.getAutoCommit();
 try {
     con.setAutoCommit(false);

     /* You execute statements against conn here transactionally */

     con.commit();
 } catch (Throwable e) {
     try { con.rollback(); } catch (Throwable ignore) {}
     throw e;
 } finally {
     try { con.setAutoCommit(autoCommitDefault); } catch (Throwable ignore) {}
 }
----
'''JDBC 2.0'''
	* Scrollable and updatable result sets
	* Batch updates (requires a DBMS support)
	* SQL 3 data types: BLOB, CLOB, ARRAY, structured type
	* JDBC 2.0 standard extensions:
		* DataSources - Database connections named through JNDI
		* ConnectionPooling (requires DBMS support)
		* RowSet
		* DistrubutedTransactionSupport
'''Data Sources'''

 // register a data source
 DataSource''''''Class dso = new DataSource''''''Class(); // Implements DataSource
 dso.setServerName("SOME_SERVER");
 dso.setDatabaseName("SOME_DATABASE");
 Context ic = new InitialContext();
 // Register dso as e named DataSource
 ic.bind("jdbc/SOME_DATABASE", dso);
 ...
 // Querying for a DataSource
 Context ic = new InitialContext();
 Data''''''Source ds = (DataSource) ic.lookup("jdbc/SOME_DATABASE");
 // make a connection
 Connection con = ds.getConnection("username", "pasword");
----
A useful tip... when setting a parameter in a PreparedStatement, if one sets the value to null when using JdbcOdbcBridge and SqlServer, the driver will throw an exception. Instead, use the setNull( int index, int type) method. Of course, this requires the mapping of types somewhere in the application.

Another tip... when a SQLException was thrown, most databases do not include the offending SQL in the exception message, making it quite difficult to trace the problem (especially if it is due to the values set in the PreparedStatement). It is a simple matter to write your own JDBC driver to pass all the work to a real JDBC driver, while capturing the values set in the PreparedStatement. When an exception is thrown, the append the SQL statement and all values to the exception message. This will help debugging a lot.

''Careful here; it's considered a security risk in some quarters to display the underlying SQL in error messages. Include a "testing" versus "production" option flag somewhere, or write it to an internal log instead of displaying it.''
----
CategoryJava CategoryDatabase