TOC BACK FORWARD HOME

Java 1.1 Unleashed

-19-
The SQL Package

by Shelley Powers

IN THIS CHAPTER

When the Java Developer's Kit (JDK) was first released in January 1996, it did not include any classes for relational database access. For many of us who had hoped Java would enable database access on the Web, this omission was a disappointment. However, in February 1996, JavaSoft announced the JDBC (Java Database Connectivity) API, which was eventually released as a set of classes and interfaces under the package name java.sql in the JDK ver- sion 1.1. This chapter provides an overview of the JDBC package.

NOTE: The remainder of this chapter refers to the java.sql package as the JDBC.

This chapter provides an overview of the JDBC classes and interfaces. For a more detailed look at the JDBC--including several complete examples--refer to these chapters: Chapter 42, "Databases and Java," Chapter 43, "Getting Started with JDBC," and Chapter 44 "Building Database Applets with JDBC."

The JDBC Architecture

The JDBC is implemented in two specific layers: the driver layer and the business layer. The driver layer consists of specific implementations of interfaces (provided by the developer of the driver) that work with a specific relational database. The business layer is the JDBC DriverManager and the classes provided by the JDBC driver to connect with a particular business database. Based on this DBMS (Database Management System) abstraction, if the underlying database changes, and there is a JDBC-compliant driver for the new database, the developer can switch the applet to the new database with very little change to existing code. This abstraction also enables the developer to connect to and access data from multiple heterogeneous databases at the same time using the same methods and techniques.

In addition to providing a database abstraction layer, the JDBC also hides lower level implementation details from the developer. Instead of having to create and manage sockets and input and output streams, the developer can issue one call to create a connection, one call to issue a query, and one or more simple calls to manipulate the results.

The real key to the JDBC is the DriverManager, a class that queries the type of driver being used and pulls in the driver-specific classes. Once this is done, the job of the DriverManager class is pretty much done; the driver itself has control of all activity at that point, including connecting to the database, issuing a transaction, and processing a result. The DriverManager and JDBC drivers are covered in more detail later in this chapter.

The JDBC Members

The JDBC consists of the following interfaces:

As mentioned in Chapter 5, "Classes, Packages, and Interfaces," interfaces are abstract classes with no implementation methods.

The SQL package also provides several classes:

The most important of these classes is DriverManager.

The JDBC Drivers

The JDBC drivers are implementations of JDBC that are specific to a database or middleware layer. JavaSoft has categorized drivers into four categories:

Drivers that require binary code such as ODBC drivers or native APIs are not effective for Internet use and should be used exclusively in corporate intranets, Java applications that connect to a local database, or in a client/server environment. Pure Java JDBC drivers such as the last two in the preceding list are very effective for use in internets.

Driver developers provide implementations of the JDBC interfaces specifically for the DBMS or middleware layer. As an example, a Java application developer can connect to an mSQL database using the following code:

Class.forName("imaginary.sql.iMsqlDriver");
String url = "jdbc:msql://yasd.com:1112/yasd";
Connection con = DriverManager.getConnection(url);
Statement stmt = con.createStatement();

This code loads the Imaginary mSQL driver written by Darryl Reese. If the driver is not located on the client machine, it is downloaded from the Web server. A connection string is then created that contains the type of database (mSQL), the host and port of the database, and the name of the database. This string can also contain any required user name and password to connect to the database. The connection made by this code is to an mSQL database located on a UNIX box and accessed remotely.

TIP: The mSQL JDBC driver can be found on the CD-ROM that comes with this book. An evaluation copy of mSQL can be downloaded from the Hughes Technologies home site at http://www.hughes.com.au/.

After the connection string is created, it is used with the DriverManager class to create the database connection. This connection is then used to make a JDBC SQL object.

The beauty of the JDBC becomes apparent when you compare the preceding code using a pure Java-based JDBC driver with the following code using the JDBC-ODBC bridge to connect to an ODBC database:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:zoo";
Connection con = DriverManager.getConnection(url);
Statement stmt = con.createStatement();

Aside from differences in the use of the forName() method and how the connection string is formed, there are virtually no differences between this code and the preceding code. However, this second bit of code generates a connection to a local Access database using an ODBC driver and the JDBC-ODBC bridge.

