Chapter 10

Database Classes

CONTENTS

• Introduction
• JDBC in Depth
  • The DriverManager Class
  • The Driver Class
  • The Connection Class
  • The Statement Class
  • The ResultSet Class
  • A JDBC Sample Program
• Making JDBC Easy to Use
  • The Connector Interface
  • The SQLFactory Interface
• The Classes
  • The DBConnector Class
  • OracleSequence
• Summary

da ta base \da'te-bas' \ n: a store of data

Introduction

Now that you have some of the easier classes under your belt, it is time to start digging into some more advanced issues. The main thrust of this chapter is to introduce you to some interfaces and classes that will help your intranet applications communicate with databases.

This chapter builds on the classes in Chapter 8, "Utility Classes," and Chapter 9, "Logging Classes." You'll also build on Sun's own JDBC classes for Java to provide enhanced functionality and a higher level of access for your applications.

This chapter will take two forks or paths. The first is the database connection path. You will design and create a connection class that you can use to connect to almost any JDBC data source. You will use this connection class throughout the rest of the book and in the applications developed in later chapters.

The second path is that of database maintenance. Writing SQL statements to update your database is a dull and dreary task, but as you'll see, you can automate the task just a little, making this bogus task almost fun.

But before you even start talking about new classes, let's take a look deep inside JDBC.

JDBC in Depth

If you're not familiar with JDBC, please go back and read the JDBC section of Chapter 6, "Database Connectivity Options," where you will find a thorough overview of JDBC. In this chapter, you will investigate the classes that make up the JDBC class hierarchy and how to use them. For your convenience, the JDBC class hierarchy is shown again in Figure 10.1.

Figure 10.1 : The JDBC class hierarchy.

JDBC is a rich set of classes that give you transparent access to a database with a single application programming interface, or API. This access is done with plug-in platform-specific modules, or drivers. Various database manufacturers provide these drivers. Using these drivers and the JDBC classes, your programs will be able to access consistently any database that supports JDBC, giving you total freedom to concentrate on your applications and not to worry about the underlying database.

The JDBC class hierarchy lives in the java.sql package. As you can see in Figure 10.1, many of the classes in the JDBC hierarchy are abstract. It is up to the database vendor to provide implementations of these classes for its customers.

All access to JDBC data sources is done through SQL. Sun has concentrated on JDBC issuing SQL commands and retrieving their results in a consistent manner. Though you gain so much ease by using this SQL interface, you do not have the "raw" database access that you might be used to. With the classes you can open a connection to a database, execute SQL statements, and do what you will with the results, however. Don't forget that SQL statements can create database objects such as tables, views, synonyms, triggers, stored procedures, and so on. Don't think that JDBC is limited.

The DriverManager Class

The cornerstone of the JDBC package is the DriverManager class. This class keeps track of all the different available database drivers. For you Bozo fans out there, think of it as Ringmaster Ned. The DriverManager manages loading and unloading drivers. Just as Ringmaster Ned is essential for introducing new circus acts, the DriverManager is instrumental in creating new database connections.

You the programmer won't usually see the DriverManager's work, though. This class mostly works behind the scenes to ensure that everything is cool for your connections.

The DriverManager maintains a Vector that holds information about all the drivers that it knows about. The elements in the Vector contain information about the driver such as the class name of the Driver object, a copy of the actual Driver object, and the Driver security context.

The DriverManager, while not a static class, maintains all static instance variables with static access methods for registering and unregistering drivers. This allows the DriverManager never to need instantiation. Its data always exists as part of the Java runtime.

The drivers managed by the DriverManager class are represented by the Driver class.

The Driver Class

If the cornerstone of JDBC is the DriverManager, then the Driver class is most certainly the bricks that build the JDBC. The Driver is the software wedge that communicates with the platform-dependent database, either directly or using another piece of software. How it communicates really depends on the database, the platform, and the implementation. Figure 10.2 illustrates the software layers in a Java JDBC application connection to a database.

Figure 10.2 : The Java-JDBC database.

You must create each Driver in your program or you can have them pre-loaded for you by specifying the class names of the driver you wish to have pre-loaded. To do this, you need to specify a value for the system property called jdbc.drivers. This property needs to contain the fully qualified class name of each Driver separated by a colon (:), which allows the DriverManager to instantiate the class because it knows how to find classes in your CLASSPATH.

As an example, a sample property setting follows:

jdbc.drivers=weblogic.jdbc.oci.Driver:jdbc.odbc.JdbcOdbcDriver

