The structure of the way data is stored in a database is referred to as the schema. In other words, the schema is the abstract representation of the way the data is organized. The schema used by relational databases is very natural to object-oriented programmers. The relational database contains a set of tables. The columns in a table represent the data that is stored in that table. In Unified Modeling Language (UML) terminology, this would be a composition relationship. The database has tables, and each table has data. Using an analogy between JavaBeans and relational databases, the JavaBean stores data and provides getter and setter methods to access that data. With database programming, the relational database stores data and SQL commands are executed to perform the get and set on the stored data. The tables in a relational database may share a common key value that allows the tables to be joined for search operations. This simple data structure, a group of tables that share a common key field, provides all the flexibility necessary to store inventories, catalogs, business and customer information, as well as enterprise objects.

Designing the Data Model

The logical data model for a relational database looks very much like a UML class diagram. The entities on the data model diagram are the tables. Each table has a unique name and a list of column names. The columns can be thought of as attributes. Each row can be thought of as an instance holding values for each of the column “attributes.” Typically, one column is used as a unique ID and is referred to as the primary key. Just as in UML class diagrams, the tables in the data model have relationships between each other. The relationship identifies a multiplicity level, which can be

• One-to-One—. When one column value in a table relates to only one column value in another table, and vice versa. One-to-one relationships are rarely seen in “real-world” data models, as the relationships result in unnecessary division of data and the two tables are merged back together.

• One-to-Many—. When one column value in a table relates to many column values in another table. One-to-many relationships are more common in “real-world” data models as they provide significant data reduction and allow for faster searching.

• Many-to-Many—. When many column values in a table relate to many column values in another table. Many-to-many relationships are typically transformed into multiple one-to-many relationships, as they cannot be represented by means of a relational model. This transformation usually requires an assignment, also referred to as a link or pivot table.

An example of a one-to-one relationship is illustrated in Figure 13.1, where one author has authored exactly one book.

Figure 13.1. The one-to-one relationship is represented between entities where one column value in a table relates to only one column value in another table.

If, on the other hand, the author wrote many books, this relationship is represented by the one-to-many multiplicity, as illustrated in Figure 13.2.

Figure 13.2. The one-to-many relationship requires two tables that share a common key, in this case, the author_id.

The common author_id allows you to relate a row in the Books table to a row in the Authors table. Each of the “many” books contains a reference to the “one” author who wrote it. This relationship enables you to join the tables and search for all the books written by a particular author. It also gives the author_name of a particular book. Details of joining tables with the SQL WHERE clause will be discussed later in this chapter.

Finally, the database schema may require a many-to-many relationship between tables. For the many-to-many relationship, a cross-reference table is created. The contents of the cross-reference table are the key fields of the two tables that have a many-to-many relationship. For example, you can organize the books into various categories. Doing so requires a many-to-many relationship because you will allow a book to be included in multiple categories and each category has multiple books. The book you are now reading could be in the Enterprise, WebLogic, and Java categories. The Books and Categories have a many-to-many relationship. The Categories table also includes a rating column that will be used in the WHERE clause examples later in this chapter. A cross-reference table (also frequently called an association table), as illustrated in Figure 13.3, is required to contain this many-to-many relationship.

Figure 13.3. The cross-reference table contains the keys of the tables in a many-to-many relationship.

From these three tables, you can perform these searches:

• Display all author names

• Display all book names

• Display all category names

• Display all books for a particular author

• Display all categories for a particular author

• Display all books for a particular category

• Display all categories for a particular book

In summary:

• A primary key that consists of one column is likely to be the “one” in a one-to-many-relationship.

• A table with a compound primary key of more than one column is likely to be the “many” in a one-to-many or a many-to-many relationship, where the join occurs on one of the columns of the primary key.

• A foreign key is likely to be the “many” in a one-to-many relationship.

This introduction to SQL is intended to present the most commonly used features of the language. From the data model, you construct the relational database using SQL commands. The tables are created using the CREATE command and are populated with data using the INSERT and UPDATE commands. Data is removed from the tables using the DELETE command. You search the database by using the SELECT command. These are the five most common SQL commands and are described in the following sections. From this brief introduction, you will be able to use JDBC productively.

