46:Connection Pooling

Learning Objectives
After completing this session, you will be able to:
‰  Define DataSource and Connection Pooling
‰  Describe transaction
javax.sql.DataSource Interface and DataSource Object
‰  Driver vendor implements the javax.sql.DataSource interface.
‰  DataSource object is the factory for creating database connections.
Three types of possible implementations are:
‰  Basic implementation: Produces standard Connection object
‰  Connection pooling implementation: Produces a Connection object that will
automatically participate in connection pooling
‰  Distributed transaction implementation: Produces a Connection object that may be
used for distributed transactions and almostalways participates in connection pooling
Properties of DataSource Object
A DataSource object has properties that can be modified when necessary. These are defined in
the configuration file of a container:
‰  Location of the database server
‰  Name of the database
‰  Network protocol to be used to communicate with the server
The benefit is that, because the properties of the data source can be changed, any code accessing
that data source does not need to be changed.
In the Sun Java System Application Server, a data source is called a JDBC resource.
Where are Properties of a DataSource Defined?
The properties of a DataSource are defined:
‰  In the configuration file of the container
‰  In Sun Java System App Server, they are defined in:
<J2EE_HOME>/domains/domain1/config/domain.xml
‰  In Tomcat, they are defined in server.xml:
<TOMCAT_HOME>/conf/server.xml
DataSource (JDBC Resource) Definition in domain.xml of Sun Java System
AppServer
<resources>
<jdbc-resource enabled="true" jndi-name="jdbc/BookDB" object-type=“
user” pool-name="PointBasePool"/>
<jdbc-connection-pool connection-validation-method=“auto-commit”
datasource-classname="com.pointbase.xa.xaDataSource" failallconnections="
false" idle-timeout-in-seconds="300" is-connectionvalidationrequired="false" is-isolation-level-guaranteed="true" maxpool-size="32"
max-wait-time-in-millis="60000" name="PointBasePool"
pool-resize-quantity="2" res-type="javax.sql.XADataSource"
steadypool-size="8">
<property name="DatabaseName"
value="jdbc:pointbase:server://localhost:9092/sun-appservsamples"/>
<property name="Password" value="pbPublic"/>
<property name="User" value="pbPublic"/>
</jdbc-connection-pool>
</resources>
JNDI Registration of a DataSource Object
‰  A driver that is accessed by a DataSource object does not register itself with the
DriverManager.
‰  Rather, a DataSource object is registered to JNDI naming service by the container and
then retrieved by a client though a lookup operation.
‰  With a basic implementation, the connection obtained through a DataSource object is
identical to a connection obtained through the DriverManager facility.
JNDI Registration of a DataSource (JDBC Resource) Object
The JNDI name of a JDBC resource is expected in the java:comp/env/jdbc subcontext. For
example, the JNDI name for the resource of a BookDB database could be
java:comp/env/jdbc/BookDB.
Because all resource JNDI names are in the java:comp/env subcontext, when you specify the
JNDI name of a JDBC resource, then enter only jdbc/name. For example, for a payroll database,
specify jdbc/BookDB.
Need of Connection Pooling
‰  Database connection is an expensive and limited resource. Using connection pooling,
a smaller number of connections are shared by a larger number of clients.
‰  Creating and destroying database connections are expensive operations.
‰  Using connection pooling, a set of database connections are created at firsthand and
made available in the pool.
‰  Then these connections are provided on requirement basis from the pool and finally
these connections are returned back to the pool.
‰  The important advantage of connection pooling mechanism is that it cuts down the
overhead of creating and destroying database connections.
Connection Pooling and DataSource
‰  DataSource objects that implement connection pooling also produce a connection to
the particular data source that the DataSource class represents.
‰  The Connection object that the getConnection method returns is a handle to a
PooledConnection object rather than being a physical connection. The application
code works in the same way.
Example: PointBasePool
‰  The Sun Java Application Server 8 is distributed with a connection pool named
PointBasePool, which handles connections to the PointBase database server.
‰  Under Sun Java Application Server, each DataSource object is associated with a
connection pool.
Retrieval and Usage of a DataSource Object
‰  Application perform JNDI lookup operation to retrieve DataSource object.
‰  DataSource object is then usedto retrieve a Connection object.
‰  In the web.xml file of the application, information on external resource, DataSource
object in this case, is provided.
‰  For Sun Java System App server, the mapping of external resource and JNDI name is
provided. This providesfurther flexibility.
Example: Retrieval of DataSource Object by JNDI
BookDBAO.java in bookstore1 application:
public class BookDBAO {
private ArrayList books;
Connection con;
private boolean conFree = true;
public BookDBAO() throws Exception {
try {
Context initCtx = new InitialContext();
Context envCtx = (Context)initCtx.lookup("java:comp/env");
DataSource ds = (DataSource)
envCtx.lookup("jdbc/BookDB");
con = ds.getConnection();
} catch (Exception ex) {
throw new Exception("Couldn't open connection to database: "
ex.getMessage());
}
}
}
JNDI Resource Information in web.xml of bookstore1
<resource-ref>
<res-ref-name>jdbc/BookDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>
JNDI and Resource Mapping in sun-web.xml of bookstore1
<sun-web-app>
<context-root>/bookstore1</context-root>
<resource-ref>
<res-ref-name>jdbc/BookDB</res-ref-name>
<jndi-name>jdbc/BookDB</jndi-name>
</resource-ref>
</sun-web-app>
Transaction
‰  One of the main benefits of using a PreparedStatement is executing the statements in
a transactional manner.
‰  The committing of each statement when it is first executed is very time consuming.
‰  By setting AutoCommit to false, the developer can update the database more then
once and then commit the entire transaction as a whole.
‰  Also, if each statement is dependant on the other, the entire transaction can be rolled
back and the user is notified.
JDBC Transaction Methods
The JDBC transaction methods are:
‰  setAutoCommit():If set true, every executed statement is committed immediately
‰  commit():
oRelevant only if setAutoCommit(false)
oCommit operations performed because the opening of a Connection or last
commit() or rollback() calls
‰  rollback():
oRelevant only if setAutoCommit(false)
oCancels all operations performed
Transactions Example
Connection connection = null;
try {
connection =
DriverManager.getConnection("jdbc:oracle:thin:@machinename:1521:db
name","username","password");
connection.setAutoCommit(false);
PreparedStatement updateQty =
connection.prepareStatement("UPDATE STORE_SALES SET QTY = ? WHERE
ITEM_CODE = ? ");
int [][] arrValueToUpdate =
{ {123, 500} ,
{124, 250},
{125, 10},
{126, 350} };
int iRecordsUpdate = 0;
for ( int items=0 ; items < arrValueToUpdate.length ;
items++) {
int itemCode = arrValueToUpdate[items][0];
int qty = arrValueToUpdate[items][1];
updateQty.setInt(1,qty);
updateQty.setInt(2,itemCode);
iRecordsUpdate += updateQty.executeUpdate();
}
connection.commit();
System.out.println(iRecordsUpdate + " record(s) have been updated");
} catch(SQLException sqle) {
System.out.println("" + sqle);
}
try {
connection.rollback();
} catch(SQLException sqleRollback) {
System.out.println("" + sqleRollback);
} finally {
try {
connection.close();
}
catch(SQLException sqleClose) {
System.out.println("" + sqleClose);
}
}
Tips and Tricks:
Provide some key points on the transaction isolation level namely
TRANSACTION_READ_COMMITTED.
Solution:
‰  The programmer sets a property namely, the transaction isolation level, which
determines the transaction isolation level given to the connection that the rowset
establishes.
‰  The owner does not want to read any data that has not been committed, so the
programmer chooses the level TRANSACTION_READ_COMMITTED.
crset.setTransactionIsolation(Connection.TRANSACTION_READ_COMMI
TTED);
‰  When you set the transaction isolation level to TRANSACTION_READ_COMMITTED, it
will not allow a value to be accessed until after it has been committed, and forbids dirty
read.
‰  Now suppose that A and B both have a transaction isolation level of
TRANSACTION_READ_COMMITTED.
‰  This level prohibits reading a value that has changed until after it has been committed,
so neither A nor B will be able to make a "dirty read."
‰  In this situation, a sensitive result set open in transaction A will not show uncommitted
changes that B makes, but it will reflect a value updated by B after B commits the
change.
‰  A sensitive result set open in B will likewise show changes that A makes after A
commits them.
‰  Similar to this level, the other field values for isolation levels provided in the
Connection interface are TRANSACTION_READ_COMMITTED,
TRANSACTION_SERIALIZABLE, TRANSACTION_NONEand
TRANSACTION_REPEATABLE_READ.
‰  By default, the isolation level is set to TRANSACTION_SERIALIZABLE.
Summary
‰  DataSource
oThe DataSource interface provides methods that allow a user to get and set the
character stream to which tracing and error logging will be written.
oA user can trace a specific data source on a given stream, or multiple data
sources can write log messages to the samestream provided that the stream is
set for each data source.
‰  Connection Pooling
oGet the connection with a DataSource object
oAlways close the connection in a finally block.
‰  Distributed Transactions:
oDo not call the methods commit or rollback, and
oDo not set auto-commit mode to true.
Test Your Understanding
1.State true or false for the following:
a)The usage of Connection Pooling mechanism results in cutting down on the
overhead of creating and destroying database connections.
b)By setting AutoCommit to false, the developer cannotupdate the database more
than once and then commit the entire transaction as a whole.