Without using some form of trusted applet (described in Chapter 8, "Introduction to Applet Programming"), the second piece of code cannot be used by an applet downloaded from a Web server. This code uses the JDBC-ODBC driver, which uses native method calls to access the ODBC driver. Calls to native methods normally violate the security standards currently implemented by most browsers. Additionally, the second approach requires that the JDBC-ODBC driver binary code be installed on the client, and that the ODBC driver for the database be installed and configured for use with the database.

The first code sample, however, uses a driver created purely in Java and that has no requirements of the client.

In spite of these differences, the code itself is very similar and the driver and connectivity issues are transparent to the developer.

The JDBC Classes

The JDBC classes are discussed in the following sections. The most important class is the DriverManager class, which controls the loading of the driver classes.

The DriverManager Class

The DriverManager class controls the loading of driver-specific classes. The classes can be loaded using a system variable to specify the drivers to be loaded; they can also be loaded dynamically at run time.

To load classes for a driver at run time, you typically use the following command:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

This code snippet uses the standard forName() method to force the loading of the classes specified in the string passed to the method. If the classes cannot be located locally using the CLASSPATH system variable, they are downloaded from the Web server using the same class loader that downloaded the applet.

Another method to load driver classes at run time is to create a new instance of the object. This technique also looks for the driver classes locally and then looks to the Web server if the classes are not found:

new imaginary.sql.iMsqlDriver();
CAUTION: If you use the new instance method to load a JDBC driver, the method may not work correctly with the DriverManager class. If you use this method, it may not correctly trigger the class loader to download the classes. To load driver classes accurately, use the forName() method.

More than one driver can be loaded at a time; the DriverManager class methods are responsible for determining which driver's classes to use to establish the database connection. The string passed to the Connection() method makes this determination. Each driver requires that the string used in the getConnection() method follows a pattern specific to that particular driver:

// JDBC-ODBC driver
String url = "jdbc:odbc:zoo";
Connection con = DriverManager.getConnection(url);
// mSQL-JDBC driver
String url = "jdbc:msql://yasd.com:1112/test";
Connection con = DriverManager.getConnection(url);

The applet or application developer uses some of the methods provided by the DriverManager class, and the driver developer uses other methods. The call to getConnection() is an example of a method called from the Java application. An example of a method called from the driver is registerDriver(), which registers a newly loaded driver class using the DriverManager class methods. The following code would most likely be called from the constructor of the driver class:

java.sql.DriverManager.registerDriver(this);

Other methods provided by the DriverManager class are listed here:

TIP: In the preceding list of methods, the synchronized modifier means that the method is thread safe. A thread-safe method is one that forces threads to enter the method one thread at a time; the method blocks entry to other threads until the first thread is through.

The Date Type Classes

There are three date classes in the JDBC: Date, Time, and TimeStamp. Each of these classes is basically a wrapper around the associated java.util.Date class. Creating a wrapper extends the base object methods to work with SQL.

The java.util.Date object provides both date and time. SQL, however, requires a separate date value, a separate time value, and a timestamp value that includes date, time, and a fractional second value in nanoseconds.

The JDBC date classes provide methods that get and set values in the associated class objects, and that convert those values to different data types and formats.

The Date Class

The Date class can be created using a single long value that represents the number of milli-seconds since January 1, 1997; it can also be created by providing three integers that represent the year, month, and date:

java.sql.Date somedate;
somedate = new java.sql.Date(9,0,20);

The preceding statements create a Date object that is equivalent to "January 20, 1909". Once the object has been created, you can access the Date class's methods to modify, access, and convert the date.

Modify the date value using one of the set override methods by using a statement like this:

somedate.setHours(20);

The other set methods are listed a little later in this section. You can also convert a SQL date to a formatted string. The following statement produces a string that is equivalent to the date value and that is formatted in the pattern "YYYY-MM-DD":

String somestring = somedate.toString();

To create a SQL date value from a string, use the following statement:

somedate = somedate.valueOf("1998-01-01");

The valueOf() method converts the string date to a SQL-specific date, converting it into a format that is understandable by the target DBMS.

TIP: When creating a Date object, note that the value for the year is equal to the year minus 1900. For example, if you want the value "1978", enter "78". A required value of "2009" is entered as "109".

Using the valueOf() and toString() methods, the developer can convert easily between Java and SQL dates. The Date methods and their arguments follow:

n public int getHours() This method returns hours. The get methods return a value based on the unit specified (in this example, the get method returns the hours), and how the Date object was created (for example, using local time).

The Time Class

The Time class extends the java.util.Date object to identify SQL time. As does the JDBC Date class, the Time class also provides for conversions between time values in Java and those applicable to a DBMS.

There are two constructors for the Time class:

Once a time object has been instantiated, the following methods can be used with it:

The TimeStamp Class

When you work with relational databases, you are aware of the timestamp data type. Database developers and administrators use timestamps in most (if not all) of their tables--to mark when a table row has been inserted, when values in a row have been changed, or both.

The TimeStamp class extends the java.util.Date class to include the concept of date, time, and a fractional second value.

The TimeStamp object can be created with a long timestamp value or by providing separate values for each component of the timestamp:

Timestamp newdate = new Timestamp(97,0,1,12,12,20,10);

This statement creates a new TimeStamp object and sets its value to "January 1, 1997 12:12:20.00000001". As you can with the JDBC Date class, you can use the TimeStamp class to set database field values and convert the timestamp values returning from a database into a string you can display:

Timestamp origtimestamp = new Timestamp(97,0,1,12,12,20,10);
Timestamp newtimestamp = origtimestamp.valueOf("1998-03-01 10:10:10.00000001");
String stimestamp = newtimestamp.toString();
g.drawString(stimestamp, 20,20);

This code creates a new TimeStamp object, which is then used to create another TimeStamp object using the valueOf() method and a string representing a new value. This value is then converted back to a string using toString(). The new string is displayed using Graphics.drawString(). This timestamp value would display as follows:

1998-03-01 10:10:10.00000001

The TimeStamp class has the following methods:

Of course, you do not use the JDBC Date, Time, and TimeStamp classes to convert values back and forth between strings and dates. However, the classes are very useful for displaying date values pulled from a database and for inserting date values retrieved from a Java input field. These techniques are demonstrated in the chapters in Part IX of this book, "Java Databases."

The DriverPropertyInfo Class

The DriverPropertyInfo class helps advanced developers provide information to a driver for a connection. Java application developers are not required to use this class; it is included here to complete the coverage of the SQL package classes rather than as a suggestion that you use it.

If you do not have the database connection information during development, but it can be provided by the user at runtime, the DriverPropertyInfo class provides the way to prompt the user for name-value pairs that represent connection-specific properties. The DriverPropertyInfo class is used with the Driver interface's getPropertyInfo() method to determine which values have been specified and which are still needed to establish the connection.

No methods are associated with this class. Its constructor requires two strings: the property name and the property value. Here is an example:

DriverPropertyInfo somevalue = new DriverPropertyInfo("password", "somepassword");

The Types Class

The last of the JDBC classes is Types, which defines constants that identify SQL types. These constants are then used to map between the SQL data type and its associated Java data type.

The types are defined as follows:

public final static int BIGINT

Each of the types is defined with the following modifiers: public (accessible by all classes), final (the value is constant), and static (the value is the same for all objects). The types currently defined are listed here:

SQL Type Description of Java Data Type
BIGINT Mapped to a Java long
BINARY Mapped to a byte array
BIT Mapped to a Java boolean
CHAR Mapped to a Java String
DATE Mapped to the JDBC Date class
DECIMAL Mapped to the class java.math.BigDecimal
DOUBLE Mapped to a double
FLOAT Mapped to a double
INTEGER Mapped to an int
LONGVARBINARY Mapped to a byte array
LONGVARCHAR Mapped to a Java String
NULL No mapping provided
NUMERIC Mapped to the java.math.BigDecimal class
OTHER Mapped using getObject() and setObject()
REAL Mapped to a float
SMALLINT Mapped to a short
TIME Mapped to the java.sql.Time class
TIMESTAMP Mapped to the java.sql.TimeStamp class
TINYINT Mapped to a byte
VARBINARY Mapped to a byte array
VARCHAR Mapped to a Java String

NOTE: As this book went to press, the JDK 1.1 was changing, resulting in a modification to the JDBC. The java.lang.Bignum class was being replaced with the java.math.BigDecimal and java.math.BigInteger classes.

The JDBC Interfaces

