Professional Java.JDK.5.Edition (Wrox)

Chapter 6

DatabaseMetaData dbmData = cConn.getMetaData();

// Make sure our driver supports batch updates if (dbmData.supportsBatchUpdates()) {

Statement sStatement = cConn.createStatement();

// Add batch commands

sStatement.addBatch(“INSERT INTO TEAMS VALUES (“‘Joon Lee’)”); sStatement.addBatch(“INSERT INTO TEAMS VALUES (‘Jennie Vitale’)”); sStatement.addBatch(“INSERT INTO TEAMS VALUES (‘Kyle Rice’)”); sStatement.addBatch(“INSERT INTO TEAMS VALUES (‘Steve Brockman’)”); sStatement.addBatch(“INSERT INTO TEAMS VALUES (‘Arnie Voketaitis’)”);

int []uCounts = sStatement.executeBatch();

// Commit the changes cConn.commit();

} else {

System.err.print(“Your driver does not support batch updates!”);

}

}catch(BatchUpdateException batchEx) {

int []uCounts = batchEx.getUpdateCounts(); for (int i = 0; i < uCounts.length; i ++) {

System.err.print(“Count #” + i + “=” + uCounts[i] + “\n”);

}

// Handle errors further here if necessary

}

Creating Batch Updates Using a PreparedStatement Object

The PreparedStatement object batch updates follow mostly the same method of operations as the Statement object batch updates, with the exception that you now have to deal with parameterized SQL statements and setting each parameter before adding a batch command. So for each command you will need to set the necessary IN parameter before issuing a PreparedStatement.addBatch call. The following code example shows how to correctly add batch commands to a PreparedStatement object:

try {

//Make sure that autocommit is off cConn.setAutoCommit(false);

//Retrieve metadata info about the data source DatabaseMetaData dbmData = cConn.getMetaData();

//Make sure our driver supports batch updates if (dbmData.supportsBatchUpdates()) {

PreparedStatement psStatement = cConn.prepareStatement( “INSERT INTO TEAMS VALUES (?)”);

//Set the IN parameter psStatement.setString(1, “Jennie Vitale”);

//Add batch command psStatement.addBatch();

296

Persisting Your Application Using Databases

//Set the IN parameter for the next command psStatement.setString(1, “Andrew Vitale”);

//Add batch command psStatement.addBatch();

int []uCounts = psStatement.executeBatch();

// Commit the changes cConn.commit();

}else {

System.err.print(“Your driver does not support batch updates!”);

}

} catch(BatchUpdateException batchEx) {

}

The key point to note from the code above is where the PreparedStatement.addBatch methods occur. They occur after the IN parameters are set, so you simply change the IN parameters for each batch command you wish to execute.

Creating Batch Updates Using a CallableStatement Object

The CallableStatement object handles batch commands in the exact same way as the PreparedStatement object. Now I know what you are thinking, “What about all the stored procedures that require OUT or INOUT parameters?” Well the answer is that OUT and INOUT parameters are not allowed to be used to call stored procedures in a batched fashion. If you did call a stored procedure that required either an OUT or an INOUT parameter, a BatchUpdateException would be thrown because SQL commands must return an update count and are not allowed to return anything else, such as result sets. So the code syntax looks remarkably the same as the PreparedStatement object, with the exception that you are calling stored procedures. The following code illustrates using a CallableStatement object to perform batch updates:

// Make sure that autocommit is off cConn.setAutoCommit(false);

CallableStatement csStatement = cConn.prepareCall( “{call updatePlayers(?)}”);

//Set the IN parameter csStatement.setString(1, “Jennie Vitale”);

//Add batch command csStatement.addBatch();

//Set the IN parameter for the next command csStatement.setString(1, “Andrew Vitale”);

//Add batch command

csStatement.addBatch();

int []uCounts = csStatement.executeBatch();

// Commit the changes cConn.commit();

297

Chapter 6

Utilizing Result Sets

In simple terms, a ResultSet object is a Java object that is created to contain the results of an SQL query that has been executed. The results are in table row fashion, meaning they contain column headers, types, and values. All this information can be obtained through either the ResultSet object or the ResultSetMetaData object.

