Basic Steps in Using JDBC

Topics

What is JDBC?

JDBC is a piece of software that knows how to talk to the actual database server. JDBC is a registered trade mark.

Seven Steps

There are seven standard steps in querying databases:
  1. Load the JDBC driver.
  2. Define the connection URL.
  3. Establish the connection.
  4. Create a statement object.
  5. Execute a query or update.
  6. Process the results.
  7. Close the connection.

Step 1: Load the JDBC driver

To load the driver, you need to load the appropriate class, make a driver instance and register it with the JDBC driver manager. Use Class.forName(String) method. This method takes a string representing a fully qualified class name and loads the corresponding class. Here is an example: 

 
try {
    Class.forName("connect.microsoft.MicrosoftDriver");
    //Class.forName("oracle.jdbc.driver.OracleDriver"); for Oracle driver
    //Class.forName("com.sybase.jdbc.SybDriver"); for sybase driver
} catch(ClassNotFoundException e) {
    System.err.println("Error loading driver: " + e);
}

The string taken in the Class.forName() method is the fully qualified class name. You should check vendor's documentation to find it out. Most database vendors supply free JDBC drivers for their databases, but there are many third-party vendors of drivers for older databases. For an up-to-date list, see SUN's drivers page.

You should pay attention to the CLASSPATH setting. If the JDBC driver vendors distribute their drivers in a JAR file, be sure to include the JAR file in your CLASSPATH setting.

Return to top

Step 2: Define the Connection URL

Once you have loaded the JDBC driver, you need to specify the location of the database server. Generally, the url format is: jdbc:vendordb:userinfo plus server host, port number and database name. You should check vendors documentation for exact information about such format. The following list two examples: 

 
  //for one of Oracle drivers
  String host = "dbhost.yourcompany.com";
  String dbName = "someName";
  int port = 1234;
  String oracleURL = "jdbc:oracle:thin:@" + host +
  ":" + port + ":" + dbName;


 
  //for sybase driver
  String host = "dbhost.yourcompany.com";
  String dbName = "someName";
  int port = 1234;  
  String sybaseURL = "jdbc:sybase:Tds:" + host +
  ":" + port + ":" + "?SERVICENAME=" + dbName;

Return to top

Step 3: Establish the connection

To make the actual network connection, pass the URL, the database username, and the password to the getConnection method of the DriverManager class, as illustrated in the following example. 

 
 String username = "jay_debesee";
 String password = "secret";
 Connection connection =
        DriverManager.getConnection(oracleURL, username, password);

An optional part of this step is to look up information about the database by using the getMetaData method of Connection. This method returns a DatabaseMetaData object which has methods to let you discover the name and version of the database itself

or of the JDBC driver Here is an example: 

 
DatabaseMetaData dbMetaData = connection.getMetaData();
String productName =
       dbMetaData.getDatabaseProductName();
System.out.println("Database: " + productName);
String productVersion =
        dbMetaData.getDatabaseProductVersion();
System.out.println("Version: " + productVersion);

Other useful methods in the Connection class include

Return to top

Step 4: Create a Statement

A Statement object is used to send queries and commands to the database and is created from the Connection as follows: 

 
Statement statement = connection.createStatement();

Return to top

Step 5: Execute a Query or update

Once you have a Statement object, you can use it to send SQL queries by using the executeQuery method, which returns an object of type ResultSet. Here is an example: 

 
String query = "SELECT col1, col2, col3 FROM sometable";
ResultSet resultSet = statement.executeQuery(query);

To modify the database, use executeUpdate instead of executeQuery, and supply a string that uses UPDATE, INSERT, or DELETE. Other useful methods in the Statement class include execute (execute an arbitrary command) and setQueryTimeout (set a maximum delay to wait for results). You can also create parameterized queries where values are supplied to a precompiled fixed-format query.

Return to top

Step 6: Process the Results

The simplest way to handle the results is to process them one row at a time, using the ResultSet’s next method to move through the table a row at a time. Within a row, ResultSet provides various getXxx methods that take a column index or column name as an argument and return the result as a variety of different Java types. For instance, use getInt if the value should be an integer, getString for a String, and so on for most other data types. If you just want to display the results, you can use getString regardless of the actual column type. However, if you use the version that takes a column index, note that columns are indexed starting at 1 (following the SQL convention), not at 0 as with arrays, vectors, and most other data structures in the Java programming language.

Note that the first column in a ResultSet row has index 1, not 0. Here is an example that prints the values of the first three columns in all rows of a ResultSet. 

 
while(resultSet.next()) {
       System.out.println(results.getString(1) + " " +
       results.getString(2) + " " +
       results.getString(3));
}

Return to top

Step 7: Close the Connection

To close the connection explicitly, you should do: 

 
connection.close();