The JDBC interfaces are abstract classes implemented by developers who are building JDBC drivers. Interfaces have no implementations themselves; they are used to derive the driver classes. Once the derived classes are implemented, the applet or application developer can use these classes to connect to a database, issue a transaction, and process a result set without being concerned about the underlying database.

The Driver Interface

The Driver interface provides the methods to establish a connection to a specific DBMS or DBMS middleware layer. Driver is usually a small class; it contains only what is necessary to register the driver using the DriverManager class methods to test whether the driver can process the connection URL, to get driver properties if the connection information is being processed dynamically, and to connect to the database.

Here are the methods provided with the Driver interface and implemented by the driver:

NOTE: JDBC-compliant drivers are drivers that implement the full set of JDBC functionality and are developed for a DBMS that is SQL 92-entry level compliant. The mSQL-JDBC driver (mentioned earlier in this chapter and provided on the CD-ROM that accompanies this book) is not JDBC compliant because the mSQL database is not SQL 92-entry compliant.

The Connection Interface

The Connection interface is used by driver developers to create a DBMS-specific implementation of the Connection class that is, in turn, used by the applet or application developer to establish a database connection. Statements are created using the createStatement() and PrepareStatement() methods; result sets are created using specific methods provided with the Statement, PreparedStatement, and CallableStatement classes.

Each of the methods defined for the Connection interface has an associated implementation in the driver. An example of how this happens is shown here:

public class iSomeDriverConnection implements java.sql.Connection
{
  ...
  public Statement createStatement() throws SQLException {
    // code to implement createStatement for driver
    ...
  }


}

The driver implements the java.sql.Connection and provides implementations for each of the classes, as shown here with the createStatement() implementation.

The applet or application developer uses the driver-specific Connection class to create statements and process result sets:

new imaginary.sql.iMsqlDriver();
java.sql.Connection con = java.sql.DriverManager.getConnection(
                          "jdbc:msql://yasd.com:1112/test");
java.sql.Statement stmt = con.createStatement();
String stringSelect = "Select retail_item.item_nbr from retail_item";
java.sql.ResultSet rs = stmt.executeQuery(stringSelect);
NOTE: If the import java.sql.* statement is issued at the beginning of the applet or application, the java.sql statement modifiers are not necessary for each object.

The Connection interface methods listed here are those defined for the interface. The driver must redefine the methods as nonabstract classes with the same parameters and return types.

Several variables are defined for the Connection class that you can use to set the transaction level for the database (if this is allowed by the DBMS). Table 19.1 lists these transaction modes.

Table 19.1. Transaction modes.

Variable Name Description
TRANSACTION_NONE Transactions are not supported
TRANSACTION_READ_COMMITTED Prevents dirty reads but allows nonrepeatable and phantom reads
TRANSACTION_READ_UNCOMMITTED Allows dirty, nonrepeatable, and phantom reads
TRANSACTION_REPEATABLE_READ Prevents dirty and nonrepeatable reads but allows phantom reads
TRANSACTION_SERIALIZABLE Prevents dirty, nonrepeatable, and phantom reads


Chapter 42, "Databases and Java," details what these transaction levels mean.

The Statement Classes

The JDBC provides three different statement classes: PreparedStatement, CallableStatement, and Statement. The Statement class is the simplest; it is used primarily for static SQL statements that do not result in multiple result sets or update counts. The PreparedStatement class is used to compile a SQL statement and invoke the compiled statement multiple times; PreparedStatement is an extension of the Statement class. The CallableStatement class is used to invoke a stored procedure call that may return multiple result sets or update counts; CallableStatement is an extension of the PreparedStatement class.

The Statement Class

The Statement class is used for SQL statements that are executed only once and that do not have to pass parameters with the statement. Some of the SQL statement types this class can process are those that perform one-time queries that process a static SQL statement:

Connection con = DriverManager.getConnection(someurl);
Statement stmt = con.createStatement();
String stmt = "Select * from retail_item where retail_item_nbr =  " + 
               somejavavariable;
ResultSet rs = stmt.executeQuery(stmt);

The statement being executed does not process any input variables and passes a hard-coded SQL statement to the executeQuery() method. The statement may or may not contain references to Java variables. A ResultSet class is returned from the executeQuery() method call.

