| [ Team LiB ] |
|
11.2 Simple Database AccessAt this point, you have the basics for connecting to a database. Example 11-1, however, did not really do much. It showed you how to make a database connection and perform simplistic result set management. You are unlikely to ever have such basic functionality in your applications. The basic database behavior common to most applications, however, does not require significantly more complex code. 11.2.1 The ConnectionJDBC represents a connection to a database through the Connection interface. Thus, connecting to a database requires you to get an instance of the Connection interface from your JDBC driver. JDBC supports two ways of getting access to a database connection:
The data source method is the preferred approach to database connectivity. Data sources, however, tend to be usable only in application server contexts. You should therefore understand both forms of connectivity since you can rely on DriverManager connectivity no matter what environment you are working in.
11.2.1.1 DataSource connectivityData source connectivity is very simple. In fact, the following code makes a connection to any database for any application: Context ctx = new InitialContext( );
DataSource ds = (DataSource)ctx.lookup("jdbc/dsn");
Connection conn = ds.getConnection( );
The only requirement is that you have a JNDI-supported directory service containing a DataSource configured with the name jdbc/dsn. The first line gets an InitialContext object in accordance with the JNDI[1] specification. If your environment isn't set up properly, you catch a javax.naming.NoInitialContextException. The InitialContext enables you to navigate a directory service. You use it in your database applications to look up a JDBC DataSource instance. Finally, once you have found that DataSource, you use it to make a connection.
Technically, DataSource connectivity does not require a directory service. Instead, you can serialize a configured DataSource instance out to a filesystem or load one in some other fashion. You will nevertheless find that the most common situation you will encounter is looking one up in a directory service via JNDI. You may be wondering how the DataSource gets into the directory service so you can look it up. JNDI does provide a process for binding objects into a directory service: SomeDataSourceClass ds = new SomeDataSourceClass( );
Context ctx = new InitialContext( );
// configure the data source through various setter methods
ctx.bind("jdbc/dsn", ds);
I have two bits of magic in this code. First, the class SomeDataSourceClass is an implementation of javax.sql.DataSource—generally written by your database or application server vendor. Next, the configuration of your data source—the step I commented out—is highly dependent on the DataSource implementation you are using. In some cases, you may configure a user ID, password, database name, and server name. You will likely specify much more. Fortunately, few programmers actually write code to bind data sources to JNDI directory services. You will mostly encounter the need to configure a data source via XML or some other means. A sample data-sources.xml file from the Orion application server looks like this: <?xml version="1.0"?>
<!DOCTYPE data-sources PUBLIC
"Orion data-sources"
"http://www.orionserver.com/dtds/data-sources.dtd">
<data-sources>
<data-source class="com.evermind.sql.DriverManagerDataSource"
name="dsn"
location="jdbc/dsn"
xa-location="jdbc/xa/HypersonicXADS"
ejb-location="jdbc/HypersonicDS"
connection-driver="org.gjt.mm.mysql.Driver"
username="dvl"
password="dvl"
url="jdbc:mysql://localhost/dvl"
inactivity-timeout="30"/>
</data-sources>
This configuration file places a DataSource instance in the application server's directory service that enables me to connect to a MySQL database named "dvl" on the localhost using the user ID "dvl" and password "dvl". 11.2.1.2 DriverManager connectivityOne of the few implementation classes in the java.sql package is the class DriverManager. It maintains a list of implementations of the java.sql.Driver interface and provides you with connections based on a JDBC URL that you provide. This JDBC URL comes in the form jdbc:protocol:subprotocol. This URL tells a DriverManager which database engine you wish to connect to and provides the DriverManager with enough information to make a connection.
The protocol part of the URL refers to a given JDBC driver. The protocol for the GNU MySQL driver, for example, is mysql. The subprotocol provides the implementation-specific connection information. Most drivers minimally require the name of a database to connect to. It is also common to specify a host and even a port number in the subprotocol. Each driver's JDBC URL is different, and so I cannot say anything more explicit that will tell you what the proper URL is for your driver. Your driver documentation, however, should have easy-to-find documentation describing the exact form of its JDBC URL. Whatever the format of the URL, the primary function of the URL is to uniquely identify the driver needed by the application and pass that driver any information it needs to make a connection to the proper database. Before you can use a URL to get a connection from the DriverManager, you first need to register your Driver implementation with the DriverManager. You have two main options for registering a Driver:
For portability's sake, I recommend that you put all configuration information in some sort of configuration file, such as a properties file, then load the configuration data at runtime. By taking this approach, your application will not rely on a particular database or JDBC driver. You can simply change the values in the configuration file to move from one driver to another or one database to another. Once you register a driver, you can ask the DriverManager for a Connection by calling the getConnection( ) method in the driver with the information identifying the desired connection. This information minimally includes a JDBC URL, a user ID, and a password: Connection conn =
DriverManager.getConnection("jdbc:mysql:/localhost/Web",
"userID", "password");
This code returns a connection to the MySQL database named Web using the GNU MySQL driver. This connection occurs under the permissions of the user "userID" identified by the password "password". An alternative signature to this method enables you to specify a Properties object that may contain values beyond the basic user ID and password: Properties p = new Properties( );
Connection conn;
p.put("user", "userID");
p.put("password", "password");
p.put("encoding"", "UTF-8");
conn = DriverManager.getConnection("jdbc:mysql:/localhost/Web", p);
Example 11-2 provides a full example that connects to a MySQL database. Example 11-2. Using the DriverManager to make a connectionimport java.sql.*;
import java.util.Properties;
public class Connect {
static public void main(String[ ] args) {
Connection conn = null;
try {
String url = "jdbc:mysql:/localhost/Web";
Properties p = new Properties( );
Class.forName("org.gjt.mm.mysql.Driver").
newInstance( );
p.put("user", "dvl");
p.put("password", "password");
conn = DriverManager.getConnection(url, p);
}
catch( SQLException e ) {
e.printStackTrace( );
}
finally {
if( conn != null ) {
try { conn.close( ); }
catch( SQLException e ) { }
}
}
}
}
In this example, I have hardcoded the driver name and connection information in the application. The only reason this is acceptable is because it is an example showing all of the elements of making a connection. In practice, you will always want to follow the best practices in the next section that avoid hardcoding these values. 11.2.1.3 Portability through propertiesJava is a language based on the concept of portability. To most people, portability means that you do not write code that will run on only one platform. In the Java world, however, portability means no proprietary dependencies—and that means no database dependencies. I touched earlier on how the JDBC URL and Driver implementation classes are driver-specific. Because both values are simple strings, you can pass them as command-line arguments or applet parameters. Unfortunately, this approach is hardly elegant since it requires users to remember long command lines or to pass authentication credentials as HTML to an applet tag. You could, of course, prompt the user for this information. However, this approach demands that the user know a JDBC URL and driver name. The elegant approach is the use of properties files. Java supports the concept of properties-based application configuration through java.util.ResourceBundle and its subclasses. Using a properties file, you can store all configuration information like the JDBC URL, driver class, user ID, and password and change it as the runtime environment changes. Example 11-3 is a sample properties file. Example 11-3. A properties file containing driver configuration dataurl=jdbc:mysql:/localhost/Web driver=org.gjt.mm.mysql.Driver user=dvl password=dvl You can now turn Example 11-2 into a portable example of making a connection as shown in Example 11-4. Example 11-4. Using a properties file to achieve portabilityimport java.sql.*;
import java.util.*
public class Connect {
static public void main(String[ ] args) {
Connection conn = null;
try {
ResourceBundle bdl = ResourceBundle.getBundle("connect");
String url = bdl.getString("url");
Properties p = new Propertes( );;
Enumeration keys = bdl.keys( );
while( keys.hasMoreElements( ) ) {
String prop = (String)keys.nextElement( );
String val = bdl.getString(prop);
p.setProperty(prop, val);
}
Class.forName(bld.getString("driver")).newInstance( );
conn = DriverManager.getConnection(url, p);
}
catch( SQLException e ) {
e.printStackTrace( );
}
finally {
if( conn != null ) {
try { conn.close( ); }
catch( SQLException e ) { }
}
}
}
}
We no longer have any code specific to MySQL or the GNU driver. This code will now work against any database engine using any JDBC driver, simply through changing the properties file. 11.2.2 Query ExecutionThe most basic element of communication over a Connection is the Statement. Your application encapsulates SQL queries into a Statement or one of its subclasses and processes the results. An SQL query can be an INSERT, UPDATE, DELETE, or any other valid SQL statement. 11.2.2.1 Simple queriesThe Connection class enables you to create Statement instances via the createStatement( ) method: Statement stmt = conn.createStatement( ); You can then use that statement to send SQL to the database: stmt.executeUpdate("UPDATE test SET val = 'cheese' WHERE id = 1");
In this case, we are sending SQL that modifies the database. If the SQL returned results, however, we would use the executeQuery( ) method and get back an instance of ResultSet: ResultSet rs = stmt.executeQuery("SELECT id, val FROM test");
Example 11-5 shows a query returning results and the processing of those results. Example 11-5. A query that returns results for processingConnection conn = null;
try {
Statement stmt = conn.createStatement( );
ResultSet rs = stmt.executeQuery("SELECT id, val FROM test");
while( rs.next( ) ) {
System.out.println("ID: " + rs.getInt(1) + ", rs.getString(2));
}
}
The query in Example 11-5 retrieves every row from the table test. The JDBC code loops through those rows and displays the values for each row's columns.
Until the first call to next( ), the result set does not point to any row returned by the query. The first call makes the result set point to the first row. Until the next call to next( ), any operations you perform on the result set act on that row. Subsequent calls to next( ) move the result set forward through the rows in the result set. In this example, I move through each row until the next( ) method returns false. A return value of false indicates that there is no next row to move the result set to. Dealing with a row means retrieving the values for its columns. Whatever the value in the database, you can retrieve it using a getter method in the ResultSet interface. In Example 11-5, I used getInt( ) to retrieve the id column and getString( ) to retrieve the val column. These getter methods can accept either the number of the column—starting with 1—or the column name. You should, however, avoid retrieving columns by name because it is generally much slower than retrieving them by number. 11.2.2.2 Scrollable result setsBy default, you are limited to simple forward navigation through a result set. JDBC does, however, provide a tool for navigating backward and forward through a result set; it is called a scrollable result set. You can get scrollable result sets back from your queries if you indicate that you want a scrollable result set when you create your statement: Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
The first argument indicates that you want a scrollable result set. The second argument indicates that you want a read-only result set. Non-read-only result sets are an advanced topic beyond the scope of this chapter. With a scrollable result set, you can make calls to:
11.2.3 TransactionsChapter 3 covered the role of transactions in database programming. The critical job of a transaction is to take the database from one consistent state to another. Your database handles many of the complexities of transaction management. When you modify a table, the underlying database acquires the appropriate locks and guarantees that your changes do not conflict with those of another client. In order for the database to properly manage your transactions, your application needs to tell it what operations constitute a single transaction. By default, JDBC treats every distinct SQL execution as a transaction. This default is called auto-commit. In other words, each statement is committed the minute it completes unless there is application logic to the contrary. The following code updates the balance of a bank account in the default auto-commit mode: float ob = account.getBalance( );
Connection conn = null;
account.calculateInterest( );
try {
Statement stmt = null;
conn = ds.getConnection( );
stmt = conn.createStatement( );
stmt.executeUpdate("UPDATE account SET balance = " +
account.getBalance( ) +
" WHERE id = " + account.getId( ));
}
catch( SQLException e ) {
account.resetBalance(ob);
}
catch( Error e ) {
account.resetBalance(ob);
throw e;
}
catch( RuntimException e ) {
account.resetBalance(ob);
throw e;
}
finally {
if( conn != null ) {
try { conn.close( ); }
catch( SQLException e ) { }
}
}
From a JDBC perspective, nothing in this sample code differs from what you have done so far. What differs is the exception handling so that your application returns to a state consistent with the database. In this case, when an exception occurs, the account object gets its balance set back to the value prior to calculating interest. 11.2.3.1 Basic transaction managementRarely are transactions as simplistic as the previous one. A given transaction can make numerous modifications that need to occur together or not at all. The classic example of such a transaction is a transfer of funds from your savings account to your checking account. This transaction includes the following steps:
If the credit to the checking account fails for whatever reason, you as the account holder certainly want that money recredited to the savings account. In JDBC's default auto-commit mode, there is no sure way to achieve this consistency. You therefore need to turn auto-commit off and manually tell JDBC where to commit the transaction. You also need to handle errors so that it will get rolled back in exceptional conditions. The following code shows this account transfer: float sb = savings.getBalance( );
float cb = checking.getBalance( );
Connection conn = null;
boolean success = false;
savings.transfer(checking, 10.00);
try {
Statement stmt = null;
conn = ds.getConnection( );
conn.setAutoCommit(false);
stmt = conn.createStatement( );
stmt.executeUpdate("UPDATE account SET balance = " +
savings.getBalance( ) +
" WHERE id = " + asavings.getId( ));
stmt.executeUpdate("UPDATE account SET balance = " +
checking.getBalance( ) +
" WHERE id = " + achecking.getId( ));
success = true;
}
catch( SQLException e ) {
e.printStackTrace( );
}
finally {
if( conn != null ) {
if( success ) {
try { conn.commit( ); }
catch( SQLException e ) {
savings.resetBalance(sb);
checking.resetBalance(cb);
try { conn.rollback( ); }
catch( SQLException e ) { }
}
}
else {
savings.resetBalance(sb);
checking.resetBalance(cb);
try { conn.rollback( ); }
catch( SQLException e ) { }
}
try { conn.close( ); }
catch( SQLException e ) { }
}
}
The bold sections illustrate what changes for multi-statement transactions. First, you need to turn off auto-commit using setAutoCommit(false). You then execute transactions as you always would. When done, you either commit the transaction (using commit( )) or roll it back (using rollback( )). In this sample, I have tracked the success or failure of the transaction and I perform the commit and rollback in the finally block. 11.2.3.2 SavepointsAs described earlier, transactions work well when you have a very straightforward beginning state with only one possible consistent end state. In other words, the database starts with one set of values and should end up with another specific set of values when the transaction completes. Some situations, however, allow for multiple possible consistent end states dependent on the events that occur during the course of the transaction. Such transactions require a much more fine grained approach to transaction management than the commit/rollback scheme allows. JDBC manages these transactions through savepoints. A savepoint is a JDBC tool for marking a database state as a possible final consistent state for a transaction. Specifically, you can execute a statement and then establish a savepoint with the connection. Depending on what happens in the transaction, you can commit the transaction, roll it back, or roll it back to the savepoint. If you roll back to the savepoint, you can chose to commit that work or execute an alternative flow for the transaction. The mechanics of savepoints are simple: Connection conn = ds.getConnection( );
Statement stmt = conn.createStatement( );
Savepoint sp;
stmt.executeUpdate(
"INSERT INTO test ( id, val ) VALUES ( 1, 'test')");
sp = conn.setSavepoint("safety");
stmt.executeUpdate(
"INSERT INTO other ( id, name ) VALUES ( 32, 'sample')");
try {
stmt.executeUpdate("UPDATE test SET other = 32 WHERE id = 1");
}
catch( SQLException e ) {
conn.rollback(sp);
}
conn.commit( );
The application sets the savepoint after the first SQL. It can now guarantee that no matter what else happens, it can return the database to a state in which a new row is in the test table without missing references or unreferenced values in the other table. If an error occurs in the SQL updating the test table so that the new test value points to the new other value, then the transaction is rolled back so that the test value exists alone in the database. This example begs the question: why not simply commit after the first statement? One reason is performance. You do not need to release and reacquire any locks to execute the transaction. The other reason, however, would be in more complex transactions in which some branches of logic after the savepoint is set need to roll back completely while others need to roll back only to the savepoint. To illustrate the kind of logic to which savepoints apply, consider a game in which you toss marbles into a jar. The goal of the game is to end up with the most marbles in the jar. You start the game with no marbles and continue tossing marbles into the jar until your fourth miss or you call it quits. Your score is the number of marbles in the jar after your fourth miss. The trick behind scoring, however, is that missing two in a row returns your marble count to its number after your first miss. If you miss three in a row, the jar is emptied and your final score is whatever you can get in the jar before your next miss. In transactional terms, the following events occur:
11.2.4 Error Handling and CleanupAll JDBC method calls can throw SQLException or one of its subclasses if something happens during a database call. Your code should be set up to catch this exception, deal with it, and clean up any database resources that have been allocated. The basic skeleton of any JDBC code I write looks like this: Connection conn = null;
try {
// create the connection and execute your transaction
}
catch( SQLException e ) {
// handle the exception
}
finally {
if( conn != null ) {
try { conn.close( ); }
catch( SQLException e ) { }
}
}
Each of the major JDBC interfaces you have encountered—Connection, Statement, and ResultSet—has a close( ) method. Practically speaking, however, you need only make sure you close your connection instances because closing a connection closes all associated statements. The closing of a statement, in turn, closes all associated result sets. By closing the connection in the finally clause, you guarantee that the connection will be closed even when an error occurs. 11.2.5 Prepared SQLJDBC Statement instances illustrate basic database programming well, but you rarely want to use them in practice. Unfortunately, Statement sends your SQL to the database each time you execute it. It provides the database with very little opportunity to optimize repeated SQL calls. Consider the following SQL: UPDATE account SET balance = 5.00 WHERE id = 2 If you use a Statement to support updating many accounts with a similar SQL call, the database has to process the SQL and determine how it will execute the query every single time you send it to the database. You can avoid this overhead, however, through prepared SQL. Databases support two kinds of prepared SQL: prepared statements and stored procedures. Prepared SQL provides an advantage over the simple SQL statements you have covered so far; a database can get the SQL ahead of time and create a query plan while you are doing other application logic. Your SQL should therefore execute faster. Furthermore, you have a generic reference to the statement for later reuse instead of repeatedly re-creating similar SQL statements.
The optimization factor comes from the database knowing what you are about to do. When you create a Java instance of a prepared statement or stored procedure, you notify the database of what SQL you intend to be calling without providing any specific values. For example, the SQL to update account balances looks like this as a prepared statement: UPDATE account SET balance = ? WHERE id = ? Instead of sending this SQL as an argument to executeUpdate( ), you pass it to the connection when you create the statement. You then assign values to the two placeholders and finish by calling executeUpdate( ). If you want to make further calls to update other accounts, you can reassign the statement new values and call executeUpdate( ) again. 11.2.5.1 Prepared statementsThe PreparedStatement interface extends the Statement interface we used earlier in the chapter. It enables a SQL statement to contain parameters like a function call. You can execute a single statement repeatedly with different values. The act of assigning values to parameters is called binding parameters. You might want to use a prepared statement when updating a group of objects stored in the same table. For example, if you update many bank accounts as described earlier, you might have a loop like this: Statement stmt = conn.createStatement( );
for(int i=0; i<accounts.length; i++) {
stmt.executeUpdate("UPDATE account " +
"SET balance = " +
accounts[i].getBalance( ) + " " +
"WHERE id = " + accounts[i].getId( ));
}
conn.commit( );
This statement keeps sending slightly different SQL to the database each time it goes through the loop. Instead of calling this statement repeatedly with different inputs, you can instead use a PreparedStatement: PreparedStatement stmt = conn.prepareStatement("UPDATE account " +
"SET balance = ? " +
"WHERE id = ?");
for(int i=0; i<accounts.length; i++) {
stmt.setFloat(1, accounts[i].getBalance( ));
stmt.setInt(2, accounts[i].getId( ));
stmt.executeUpdate( );
stmt.clearParameters( );
}
conn.commit( );
With a prepared statement, you send the actual SQL to the database when you get the PreparedStatement object through the prepareStatement( ) method in java.sql.Connection. Keep in mind that you have not yet actually executed any SQL. You execute that prepared SQL statement multiple times inside the for( ) loop, but you build the query plan only a single time. Before each execution of the prepared statement, you tell JDBC which values to use as input for that execution of the statement. In order to bind the input parameters, PreparedStatement provides setter methods—like setFloat( ) and setInt( )—that mirror the getter methods you saw in ResultSet. Just as the getter methods read results according to the order in which you constructed your SQL, the setter methods bind parameters from left to right in the order you placed them in the prepared statement. In the previous example, I bound parameter 1 as a float to the account balance that I retrieved from the account object. The first ? was thus associated with parameter 1. 11.2.5.2 Stored proceduresWhile prepared statements enable you to access similar database queries through a single PreparedStatement object, stored procedures attempt to take the "black box" concept for database access one step further. A stored procedure is built inside the database before you run your application. You access that stored procedure by name at runtime. In other words, a stored procedure is almost like a method you call in the database. Stored procedures have the following advantages:
The downside to stored procedures, however, is that every database has its own stored procedure language. If you use stored procedures heavily, you can go to great lengths to make sure your Java application is database-independent yet still be tied to a specific database because of the stored procedures. Worse, different databases do not even share basic semantics that would facilitate porting between database engines. For example, you can retrieve results from a Sybase stored procedure using a plain ResultSet. With Oracle, however, retrieving results from a stored procedure is much more complex. Using stored procedures, we can revise the balance updating code to the following: CallableStatement stmt = conn.prepareCall("{call sp_balance(?,?)}");
for(int i=0; i<accounts.length; i++) {
stmt.setInt(1, accounts[i].getId( ));
stmt.setFloat(2, accounts[i].getBalance( ));
stmt.executeUpdate( );;
}
conn.commit( );
This example illustrates how close stored procedures are to prepared statements from a JDBC perspective. The difference is that you are referencing the stored procedure by name rather than spelling out the SQL you are calling. The result is simply increased performance at the expense of portability. Some stored procedures may have output parameters. For those stored procedures, you need to register the output parameter before you execute the SQL: CallableStatement stmt =
conn.prepareCall("{call sp_interest(?, ?)}");
stmt.registerOutParameter(2, java.sql.Types.FLOAT);
for(int i=0; i<accounts.length; i++) {
stmt.setInt(1, accounts[i].getId( ));
stmt.executeUpdate( );
accounts[i].setBalancce(stmt.getFloat(2));
}
conn.commit( );
The prepareCall( ) method creates a stored procedure object that will make a call to a specific stored procedure. This syntax sets up the order you will use in binding parameters. By calling registerOutParameter( ), you tell the CallableStatement instance to expect the second parameter as output of type float. Once this procedure is set up, you can bind the ID using setInt( ) and then get the output using getFloat( ). |
| [ Team LiB ] |
|
