JDBC - Java Datbase Connectivity

This page summarizes how to make Java Datbase Connectivity work.

The Java Tutorial Database Access Trail: JDBC by Maydene Fisher
Sun's JDBC Data Access API
Dick Baldwin's Intro to JDBC [1999]

Javaworld articles

Java JDBC SQL Client (released January 27, 2000)

Topics this page:

Site Map  
About this site  

Newsgroup: comp.lang.java

javasoft.com/jdbc/:
FirstSQL for Java

Summary

JDBC 1.1 from JDK 1.0,
JDBC 1.2 from JDK 1.1.x,
JDBC 2.0 for JDK1.2/Java 2 to do batch updates and forward, backward, jumping around records with a result set.
JDBC 3.0 from JDK1.4 added SQL3 BLOB/CLOB datatypes, batch updates, JDBC extension API for JavaBeans, the JDBC Optional Package for JNDI and connection pooling.
JDBC 1.5 for Java 1.5

The JBoss commercial open-source database-access program Hibernate 3 for the JBoss Application Server 4 (July 2005) are designed to comply with the EJB 3.0 spec, a Java standard meant to make it easier to build server-side Java programs.

Typically, only the persistence layer should interact with databases. Persistence is the mechanism by which objects are saved and restored between program executions (typically to a database).

Watch for

• time to obtain connection from pool (>1 second)
• number of transaction timeouts

JavaJunkies

Caribou Lake offers products to 1) compare an ordered set of rows between two JDBC tables 2) view, search, and update a database from a web browser, 3) transfer of data from one JDBC table to another like table in a different JDBC database. 4) compute a checksum on any set of rows in a table from a JDBC data source 5) pools, reuses, and serves JDBC connections to multi-threaded Java applications

import java.jdbc.*;