This example will load WebLogic's Oracle driver and the JDBC-ODBC bridge drivers. If you were using Sun's HotJava browser, this line would go in your .hotjava/properties file. You can also start your programs with the following code as a command-line argument:

java -Djdbc.drivers=weblogic.jdbc.oci.Driver:jdbc.odbc.JdbcOdbcDriver
    MyApplication

For your purposes, you will place them in your application configuration files and let your ConfigProperties class move it for us.

After a Driver is constructed, you really never need to worry about it again. In fact, if you use the jdbc.drivers property as outlined above, you never even have to create driver objects.

It is the Driver's responsibility to register with the DriverManager and connect with the database. Database connections are represented by the Connection class.

The Connection Class

The Connection class encapsulates the actual database connection into an easy-to-use package. Sticking with your foundation building analogy here, the Connection class is the mortar that binds the JDBC together. It is created by the DriverManager when its getConnection() method is called. This method accepts a database connection URL and returns a database Connection to the caller.

Remember that Vector that holds driver information in the DriverManager class? Well, here's where you'll use it.

When you call the getConnection() method, the DriverManager asks each driver that has registered with it whether the database connection URL is valid. If one driver responds positively, the DriverManager assumes a match. If no driver responds positively, an SQLException is thrown. The DriverManager returns the error "no suitable driver," which means that of all the drivers that the DriverManager knows about, not one of them could figure out the URL you passed to it.

Assuming that the URL was good and a Driver stepped up to the plate and said, "I can handle this!", then the DriverManager will return a Connection object to you. What can you do with a Connection object? Not much. This class is nothing more than an encapsulation of your database connection. It is a factory and manager object, and is responsible for creating and managing Statement objects.

The Statement Class

Picture your Connection as an open pipeline to your database. Database transactions travel back and forth between your program and the database through this pipeline. The Statement class represents these transactions.

The Statement class encapsulates SQL queries to your database. Using several methods, these calls return objects that contain the results of your SQL query. When you execute an SQL query, the data that is returned to you is commonly called the result set. You can choose from several result sets, depending on your needs:

• ResultSet executeQuery( String sqlStatement )
  This method sends the SQL query contained in sqlStatement and returns a single set of results. This method is best used in sending SELECT statements. These statements typically return a result set.
• int executeUpdate( String sqlStatement )
  This method sends the SQL query contained in sqlStatement and returns an integer. This method is useful when you send SQL INSERTs, DELETEs, and UPDATEs. These commands return a count of rows that were affected by your query. This statement should not be used for queries that return result sets.
• boolean execute( String sqlStatement )
  This method sends the sqlStatement to the database and returns true if the statement returns a result set or false if the statement returns an integer. This method is best used when multiple result sets can be returned.

Use the following methods to easily navigate the results a query returns:

• boolean getMoreResults()
  This moves to the next result set in the Statement. This, like the execute() method, returns true if the next result is a result set or false if it is an integer. If you have already retrieved a ResultSet from the Statement, this method will close it before returning.
• ResultSet getResultSet()
  This method returns to you a result set in a ResultSet object. This result set is the current result set.
• int getUpdateCount()
  This method returns to you the integer result that an execute() method returned.

By now you are probably wondering what this ResultSet class is all about, and how it can possibly hold all that data. Well, it's quite a class, as you are about to see.

The ResultSet Class

As you've probably guessed, the ResultSet class encapsulates the results returned from an SQL query. Normally, those results are in the form of rows of data. Each row contains one or more columns. The ResultSet class acts as a cursor, pointing to one record at a time, enabling you to pick out the data you need.

Gaining Access to a Column's Data

You can gain access to the data within the ResultSet using many different methods. These methods are in the form of gettype(), where type is the data type of the column. These functions return a new instance of the type that contains the data from the result set. If the column value is NULL, the value returned from these methods is NULL.

You can access the column's data either by column number or name. Sometimes you might know the column number, sometimes you might not. These methods give you the flexibility to access the columns either way.

The following list presents the gettype() methods provided by the ResultSet class and their return types:

• String getString()
• boolean getBoolean()
• byte getByte()
• short getShort()
• int getInt()
• long getLong()
• float getFloat()
• double getDouble()
• Numeric getNumeric()
• byte[] getBytes()
• java.sql.Date getDate()
• java.sql.Time getTime()
• java.sql.Timestamp getTimestamp()
• java.io.InputStream getAsciiStream()
• java.io.InputStream getUnicodeStream()
• java.io.InputStream getBinaryStream()

Getting to the Next Row