WHERE author_name = 'MY_FAVORITE_AUTHOR'

WHERE name LIKE 'C%'

CREATE TABLE table_name (
    column_1_name column_1_type column_1_modifier,
    column_n_name column_n_type column_n_modifier )

INSERT INTO table_name ( column_1_name, column_n_name )
VALUES (value_1, value_n )

UPDATE table_name
SET column_1_name = value_1
   column_n_name = value_n
WHERE column_name = value

DELETE FROM table_name
WHERE column_name = value

SELECT column_1_name, column_n_name
FROM table_name
WHERE column_name = value

SELECT table_1.column_name, table_2.column_name
FROM table_1, table_2
WHERE table_1.key_column = table_2.key_column

SELECT books.book_name, authors.author_name
FROM books, authors
WHERE books.author_id = authors.author_id

SELECT authors.author_name
FROM authors, books
WHERE books.author_id = authors.author_id
AND books.book_id IN
( SELECT publishers.book_id
  FROM publishers, books
  WHERE publishers.publisher_name = 'MY_PUBLISHER'
  AND books.publisher_id = publisher.publisher_id )

SELECT count(*) FROM BOOKS

SELECT * FROM authors ORDER BY author_name

SELECT b.book_name, a.author_name
FROM authors a, books b, categories c
WHERE c.book_id = b.book_id AND
c.author_id = a.author_id AND
5 >= ( SELECT count(*) FROM categories cc
WHERE cc.rating >= c.rating );

The JDBC driver performs the actual communication with the database. It is hidden from your application by the DriverManager. This means your application never directly invokes methods on the JDBC driver. The Java SDK comes with a JDBC driver known as the JDBC-ODBC bridge. It can communicate with any database that supports the Open Database Connectivity (ODBC) introduced by Microsoft. It is really meant to be used only for learning JDBC. It has performance penalties and limitations that make it unsuitable for production software. The JDBC-ODBC bridge is loaded into your application in a fairly unique fashion. It automatically registers itself with the DriverManager when it is loaded. The Java code to load the JDBC-ODBC bridge is shown in Listing 13.1.

// load the JDBC-ODBC bridge
try {
  Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
}
catch( ClassNotFoundException x ) {
  System.err.println( x.toString() );
}

The JDBC specification defines four categories for JDBC drivers, as represented in Table 13.3. These categories allow you to select a JDBC driver that meets your performance and deployment needs.

// create a static instance of the Weblogic JDBC driver
static {
  new Weblogic.jdbc.jts.Driver();
}

The Connection object is obtained through a static method of the DriverManager. You must load a JDBC driver prior to getting a connection from the DriverManager. The static method name is DriverManager.getConnection(). Three parameters are passed to this method: the JDBC URL, database user, and database password. The JDBC URL specifies the protocol, which is always jdbc; the subprotocol; and the database identifier. If you are using the JDBC-ODBC bridge, the URL would be the following string:

"jdbc:odbc:DATABASE_ID"

The DATABASE_ID is the name registered by your database administration software to identify the database. On a Microsoft Windows system, the DATABASE_ID is typically the Data Source Name for the ODBC database. If you are using the BEA WebLogic jDriver, the JDBC URL is

"jdbc:Weblogic:jts:ejbpool"

The user and password are also set by the database administration software. The getConnection() method is overloaded to pass only the URL parameter if the database is not password protected. Like most of the JDBC methods, getConnection() throws an SQLException to indicate an error condition. The Java code to get a Connection object using the WebLogic jDriver is

Connection con;
try {
  con = DriverManager.getConnection( "jdbc:Weblogic:jts:ejbpool" );
} catch( SQLException x ) {
  System.err.println( x.toString() );
}