A statement may return multiple result sets when the statement executes a stored procedure that performs several statements (a statement may also return multiple result sets for some other reason). When the SQL statement is unknown, the results are unknown. When this is the case, the developer must use the execute statement and then process the result sets by using the getResultSet(), getUpdateCount(), and getMoreResults() methods. An example of this situation is presented in Chapter 43, "Getting Started with JDBC."

The SQL statement can be an update statement such as this:

Connection con = DriverManager.getConnection(someurl);
Statement stmt = con.createStatement();
int i = stmt.executeUpdate("Insert into company_codes(company_cd, company_desc) 
                            values('" + value1 + "','" + value2 + "')");

This statement inserts a row into the table company_codes. This type of statement can also be used to update or delete from a database table.

The methods defined for the Statement interface are listed here:

The PreparedStatement Class

The PreparedStatement class allows the developer to create a SQL statement that is compiled and stored in the database. The statement can then be efficiently invoked several times, passing in parameters for each execution.

Here is an example of the use of a PreparedStatement:

ResultSet rs;
Connection con = DriverManager.getConnection(someurl);
PreparedStatement prepstmt = con.prepareStatement("Select var1,     
   var2 from sometable where varx = ?");
for (int i = 1; i < 10; i++) {
   rs = prepstmt.executeQuery(prepstmt);
   // process rs


}

The PreparedStatement class contains the same methods used in the Statement class and are not repeated here. However, this class has some additional methods. The set methods listed here set a parameter to the named Java value. This parameter is then converted to the appropriate database type based on the SQL-to-Java type mapping listed earlier in this chapter.

The CallableStatement Class

The CallableStatement class is an extension of the PreparedStatement class with the added capability of processing stored procedure results. The methods defined for this class allow the developer to pull different database types from a result set using a series of functions that convert the SQL type to the appropriate Java type:

The ResultSet Class

After a connection is opened and a statement is created and executed, you will most likely want to process the return values from a query; the ResultSet class is the object to use.

The following code creates a connection, issues a statement, executes a simple query, and then processes the results:

Connection con = DriverManager.getConnection(someurl);
Statement stmt = con.createStatement();
String stmt = "Select item_desc, item_cat from retail_item where retail_item_nbr = 
              "+ somejavavariable;
ResultSet rs = stmt.executeQuery(stmt);
String stringResult = "";
while (rs.next()) {
   tmpString = "Item Description is " + rs.getObject(1) + "\n";
   stringResult+=tmpString;
   tmpString = "Item Category is " + rs.getObject(2) + "\n";
   stringResult+=tmpString;
}
resultsTextArea.setText(stringResult);

As you can see, the ResultSet class provides methods that allow you to access the individual fields for each row returned from a query. A ResultSet object also maintains a cursor pointing to the row currently being accessed. The get methods defined for this type of object return the Java data type specified in the method, applying the appropriate SQL-data-type to Java-data-type conversion approach.

The ResultSetMetaData and DatabaseMetaData Classes

The remaining JDBC interfaces are the ResultSetMetaData and DatabaseMetaData classes. The ResultSetMetaData class provides information about properties of the columns in a result set. The DatabaseMetaData class provides information about the database itself. Because neither of these classes is commonly used when developing Java applets or applications, the individual methods and variables for each of the interfaces are not listed here; they are listed instead in Chapter 42, "Databases and Java."

Summary

The JDBC is a powerful mechanism that allows Java applets to provide database access over an internet or intranet. The JDBC is also valuable when you are using Java to create local or client/server applications that have to access one or more databases.

This chapter provided an overview of the JDBC classes and interfaces as well as a description of the multiple-layer approach to using JDBC. Brief examples of some of the more commonly used JDBC classes were given, and descriptions of most of the JDBC methods were provided.

For more detailed information and complete code examples, refer to the chapters in Part IX of this book, "Java Databases": Chapter 42, "Databases and Java," provides a detailed description of the way Java accesses databases, gives sample code for database utility tools, and discusses some non-JDBC database techniques currently being used with Java. Chapter 43, "Getting Started with JDBC," provides several examples that use JDBC to create Java applications, including a more thorough look at the different statement types. Chapter 44, "Building Database Applets with JDBC," provides code for several applets that use the JDBC to both query and update a database.

TOCBACKFORWARDHOME


©Copyright, Macmillan Computer Publishing. All rights reserved.