When you've retrieved all the data you can from a column, it is time to move on to the next row. Moving to the next row is done with the next() method. This method returns a boolean value indicating the status of the row. Internally, it moves the ResultSet's cursor to the next row, thereby giving access to the data stored in the columns of that row.

When a ResultSet object is created, its position is always before the first row of data contained within. This makes it necessary to call the next() method before you can access any column data. The first call to next() makes the first row available to your program. Subsequent calls to next() make the next rows available. If no more rows are available, next() returns false.

A JDBC Sample Program

Programming concepts aren't always clear when you read about them, therefore a small example of JDBC program is definitely in order. The following program in Listing 10.1 is a sample program that illustrates the concepts presented so far. These concepts follow:

• Connecting to a JDBC data source
• Executing a query
• Parsing the results of the query

Listing 10.1. A sample JDBC program.

//****************************************************************************
//* Imports                                         &nb sp;                        *
//****************************************************************************

import                            java.sql.*;

//****************************************************************************
//* JDBCExample                                          ;                     *
//****************************************************************************

public class
JDBCExample
{

//****************************************************************************
//* main                                                                      *
//****************************************************************************

    public static void
    main( String args[] )
    throws Exception
    {
        //    Find the class...
        Class.forName( "weblogic.jdbc.oci.Driver" );

        //    Open a connection...
        Connection myConnection = DriverManager.getConnection(
            "jdbc:weblogic:oracle:tcp-loopback.world",
            "scott",
            "tiger" );

        //    Create a statement...
        Statement myStatement = myConnection.createStatement();

        //    Execute a query...
        try
        {
            //    Execute the query...
            myStatement.execute( "select * from emp" );

            //    Get the result set...
            ResultSet mySet = myStatement.getResultSet();

            //    Advance to the next row...
            while ( mySet.next() )
            {
                //    Get the data...
                int empno = mySet.getInt( "empno" );
                String ename = mySet.getString( "ename" );
                long salary = mySet.getLong( "sal" );

                //    Print it all out...
                System.out.println( Integer.toString( empno ) + " - " +
                    ename + " - " + Integer.toString( empno ) );
            }
        }
        catch ( SQLException e )
        {
            System.out.println( "SQL Error: " + e.toString() );
        }

        //    Close everything up...
        myStatement.close();
        myConnection.close();
  }

}

This program uses jdbcKona drivers to communicate with a Personal Oracle7 database on a local machine. When Personal Oracle7 is installed, it creates a database alias called
tcp-loopback.world. You will connect with this alias in this example. Also included with Personal Oracle7 is a set of sample tables with data. This program will retrieve and print three of the columns from the employee table that comes with Personal Oracle7.

Making JDBC Easy to Use

To make JDBC even easier to use in your intranet applications, you're going to create a set of interfaces and classes. These objects will greatly simplify your use of JBDC and database programming in general.

As you have been doing, you'll look at the interfaces first then move on to the classes. Two interfaces in particular will be useful to us: Connector and SQLFactory. They both provide very different functions, so let's cover them individually.

The Connector Interface

The Connector interface defines the pattern or template that a class must follow to comply with your database connection standard, JDBC. The standard is defined by this interface, however, for the most part. By extending this interface, you are extending the standard. The Connector is the place to implement the functionality your applications required.

The Connector interface defines four standard methods, which follow:

• connect: Connects with a data source
• disconnect: Disconnects from a data source
• connected: Indicates the connection status
• getConnectionURL: Returns an URL used for connecting to a JDBC data source

Let's take a look at each of these interface methods.

The connect Method

The connect method is defined as follows:

//****************************************************************************
//* connect                                         &nb sp;                        *
//****************************************************************************

    public boolean
    connect( String user, String password, String server );

The user's name and password and the server where the data exists are accepted as input arguments. The third parameter, the server where the data exists, might be optional with some Connector implementations, but I have included it here for completeness.

The getConnectionURL method used this information to connect with a JDBC data source. The implementor must connect to the database in this method.

The disconnect Method

The disconnect method is defined as follows:

//****************************************************************************
//* disconnect                                                                *
//****************************************************************************

    public boolean
    disconnect();

Nothing spectacular here. The implementor must disconnect from the database in this method.

The connected Method

The connected method simply returns true or false if the object is currently connected to a database. The method is defined as follows:

//****************************************************************************
//* connected                                         & nbsp;                      *
//****************************************************************************

    public boolean
    connected();

The implementor must keep track of the connection status and report on it through this method.

The getConnectionURL Method