The Statement object is obtained by calling the createStatement() method on your Connection object. It is used to submit SQL commands to the database. Previously, we noted that the most common SQL commands are CREATE, INSERT, DELETE, UPDATE, and SELECT. You use the SELECT command to query the database and the other four commands to update or modify the database. The Statement interface includes executeUpdate() and executeQuery() methods, which have a return value corresponding to the expected results for the SQL command being executed (see Table 13.6). The executeUpdate() method is used with CREATE, INSERT, DELETE, and UPDATE. The return value is an int that contains the number of rows affected in the database. The executeQuery() method is used with SELECT. The return value is a ResultSet that contains the results that match the query.

Assuming that con is a Connection object that was created previously, the Java code to create a Statement object from the Connection object is

// create Statement object from Connection
Statement stmt = con.createStatement();

With the Statement object named stmt, you call either the executeQuery() or executeUpdate() method, depending on the SQL command that you want to perform.

The parameter to executeQuery() and executeUpdate() in the Statement class is a String representing an SQL command. Generating this String can be a very complex, unreadable series of concatenations. The string constants containing SQL keywords are interspersed with the table names, column names, and values that make up your SQL command. Listing 13.2 shows a simple example that uses INSERT to add elements into a table.

private int update(
Connection con, String table_name,
String column_name, String value )
{
    Statement stmt = con.createStatement();

    String command =
"INSERT INTO " + table_name + " ( " + column_name + " ) " +
"VALUES ( " + value + " )";

return stmt.executeUpdate( command );
    }

The PreparedStatement class gives a much more readable interface for building complex SQL commands over the string concatentation required for the executeUpdate() method. There is also an added bonus: Using this class is a much more efficient way to send the SQL command to the database. When the PreparedStatement object is created, the SQL for the command is immediately sent to the database. Through the PreparedStatement object, you can bind new and different parameters before each execution of the statement, thus allowing you to reuse the prepared SQL command over and over. Listing 13.3 shows a rewrite of the update() method in Listing 13.2 using PreparedStatement.

private int update(
Connection con, String column_name,
String value)
{
    PreparedStatement ps = con.prepareStatement(
    "INSERT INTO TABLE_NAME ( ? ) VALUES ( ? )" );
ps.setString( 1, column_name );
ps.setString( 2, value );

int count = ps.executeUpdate();
ps.close();

return count;
    }

A ResultSet object is the return value from the executeQuery() method. It provides methods to navigate through the selected rows and retrieve the values from individual columns. Remember, each column in the table of a relational database has a column name and column type. The column name is a String. Table 13.1 in the “SQL Datatypes” section earlier in this chapter showed how to map between SQL datatypes and Java types. The ResultSet API includes a separate getXXX() method for each type of column data. You therefore must know what type of data is stored in the column and use the appropriate getXXX() method. All the getXXX() methods, shown in Table 13.7, are overloaded to take either a column index or a column name as the parameter. The column index is 1 relative; the first column is column 1.

You can use various methods in ResultSet to navigate through the rows. Typically, you just want to start at the beginning and work through to the end. This approach is analogous to treating the ResultSet as an enumeration. The most straightforward way is to use the next() method. Immediately upon return from the executeQuery() method, the ResultSet cursor appears just before the first row. Therefore, the first call to next() sets the current position to the first row. Each subsequent call to next() increments the current position by one row. The next() method continues to return true until you move past the last row. This makes it well suited for the Boolean expression of a while() statement, as shown in Listing 13.4.

