The JDBC Core API is straightforward and intuitive. A simple application that queries a database is basically a five-step process. The steps are as follows :
The primary JDBC classes and interfaces used by the application are DriverManager , Connection , Statement , and ResultSet . They are located in the java.sql package. The distributed processing and performance requirements of J2EE applications require additional capabilities and features for the database connectivity. An entity EJB may perform hundreds of database accesses per minute. The database architecture may also be implemented across a cluster of database storage providers. The JDBC 2.0 Optional Package addressed these issues and provided support for distributed transactions and connection pooling. Once again, the client application is freed from the details of the database implementation. As with all database-specific details, this is encapsulated within the JDBC driver. The one task you must be aware of is how to choose the proper JDBC driver to meet your needs. To support distributed transactions and connection pooling, you must select a Type 3 multi- tier JDBC driver in conjunction with a Type 1, 2 or 4 driver to access the back-end database. Connection pools can be utilized from HTTP servlets or EJBs using the WebLogic Pool Driver. A standalone Java client must use the WebLogic RMI Driver in order to use connection pools. Distributed transactions require the WebLogic JTS Driver. The JDBC driver selection is explained further in the "Using the DriverManager" section. Using the DriverManagerThe 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. Listing 13.1 Loading the JDBC-ODBC Bridge// 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. Table 13.3. Categories of JDBC Drivers
JDBC Drivers from BEA WebLogicBEA WebLogic provides JDBC drivers that are much more reliable and robust than the JDBC-ODBC bridge. The WebLogic drivers offer much higher performance because they do not translate between JDBC and ODBC; they interact with the database directly. The WebLogic drivers are marketed as the jDriver line of JDBC drivers. Instead of loading the JDBC-ODBC bridge, you create a static instance of the WebLogic database driver: // create a static instance of the Weblogic JDBC driver static { new Weblogic.jdbc.jts.Driver(); } BEA WebLogic offers JDBC drivers of types 2 and 3. The complete list of JDBC driver types provided by BEA WebLogic is shown in Table 13.4. Tip When you're choosing a driver, your first decision depends on the ability to connect to the database you intend to use. Second, the performance and features offered by the JDBC driver determine which driver best suits your needs. Table 13.4. JDBC Drivers Provided by BEA WebLogic
Like all Type 2 drivers, the Oracle client installation is required for this JDBC driver. The WebLogic jDrivers for MS SQL Server, Informix, and Sybase also are included with the WebLogic Server distribution. Being Type 4 drivers, they offer a pure Java interface into the database. The Type 3 Multitier JDBC drivers come in three flavors: RMI Driver, Pool Driver, and JTS Driver. As mentioned previously, Type 3 drivers support distributed transactions and connection pooling. A brief description of each driver in Table 13.5 shows its strongest points. Table 13.5. Three Multitier JDBC Drivers
Third-Party Drivers for WebLogic ServerJDBC drivers from third-party vendors can be configured for WebLogic Server. They are the Oracle Thin Driver and the Sybase jConnect Driver. Both are pure Java, Type 4, JDBC drivers that provide connectivity to their respective databases. These drivers come bundled with the WebLogic Server distribution. The Connection ObjectThe 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 ObjectThe 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. Table 13.6. Statement Interface Methods
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 PreparedStatement ObjectThe 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. Listing 13.2 Example Using INSERT to Add Elements into a Tableprivate 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 . Listing 13.3 Rewrite of the update() Method Using the 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; } Caution The table name must be explicitly stated in the PreparedStatement and cannot be a variable. The ResultSet ObjectA 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 get XXX() method for each type of column data. You therefore must know what type of data is stored in the column and use the appropriate get XXX() method. All the get XXX() 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. Table 13.7. The getXXX() Methods
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. Listing 13.4 Boolean Expression of a while() Statement// 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" ); } Working with BLOB and CLOB Data TypesThe ResultSet interface includes the two methods getBlob() for accessing the data in a column of type BLOB , and getClob() for accessing the data in a column of type CLOB . The return value from these methods is a java.sql.Blob or java.sql.Clob object, respectively. The APIs for BLOB and CLOB are similar; both allow the query result data to be accessed in chunks or as a data stream. The difference is that BLOB accesses the data as an array of bytes, and CLOB accesses the data as an array of characters . In the example shown in Listing 13.5, you will add a column to the Authors table that contains a JPG image of the author. This data value is stored in the database as a large amount of binary data. The BLOB datatype is used as the column type for the table to contain the JPG image data. The SQL INSERT command stores the image data into the BLOB using PreparedStatement . Listing 13.5 Example of SQL INSERT Command Storing Image Data into the BLOB Using PreparedStatement /** 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(); } To retrieve the BLOB data from the database, you must execute a SELECT command that returns the BLOB column in the ResultSet (see Listing 13.6). From the ResultSet , get the JDBC BLOB object. The API for java.sql.Blob provides the methods getBytes() , to retrieve the data as a byte[] , and getBinaryStream() , to read the data from an InputStream . Listing 13.6 Retrieving an Image from a Database/** 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 ); } The Bookstore JDBC ExampleA 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:
Listing 13.7 Java Source for Bookstore Example/** 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() ); } } |