Understanding JDBC


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 :

  1. Load the JDBC driver.

  2. Get a Connection from the DriverManager .

  3. Create a Statement from the Connection .

  4. Execute an SQL command.

  5. Check the results.

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 DriverManager

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.

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

Category

Description

Type 1

These drivers use a bridging technology to create a gateway between JDBC and the API provided by the database. The JDBC-ODBC bridge is a Type 1 driver.

Type 2

These drivers place a Java wrapper on the native C or C++ library provided by the database. The client library from the database vendor must also be installed for Type 2 drivers to work.

Type 3

These drivers decouple the connection between the client and database, and serve as a proxy between the two systems, providing such features as load/transaction management, caching result sets, and pooling database connections (via a connection pool connection).

Type 3 JDBC drivers allow you to manage your database resources centrally in the WebLogic Server.

Type 4

These drivers are pure Java, using proprietary network protocols to communicate directly with the database server.

JDBC Drivers from BEA WebLogic

BEA 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

Driver Tier

Type and Name

Database Connectivity

Two-tier without support for distributed transactions (non-XA)

Type 2

WebLogic jDriver for Oracle Third-party drivers (requires native libraries)

Between WebLogic Server and DBMS in local transactions.

 

Type 4 (pure Java)

WebLogic jDrivers for Microsoft SQL Server Third-party drivers, including: Oracle Thin and Sybase jConnect

 

Two-tier with support for distributed transactions (XA)

Type 2

WebLogic jDriver for Oracle XA (requires native libraries)

Between WebLogic Server and DBMS in distributed transactions.

Multitier

Type 3

WebLogic RMI Driver

WebLogic Pool Driver

WebLogic JTS (not Type 3)

Between client and WebLogic Server (connection pool). The JTS driver is used in distributed transactions.

The Pool and JTS drivers are server-side only.

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

Type of Driver

Description

RMI Driver

Enables the utilization of load balancing and failover when configured in a cluster of WebLogic Servers.

Pool Driver

Enables the utilization of connection pools from server-side applications such as servlets or Enterprise Java Beans. The Pool Driver can be used with a standalone Java client in a simple two-tier application.

JTS Driver

Enables the utilization of distributed transactions across multiple servers with only one database instance.

Third-Party Drivers for WebLogic Server

JDBC 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 Object

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

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.

Table 13.6. Statement Interface Methods

Statement Method

Return Type

Description

executeUpdate()

int

The parameter is an SQL statement as a java.lang.String . The return value is the number of rows affected by the update. Used with CREATE , INSERT , DELETE , and UPDATE .

executeQuery()

ResultSet

The parameter is an SQL SELECT statement as a java.lang.String . The object returned is a ResultSet that allows you to parse through the selected rows of data.

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 Object

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.

Listing 13.2 Example Using 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 .

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 Object

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

Statement Method

Return Type

Column Type

getBoolean()

boolean

BIT

getByte()

byte

TINYINT

getShort()

short

SMALLINT

getInt()

int

INTEGER

getLong()

long

BIGINT

getFloat()

float

REAL

getDouble()

double

DOUBLE

getBytes()

byte[]

VARBINARY

getString()

java.lang.String

VARCHAR

getDate()

java.sql.Date

DATE

getTime()

java.sql.Time

TIME

getTimeStamp()

java.sql.Timestamp

TIMESTAMP

getBlob()

java.sql.Blob

BLOB

getClob()

java.sql.Clob

CLOB

getArray()

java.sql.Array

ARRAY

getRef()

java.sql.Ref

REF

getObject()

java.lang.Object

STRUCT

getBigDecimal

java.math.BigDecimal

NUMERIC

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 Types

The 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 Example

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:

  1. Load the JDBC-ODBC bridge.

  2. Get a Connection .

  3. Create a Statement .

  4. Create the tables.

  5. Insert data into the tables.

  6. Query the database by joining tables.

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


BEA WebLogic Platform 7
BEA WebLogic Platform 7
ISBN: 0789727129
EAN: 2147483647
Year: 2003
Pages: 360

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net