// Query the database for all columns in the Authors table
ResultSet rs = stmt.executeQuery( "SELECT * from Authors" );
// parse through the ResultSet of an SQL Query
while( rs.next() ) {
  // display the Author's name
  System.out.println( "Author's name is " + rs.getString( "author_name" );
}

/**
    Store image data as BLOB in database
    @param    con        Connection
    @param table_name    String
    @param column_name    String
    @param    image        byte[]
    @exception        SQLException
*/
public void storeImage(
Connection con, String table_name, String column_name, byte[] image )
throws SQLException {
    PreparedStatement ps = con.prepareStatement(
    "INSERT INTO TABLE_NAME ( ? ) VALUES ( ? )" );
ps.setString( 1, column_name );
ps.setObject( 2, image );
int count = ps.executeUpdate();
ps.close();
    }

/**
    Retrieve image from database
    @param    con        Connection
    @param    table_name    String
    @param    column_name    String
    @return        image data
    @exception    Exception
*/
public byte[] retrieveImage( Connection con, String table_name, String column_name )
throws SQLException {
    Statement statement = con.createStatement();
    ResultSet rs = statement.executeQuery(
"SELECT " + column_name + " FROM " + table_name );
        rs.next();
        Blob b = rs.getBlob();
        int len = (int)b.length();
        return b.getBytes( 0, len );

    }

A complete JDBC example is provided in Listing 13.7 to show the syntax and semantics of using the JDBC API. It uses the JDBC-ODBC bridge as the driver. This is what the bridge is intended for, to allow Java developers to learn how to interact with an SQL relational database using JDBC. Production software would use a commercial-quality JDBC driver, such as WebLogic jDriver, to benefit from the higher performance and robustness. This Bookstore JDBC example creates and queries the Authors and Books tables described previously. The BookStore class includes a main() method and therefore can be compiled and run as a stand-alone application. In this simple client/server example, the BookStore is the client, and a database server is the server. A query will be performed to join these two tables. The application will perform the following steps:

/** The Book Store JDBC Example. */
public class BookStore {
  // constants
  private static final String JDBC_DRIVER =
"sun.jdbc.odbc.JdbOdbcDriver";
  private static final String JDBC_URL =
    "jdbc:odbc:bookstore";
  // object attributes
  private Connection con;
  private Statement stmt;
  /** Default Constructor
      @param user database user
      @param pswd database password
      @exception ClassNotFoundException  failed to load driver
      @exception SQLException  failed to get connection
  */
  public BookStore( String user, String pswd )
  throws ClassNotFoundException, SQLException {
    // load the JDBC driver
    Class.forName( JDBC_DRIVER );
    // get Connection
    con = DriverManager.getConnection( JDBC_URL, user, pswd );
    // create Statement
    stmt = con.createStatement();
  }
  /** Create table
      @param sqlCommand String
      @exception SQLException  failed to execute SQL command
  */
  public void createTable( String sqlCommand )
  throws SQLException {
    // perform the SQL update
    statement.executeUpdate( sqlCommand );
  }
  /** Insert Data
      @param sqlCommand String
      @return numRows  numbers of rows inserted
      @exception SQLException  failed to execute SQL command
  */
  public void insertData( String sqlCommand )
  throws SQLException {
    // perform the SQL update
    int rowCount = statement.executeUpdate( sqlCommand );
    return rowCount;
  }
  /** Query
      @param sqlCommand String
      @exception SQLException  failed to execute SQL command
  */
  public ResultSet query( String sqlCommand )
  throws SQLException {
    // perform the SQL query
    ResultSet rs = statement.executeQuery( sqlCommand );
    return rs;
  }
  /** Main method */
  public static void main( String[] args ) {
    // SQL commands to create tables
    String authorTable =
      "CREATE TABLE authors ( " +
      "author_id INTEGER PRIMARY KEY, " +
      "author_name VARCHAR(40) )";
    String bookTable =
      "CREATE TABLE books ( " +
      "book_id INTEGER PRIMARY KEY, " +
      "author_id INTEGER, " +
      "book_name VARCHAR(80) )";
    // Author Data
    String[] authorData = { "Twain", "Shakespeare" };
    // Book Data
    Object[][] bookData = {
      { new Integer( 1 ), "Huckleberry Finn" },
      { new Integer( 2 ), "Romeo and Juliet" },
      { new Integer( 2 ), "Julius Caesar" } };
    try {
      BookStore books = new BookStore( "dbuser", "dbpswd" );
      books.createTable( authorTable );
      books.createTable( bookTable );
      // insert author data
      for( int I = 0; I < authorData.length; I++ ) {
        books.insertData( "INSERT INTO authors" +
          " ( author_id, author_name ) VALUES ( " +
           (I + 1) + ", " + authorData[ I ] + ")" );
      // insert book data
      for( int I = 0; I < bookData.length; I++ ) {
        books.insertData( "INSERT INTO books" +
          " ( book_id, author_id, book_name ) VALUES ( " +
           (I + 1) + ", " +
           bookData[ I ][ 0 ] + ", " +
           bookData[ I ][ 1 ] + ")" );
      // query database
      ResultSet rs = books.query(
        "SELECT books.book_name, authors.author_name " +
        "FROM books, authors " +
        "WHERE books.author_id = authors.author_id" );
      // display results
      while( rs.next() ) {
        System.out.println(
          rs.getString( 0 ) + ", " + rs.getString( 1 ) );
      }
    } catch( Exception x )
      System.err.println( x.printStackTrace() );
  }
}

Connection pooling provides a significant performance enhancement by maintaining a set of open database connections. These ready-to-use connections are made available by a connection pool, which saves the overhead of creating connections whenever an application wants to connect to the database. WebLogic Server can also load balance database connections by dynamically shrinking and increasing the number of connections in a connection pool to a database based upon peak and off-peak workload conditions.

The connections in the connection pool are accessed through a DataSource or TxDataSource. Through the DataSource, the application borrows a connection from the connection pool, uses it, and returns it to the connection pool by closing it.

To create a connection pool using the Administration Console, follow these steps:

Once you have created your connection pool, you can have your database connections verified and monitored by WebLogic Server by setting the attributes in the Configuration, Testing tab of your connection pool in the Administration Console. Setting these attributes allows WebLogic Server to test a database connection with a test table in your database before and/or after assigning and releasing a database connection from the connection pool. Since this procedure will add a small delay in either serving a client requesting a database connection, or releasing a database connection back to the connection pool, you should use this option for development environments only.

The attributes for the Configuration, Testing tab, as shown in Figure 13.7, are:

Figure 13.7. Enabling WebLogic Server to test your database connections in the connection pool.

The TestTableName must exist and be accessible to the database user for the connection. Also, it is important to note that the default SQL code used to test a connection is

select count(*) from TestTableName

For this reason it is a good idea to set TestTableName to the name of a table that is known to have few rows, or even no rows, for example DUAL in Oracle, MEMBER in Microsoft SQL Server or USERS in Pointbase.

To monitor the status and activity of your connections using the Administration Console, follow these steps:

A MultiPool is a meta-pool, a pool of connection pools that provides load balancing or high availability for the connection pools which they contain.

MultiPools and Connection Pools are completely hidden from the application. As stated in the previous section, “Configuring and Using Connection Pools,” the application locates a DataSource and uses the DataSource for the database connection. The DataSource or TxDataSource is configured to be a member of a connection pool, which can be configured to be members of a MultiPool. Hence, the application accesses the DataSource while the application server provides the high performance through the connection pool configured for that DataSource. The load-balancing or high-availability of the connection pool is provided through the MultiPool configured for that connection pool.

To create a MultiPool using the Administration Console, follow these steps:

The DataSource is an enhancement to JDBC and is provided in the JDBC Optional Package. DataSources provide access to connection pools to establish database connectivity, and therefore must be created for each individual connection pool you have defined.

Create and configure DataSources through the WebLogic Administration Console by following these steps:

The TxDataSource is a DataSource that supports distributed transactions. Distributed transactions are the subject of Chapter 16, which also describes TxDataSources and how to configure them.

The JDBC DataSource Factories are used by entity EJBs to produce database connections from a connection pool. To configure a JDBC DataSource Factory using the Administration Console, follow these steps:

In the description of the SELECT statement, it was noted that you should create smart queries to let the database server do most of the work for you. Stored procedures take the idea of putting the database to work a step further. A stored procedure is a method that is built into the database. From your application you call this procedure and can also pass parameters to it. There are some very significant advantages to using stored procedures:

The JDBC class CallableStatement is used to prepare and execute the stored procedure. The CallableStatement object is returned from the Connection object with the method prepareCall(). The parameter to prepareCall() is a String that identifies the stored procedure name and parameter list. Once the statement is prepared, invoke the execute() method to run the stored procedure.

Listing 13.9 shows a sample of a stored procedure that is written in Oracle’s stored procedure language. For this example we have added a price column to the books table. The parameter to this stored procedure is a book_name. The stored procedure will reduce the price of the book by 10%.

CREATE OR REPLACE PROCEDURE discount_book
(name IN VARCHAR,
pr IN OUT FLOAT) IS
BEGIN
SELECT price
INTO pr
FROM books
WHERE book_name = name;

pr := pr * 0.90;

UPDATE books
SET price = pr
WHERE book_name = name;
END;

Assuming con is an open JDBC Connection object, Listing 13.10 shows the JDBC code to call this stored procedure.

try {
    CallableStatement st = con.prepareCall( "{call discount_book[(?,?)]}");
    st.setString(1, MY_FAVORITE_BOOK);
    st.registerOutParameter(2, java.sql.Types.FLOAT);
    st.execute();
    System.out.println( "New price is " + st.getFloat(2);
}
catch( Exception x ) { x.printStackTrace();}

There are two distinct modes of processing data with a database. So far, we have dealt exclusively with online processing. This is basically an interactive process where a user is waiting for the results. Alternatively, batch processing is a set of transactions that are processed in the background. Most likely you are familiar with the term batch as used with computer applications. Listing 13.11 shows an example to set the price for a list of books using batch processing.

String[] titles = { "title_1", "title_2", "title_3" };
float[] prices = { 29.99, 7.98. 14.92 };
Statement st = con.createStatement();
for( int i = 0; i < titles.length; i++ ) {
    st.addBatch( "UPDATE books SET price = " + price[i] +
    "WHERE book_name = " + titles[i] );
}
int[] rows = st.executeBatch();

The return value from executeBatch() is an array of int containing each of the individual return values.

Meta-Data is a means whereby you can interrogate the schema of the database. The concept is very similar to reflection or runtime type identification in Java. JDBC meta-data is provided by two classes: java.sql.ResultSetMetaData and java.sql.DatabaseMetaData. The ResultSetMetaData class is obtained from a ResultSet object using the getMetaData() method. The executeQuery() method of Statement has been covered. In addition, the Statement class has an execute() method that can be used for an SQL command. This method returns true if the command returned a ResultSet, and false otherwise. The getResultSet() method on the Statement object is used to get the ResultSet. The information that can be derived from a ResultSetMetaData is

The getColumnCount() method returns the number of columns in the ResultSet. From this value, a for loop can be used to get specific information about individual columns. The getXXX() methods take an int column index as a parameter, which is 1 relative. Listing 13.12 shows an example of using ResultSetMetaData to list all the column names for a table.

private void dumpMetaData( String sqlStatement ) {
    try {
        Statement st = con.createStatement();
        if( st.execute( sqlStatement ) ) {
        ResultSet rs = st.getResultSet();
        ResultSetMetaData meta = rs.getMetaData();
        int count = rsmd.getColumnCount();
        for( int i = 1; i <= count; i++ )
            System.out.print( meta.getColumnLabel( i ) );
        System.out.println();
    } catch( Exception x ) { x.printStackTrace(); }
}

While ResultSetMetaData relates to a ResultSet, DatabaseMetaData relates to an open Connection. The type of information that can be derived from the DatabaseMetaData includes

As stated at the beginning of this chapter, the data in a relational database is defined by a schema. The DatabaseMetaData allows you to retrieve the schema which contains a ResultSet of catalog names. Each catalog name can be passed to the getTables() method of DatabaseMetaData along with table matching parameters to get the tables for that particular catalog. The API for java.sql.DatabaseMetaData provides methods to retrieve all the information described in the preceding list. Listing 13.13 uses DatabaseMetaData to retrieve the schemas for the database connection in a ResultSet.

private ResultSet getDatabaseTables( Connection con ) {
    ResultSet schemas = null;

    try {
        DatabaseMetaData metadata = con.getMetaData();
        schemas = metadata.getSchemas();
    } catch( SQLException x ) { x.printStackTrace(); }

    return schemas;
}