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

        }

    }

}

Java Tips - Formatting Numbers

The java.text package includes a Locale class, and, furthermore, the Java runtime automatically sets a default Locale object based on the user's environment; e.g., on the Macintosh and Windows, the user's preferences; on Unix, the user's environment variables. To provide formatters customized for numbers, currencies, and percentages, the NumberFormat class has static factory methods that normally return a DecimalFormat with the correct pattern already instantiated. A DecimalFormat object appropriate to the user's locale can be obtained from the factory method NumberFormat.getInstance( ) and manipulated using set methods. Surprisingly, the method setMinimumIntegerDigits( ) turns out to be the easy way to generate a number format with leading zeros. Here is an example:

import java.text.*;

import java.util.*;



/*

 * Format a number our way and the default way.

 */

public class NumFormat2 {

    /** A number to format */

    public static final double data[] = {

        0, 1, 22d/7, 100.2345678

    };



    /** The main (and only) method in this class. */

    public static void main(String av[]) { 

        // Get a format instance

        NumberFormat form = NumberFormat.getInstance( );



        // Set it to look like 999.99[99]

        form.setMinimumIntegerDigits(3);

        form.setMinimumFractionDigits(2);

        form.setMaximumFractionDigits(4);



        // Now print using it.

        for (int i=0; i<data.length; i++)

            System.out.println(data[i] + "\tformats as " +

                form.format(data[i]));

    }

}

This prints the contents of the array using the NumberFormat instance form:

$ java NumFormat2

0.0     formats as 000.00

1.0     formats as 001.00

3.142857142857143       formats as 003.1429

100.2345678     formats as 100.2346

$

You can also construct a DecimalFormat with a particular pattern or change the pattern dynamically using applyPattern( ). Some of the more common pattern characters are shown in the following.

# = Numeric digit (leading zeros suppressed)
0 = Numeric digit (leading zeros provided)
. = Locale-specific decimal separator (decimal point)
, = Locale-specific grouping separator (comma in English)
- = Locale-specific negative indicator (minus sign)
% = Shows the value as a percentage
; = Separates two formats: the first for positive and the second for negative values
, = Escapes one of the above characters so it appears

The NumFormatTest program uses one DecimalFormat to print a number with only two decimal places and a second to format the number according to the default locale:

// NumFormatTest.java 

/** A number to format */ 

public static final double intlNumber = 1024.25; 

/** Another number to format */ 

public static final double ourNumber = 100.2345678; 

NumberFormat defForm = NumberFormat.getInstance( ); 

NumberFormat ourForm = new DecimalFormat("##0.##"); 

// toPattern( ) shows the combination of #0., etc 

// that this particular local uses to format with 

System.out.println("defForm's pattern is " + 

    ((DecimalFormat)defForm).toPattern( )); 

System.out.println(intlNumber + " formats as " + 

    defForm.format(intlNumber)); 

System.out.println(ourNumber + " formats as " + 

    ourForm.format(ourNumber)); 

System.out.println(ourNumber + " formats as " + 

    defForm.format(ourNumber) + " using the default format");

This program prints the given pattern and then formats the same number using several formats:

defForm's pattern is #,##0.###

1024.25 formats as 1,024.25

100.2345678 formats as 100.23

100.2345678 formats as 100.235 using the default format   

Java Tips - Parsing Strings into Dates

The DateFormat class has some additional methods, notably parse( ) , which tries to parse a string according to the format stored in the given DateFormat object:

// DateParse1.java 

SimpleDateFormat formatter = new SimpleDateFormat ("yyyy-MM-dd"); 

String input = args.length == 0 ? "1818-11-11" : args[0]; 

System.out.print(input + " parses as "); 

Date t; 

try { 

    t = formatter.parse(input); 

    System.out.println(t); 

} catch (ParseException e) { 

    System.out.println("unparseable using " + formatter); 

}

This program parses any date back to Year Zero and well beyond Year 2000.

What if the date is embedded in an input string? You could, of course, use the string's substring( ) method to extract it, but there is an easier way. The ParsePosition object from java.text is designed to represent (and track) the position of an imaginary cursor in a string. Suppose we have genealogical data with input strings representing the times of a person's life:

BD: 1913-10-01 Vancouver, B.C. 

DD: 1983-06-06 Toronto, ON

This lists one person's birth date (BD) and place, and death date (DD) and place. We can parse these using String.indexOf(' ') to find the space after the : character, DateFormat parse( ) to parse the date, and String.substring( ) to get the city and other geographic information. Here's how:

// DateParse2.java 

SimpleDateFormat formatter = 

    new SimpleDateFormat ("yyyy-MM-dd"); 

String input[] = {  

    "BD: 1913-10-01 Vancouver, B.C.", 

    "MD: 1948-03-01 Ottawa, ON", 

    "DD: 1983-06-06 Toronto, ON" }; 

for (int i=0; i<input.length; i++) { 

    String aLine = input[i]; 

    String action; 

    switch(aLine.charAt(0)) { 

        case 'B': action = "Born"; break; 

        case 'M': action = "Married"; break; 

        case 'D': action = "Died"; break; 

        // others... 

        default: System.err.println("Invalid code in " + aLine); 

        continue; 

    } 

    int p = aLine.indexOf(' '); 

    ParsePosition pp = new ParsePosition(p); 

    Date d = formatter.parse(aLine, pp); 

    if (d == null) { 

        System.err.println("Invalid date in " + aLine); 

        continue; 

    } 

    String location = aLine.substring(pp.getIndex( )); 

    System.out.println( 

        action + " on " + d + " in " + location); 

}

This works like I said it would:

Born on Wed Oct 01 00:00:00 PDT 1913 in  Vancouver, B.C. 

Married on Mon Mar 01 00:00:00 PST 1948 in  Ottawa, ON 

Died on Mon Jun 06 00:00:00 PDT 1983 in  Toronto, ON