How I may help
LinkedIn Profile Email me!
Call me using Skype client on your machine

Reload this page JDBC - Java Datbase Connectivity

This page summarizes how to make Java Datbase Connectivity work.

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

A website external to this site Javaworld articles

A website external to this site Java JDBC SQL Client (released January 27, 2000)


Topics this page:

  • Summary
  • Steps to using a database:
    1. Register Database Drivers
    2. Load Database Driver to setup a JDBC Connection object
    3. Create a Statement object
    4. Prepare Database with executeUpdate
    5. Catch and Handle Any Errors
    6. Get Columns From the Result Set Returned
  • SQL3
  • Savepoints & Batch Updates
    7. Close the Database Connection
  • Resources
  • Your comments???


    Site Map List all pages on this site 
    About this site About this site 
    Go to first topic Go to Bottom of this page

    Newsgroup: website external to this site:
    tool FirstSQL for Java

    Set screen 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

      A website external to this site JavaJunkies

      tool 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

    Go to Top of this page.
    Previous topic this page
    Next topic this page
    Set screen

      import java.jdbc.*;

      	// Step 1. Load the Type 1 database driver:
      	// Step 2. Create a connection to the database
      	//         using pre-defined Data Source:
      	Connection con = DriverManager.getConnection
      	// 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) {

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen 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:

        String serverName = "";
        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 JDBC Driver 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

        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
        Data source

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

        con = DriverManager.getConnection

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


    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Create a Data Source

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


      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: 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:


      See Charles Carroll

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen 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.*;



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

        Idea Before using it, make sure the driver is valid:

        String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
        try {
        } catch (ClassNotFoundException e) {
          e.printStackTrace("Not a valid driver");

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen 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().

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen 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.updateString("FIELDNAME", "VALUE");

      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.


      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) );

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen 4. Prepare Database with executeUpdate Statements

      The Statement class provides many methods to manipulate the data in the database by specifying SQL statementsanother page on this site

      1. The executeUpdate() method process SQL commands (such as CREATE, INSERT, UPDATE or DELETE TABLE) or call stored procedures.

        stmt.executeUpdate("CREATE TABLE myTable");

        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");

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen 5. Catch and Handle Errors


      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();
        }//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

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen 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. Reminder 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).

      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( ) {
        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.

    Go to Top of this page.
    Previous topic this page
    Next topic this page

      Set screen SQL3 data types

      BLOBs and Other SQL3 Advanced Datatypesanother page on this site

      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 );

    Go to Top of this page.
    Previous topic this page
    Next topic this page

      Set screen Savepoints and Batch Updates


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

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen 7. Close the Database Connection

      //clean up.
      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.


      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).

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Portions ©Copyright 1996-2010 Wilson Mar. All rights reserved. | Privacy Policy |

    How I may help

    Send a message with your email client program

    Your rating of this page:
    Low High

    Your first name:

    Your family name:

    Your location (city, country):

    Your Email address: 

      Top of PageGo to top of page

    Thank you!