Advanced JDBC Topics

This chapter provided an overview of the JDBC Core API. The topics addressed the needs of the majority of database applications. The PreparedStatement object introduced in this chapter is just one of many topics for advanced JDBC. The advanced needs for JDBC focus on extended functionality. The JDBC Core API offers everything that most programmers require. The additional extended functionality provided by JDBC includes

• Stored Procedures

• Batch Processing

• Meta-Data

Stored Procedures

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

• Most database engines pre-compile stored procedures, therefore, they run much faster than dynamic SQL.

• Errors in the procedure can be identified at compile time rather than runtime.

• The application developer only needs to know the name of the procedure and its input and output parameters. Since the implementation is hidden, you do not need to know the schema of the data being accessed.

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

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

Listing 13.9 Stored Procedure Example

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

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

Listing 13.10 Java Source Calling a Stored Procedure Example

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

Batch Processing

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

Listing 13.11 Java Source for Batch Processing Example

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

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

Meta-Data

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

• The number of columns in ResultSet

• Case sensitivity of the column names

• Which columns are searchable

• Whether null is allowed as a column value

• The maximum display size of a given column

• The name of a given column

• The name of the table that a given column came from

• The data type of a given column

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

Listing 13.12 Java Source for Meta-Data Example

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

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

• User-visible tables

• The username being used

• Whether the database connection is read-only

• Database proprietary keywords

• Whether there is support for column aliasing

• Whether outer joins are supported

• The primary keys for a table

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

Listing 13.13 Java Source for Database Meta-Data Example

 private ResultSet getDatabaseTables( Connection con ) {     ResultSet schemas = null;     try {         DatabaseMetaData metadata = con.getMetaData();         schemas = metadata.getSchemas();     } catch( SQLException x ) { x.printStackTrace(); }     return schemas; }