Java Tips - Changing Data Using a ResultSet

If you have JDBC 2 and a conforming driver, you can request an updatable ResultSet when you create the statement object. When you're on the row you want to change, use the update( ) methods and end with updateRow( ).

You need to create the statement with the attribute ResultSet.CONCUR_UPDATABLE. Do an SQL SELECT with this statement. When you are on the row (only one row matches this particular query because it is selecting on the primary key), use the appropriate update method for the type of data in the column you want to change, passing in the column name or number and the new value. You can change more than one column in the current row this way. When you're done, call updateRow( ) on the ResultSet. Assuming that you didn't change the autocommit state, the data is committed to the database.

Example: ResultSetUpdate.java (partial listing)

try {

   con = DriverManager.getConnection(url, user, pass);

   stmt = con.createStatement(

       ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

   rs = stmt.executeQuery("SELECT * FROM Users where nick=\"ian\"");



   // Get the resultset ready, update the passwd field, commit

   rs.first( );

   rs.updateString("password", "unguessable");

   rs.updateRow( );



   rs.close( );

   stmt.close( );

   con.close( );

} catch(SQLException ex) {

   System.err.println("SQLException: " + ex.getMessage( ));

}

Java Tips - Connecting to a JDBC Database

The static method DriverManager.getConnection( ) lets you connect to the database using a URL-like syntax for the database name (for example, jdbc:dbmsnetproto://server:4567/mydatabase) and a login name and password. The "dbURL" that you give must begin with jdbc:. The rest of it can be in whatever form the driver vendor's documentation requires and is checked by the driver. The DriverManager asks each driver you have loaded (if you've loaded any) to see if it can handle a URL of the form you provided. The first one that responds in the affirmative gets to handle the connection, and its connect( ) method is called for you (by DriverManager.getConnection( )).

Four types of drivers are defined by Sun (not in the JDBC specification but in their less formal documentation)

1. JDBC-ODBC bridge = Provide JDBC API Access.
2. Java and Native Driver = Java code calls Native DB driver.
3. Java and Middleware = Java contacts Middleware Server.
4. Pure Java = java contacts (possibly remote) DB directly.

Example: Connect.java

import java.awt.*;

import java.sql.*;



/** Load a driver and connect to a database.

 */

public class Connect {



    public static void main(String[] av) {

        String dbURL = "jdbc:odbc:Companies";

        try {

            // Load the jdbc-odbc bridge driver

            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");



            // Enable logging

            DriverManager.setLogStream(System.err);



            System.out.println("Getting Connection");

            Connection conn = 

                DriverManager.getConnection(dbURL, "ian", "");    // user, passwd



            // If a SQLWarning object is available, print its

            // warning(s).  There may be multiple warnings chained.



            SQLWarning warn = conn.getWarnings( );

            while (warn != null) {

                System.out.println("SQLState: " + warn.getSQLState( ));

                System.out.println("Message:  " + warn.getMessage( ));

                System.out.println("Vendor:   " + warn.getErrorCode( ));

                System.out.println("");

                warn = warn.getNextWarning( );

            }



            // Process the connection here...



            conn.close( );    // All done with that DB connection



        } catch (ClassNotFoundException e) {

            System.out.println("Can't load driver " + e);

        } catch (SQLException e) {

            System.out.println("Database access failed " + e);

        }

    }

}

Java Tips - JDBC Setup and Connection

JDBC has two Levels, JDBC 1 and JDBC 2. Level 1 is included in all JDBC implementation and drivers; Level 2 is optional and requires a Level 2 driver.

To create a JDBC query:

1. Load the appropriate Driver class, which has the side effect of registering with the DriverManager.
2. Get a Connection object, using DriverManager.getConnection( ) :

   Connection con = DriverManager.getConnection (dbURL, name, pass);

3. Get a Statement object, using the Connection object's createStatement( ) :

   Statement stmt = con.createStatement( );

4. Get a ResultSet object, using the Statement object's executeQuery( ) :

   ResultSet rs = stmt.executeQuery("select * from MyTable");

5. Iterate over the ResultSet:

   while (rs.next( )) {
   
       int x = rs.getInt("CustNO");

6. Close the ResultSet.
7. Close the Statement.
8. Close the Connection.
    

The first step in using JDBC 1 is to load your database's driver. This is performed using some Java JVM magic. The class java.lang.Class has a method called forName( ) that takes a string containing the full Java name for a class and loads the class, returning a Class object describing it. This is part of the introspection or reflection API  but can be used anytime to ensure that a class has been correctly configured into your CLASSPATH. This is the use that we'll see here. And, in fact, part of the challenge of installing JDBC drivers is ensuring that they are in your CLASSPATH at deployment time. The advantage of my slightly convoluted approach is that the drivers do not have to be on your CLASSPATH at compile time. In some cases, this can allow customers to use your software with database drivers that didn't even exist when your software was written; how's that for flexibility?

In addition to checking your CLASSPATH, this method also registers the driver with another class called the DriverManager . How does it work? Each valid JDBC driver has a bit of method-like code called a static initializer . This is used whenever the class is loaded—just what the doctor ordered! So the static block registers the class with the DriverManager when you call Class.forName( ) on the driver class.

Example: LoadDriver.java

import java.awt.*;

import java.sql.*;



/** Load some drivers.  */

public class LoadDriver {

    public static void main(String[] av) {

        try {

            // Try to load the jdbc-odbc bridge driver

            // Should be present on Sun JDK implementations.

            Class c = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            System.out.println("Loaded " + c);
           
        } catch (ClassNotFoundException ex) {

            System.err.println(ex);

        }

    }

}