You should postpone this step if you expect to perform additional database operations, since the overhead of opening a connection is usually large. In fact, reusing existing connections is such an important optimization.

Return to top

Example

Let's put all things together.

Here we learn how to connect to Microsoft Access database. In order to make the following code workable, we have to set up environment first. Following the steps below: 

 
Open Windows' ODBC Data Source Administrator as follows: 

In Windows 95, 98, or NT, 

choose Start > 
       Settings > 
       Control Panel, then double-click 
       the ODBC Data Sources icon. 
       
Depending on your system, the icon could also be called ODBC or 32bit ODBC. 

In Windows 2000, 

choose Start > 
       Settings > 
       Control Panel > 
       Administrative Tools > 
       Data Sources. 
-----------------------------------    
In the ODBC Data Source Administrator dialog box, 

    click the System DSN tab. 
    click Add to add a new DSN to the list. 
    
Scroll down and select the Microsoft Access (.MDB) driver 
    
    click Finish button
    
Type in the name "judydriver" (no quotes -- any name you want to use) 
for the Data Source Name 

    click Select Button to select a database name (or create a new one)
on the driver "A:\mdbTest.mdb" for this exercise purpose.

    click three OK buttons out. Run the program
    
The data source name you entered should be the name of dsn in the 
above code.

The following example shows you how to follow the above steps to load a driver, make a connection, use Statement and PreparedStatement, and insert and query data. 

 
import java.sql.*;

public class TestDBDriver {
    static Connection con;
    static Statement stmt;
    static ResultSet rs;
    public static void main(String[] args) {

        //step 1: load driver
        loadDriver();

        //step 3: establish connection
        makeConnection();

        //create a table
        createTable();

        //insert data
        insertData();

        //use precompiled statement to update data
        usePreparedStatement();

        //retrieve data
        retrieveData();

        //close all resources
        closeAll();
    }

    // load a driver
    static void loadDriver() {
        try {
            //step 2: Define connection URL
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        } catch(java.lang.ClassNotFoundException e) {
            System.err.print("ClassNotFoundException: ");
            System.err.println(e.getMessage());
        }
    }

    // make a connection  step 3: establish connection
    static void makeConnection() {
       //for how to set up data source name see below.
       String dsn = "judydriver";
       String url = "jdbc:odbc:" + dsn;
        try {
           con = DriverManager.getConnection(url, "", "");
        }catch(SQLException ex) {
           System.err.println("database connection: " + ex.getMessage());
        }
    }

    //create a table
    static void createTable() {
        String createString = "create table COFFEES " +
                          "(COF_NAME VARCHAR(32), " +
                          "SUP_ID INTEGER, " +
                          "PRICE FLOAT, " +
                          "SALES INTEGER, " +
                          "TOTAL INTEGER)";



        try {
            //step 4: create a statement
            stmt = con.createStatement();
            //step 5: execute a query or update.
            stmt.execute("drop table COFFEES");//if exists, drop it, get new one
            stmt.executeUpdate(createString);

        }catch(SQLException ex) {
            System.err.println("CreateTable: " + ex.getMessage());
        }
     }

     //insert data to table COFFEES
     static void insertData() {
        try {
            stmt.executeUpdate("INSERT INTO COFFEES " +  "VALUES ('Colombian', 101, 7.99, 0, 0)");
            stmt.executeUpdate("INSERT INTO COFFEES " +  "VALUES ('French_Roast', 49, 8.99, 0, 0)");
            stmt.executeUpdate("INSERT INTO COFFEES " +  "VALUES ('Espresso', 35, 5.99, 0, 0)");
            stmt.executeUpdate("INSERT INTO COFFEES " +  "VALUES ('Colombian_Decaf', 101, 4.99, 0, 0)");
            stmt.executeUpdate("INSERT INTO COFFEES " +  "VALUES ('French_Roast_Decaf', 49,6.99, 0, 0)");

        }catch(SQLException ex) {
            System.err.println("InsertData: " + ex.getMessage());
        }
     }

    //use PreparedStatement to precompile sql statement
    static void usePreparedStatement() {
        try {
            PreparedStatement updateSales;
            String updateString = "update COFFEES " +
                      "set SALES = ? where COF_NAME like ?";
            updateSales = con.prepareStatement(updateString);
            int [] salesForWeek = {175, 150, 60, 155, 90};
            String [] coffees = {"Colombian", "French_Roast", "Espresso",
                     "Colombian_Decaf", "French_Roast_Decaf"};
            int len = coffees.length;
            for(int i = 0; i < len; i++) {
                updateSales.setInt(1, salesForWeek[i]);
                updateSales.setString(2, coffees[i]);
                updateSales.executeUpdate();
            }
        }catch(SQLException ex) {
            System.err.println("UsePreparedStatement: " + ex.getMessage());
       }
    }

