Connection Pools

A connection pool is a set of cached database connection objects, created and kept in a pool, which can be multiplexed to provide multiple clients access to a database resource. Figure 7.7 illustrates the concept of a connection pool.

Figure 7.7. Connection pool.

Besides the features explained earlier, note the following important points in favor of connection pools. While programming direct JDBC connections, generally the user ID and password have to be included into the code or have to be picked up from a .properties file. This could turn out to be a sensitive issue in terms of the security of a system. To ensure security of the application, the WebLogic Server keeps the password of a configured connection pool encrypted in the WebLogic system files.

Tip

If the database used for development is different from the one used in production (which almost always is the case), the connection properties have to be changed before deployment on the production system. One of the options for doing this is to pick up these values from a .properties file. A better and easier way is to use different connection pools for development and production databases.

You can implement a connection pool either by maintaining a collection of database connection objects or by writing wrapper JDBC driver classes that can mimic the connection pool functionality. This is not easy, however, since you have to appropriately handle cases such as deadlocks, excessive numbers of connections, closing of connections, and so on in the code.

Alternatively, vendors complying with the J2EE architecture specifications implement the connection pool. The JDBC 2.0 API, a part of the J2EE specification, provides a facility to define and use connection pools in Java applications. JDBC drivers compliant with the JDBC 2.0 API provide this support.

WebLogic provides the functionality of ready-to-use connection pools. These connection pools can be accessed by multitier drivers.

WebLogic maintains a number of driver types to access connection pools via different kinds of applications. Take a quick look at the different ways provided by the JDBC drivers in WebLogic Server 7.0:

• To access a connection pool from server-side HTTP servlets or EJBs, you can use the WebLogic pool driver.

• To access a connection pool from standalone Java applications, client-side multitier JDBC applications, and clustered JDBC applications, you should use WebLogic's multitier JDBC type 3 RMI driver. The type 3 services provided in previous WebLogic Server versions are deprecated in the WebLogic Server version 7.0. Hence, for all new client-side multitier JDBC applications, BEA recommends the use of WebLogic's RMI driver. This type 3 driver uses RMI and a DataSource object to create the connection pool. This driver handles clustered JDBC, thus enhancing WebLogic's inherent load-balancing and failover features for WebLogic clusters. The DataSource object, however, may or may not be enabled for transactional support. Transactional support is an important aspect of database applications to provide integrity to the data persisted or retrieved, especially regarding the ACID properties: atomicity (transaction is either performed completely or not done at all), consistency (no data is partially processed), isolation (intermediate stages are invisible to other transactions), and durability (changes must persist on completion of transaction). Transactions will be explained on Day 14, "Understanding JTA Transactions."

• For applications using the two-phase commit process, use WebLogic Server's JDBC/XA driver.

• For server-side Oracle/XA applications, use WebLogic's jDriver.

• For applications distributed across multiple servers that all connect to the same instance of a database, use WebLogic's JTS driver.

• For JDBC applications deployed in a cluster of WebLogic Servers, use the WebLogic RMI driver. (Clustered applications use a group of servers working together to provide high reliability and scalability to the user. The existence of the multiple servers is hidden from the user/client. The information on all these servers is identical. A clustered JDBC application involves the use of an API or an interface that is available on multiple servers in the cluster, depending on how you configure it.)

WebLogic Server's connection pools are created by registering the connection pool with a naming service or a directory service. The registered connection pool can be retrieved using the Java Naming and Directory Interface (JNDI) API supported by WebLogic Server. Additionally, you can access the connection pools using the DataSource object.

While registering a DataSource through the WebLogic's Administration Console (a tool provided with WebLogic Server to perform administrative tasks), you need to specify the JNDI name with which the WebLogic Server binds the DataSource to its naming service. This configuration of a DataSource is discussed a little later. For now, you must know that each DataSource is associated with a JNDI name on the WebLogic Server. To use this DataSource in your application, you need to perform a lookup and obtain a connection. You can view the name you have specified for your DataSource either through the WebLogic Administration Console or in the config.xml file in the tag <JDBCTxDataSource JNDIName="your DataSource name"/>, where your pool name is specified as one of the parameters.

For example, if the JNDI name you have specified for your data source is datasources-mydatasource, then to open a connection, you would need to perform the following context lookup:

 Context ctx = new IntialContext();  javax.sql.Datasource ds =     (javax.sql.Datasource) ctx.lookup("datasources-mydatasource"); java.sql.Connection myConn = ds.getConnection(); java.sql.Statement stmt = conn.createStatement(); ... 

Connection pools in WebLogic need a two-tier JDBC driver to connect to the DBMS. For this, the WebLogic Server uses the DataSource object, a part of the JDBC 2.0 API and WebLogic RMI driver, to access the two-tier JDBC driver.

The WebLogic Server on startup opens a set of identical JDBC connections to the database. This pool has to be registered on the server using the WebLogic Administration Console. The WebLogic Server maintains a record of which connections are being used and which are open. When a connection to the database is requested, the WebLogic's connection pool manager gives a handle to a free connection from its pool. The calling class uses this connection to perform all its tasks and on completion returns this connection to the pool. In this manner, multiple calls to the database engine to open connections are avoided.

