TOC
BACK
FORWARD
HOME

Java 1.1 Unleashed

-19-
The SQL Package

by Shelley Powers

IN THIS CHAPTER

  • The JDBC Architecture
  • The JDBC Drivers
  • The JDBC Classes
  • The JDBC Interfaces

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:

  • CallableStatement

  • Connection

  • DatabaseMetaData

  • Driver

  • PreparedStatement

  • ResultSet

  • ResultSetMetaData

  • Statement

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

The SQL package also provides several classes:

  • Date

  • DriverManager

  • DriverPropertyInfo

  • Time

  • TimeStamp

  • Types

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:

  • The JDBC-ODBC bridge. This type of driver uses the ODBC driver specific to the database. It also requires that the ODBC driver be available on the client machine running the Java application, and that the JDBC-ODBC binary code be installed where the system can access it. This approach is best reserved for intranet, local, or client/server-based data access.


  • Native-API drive     This type of driver uses Java to make calls to a database-access API on the client, which, in turn, provides connectivity to the database. The driver is only partially implemented in Java and depends on the API binary code being available on the client.


  • JDBC-Net drive     This type of driver uses the network protocols that come with the JDK to connect to a server. The server, in turn, translates the request into DBMS-specific transactions. This approach does not require any client-side binary code and implies that a middleware layer exists to process specific transactions.


  • Native protocol--All Java driver     This type of driver uses the DBMS network protocol to connect to the database. As with drivers from the JDBC-Net driver category, the drivers in the Native protocol--All Java drivers category also use Java exclusively.

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:

  • public static void deregisterDriver(Driver driver) throws SQLException This method removes driver from the list of those drivers currently available to the DriverManager.

  • public static synchronized Connection getConnection(Stringurl) throws SQLException This method creates a connection to the database specified in the url passed to the method. This method also forces the DriverManager to look for a driver that works with the URL specification.

  • public static synchronized Connection getConnection(Stringurl, Properties info) throws SQLException This method creates a connection to the database specified in the url passed to the method. This function also accepts properties in a tag-value format such as "user=username".

  • public static synchronized Connection getConnection(Stringurl, String user, String password) throws SQLException This method creates a connection to the database specified in the url passed to the method. The connection also passes as additional parameters user (the username) and password.

  • public static Driver getDriver(Stringurl) throws SQLException This method attempts to load a driver to match the url passed as a parameter.

  • public static Enumeration getDrivers( This method returns an enumeration of all loaded JDBC drivers. The names of the drivers can be accessed and printed using the methods provided with the Enumeration class.

  • public static int getLoginTimeout( This method returns the time a driver can wait to connect to the database.

  • public static PrintStream getLogStream( This method gets the logging/tracing of the PrintStream. You can use the PrintStream object to print information about the driver.

  • public static void println(Stringmessage) This method prints a string to the current logging stream. The logging stream is an output stream (to a file or standard output) used for logging messages or errors.

  • public static synchronized void registerDriver(Driverdriver) throws SQLException This method registers a newly created and loaded driver. It is called by the driver class itself.

  • public static void setLoginTimeout(intseconds) This method sets the timeout all drivers can wait to establish a connection to a database.

  • public static void setLogStream(PrintStreamout) This method sets the logging/tracing of the PrintStream. The PrintStream handles all output of logging and tracing calls using the println() method.
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).

  • public int getMinutes() This method returns the minutes value of the defined Date object.

  • public int getSeconds( This method returns the seconds value of the defined Date object.

  • public void setHours(inti) This method sets the hour value of the defined Date object.

  • public void setMinutes(inti) This method sets the minutes value of the defined Date object.

  • public void setSeconds(inti) This method sets the seconds value of the defined Date object.

  • public void setTime(longdate) This method sets the value of the Date object using the number of milliseconds since January 1, 1971.

  • public static Date valueOf(Strings) This method returns the date value given a string containing a date, such as "01/01/99".

  • public String toString( This method returns a formatted date string from the given Date object.

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:

  • public Time(int hour, int minute, int second)

  • public Time(long time)

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

  • public int getDate() This method returns the day of the month of the Time object.

  • public int getDay( This method returns the current day of the week of the Time object; the range is from 0 to 6, with Sunday being 0.

  • public int getMonth( This method returns the current month of the Time object; the range is from 0 to 11, with January being 0.

  • public int getYear()This method returns the current year of the defined Time object.

  • public int setDate(inti) This method sets the month of the date for the Time object.

  • public void setDay(inti) This method sets the day of the week for the defined Time object.

  • public void setMonth(inti) This method sets the month, with a value in the range from 0 to 11, with January being 0.

  • public void setYear(inti) This method sets the year for the Time object.

  • public static Time valueOf(Strings) This method returns the value of the specified string in time format ("hh:mm:ss").

  • public String toString( This method returns a formatted time string in JDBC escape syntax (for use directly with the databases) for the defined Time object.

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:

  • public boolean after(TimeStamp ts) This method returns true if the timestamp of the defined TimeStamp object is later than the timestamp argument.

  • public boolean before(TimeStampts) This method returns true if the timestamp of the defined TimeStamp object is earlier than the timestamp argument.

  • public boolean equals(TimeStampts) This method returns true if the timestamp and the timestamp argument are equal.

  • public int getNanos()This method returns the timestamp nanoseconds value of the TimeStamp object.

  • public void setNanos(inti) This method sets the timestamp nanoseconds value of the defined TimeStamp object.

  • public String toString( This method returns a formatted string derived from the TimeStamp object.

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:

  • public abstract boolean accceptsURL(String url) throws SQLException This method tests whether the driver can understand the protocol specified in the connection url.

  • public abstract Connection connect(Stringurl, Properties info) throws SQLException This method establishes a connection to the DBMS and returns a Connection object. The DriverManager class methods call this method for each driver loaded. If the driver cannot process the connection, it returns a null value.

  • public abstract int getMajorVersion( This method returns the driver's major version number.

  • public abstract int getMinorVersion( This method returns the driver's minor version number.

  • public abstract DriverPropertyInfo[] getPropertyInfo (Stringurl, Properties info) throws SQLException This method enables a user interface to query for which properties already exist for a connection and then to query for additional properties the connection may need.

  • public abstract boolean jdbcCompliant( This method is coded to return true if the driver developer determines that the driver is JDBC compliant; else it returns false.

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.

  • public abstract void clearWarnings() throws SQLException This method results in the getWarnings() method returning null until a new warning is generated for the current Connection object.

  • public abstract void close() throws SQLExceptio This method closes a database connection and releases JDBC resources without waiting for traditional garbage collection (when the program goes out of the Connection object's scope).

  • public abstract void commit() throws SQLExceptionThis method commits changes to the database and releases database locks. commit() applies only if auto commit has been turned off. Auto commit is a flag that can be set with the setAutoCommit() method. Auto commit means that every transaction is automatically committed if it is successful.

  • public abstract Statement createStatement() throws SQLExceptionThis method creates and returns a Statement object.

  • public abstract boolean getAutoCommit() throws SQLExceptionThis method gets the status of the auto commit.

  • public abstract String getCatalog() throws SQLExceptio This method returns the current catalog name. Catalogs are the names of the meta database (or database about the database) and contain information about the database objects themselves.

  • public abstract DatabaseMetaData getMetaData() throws SQLExceptio This method returns a DatabaseMetaData object with information about the database (such as stored procedures, SQL grammar, and so on).

  • public abstract int getTransactionIsolation() throws SQLExceptio This method gets the current transaction isolation mode. The transaction modes are listed in Table 19.1.

  • public abstract SQLWarning getWarnings() throws SQLExceptio This method returns the SQLWarning object associated with the first warning reported with the Connection. Additional warnings are chained onto the object.

  • public abstract boolean isClose() throws SQLExceptio This method tests to see whether the connection is still open.

  • public abstract boolean isReadOnly() throws SQLExceptio This method tests to see whether the database is read-only.

  • public abstract String nativeSQL(Stringsql) throws SQLException This method returns the native form of the SQL. Native form means that the SQL is converted by the database driver before being sent to the database.

  • public abstract CallableStatement prepareCall(Stringsql) throws SQLException This method processes the JDBC procedure call statement and returns a CallableStatement object.

  • public abstract PreparedStatement prepareStatement(Stringsql) throws SQLException This method processes a string that contains one or more ? placeholders and returns a PreparedStatement object.

  • public abstract void rollback() throws SQLExceptio This method rolls back all database changes that have occurred since the previous rollback or commit statement. Any database locks being maintained are released. This method is applicable only if auto commit is turned off.

  • public abstract void setAutoCommit(booleanautocommit) throws SQLException This method turns on or off the auto commit feature if the database allows.

  • public abstract void setCatalog(Stringcatalog) throws SQLException This method sets a subspace of the database catalog if this feature is supported in the database.

  • public abstract void setReadOnly(booleanreadonly) throws SQLException This method sets the read-only mode for succeeding transactions to enable some database optimizations.

  • public abstract void setTransactionIsolation(int level) throws SQLException This method attempts to change the transaction level using one of the transaction modes listed in Table 19.1.

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:

  • public abstract void cancel() throws SQLException In a multithreaded application, this method can be used to cancel the execution of a statement occurring in another thread.

  • public abstract void clearWarnings() throws SQLExceptio This method results in the getWarnings() method returning null until a new warning is generated for the defined Statement object.

  • public abstract void close() throws SQLExceptio This method releases the resources being maintained for the statement instead of waiting for them to be released with the standard garbage collection (when the Statement object falls out of scope).

  • public abstract boolean execute(Stringsql) throws SQLException This method is used when processing a SQL statement that may have multiple result sets, multiple update counts, or both.

  • public abstract ResultSet executeQuery(Stringsql) throws SQLException This method executes a SQL statement that returns only one result set. Additionally, the SQL statement is static (that is, it is not dynamically created at run time).

  • public abstract int executeUpdate(Stringsql) throws SQLException This method executes a SQL INSERT, UPDATE, or DELETE statement and returns a count of the rows impacted by the statement.

  • public abstract int getMaxFieldSize() throws SQLExceptio The value returned by this method is the maximum size of the data returned by any of the columns.

  • public abstract int getMaxRows() throws SQLExceptio The value returned by this method is the maximum number of rows that can be returned in a result set.

  • public abstract boolean getMoreResults() throws SQLExceptio This method tests to see whether the statement's next result contains a result set. It also closes the open result set.

  • public abstract int getQueryTimeout() throws SQLExceptio The value returned by this method is the number of seconds allowed for the statement to execute.

  • public abstract ResultSet getResultSet() throws SQLExceptio This method returns the result set for the execute statement if the current result is a result set; the method returns -1 otherwise.

  • public abstract int getUpdateCount() throws SQLExceptio This method returns the current results as a count; it returns -1 if there are no results or the current result is a result set.

  • public abstract SQLWarning getWarnings() throws SQLExceptionThis method returns the SQLWarning object with the first warning reported with the Statement. Additional warnings for the Statement object are appended to the SQLWarning object.

  • public abstract void setCursorName(Stringname) throws SQLException This method defines the cursor name used by each execute method call that follows.

  • public abstract void setEscapeProcessing(booleanenable) throws SQLException If escapes are processes, the driver will do the escape substitution before sending SQL. This method enables or disables escape processing.

  • public abstract void setMaxFieldSize(intmax) throws SQL Exception This method sets the maximum field size for any column that can be returned. You can set only certain data types such as VARCHAR, LONGVARCHAR, BINARY, VARBINARY, and CHAR fields.

  • public abstract void setMaxRows(intmax) throws SQLException This method sets the maximum number of rows that can be returned in a result set. Excess rows are discarded.

  • public abstract void setQueryTimeout(intseconds) throws SQLException This method sets how long a driver will wait for a query to execute.

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.

  • public abstract void clearParameters() throws SQLException This method clears all parameters currently in effect for the statement.

  • public abstract void setAsciiStream(intparameterindex, InputStream x, int length) throws SQLException This method inputs a parameter of LONGVARCHAR that can be very large. This method is more efficient and makes use of java.io.InputStream.

  • public abstract void setBignum (intparameterindex, Bignum x) throws SQLException This method sets a parameter to a type of java.lang.Bignum. Note that this class is currently being replaced with the java.math.BigDecimal and java.math.BigInteger classes.

  • public abstract void setBinaryStream(intparameterindex, InputStream x, int length) throws SQLException This method inputs a parameter of type LONGVARBINARY that can be quite large. This method is more efficient than using a LONGVARCHAR data type directly in a statement going to the database and makes use of java.io.InputStream class.

  • public abstract void setBoolean(intparameterindex, boolean b) throws SQLException This method sets a parameter to a boolean value.

  • public abstract void setByte(intparameterindex, byte x) throws SQLException This method sets a parameters to a Java byte value.

  • public abstract void setBytes(intparameterindex, byte x[]) throws SQLException This method sets a parameter to a Java array of bytes. Values convert to VARBINARY or ONGVARBINARY before reaching the database.

  • public abstract void setDate(intparameterindex, Date x) throws SQLException This method sets a parameter to a Java Date value.

  • public abstract void setDouble (intparameterindex, double x) throws SQLException This method sets a parameter to a Java double value.

  • public abstract void setFloat(intparameterindex, float f) throws SQLException This method sets a parameter to a Java float value.

  • public abstract void setInt (intparameterindex, int I) This method sets a parameter to a Java int value.

  • public abstract void setLong(intparameterindex, long l) This method sets a parameter to a Java long value.

  • public abstract void setNull(intparameterindex, int SqlType) throws SQLException This method sets a parameter to SQL NULL. The second argument is type SqlType as defined in java.sql.Types.

  • public abstract void setObject(intparameterindex, Object x) throws SQLException This method sets the parameter to an Object, which is a generic Java object that can be used to dynamically pass in any class or Java object as a parameter.

  • public abstract void setObject (intparameterindex, Object x, int targetsqltype) throws SQLException This method sets the parameter to an Object and provides the SQL type defined by java.sql.Types.

  • public abstract void setShort(intparameterindex, short s) throws SQLException This method sets a parameter to a Java short value.

  • public abstract void setString(intparameterindex, String s) throws SQLException This method sets a parameter to a Java string value.

  • public abstract void setTime(intparameterindex, Time t) throws SQLException This method sets a parameter to a Java Time value.

  • public abstract void setTimestamp(intparameterindex, TimeStamp tstmp) throws SQLException This method sets a parameter to a Java TimeStamp value.

  • public abstract void setUnicodeStream(int parameterindex, InputStream x, int length) throws SQLExceptio This method inputs a parameter of Unicode to LONGVARCHAR that can be very large. This method is more efficient than supplying a LONGVARCHAR data type directly, and makes use of java.io.InputStream class.

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:

  • public abstract boolean getBoolean(int parameterindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java boolean value.

  • public abstract byte getByte(intparameterindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java byte value.

  • public abstract byte[] getBytes(intparameterindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java byte array value.

  • public abstract Date getDate(intparameterindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java Date value.

  • public abstract double getDouble(intparameterindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java double value.

  • public abstract float getFloat(intparameterindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java float value.

  • public abstract int getInt(intparameterindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java int value.

  • public abstract long getLong(intparameterindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java long value.

  • public abstract Object getObject(intparameterindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java generic Object. This method can be used to dynamically access an object as any Java class.

  • public abstract short getShort(intparameterindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java short value.

  • public abstract String getString(intparameterindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java string value.

  • public abstract Time getTime(intparameterindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java Time value.

  • public abstract Timestamp getTimestamp(intparameterindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java TimeStamp value.

  • public abstract void registerOutParameter(intparameterindex, int SqlType) throws SQLException This method must be called for each parameter to register the java.sql.Type of the parameter.

  • public abstract void registerOutParameter(intparameterindex, int SqlType, int scale) throws SQLException This method registers Numeric or Decimal java.sql.Type parameters.

  • public abstract boolean wasNull() throws SQLException This method is used to report whether the last parameter was SQL NULL. This method is used in combination with the appropriate get OUT parameter method.

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.

  • public abstract void clearWarnings() throws SQLException This method results in the getWarnings() method returning null until a new warning is generated for the result set

  • public abstract void close() throws SQLExceptionThis method releases the resources being maintained for the result set rather than waiting for them to be released with the standard garbage collection (when the result set object falls out of scope).

  • public abstract int findColumn(Stringcolname) throws SQLException This method returns the column index of the specified column name beginning with column number 1.

  • public abstract InputStream getAsciiStream(Stringcolname) throws SQLException This method returns ASCII characters as a stream. All the data for the column must be read before proceeding to another column.

  • public abstract InputStream getBinaryStream(intcolumnindex) throws SQLException This method returns a stream that can then be read in chunks. The index provides the location of the column in which the binary stream is located.

  • public abstract InputStream getBinaryStream(Stringcolname) throws SQLException This method returns a stream that can then be read in chunks. The name of the column is passed to the method.

  • public abstract boolean getBoolean(intcolumnindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java boolean value, given the column index.

  • public abstract boolean getBoolean(stringcolname) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java boolean, given the column name.

  • public abstract byte getByte (intcolumnindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java byte value, given the column index.

  • public abstract byte getByte (Stringcolname) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java byte value, given the column name.

  • public abstract byte[] getBytes (intcolumnindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java byte array, given the column index.

  • public abstract byte[] getBytes (Stringcolname) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java byte array, given the column name.

  • public abstract String getCursorName()throws SQLExceptio This method returns the name of the cursor being used by the result set.

  • public abstract Date getDate(intcolumnindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java Date value, given the column index.

  • public abstract Date getDate(Stringcolname) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java Date value, given the column name.

  • public abstract double getDouble(intcolumnindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java double value, given the column index.

  • public abstract double getDouble(Stringcolname) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java double value, given the column name.

  • public abstract float getFloat(intcolumnindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java float value, given the column index.

  • public abstract float getFloat(Stringcolname) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java float value, given the column name.

  • public abstract int getInt(intcolumnindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java int value, given the column index.

  • public abstract int getInt(Stringcolname) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java int value, given the column name.

  • public abstract long getLong(intcolumnindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java long value, given the column index.

  • public abstract long getLong(Stringcolname) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java long value, given the column name.

  • public abstract Object getObject(intcolumnindex) throws SQLException This method returns a java.lang.Object type and can also be used to handle abstract SQL data types, given a column index.

  • public abstract Object getObject(Stringcolname) throws SQLException This method returns a java.lang.Object type and can also be used to handle abstract SQL data types, given a column name.

  • public abstract short getShort(intcolumnindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java short value, given a column index.

  • public abstract short getShort(Stringcolname) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java short value, given a column name.

  • public abstract String getString(intcolumnindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java string value, given a column index.

  • public abstract String getString(Stringcolname) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java string value, given a column name.

  • public abstract Time getTime(intcolumnindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java Time value, given a column index.

  • public abstract Time getTime(Stringcolname) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java Time value, given a column name.

  • public abstract TimeStamp getTimestamp(intcolumnindex) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java TimeStamp value, given a column index.

  • public abstract TimeStamp getTimestamp(Stringcolname) throws SQLException This method gets the value of the column indicated by the index and returns it as a Java TimeStamp value, given a column name.

  • public abstract InputStream getUnicodeStream(intcolumnindex) throws SQLException This method returns a stream of Unicode characters that can then be read in chunks, given a column index.

  • public abstract long getLong(Stringcolname) throws SQLException This method returns a stream of Unicode characters that can then be read in chunks, given a column name.

  • public abstract SQLWarning getWarnings() throws SQLExceptio This method returns the SQLWarning object with the first warning reported with the result set object. Additional warnings are then chained to this SQLWarning object.

  • public abstract boolean next() throws SQLExceptio Calls to next() move the position of the current row of the result set; returns false when no more rows can be read. This method also implicitly closes the input stream from the previous row.

  • public abstract boolean wasNull() throws SQLException This method is used to report whether the column contains a SQL NULL. This method is used in combination with the appropriate get method.

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.