The final method in the Connector interface is the getConnectionURL method. It is defined as follows:

//****************************************************************************
//* constructConnectionURL                                                    *
//****************************************************************************

    public String
    getConnectionURL()

The connection URL is used to connect with a JDBC data source. It is up to the implementor of this interface to return the correct connection URL for the database with which it is working.

The SQLFactory Interface

The second interface you need is one with which you can create classes that generate SQL statements for the objects that they contain. The implementor of this interface decides the method of generation.

Why would you want to generate SQL on-the-fly? Because with SQL, you can build smart objects that will know and keep track of whether they have been modified. If you then place these smart objects into a smart container, the container can produce an SQL statement to update the database. But there needs to be a template or pattern for these smart objects to follow, and that's where the SQLFactory class comes in.

The SQLFactory interface exists to provide a common set of methods for objects to use to generate SQL. The SQL generated is used to send information back to the database when values change. Chapter 11, "User Interface Classes," contains some classes that implement this interface.

The SQLFactory interface defines several methods. Two methods are very important: generateUpdateSQL and generateInsertSQL.

The generateUpdateSQL Method

This method is defined as follows:

//****************************************************************************
//* generateUpdateSQl                                         ;                *
//****************************************************************************

    public String
    generateUpdateSQL( boolean addSet );

The generateUpdateSQL method takes a single argument: addSet. In an SQL UPDATE statement, you must place the keyword SET in front of the very first column. By setting this argument to true, a SET is placed in front of the UPDATE SQL returned. Sample return values follow:

emp_salary = 75000 if addSet is set to false

or

SET emp_salary = 750000 if addSet is set to true

These statements can be concatenated and used in an SQL UPDATE statement.

The generateInsertSQL Method

This method is defined as follows:

//****************************************************************************
//* generateInsertSQL                                         ;                *
//****************************************************************************

    public String
    generateInsertSQL( boolean addParen );