The WebLogic Server implements a pool manager that manages and optimizes the connection pool by dynamically altering its size, depending on the load on the server.

Take a quick look at this code snippet to understand how to get a connection from a connection pool:

 Driver myDriver = (Driver) Class.forName(driverName).newInstance();  conn = myDriver.connect("jdbc:weblogic:pool:" + poolName, null); 

In order to proceed further and try out some examples, you need to have access to a database. WebLogic Server 7.0 comes with an evaluation copy of the PointBase database. Next you will learn a little more about using this database.

PointBase Evaluation Database

PointBase is a pure Java-based RDBMS database that can run on your local machine. You can find some documentation on how to start PointBase in your WebLogic home direc-tory, under samples\server\eval\pointbase\docs, in the files called pbdeveloper.pdf, pbconsole.pdf, and pbsystem.pdf. The database automatically starts up at a separate DOS prompt when you start your examples WebLogic Server. While starting your domain, you need to set the CLASSPATH for pointbase before starting your WebLogic server. Once you have configured your connection pool and started your WebLogic Server, you cannot open the console to the PointBase database. When WebLogic starts up, it creates all the connection pools it needs to create and locks the user ID. Hence, if you try opening the console after starting your WebLogic Server, you will get the error message shown in Figure 7.8.

Figure 7.8. PointBase database lock error message.

If you start up your PointBase console first and then the WebLogic Server, you will not get this error. However, when you try to run your example using connection pools, you will get a java.sql.sqlException stating, "Pool connection failed and the connection pool does not exist." Since the PointBase console was already up, when your WebLogic Server started, WebLogic did not instantiate the connection pool, which results in this error. If you check your WebLogic server console, you will notice the DBMS driver exception thrown during the server initialization, indicating that there is a lock on your database home directory.

To start up your PointBase console, do the following:

Note

The following commands are for Windows systems. For a Unix system, follow the same steps, replacing \with / and %VARIABLE% with $VARIABLE.

1. Open a DOS prompt. (Please note that you must set the variable POINTBASE_HOME to the path of your pointbase home directory. Also, you must check if the names of the jar files being set in the CLASSPATH exist as specified in the next step. If you see a difference in the name, you will need to change it.)

2. Enter the following commands to set the CLASSPATH for pointbase:

    set POINTBASE_HOME=C:\bea\weblogic700\samples\server\eval\pointbase  set CLASSPATH=%POINTBASE_HOME%\lib\pbclient42ECF172.jar; %POINTBASE_HOME%\lib\pbtools42ECF172.jar; %POINTBASE_HOME%\lib\pbserver42ECF172.jar;%CLASSPATH% 

3. To start the PointBase console, type the following command on the DOS prompt, after setting the CLASSPATH:

    java com.pointbase.tools.toolsConsole  

You'll find that it's very convenient to make and save a batch (or command) file for these commands. You can then instantiate the PointBase console just by executing the batch or command file. Figure 7.9 shows the DOS prompt while executing these commands.

Figure 7.9. Running the commands to open the PointBase console.

Once your PointBase console is open, you have the following options:

• Create a new database. (Select the check box at the bottom.)

• Specify the driver that you wish to use.

• Specify the URL of the database when you access it.

• Specify the user name you want to log on with or to create if you are creating a new database or schema.

• Specify the password for an existing user ID and an existing database schema or a new password if you are creating a new database or schema.

Figure 7.10 shows the window you will see when you start your PointBase console and the settings you will make for your sample program.

Figure 7.10. PointBase console to create a new database.

Note the following settings:

• Driver The default is com.pointbase.jdbc.jdbcUniversalDriver.

• URL jdbc:pointbase:learnweblogic7. The value that appears here by default is jdbc:pointbase:sample.

• User system. You can specify any other user here. But keep in mind that you have to enter the same user in your connection pool settings on the WebLogic console and when you use a DataSource. This is discussed in more detail a little later.

• Password password. As stated earlier, note this value, which you will need for your later JDBC trial programs.

Click the Create New Database check box, and then click OK. Congratulations! You have created your first instance of a PointBase database.

Now make a table named transaction_details, to be used for the JDBC code sample tomorrow. This is not going to be a very well developed set of tables. You will use only a single table to store all the data. Here, the important lesson is not the table design but the way to implement JDBC, configure pools, and execute applications in WebLogic using JDBC. You will now see a screen like that shown in Figure 7.11.

Figure 7.11. Creating a table in PointBase after creating the database.

Type this statement at the SQL command prompt of PointBase:

 create table  transaction_details (transaction_id  number(10),          customer_name  varchar2(40), total_amount number(10),         creditcard_no  VARCHAR2(16), card_type VARCHAR2(10)); 

After typing the statement, select the statement, and click the green arrow. The statement is executed, and the table is created.

Now you are all set to extend the shopping cart application tomorrow!