    //retrieve data from table COFFEES
    static void retrieveData() {
       try {
           String gdta="SELECT COF_NAME, PRICE FROM COFFEES WHERE PRICE < 9.00";
           //step 6: process the results.
           rs = stmt.executeQuery(gdta);
           while (rs.next()) {
                String s = rs.getString("COF_NAME");
                float n = rs.getFloat("PRICE");
                System.out.println(s + "   " + n);
           }
       }catch(SQLException ex) {
           System.err.println("RetrieveData: " + ex.getMessage());
       }
     }

    //close statement and connection
    //step 7: close connection, etc. 
     static void closeAll() {
         try {
            stmt.close();
            con.close();
         } catch(SQLException ex) {
            System.err.println("closeAll: " + ex.getMessage());
         }
    }
}

Return to top

How to use AS/400 JDBC driver

Follow the seven steps.

Load the JDBC driver

The driver name may be different in the real case. Check the specification first. 

DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());

Define the connection URL 

 
String systemName  = ;
String databaseName = ;
String url = "jdbc:as400://+ systemName + "/" + databaseName";

Establish the connection 

 
Connection connection   = null;
connection = DriverManager.getConnection (url);

Create a statement object 

 
Statement dropTable = connection.createStatement ();
Statement createTable = connection.createStatement ();
PreparedStatement insert = connection.prepareStatement ("INSERT INTO "
                + tableName + " (I, WORD, SQUARE, SQUAREROOT) "
                + " VALUES (?, ?, ?, ?)");
insert.setInt (1, anyNum); 
insert.setString (2, "example"); 
insert.setInt (3, anyNum); 
insert.setDouble (4, 1.11); 
insert.executeUpdate ();

Execute a query or update 

 
dropTable.executeUpdate ("DROP TABLE " + tableName);
createTable.executeUpdate ("CREATE TABLE " + tableName
                + " (I INTEGER, WORD VARCHAR(20), SQUARE INTEGER, "
                + " SQUAREROOT DOUBLE)");

Process the results 

 
Statement select = connection.createStatement ();
ResultSet rs = select.executeQuery ("SELECT * FROM "
                + databaseName + ","  + tableName);
while (rs.next ()) {
  
    int num = rs.getInt(1);
    String value = rs.getString (1);
    if (rs.wasNull ())
       value = "";
    //...
      
}

Close the connection 

 
try {
    if (connection != null)
       connection.close (); 
}
catch (SQLException e) {
      // ...
}

Here is a code example for accessing metadata of a table. 

 
Connection connection   = null;
	
try { 
   DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver()); 

   // Get a connection to the database. password and id may be required 
   connection = DriverManager.getConnection ("jdbc:as400://" + databaseName);
   DatabaseMetaData dmd = connection.getMetaData (); 

   // Execute the query.
    Statement select = connection.createStatement ();
    ResultSet rs = select.executeQuery ("SELECT * FROM "
                + databaseName + dmd.getCatalogSeparator() + tableName);

    // Get information about the result set.  Set the column
    // width to whichever is longer: the length of the label
   // or the length of the data.
    ResultSetMetaData rsmd = rs.getMetaData ();
    int columnCount = rsmd.getColumnCount (); 
    String[] columnLabels = new String[columnCount];
    int[] columnWidths = new int[columnCount];
    for (int i = 1; i <= columnCount; ++i) { 
          columnLabels[i-1] = rsmd.getColumnLabel (i);
          columnWidths[i-1] = Math.max (columnLabels[i-1].length(),
          rsmd.getColumnDisplaySize (i));5 
     }

     // Output the column headings.
     for (int i = 1; i <= columnCount; ++i) {
           System.out.print (format (rsmd.getColumnLabel(i), columnWidths[i-1]));
           System.out.print (" ");
     }
     System.out.println ();

     // Output a dashed line.
     StringBuffer dashedLine;
     for (int i = 1; i <= columnCount; ++i) {
         for (int j = 1; j <= columnWidths[i-1]; ++j)
            System.out.print ("-");
         System.out.print (" ");
     }
      System.out.println ();

      // Iterate throught the rows in the result set and output
      // the columns for each row. 
      while (rs.next ()) {
           for (int i = 1; i <= columnCount; ++i) {
               String value = rs.getString (i);
                    if (rs.wasNull ())
                        value = "";
                    System.out.print (format (value, columnWidths[i-1]));
                    System.out.print (" ");
            }
                System.out.println ();
        }

}
catch (Exception e) {
     System.out.println ();
     System.out.println ("ERROR: " + e.getMessage());
}
finally {
   try {
      if (connection != null)
          connection.close ();
      }
      catch (SQLException e) {
        //...
      }
   }
   System.exit(0);
}

Return to top

Any questions about this tutorial, please feel free to contact us