The generateInsertSQL method takes a single argument: addParen. In an SQL INSERT statement, you must place an opening parenthesis (() in front of the very first column. By setting this argument to true, the parenthesis is placed in front of the INSERT SQL returned. Sample return values follow:

emp_salary = 75000 if addParen is set to false

or

( emp_salary = 750000 if addParen is set to true

These statements can be concatenated and used in an SQL INSERT statement.

The Classes

Let's create some classes to access these JDBC data sources. The first class you'll create should encapsulate the connection to a particular database. This class also should implement the Connector interface you've just defined. You might want to build several of these specialized connection classes however, so you'll place all of your base functionality into an abstract class, then create subclasses of it that implement the database specifics.

The first class, DBConnector, is the abstract base class. You will use it as a basis for specific database connection objects. Several classes will be derived from this base class:

• MSQLConnector: Connects to mSQL data sources
• MSSQLServerConnector: Connects to Microsoft SQL Server data sources
• ODBcconnector: Connects to ODBC data sources
• OracleConnector: Connects to Oracle data sources
• SybaseConnector: Connects to Sybase data sources

These classes really do nothing more than provide the base class with a database URL with which to connect. The DriverManager does the real work.

The last two classes that you've built for your intranet are the OracleSequence and the SequenceGenerator classes.

OracleSequence uses Oracle's sequence object to create the unique number. This object is Oracle's internal counter. When you query it for its next value, it increments its counter and stores the result. It also returns the result to you. Because not everyone will have access to Oracle sequences, however, the SequenceGenerator class is provided.

The SequenceGenerator class provides the exact same functionality as the OracleSequence class but in a generic manner that can be used with any database.

Let's look at some classes in depth.

The DBConnector Class

The DBConnector class encapsulates a lot of the repetitive tasks that are needed to connect with a JDBC data source. It is an abstract class; therefore, it is incomplete. You cannot instantiate the DBConnector class directly; you can only instantiate its derivatives.

The declaration of the DBConnector class follows:

//****************************************************************************
//* DBConnector                                          ;                     *
//****************************************************************************

public abstract class
DBConnector
implements Connector

The DBConnector class implements the Connector interface but not the entire interface. Extensions of this class must implement one method, getConnectionURL; therefore it is declared abstract.

Instance Variables

The DBConnector class has several important instance variables:

• protected Connection myConnection: Holds a JDBC Connection object for talking to the database
• protected Statement myStatement: Holds a JDBC Statement object for executing SQL queries
• protected boolean isConnected: Holds an indicator of the connection status
• protected String lastError: Holds the last error that occurred, if any

All of these instance variables are protected. Only myStatement is available outside of the DBConnector hierarchy through the getStatement() method. This class is mainly used to execute database queries.

OracleSequence

A sequence is a database object that provides unique numbers for storing in the database. For example, you could set up a sequence to provide new employee identification numbers for your employee table. This sequence would then generate the numbers for you.

This class encapsulates using an Oracle sequence. The sequence must exist for this class to work. If you don't have any sequences, you can create one with the following command:

CREATE SEQUEncE sequence name IncREMENT BY increment START
    WITH starting value

where

sequence name is the name you wish to call the sequence.

increment is the amount to increment each time.

starting value is the value at which to start the sequence.

This class provided two methods: getCurrentValue() and getNextValue(). Each returns ints and provides the current and next values of the Oracle sequence. Listing 10.2 shows the source code for the OracleSequence class.

Listing 10.2. The OracleSequence class.

//****************************************************************************
//* OracleSequence                                        &n bsp;                  *
//****************************************************************************

public class
OracleSequence
{
//****************************************************************************
//* Members                                         &nb sp;                        *
//****************************************************************************

    /**
    * The name of the database sequence
    */
    String                        mySequenceName;

    /**
    * The name of the database sequence
    */
    OracleConnector            myConnection;
//****************************************************************************
//* Constructors                                        &nbs p;                    *
//****************************************************************************

    public
    OracleSequence( OracleConnector connector, String sequenceName )
    {
        myConnection = connector;
        mySequenceName = sequenceName;
    }
//****************************************************************************
//* getNextValue                                        &nbs p;                    *
//****************************************************************************

    public int
    getNextValue()
    {
        return( getDBSequenceValue( false ) );
    }

//****************************************************************************
//* getCurrentValue                                        & nbsp;                 *
//****************************************************************************

    public int
    getCurrentValue()
    {
        return( getDBSequenceValue( true ) );
    }

//****************************************************************************
//* getDBSequenceValue                                       &nbs p;               *
//****************************************************************************

    protected int
    getDBSequenceValue( boolean currentVal )
    {
        //    Placeholder for the value...
        int                currentValue = 0;

        //    This statement retrieves the current
value from the database
        String sql;

        if ( currentVal )
            sql = "select " + mySequenceName + ".currval from dual";
        else
            sql = "select " + mySequenceName + ".nextval from dual";

        try
        {
            if ( myConnection.getStatement().execute( sql ) )
            {
                //    The results are received here...
                ResultSet rs =
myConnection.getStatement().getResultSet();
                rs.next();
                currentValue = rs.getInt( 1 );
            }
        }
        catch ( SQLException e )
        {
            //    No records? Set to 1...
            currentValue = 1;
        }

        return( currentValue );
    }

}

The getDBSequenceValue() method constructs an SQL statement dynamically with the construction values that were passed. This statement is then sent to the database and the results are returned.

The SequenceGenerator Class

The last class of note is the SequenceGenerator class. This class generates a unique sequence number that identifies the rows in your database tables. For example, you can use the SequenceGenerator class to generate employee identification numbers. You'll see that use illustrated in Chapter 13, "Employee Files."

The SequenceGenerator works on a simple principle. Given a table and column, it retrieves the data in that column of the table in reverse sorted order. Therefore, the first row returned is the highest. It takes this highest value and increments it, creating your unique number.

The constructor accepts as input the necessary components to create the sequence retrieving SQL:

public
SequenceGenerator( DBConnector connector, String table,
String column )

The constructor records the value of these input parameters and calls the following code to get the current highest value:

//****************************************************************************
//* getDBSequenceValue                                       &nbs p;               *
//****************************************************************************

    protected void
    getDBSequenceValue()
    {
        //    The first row returned by this SQL
statement is the largest number...
        String sql = "select " + myColumn + " from " + myTable +
            " order by " + myColumn + " desc";

        try
        {
            if ( myConnection.getStatement().execute( sql ) )
            {
                //    Check the first row!
                ResultSet rs =
myConnection.getStatement().getResultSet();
                rs.next();
                currentValue = rs.getInt( 1 );
            }
        }
        catch ( SQLException e )
        {
            //    No records? Set to 1...
            currentValue = 1;
        }
    }

Summary

Well, this has been quite a chapter. You learned about many interfaces and classes that will help you construct intranet applications. These components also provide a consistent framework from which to build connections to JDBC data sources. In addition, you saw some examples of how to use these classes in real applications.

In Chapter 11, you will learn a few neat tricks for creating visually stunning user interfaces and you'll implement the SQLFactory interface in some of your classes.