ResultSet objects are very common, and you will interface with them on a continuous basis when doing JDBC programming, so it is important to understand the different types of ResultSet objects that are available for you to exploit. Understanding how ResultSet objects are created and manipulated is crucial when you are designing different algorithms, especially with regard to performance. So find the best possible option for executing a query, and manipulate its results for your particular situation.

Investigating Types of Result Sets

There are two main areas of interest when dealing with result sets of which you must be aware. The first area of interest is the concentration on how the cursor in a result set can be exploited. Cursors can be limited to only moving forward, or they can be allowed to move in both forward and backward directions. The second area of interest is how changes in the data source affect the result set. You can instruct a result set to be aware of changes that occur in an underlying data source and have a ResultSet object reflect those changes.

There are three types of result sets that warrant explanation. Each of these types will be scrollable or nonscrollable, sensitive or insensitive. Scrollable means that the cursor in the result set can move both forward and backward. Non-scrollable signifies that the cursor can only move in one direction: forward. If the result set is sensitive to change, it will reflect changes that occur while the result set is open. If the result set is insensitive to change, it will usually remain fixed with no change to its structure, even if

the underlying data source changes. The following is a list of constants that are in the ResultSet interface that you can use to specify a result set type:

TYPE_FORWARD_ONLY — The result set cursor can only be moved forward from the beginning to the end. It cannot move backwards. Also, the result set is not sensitive to change from the data source.

TYPE_SCROLL_INSENSITIVE — The result set cursor can move forward and backward and jump to rows specified by the application. Also, the result set is not sensitive to change from the data source.

TYPE_SCROLL_SENSITIVE — The result set cursor can move forward and backward and jump to rows specified by the application. This time the result is sensitive to changes to the data source while the result set is open. This provides a dynamic view to the data.

Setting Concurrency of Result Sets

Result sets have only two levels of concurrency: read-only and updatable. To find out if your driver supports a specific concurrency type, use the DatabaseMetaData.supportResultSetConcurrency method to find out. The following is a list of constants that are in the ResultSet interface that you can use to specify a result set concurrency type:

CONCUR_READ_ONLY — Specify this constant when you want your result set to be read-only, meaning it cannot be updated programmatically.

CONCUR_UPDATABLE — Specify this constant when you want your result set to be updatable, meaning it can be updated programmatically.

298

Persisting Your Application Using Databases

Setting Holdability of Result Sets

Result sets are generally closed when a transaction has been completed. This means that a Connection. commit has been called, which in turn closes any related result sets. In special cases, this may not be the desired behavior that you were hoping for. It is possible to hold a result set open and keep its cursor position in the result set after a Connection.commit has been called by creating your statements with the following ResultSet interface constants present:

HOLD_CURSORS_OVER_COMMIT — Specifies that a ResultSet object will not be closed when a Connection.commit is called. Instead, it will remain open until the program calls the method ResultSet.close. If you are interested in better performance, this is usually not the best option.

CLOSE_CURSORS_AT_COMMIT — Specifies that a ResultSet object will be closed when a Connection.commit occurs. This is the best performance option.

Another interesting point to note is that the default holdability is determined by the DBMS that you are interfacing with. In order to determine the default holdability, use the DatabaseMetaData.getResultSetHoldability method to retrieve the default holdability for the DBMS.

Using Result Sets

Now that you know the different types of result sets that exist and the concurrency and holdability levels, it is time to see what a result set looks like in action. The following code shows how to create a statement that is scrollable, updatable, insensitive to data source changes, and closes the cursor when a commit occurs:

// Look up the registered data source from JNDI

DataSource dsDataSource = (DataSource) ctx.lookup(“jdbc/OurDB”);

// Obtain a Connection object from the data source

Connection cConn = dsDataSource.getConnection(“username”, “password”);

Statement sStatement = cConn.createStatement(

ResultSet.CONCUR_UPDATABLE,

ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CLOSE_CURSORS_AT_COMMIT

);

ResultSet rsResults = sStatement.executeQuery(“SELECT NAME, TEAM FROM PLAYERS”);

//Though we have not done anything to warrant a commit we put this here to show where the ResultSet would be closed

cConn.commit();

//Close the connection

cConn.close();

Navigating Result Sets

The ResultSet interface of the JDBC API provides a rich set of methods for navigating through ResultSet objects. If your ResultSet object is scrollable, you can easily jump to different rows in the ResultSet object with little effort. Here is a list of the main methods provided in the ResultSet interface for navigation with a ResultSet object.