try
{
	// Step 1. Load the Type 1 database driver:
	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	// Step 2. Create a connection to the database
	//         using pre-defined Data Source:
	Connection con = DriverManager.getConnection
		("jdbc:odbc:PreDefinedDB",userid,password);
	// Step 3. Create a Statement object:
	Statement stmt = con.createStatement();
	// Step 4. Build and send the database SQL query:
	String query = "SELECT * FROM DataTable";
	// Step 5. Get the resultset from the database:
	ResultSet rs = stmt.executeQuery(query);
	// Step 6. Process the data:
	// ...
}
// Microsoft Access returns this exception if the ResultSet 
// object is not closed before another query is executed:
catch (SQLException se) {
{

Database Drivers

Because databases connections are abstract, a single interface can accomodate the different protocols used by various databases. This allows developers to access data across incompatible database management systems running on varied platforms. As of 5/15/03, over 178 java.sql database drivers are listed or certified by Javasoft

• Type 1 third-party Javasoft JDBC-ODBC bridge drivers provide a gateway to third-party ODBC API software running on non-Java (typically MS Windows) clients. Although not good for large-scale apps, this is convenient for desktop debugging and for using Microsoft Access software. (Microsoft does not provide a JDBC driver for Access).

• Type 2 native API drivers use native ANSI C or C++ functions in (platform specific) client executables to communicate with Oracle, Lotus Domino, Intersolv, and other databases. API libraries such as CodeBase for Java offers high performance access to legacy dBase/Foxpro databases.

  C language programs can use Oracle's Pro*C precompiler to program embedded SQL statements to and interact with Oracle databases through its Oracle Call Interface (OCI).
  C++ programs use the Oracle C++ Interface (OCCI),
  The classic version of this API underwent a major revision with Oracle8. The Oracle8 OCI API has several hundred routines that use native methods and ODBC to go through SQL*NET or Net8.

• Type 3 Pure Java generic network API drivers use sockets to call an intermediate middleware server (using middleware protocols). Because the middleware server translates and transfers requests to a database server, no client software is required. Create your own. The driver for an Oracle database:
  "oracle.jdbc.driver.OracleDriver";
  String serverName = "127.0.0.1";
  String portNumber = "1521";
  String sid = "mydatabase";
  String url = "jdbc:oracle:thin:@" + serverName + ":" +
  portNumber + ":" + sid;

• Type 4 Pure Java socket API drivers use native protocols to talk directly to database servers over (an intranet) network.

  DataDirect Connect for JDBC is available for Oracle, DB2, Microsoft SQL Server, Sybase, and Informix. They are licensed by TIBCO and other packages.

  jTDS is the most performant and supported pure Java (type 4) JDBC 3.0 driver for Microsoft SQL Server and Sybase. It is open source based on FreeTDS.

  Once the drivers are installed and configured, applications can connect to databases with a connection URL through the JDBC Driver Manager:

  Connection conn = DriverManager.getConnection
  ("jdbc:datadirect:oracle://server_name:1521;User=test;Password=secret");
  

  Alternately, connection can be through a JNDI (Java Naming Directory Interface) data source.

  With their base.jar, util.jar, and driver.jar libraries in CLASSPATH
  driver class com.ddtek.jdbc.oracle.OracleDriver
  Data source com.ddtek.jdbcx.oracle.OracleDataSource

  With the GNU mySQL database and Mark Matthews' Connector/J (formerly Open Source MM.MySQL) driver:

  Class.forName("org.gjt.mm.mysql.Driver");
  con = DriverManager.getConnection

  ("jdbc:mysql://localhost/ImagesDB?user=user1=passwd");

  SPI (Service Provider Interfaces) such as OMG Java access protocol for object databases...

Using Oracle-JDBC classes12.jar

towson.edu

onjava.com

O'Reilly

cs.nott.ac.uk

Java Database Connectivity Computerworld article by Carol Sliwa Dec 13, 1999

Ken North's list of JDBC Drivers, Servers, and Vendors with an explanation

Microsoft Jet 4.0 Service Pack 4 (SP4) updates the Microsoft Jet 4.0 database engine. Microsoft Knowledge Base: Q282010 ACC2000: Updated Version of Microsoft Jet 4.0 Available in Download Center First Published: May 7 2001 12:24PM

Create a Data Source

On Windows machines, to pre-define an Access database, invoke the Microsoft ODBC Administrator program in C:\WINNT\System32\.
odbcad32

Some programs place this in the Control Panel as the "ODBC Data Sources (32bit)" icon. or Administrator Tools

1. Invoke the "ODBC Data Sources (32bit)" applet.

2. When you see the ODBC Data Source Administrator, select "MS Access Database" and click the "Configure..." button.

3. At the "ODBC Microsoft Access Setup" screen, change the Data Source Name from "MS Access Database" to the name you want to use.

   The Microsoft ODBC 32 bit Desktop Driver Pack is available on the Microsoft web site at: www.microsoft.com/msdownload/uda/mdac_typ.asp. See Microsoft's Data Access Technologies website .

   The Data Source Name (DSN) is referenced by application programs. Select the location of an existing .mdb file or Create a new one. To assign a login name and password for the database, click the "Advanced" button.

   Click the "File DSN" tab. Look in Data Sources. Click "Add..." to add a Data Source.

   In the "Create New Data Source" screen, click "Microsoft Access Driver (*.mdb)", which points to "ODBCJT32.DLL". Click OK and provide a name for the data file source, such as "myzones". Select the data source mdb file. When you see "myzones.dsn", click OK to exit.

   Information about each System DSN is stored under Windows Registry key:

   HKEY_LOCAL_MACHINE \SOFTWARE \ODBC \ODBC.INI

   See Charles Carroll http://www.learnasp.com/learn/dbopen.asp
   http://jlmeek.com/demo/faq.htm

1. Register Database Driver

To define the driver used to connect to a data source.
1. Use a MsqlDataSource object with JNDI (Java Naming and Directory Interface) via the JDBC Optional Package introduced with JDBC 2.0 Core API using LDAP-compliant directory service such as Microsoft Active Directory or Novell:
   import java.sql.*;
   ...
   // obtain context properties:
   Properties props = new Properties():
   props.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.fscontext.RefFSContextFactory");
   // create a context:
   Context ctx = new InitialContext(props);
   // Lookup in the namespace (or throw an exception):
   DataSource ds = (DataSource)ctx.lookup("jdbc/ora");
   // bind context object to the JNDI service:
   ctx.bind("/tmp/jdbc/jindex", ds);

   This creates a temporary hidden file named with .bindings suffix in file folder /tmp/jdbc.

2. To add an instance to the a list of classes that implements the java.sql.Driver interface kept by java.sql.DriverManager, a driver for the database engine to be used to open a connection. provided with run-time parameters invoking apps, not applets.
   java -Djdbc.drivers=com.imaginary.sql.msql.MsqlDriver Jdbc01

3. A program can be hard-coded and compiled to explicitly load JDBC drivers at any time using the forName() method
   import java.sql.*;
   ...
   Class.forName("com.imaginary.sql.msql.MsqlDriver");

   or

   Class.forName().newInstance("com.imaginary.sql.msql.MsqlDriver");

   There is also a static registerDriver() method of the DriverManager class that can be used for this purpose.

   Before using it, make sure the driver is valid:

   String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
   try {

   Class.forName(driver);

   } catch (ClassNotFoundException e) {

   e.printStackTrace("Not a valid driver");

   }

2. Load Database Driver to setup a Connection object

Obtain a Connection object named "con" (a common abbreviated local name for a connection) using the getConnection static method from class DriverManager within the java.sql.Connection package.
import java.sql.*;
. . .
String url = "jdbc:odbc:datasource1://localhost:1234/myDB";
Connection con = DriverManager.getConnection( url, user, pwd );

public static Connection getConnection(String url, String user, String password) throws SQLException

There are three parameters: the URL, the user name, and a password. The protocol section of the URL has the form jdbc:subprotocol:datasource. The "//" is not a comment indicators but part of the URL; and The port is typically 1114. There is only one "/" between the port number and database name.

If this is not done right, the compiler issues a "Driver not found" message.

When getConnection() is called, the DriverManager uses the first two terms of the URL (jdbc:msql) to locate a suitable driver from among those loaded at initialization and those loaded explicitly using the same classloader as the current applet or application.

There are several overloaded versions of getConnection().

3. Create a Statement object

Within the context of a connection created in the previous step, define a java.sql.statement object containing a SQL statement to be read by the database using the createStatement() method of the Connection class.
Statement stmt = con.createStatement();

To obtain an updatable ResultSet, specify scroll-sensitive and updatable parameters.

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
To update the 9th row in a scrollable ResultSet
resultSet.absolute(9);
resultSet.updateString("FIELDNAME", "VALUE");
resultSet.updateRow();

JDBC allows programmers to update multiple data items with a single command, or even access multiple database servers within a single transaction.

In addition, it lets programmers reuse database connections with connection pooling) so a new connection doesn't need to be made to a database for each new JDBC command.

The PrepareStatement object is the method of sending SQL to the database. A PreparedStatement object holds reusable SQL statements.

MetaData

Get column names from a query through the ResultSetMetaData associated with the object:
ResultSetMetaData rsmd = rs.getMetaData();
for( int i=0; i<rsmd.getColumnCount(); i++ ) {
System.out.println( rsmd.getColumnName(i+1) );
}

4. Prepare Database with executeUpdate Statements

The Statement class provides many methods to manipulate the data in the database by specifying SQL statements
1. The executeUpdate() method process SQL commands (such as CREATE, INSERT, UPDATE or DELETE TABLE) or call stored procedures.
   stmt.executeUpdate("CREATE TABLE myTable");
   stmt.executeUpdate("countRecs();");

   This returns the number of rows affected by the SQL command, not a ResultSet of data values.

2. The executeQuery() method is used when a ResultSet is expected back after executing a single SQL String parameter:
   rs = stmt.executeQuery( "SELECT * from myTable ORDER BY test_id");

5. Catch and Handle Errors

Java

Each SQLException object provides several kinds of information:

A string describing the error. This is used as the Java Exception message, available via the method getMessage(). The DatabaseMetaData getSQLStateType method of the SQL 99 or XOPEN SQL specification can discover whether a "SQLstate" string was returned. An integer error code that is specific to each vendor. Normally this will be the actual error code returned by the underlying database. This information is available via the getErrorCode() method. A chain to a next Exception. This can be used to provide additional error information by invoking the getNextException() method.

catch(SQLException ex) {
while (ex != null) {
System.out.println("Message: " + ex.getMessage ());
System.out.println("SQLState: " + ex.getSQLState ());
System.out.println("ErrorCode: " + ex.getErrorCode ());
ex = ex.getNextException();
System.out.println("");
}//end while loop
}//end catch block

Additional code is needed to catch warnings.

MS-Access "command text was not set for the command object"

Microsoft Access uses Jet, and Jet is thread safe starting from Jet 4.0. From MDAC 2.1 and later, you can have connection pooling for Access ODBC driver. Connections that use older Jet OLE DB providers and ODBC drivers are not pooled because those providers and drivers do not support pooling. The connection continues to remain open and unused until the connection times out. The default timeout with connection pooling enabled for SQL Server is 60 seconds.

An ODBC application can enable connection pooling by calling SQLSetEnvAttr with the SQL_ATTR_CONNECTION_POOLING attribute. 164221

Implicitly setting the ADO ActiveConnection (Conn) to a connection string defeats efficiencies from connection pooling, one the new features introduced in ODBC driver manager 3.0. Connection pooling enables an ODBC application to reuse a connection from a pool of connections. Once a connection has been created and placed in the pool, an ODBC application can reuse the same driver and the connection within the same shared environment (henv) without performing the complete connection process.

Set it to a connection object.

191572 INFO: Connection Pool Management by ADO Objects Called From ASP

6. Using ResultSet Returned from executeQuery

A java.sql.ResultSet object called "rs" provides access to a table of data. It maintains a cursor pointing to the current row of data. Columns are numbered beginning with 1 (not with 0). Initially the cursor is positioned before the first row.

The next() method moves the cursor to the next row (similar to an iterator or an enumerator in Java).

getString()
getDouble()
getInt() and other methods to retrieve the data from the resultset - to retrieve the data from the current row in the resulting data table

The absolute() method moves the cursor to a specific row in the result set.

The JDBC driver attempts to convert the underlying data to the specified Java type and returns a suitable Java value. The next fragment uses several methods of the ResultSet class in a while loop to display all of the data in the ResultSet object produced by the earlier query.

System.out.println("Display all results:");
while( rs.next() ) {
int theInt= rs.getInt("test_id");
String str = rs.getString("test_val");
if !wasNull( "test_val" ){ \\ not SQL null
\\ work with real data returned.
}
System.out.println("\ttest_id= " + theInt + "\tstr = " + str);
}//end while loop

Methods getString(), getInt() retrieve column values for the current row. You can retrieve values using either the index number of the column or the name of the column. In general, using the column index is more efficient.

The boolean wasNull() method is needed so that nulls are ignored. To avoid incompatibilities, test with it instead of the Java base class Null literal.

SQL3 data types

BLOBs and Other SQL3 Advanced Datatypes

JDBC defaults all Connection objects to auto-committing database transactions immediately after they are issued. But support of component transactions requires this to be turned off so that a JDBC-specific implementation of the Transaction class can manage the commit logic.

con.setAutoCommit( false );

Savepoints and Batch Updates

...

This fragment is followed by an exception handler (not shown) which ends the program.

7. Close the Database Connection

//clean up.
rs.close();
conn.close();
rs = null;
st = null;
conn = null;

A ResultSet is automatically closed by the Statement object that generated it when that Statement object is closed, re-executed, or used to retrieve another result.

However, the database connection should be closed to recover memory resources.

con.close();

Under the covers, the connection may actually be returned to a connection pool.

This fragment is followed by an exception handler (not shown) which ends the program.

This is if connection pooling is not being used (with ConnectionEventListener, ConnectionPoolDataSource, PooledConnection).