Java Reference
In-Depth Information
In basic JDBC usage, connections have an autocommit mode (set via the setAutoCommit()
method). If autocommit is turned on (and for most JDBC drivers that is the default), then
each statement in a JDBC program is its own transaction. In that case, a program need take
no action to commit a transaction (in fact, if the commit() method is called, performance
will often suffer).
If autocommit is turned off, then a transaction implicitly begins when the first call is made on
the connection object (e.g., by calling the executeQuery() method). The transaction contin-
ues until the commit() method (or the rollback() method) is called. A new transaction will
begin when the connection is used for the next database call.
Transactions are expensive to commit, so one goal is to perform as much work in a transac-
tion as is possible. Unfortunately, that principle is completely at odds with another goal: be-
cause transactions can hold locks, they should be as short as possible. There is definitely a
balance here, and striking the balance will depend on the application and its locking require-
ments. The next section, on transaction isolation and locking, covers that in more detail; first
let's look into the options for optimizing the transaction handling itself.
Consider some sample code that inserts data into a database for use by the stock application.
For each day of valid data, one row must be inserted into the STOCKPRICE table, and five
rows into the STOCKOPTIONPRICE table. A basic loop to accomplish that looks like this:
Connection c = DriverManager . getConnection ( URL , user , pw );
PreparedStatement ps = c . prepareStatement ( insertStockSQL );
PreparedStatement ps2 = c . prepareStatement ( insertOptionSQL )) {
Date curDate = new
new Date ( startDate . getTime ());
while (! curDate . after ( endDate )) {
StockPrice sp = createRandomStock ( symbol , curDate );
iif ( sp != null
null ) {
ps . clearParameters ();
ps . setBigDecimal ( 1 , sp . getClosingPrice ());
// Similar set calls for the remaining fields
ps . executeUpdate ();
for ( int
int j = 0 ; j < 5 ; j ++) {
ps2 . clearParameters ();
ps2 . setBigDecimal ( 1 , ...);
// Similar set calls for the remaining fields
ps2 . executeUpdate ();
} // else curDate is a weekend and it is skipped
curDate . setTime ( curDate . getTime () + msPerDay );
Search WWH ::

Custom Search