299

Chapter 6

Manipulating Result Sets

The ResultSet interface has an enormous number of methods that can be used for updating a ResultSet object. The majority of the methods are prefixed with the word update. In order to be able to update a ResultSet object, it must have a concurrency of type CONCUR_UPDATABLE. If a ResultSet object is updatable, its columns can be altered, its rows can be deleted, new rows can be added, and its data can be changed. The following code example shows several ways to manipulate a ResultSet object:

Statement sStatement = cConn.createStatement( ResultSet.CONCUR_UPDATABLE, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CLOSE_CURSORS_AT_COMMIT

);

ResultSet rsResults = sStatement.executeQuery(“SELECT NAME, TEAM, AGE, “ +

300

Persisting Your Application Using Databases

“RANK FROM PLAYERS”);

//Move to the last row rsResults.last();

//Update specific data in the row rsResults.updateString(2, “Hornets”); rsResults.updateInt(3, 27); rsResults.updateLong(4, 502l);

//Commit the changes to the row rsResults.updateRow(); cConn.commit();

//Close the connection cConn.close();

The following example will show you how to insert and delete rows. Inserting rows is not a difficult process but it does require a bit of know-how since it is not initially intuitive. In order to insert a row into a ResultSet object, you must first make a call to ResultSet.moveToInsertRow. This may seem confusing, but the JDBC API defines a concept of an insert row in the ResultSet object. When you call ResultSet. moveToInsertRow, this essentially allows you to remember your current cursor position, move to a temporary area in memory, perform the creation of your new row, and call the ResultSet.insertRow to insert the newly created row into the ResultSet object at the cursor position you were at before calling ResultSet. moveToInsertRow.

Deleting a row is much more trivial than inserting a row. To delete a row, you simply move to the row you want to delete and call ResultSet.deleteRow. The following code will demonstrate how to delete and insert a row using the methods that were just described:

Statement sStatement = cConn.createStatement(ResultSet.CONCUR_UPDATABLE);

ResultSet rsResults = sStatement.executeQuery(“SELECT NAME, TEAM, AGE,” + “RANK FROM PLAYERS”);

//Move to the fourth row rsResults.absolute(4);

//Delete the fourth row rsResults.deleteRow();

//Now let’s insert a new row rsResults.moveToInsertRow();

//Build data for new row rsResults.updateString(1, “Ken Pratt”); rsResults.updateString(2, “Tigers”); rsResults.updateInt(3, 32); rsResults.updateLong(4, 752l);

//Add the new row to the ResultsSet

301

Chapter 6

rsResults.insertRow();

//Move the cursor back the original position rsResults.moveToCurrentRow();

//Commit changes

cConn.commit();

// Close the connection cConn.close();

Closing Result Sets

If the Statement object that created the ResultSet object is not yet closed, you can use the ResultSet.close method to close a ResultSet object and free its resources. If you specified the HOLD_CURSORS_OVER_COMMIT flag when you created the Statement object, then you will also need to call the ResultSet.close method when you are done with the ResultSet object. Otherwise it would remain open even if a Connect.commit is called. However, if the Statement object that created the ResultSet object is closed, the ResultSet object would be closed as well even if the HOLD_CURSORS_OVER_COMMIT was specified during creation.

Examining JDBC Advanced Concepts

This portion of the chapter will discuss concepts that are generally used in advanced Java applications that definitely fall in the three-tier model that was described in the section “JDBC API Usage in the Real World,” earlier in this chapter. This section will cover the following:

Meta data — Explore retrieving meta data about your data source and understanding how to use it.

RowSets — Explain RowSets in depth.

Connection Pooling — Discuss all the ins and outs of connection pooling.

Transactions — Both standard and distributed transactions.

Managing Database Meta Data

Sometimes the JDBC-supported applications that you write may need to acquire more information about a data source. Specifically, information that is not readily available through Statement objects with embedded SQL calls or through the results that they return. Suppose you want to obtain information about whether or not your DBMS supports transactions, batch updates, or save points. The only way

to determine this type of information is through the DBMS’s meta data. The JDBC API has an interface called DatabaseMetaData that allows an application to retrieve meta data about a DBMS through an enormous array of methods. These methods can be used to retrieve meta data information that is classified into the following categories:

Discovering limitations of the data source

Determining what capabilities and features a data source supports

Retrieving general information about a data source such as a database version or what SQL keywords it supports

302

Persisting Your Application Using Databases

With the DatabaseMetaData interface, you can even retrieve the tables, columns, user-defined types, and the schema of a particular data source. This can be a very useful tool when you know very little about the data source with which you are interfacing.

Discovering Limitations of a Data Source

Discovering the limitations of a data source is easily done using a DatabaseMetaData object. Most of the limitations methods are prefixed with the words getMax. For example, DatabaseMetaData.getMaxConnections retrieves the maximum number of connections that can occur at the same time within a data source.

Listed below are some of the more common limitation methods that are used for a given data source. To see a list of all the limitation methods, please see the DatabaseMetaData Java doc.

Determining Which Features a Data Source Supports

The DatabaseMetaData object provides numerous methods for determining whether or not your DBMS driver supports a feature that you are interested in using. Most of the methods begin with the prefix supports. The most commonly used features are listed in the following table:

303

Chapter 6

Retrieving General Information about a Data Source

There is an enormous amount of general information that can be retrieved about a data source using the DatabaseMetaData methods. There are over 100 methods, so I decided to focus on a few of the core methods that you are most likely to use, such as: retrieving the database schema; obtaining the names of the tables in the database; and retrieving the columns for a specific table. The rest of the methods can be found in the DatabaseMetaData Java docs that come with the Java SDK 1.5 documentation.

I decided to show you a practical example of how to use these methods, rather than bore you with the details of how each method operates. This example will show you how to create a keyword search that can explore an entire database without knowing anything about it except how to connect to it and retrieve specific rows that contain the keywords for which you are searching. The keyword search example has three main classes that make up its architecture: DBDatabase, DBTable, and DBColumn. DBDatabase handles the connection to the data source, reading meta data information such as tables and columns, and searching for specific keywords.

The DBTable class stores information about a table’s makeup as well as its individual columns that are associated with the table. The column objects are stored in an ArrayList and can be accessed via the DBTable.getColumns method:

public class DBTable {

private String m_sTblName; private ArrayList m_alColumns;

public DBTable(String sName) { m_sTblName = sName; m_alColumns = new ArrayList();

}

public String getTableName() { return m_sTblName;

}

public void addColumn(DBColumn Column) { m_alColumns.add((DBColumn) Column);

}

public ArrayList getColumns() { return m_alColumns;

}

}

The DBColumn class contains information about a specific column that belongs to a table. This information consists of the table name it belongs to, the column’s name, the SQL data type of the column, the size of the data contained in the column, and whether or not the column is nullable:

public class DBColumn { private String m_sTblName; private String m_columnName;

private String m_datatype; private int m_datasize; private int m_digits;

304

Persisting Your Application Using Databases

private boolean m_nullable;

public DBColumn(String sTableName, String sCol, String sDType, int idsize, int idigits, boolean bnullable) {

// Initialize variables here

}

// getter methods

}

The final class in this example is the DBDatabase class, which is too large to display in its entirety so I will only illustrate its basic structure. Its main purpose is to create a connection with a database and start the process to load the DBTable and DBColumn objects with data through the load, readTables, and readTableColumns methods. The searchAllByKeyword method allows the application to search an entire database for a specific keyword:

public class DBDatabase {

private Connection m_cConnection;

private ArrayList m_alTables; private ArrayList m_alResults;

public DBDatabase(String sDriver, String sURL, String sUser, String sPass) { }

public boolean load() { }

public void readTables(Connection currentConnection) throws Exception { }

public void readTableColumns(DatabaseMetaData meta, DBTable table) throws Exception

{ }

public ArrayList searchAllByKeyword(String saKeyword) { }

I demonstrate the classes that are contained in this example and show the steps in action to execute a keyword search of the database. Here are the steps:

1.The first thing you need to do is call the DBDatabase.load method to create a connection to the data source and read the meta data from the data source that contains the tables and columns. The DBDatabase.readTables creates a DBTable object for each table in the database and also calls the DBDatabase.readTableColumns method to associate DBColumn objects with the appropriate DBTable objects.

305