|
|
|
Creating and Using Connection Pools
Creating and Using Connection Pools
Creating and Managing Connection Pools
Creating a Startup Connection Pool
Creating a start-up connection pool using the WebLogic jDrivers
Creating a start-up connection pool using third-party JDBC Drivers
Creating a dynamic connection pool
Retrieving information about a pool
Shutting down a connection pool
Refreshing a single pool connection
Refreshing a single pool connection from a T3 client
Setting up ACLs for connection pools in the WebLogic Realm
Using a Connection Pool in an Application
Overview
A connection pool is a named group of identical JDBC connections to a database that are created when the connection pool is registered, usually when starting up WebLogic Server. Your application "borrows" a connection from the pool, uses it, then returns it to the pool by closing it. Connection Pools provide numerous performance and application design advantages:
The connection properties for a start-up connection pool are defined in the weblogic.properties file. There is also an API that you can use to programmatically create connection pools in a running WebLogic Server.
There are several ways to use a connection from a connection pool in your application. See Using a Connection Pool in an Application for more information.
Creating and Managing Connection Pools
Creating a Startup Connection Pool
A startup connection pool is declared in the weblogic.properties file. The WebLogic Server opens JDBC connections to the database during the startup process and adds the connections to the pool.
You define a startup connection pool with an entry using the following pattern in your weblogic.properties file. An example is commented out in the properties file that is shipped with the distribution, under the heading "JDBC Connection Pool Management:"
weblogic.jdbc.connectionPool.VirtualName=\
url=JDBC driver URL,\
driver=full package name for JDBC driver,\
loginDelaySecs=seconds between each login attempt,\
initialCapacity=initial number of connections in the pool,\
maxCapacity=max number of connections in the pool,\
capacityIncrement=number of connections to add at a time,\
allowShrinking=true to allow shrinking,\
shrinkPeriodMins=interval before shrinking,\
testTable=name of table for connection test,\
refreshTestMinutes=interval for connection test,\
testConnsOnReserve=true to test connection at reserve,\
testConnsOnRelease=true to test connection at release,\
props=DBMS connection properties
weblogic.allow.reserve.weblogic.jdbc.connectionPool.name=\
Users who can use this pool
weblogic.allow.reset.weblogic.jdbc.connectionPool.name=\
Users who can reset this pool
weblogic.allow.shrink.weblogic.jdbc.connectionPool.name=\
Users who can shrink this pool
The information that you supply is shown above in italics. Each argument is described in detail below. Some of the arguments are required. If you do not supply a value that is required, an exception is thrown when you start the WebLogic Server.
Here is a short description of the arguments for this property:
This example, taken from the weblogic.properties file that is shipped with the WebLogic distribution, creates a connection pool named "eng," which is accessible to 3 users (Guest, Joe, and Jill). It allocates a minimum of 4 and a maximum of 10 JDBC connections for an Oracle database with a username of "SCOTT," password "tiger," and server name "DEMO." The WebLogic Server sleeps for 1 second between each connection attempt to prevent refused logins from a DBMS that may be under load or on a saturated network. The connection pool shrinks back to 4 connections when connections in the pool are unused for 15 minutes or more. Every 10 minutes, unused connections are tested and refreshed if they have gone stale.
weblogic.jdbc.connectionPool.eng=\
url=jdbc:weblogic:oracle,\
driver=weblogic.jdbc.oci.Driver,\
loginDelaySecs=1,\
initialCapacity=4,\
maxCapacity=10,\
capacityIncrement=2,\
allowShrinking=true,\
shrinkPeriodMins=15,\
refreshTestMinutes=10,\
testTable=dual,\
props=user=SCOTT;password=tiger;server=DEMO
weblogic.allow.reserve.weblogic.jdbc.connectionPool.eng=\
guest,joe,jill
weblogic.allow.reset.weblogic.jdbc.connectionPool.eng=\
joe,jill
weblogic.allow.shrink.weblogic.jdbc.connectionPool.eng=\
joe,jill
Note that if you have a username with a null password, you shouldn't enter an empty string for the password in the connection pool registration; rather you should simply leave it blank. Here is an example taken from the WebLogic Administrators Guide document on properties:
weblogic.jdbc.connectionPool.eng=\
url=jdbc:weblogic:oracle,\
driver=weblogic.jdbc.oci.Driver,\
loginDelaySecs=1,\
initialCapacity=4,\
capacityIncrement=2,\
maxCapacity=10,\
props=user=sa;password=;server=demo
weblogic.allow.reserve.weblogic.jdbc.connectionPool.eng=guest,joe,jill
Creating a start-up connection pool using the WebLogic jDrivers
The links below contain sample connection pool definitions for each of the WebLogic jDrivers. The WebLogic jDrivers are two-tier JDBC drivers.
Creating a start-up connection pool using third-party JDBC Drivers
Creating a dynamic connection pool
A JNDI-based API introduced in WebLogic release 4.0 allows you to create a connection pool from within a Java application. With this API, you can create a connection pool in a WebLogic Server that is already running.
Dynamic pools can be temporarily disabled, which suspends communication with the database server through any connection in the pool. When a disabled pool is enabled, the state of each connection is the same as when the pool was disabled; clients can continue their database operations right where they left off.
A property in the weblogic.properties file, weblogic.allow.admin.weblogic.jdbc.connectionPoolcreate, determines who can create dynamic connection pools. If the property is not set, then only the "system" user can create a dynamic connection pool.
For example, the following property allows users "joe" and "jane" to create dynamic connection pools:
weblogic.allow.admin.weblogic.jdbc.connectionPoolcreate=joe,jane
You can also create ACLs for dynamic connection pools by adding weblogic.allow.reserve.ACLname and weblogic.allow.admin.ACLname entries to the weblogic.properties file. For example, the following two properties define an ACL named "dynapool" that allows anyone (the "everyone" group) to use a connection pool, and users "joe" and "jane" to administer a connection pool:
weblogic.allow.admin.dynapool=joe,jane
weblogic.allow.reserve.dynapool=everyone
You associate an ACL with a dynamic connection pool when you create the connection pool. The ACL and connection pool are not required to have the same name, and more than one connection pool can make use of a single ACL. If you do not specify an ACL, the "system" user is the default administrative user for the pool and any user can use a connection from the pool.
To create a dynamic connection pool in a Java application, you get an initial JNDI context to the WebLogic JNDI provider, and then look up "weblogic.jdbc.common.JdbcServices." This example shows how this is done:
Hashtable env = new Hashtable();
env.put(Context.INITIAL_CONTEXT_FACTORY,
"weblogic.jndi.WLInitialContextFactory");
// URL for the WebLogic Server
env.put(Context.PROVIDER_URL, "t3://localhost:7001");
env.put(Context.SECURITY_PRINCIPAL, "Fred");
env.put(Context.SECURITY_CREDENTIALS, "secret");
Context ctx = new InitialContext(env);
// Look up weblogic.jdbc.JdbcServices
weblogic.jdbc.common.JdbcServices jdbc =
(weblogic.jdbc.common.JdbcServices)
ctx.lookup("weblogic.jdbc.JdbcServices");
Once you have loaded weblogic.jdbc.JdbcServices, you pass the weblogic.jdbc.common.JdbcServices.createPool() method a Properties object that describes the pool. The Properties object contains the same properties you use to create a connection pool in the weblogic.properties file, except that the "aclName" property is specific to dynamic connection pools.
The following example creates a connection pool named "eng2" for the DEMO Oracle database. The connections log into the database as user "SCOTT" with password "tiger." When the pool is created, one database connection is opened. A maximum of ten connections can be created on this pool. The "aclName" property specifies that the connection pool will use the "dynapool" ACL in the weblogic.properties file.
weblogic.jdbc.common.Pool pool = null;
try {
// Set properties for the Connection Pool.
// The properties are the same as those used to define a startup
// connection pool in the weblogic.properties file.
Properties poolProps = new Properties();
poolProps.put("poolName", "eng2");
poolProps.put("url", "jdbc:weblogic:oracle");
poolProps.put("driver", "weblogic.jdbc.oci.Driver");
poolProps.put("initialCapacity", "1");
poolProps.put("maxCapacity", "10");
poolProps.put("props", "user=SCOTT;
password=tiger;server=DEMO");
poolProps.put("aclName", "dynapool"); // the ACL to use
// Creation fails if there is an existing pool
// with the same name.
jdbc.createPool(poolProps);
}
catch (Exception e) {
system.out.Println("Error creating connection pool eng2.");
}
finally { // close the JNDI context
ctx.close();
}
Managing connection pools
The weblogic.jdbc.common.Pool and weblogic.jdbc.common.JdbcServices interfaces provide methods to manage connection pools and obtain information about them. Methods are provided for:
Retrieving information about a pool
The poolExists() method tests whether a connection pool with a specified name exists in the WebLogic Server. You can use this method to determine whether a dynamic connection pool has already been created or to ensure that you select a unique name for a dynamic connection pool you want to create.
The getProperties() method retrieves the properties for a connection pool.
Disabling a connection pool
You can temporarily disable a connection pool, preventing any clients from obtaining a connection from the pool. Only the "system" user or users granted "admin" permission by an ACL associated with a connection pool can disable or enable the pool.
After you call disableFreezingUsers(), clients that currently have a connection from the pool are suspended. Attempts to communicate with the database server throw an exception. Clients can, however, close their connections while the connection pool is disabled; the connections are then returned to the pool and cannot be reserved by another client until the pool is enabled.
Use disableDroppingUsers() to not only disable the connection pool, but to destroy the client's JDBC connection to the pool. Any transaction on the connection is rolled back and the connection is returned to the connection pool. The client's JDBC connection context is no longer valid.
When a pool is enabled after it has been disabled with disableFreezingUsers(), the JDBC connection states for each in-use connection are exactly as they were when the connection pool was disabled; clients can continue JDBC operations exactly where they left off.
You can also use the disable_pool and enable_pool commands of the weblogic.Admin class to disable and enable a pool.
Shrinking a connection pool
A connection pool has a set of properties that define the initial and maximum number of connections in the pool (initialCapacity and maxCapacity), and the number of connections added to the pool when all connections are in use (capacityIncrement). When the pool reaches its maximum capacity, the maximum number of connections are opened, and they remain opened unless you shrink the pool.
You may want to drop some connections from the connection pool when a peak usage period has ended, freeing up resources on the WebLogic Server and DBMS.
Shutting down a connection pool
These methods destroy a connection pool. Connections are closed and removed from the pool and the pool dies when it has no remaining connections. Only the "system" user or users granted "admin" permission by an ACL associated with a connection pool can destroy the pool.
The shutdownSoft() method waits for connections to be returned to the pool before closing them.
The shutdownHard() method kills all connections immediately. Clients using connections from the pool get exceptions if they attempt to use a connection after shutdownHard() is called.
You can also use the destroy_pool command of the weblogic.Admin class to destroy a pool.
Resetting a pool
You can configure a connection pool to test its connections either periodically, or every time a connection is reserved or released. Allowing the WebLogic Server to automatically maintain the integrity of pool connections should prevent most DBMS connection problems. In addition, WebLogic provides methods you can call from an application to refresh all connections in the pool or a single connection you have reserved from the pool.
The weblogic.jdbc.common.Pool.reset() method closes and reopens all allocated connections in a connection pool. This may be necessary after the DBMS has been restarted, for example. Often when one connection in a connection pool has failed, all of the connections in the pool are bad.
Refreshing a single pool connection
You can refresh a single connection from a connection pool, or reset the entire connection pool, if one or more connections in the pool go stale. For example, if the DBMS is taken down while the WebLogic Server is actively supporting a pool of connections. A connection pool autorefresh feature can also be enabled to periodically test and refresh connections.
There are only certain instances when resetting a pool is appropriate. You should never use this feature as a routine part of a user program. Usually, what makes resetting a connection pool necessary is that the DBMS has gone down and the connections in the pool are no longer viable. Attempting to reset the pool before you are certain that the DBMS is up and available again will cause an Exception to be thrown. Resetting a pool should always be a special operation that is carried out by a user with administrative privileges.
There are several ways to reset connections pools:
$ java weblogic.Admin WebLogicURL RESET_POOL poolName system passwd
You might use this method from the command line on an infrequent basis. There are more efficient programmatic ways that are also discussed here. For more on the Admin commands, read the WebLogic Administrators Guide, Running and maintaining the WebLogic Server.
The last case requires the most work for you, but also gives you more flexibility than the first two. Here how to reset a pool using the reset() method:
Refreshing a single pool connection from a T3 client
To refresh a single connection, use the refresh() method in the weblogic.jdbc.t3.Connection class. When you call this method, you lose any Statements and Resultsets you had on the connection, plus your application incurs the relatively high cost of opening the connection. Therefore, we recommend that you only refresh a single connection when you get an error that implies that the connection has gone bad.
You will need to explicitly cast the JDBC Connection as a (weblogic.jdbc.t3.Connection). Other than that, the refresh() method in the Connection class is used in the same way the reset() method is used for a connection pool. First try an execute action on the Connection that is guaranteed to succeed if the Connection itself is viable. Catch the exception and call the refresh() method.
Here is an example that uses a T3 client to test and, if necessary, refresh the pool. Notice that we cast the JDBC Connection as a weblogic.jdbc.t3.Connection in the last line of this example, in order to take advantage of the WebLogic extension to JDBC.
Note: The T3 client services are scheduled for deprecation in a future release of WebLogic Server.
T3Client t3 = new T3Client("t3://localhost:7001");
t3.connect();
Properties t3props = new Properties();
t3props.put("weblogic.t3", t3);
t3props.put("weblogic.t3.connectionPoolID", "eng");
Class.forName("weblogic.jdbc.t3.Driver").newInstance();
Connection conn = DriverManager.getConnection
("jdbc:weblogic:t3", t3props);
Connection conn = null;
try {
Statement stmt = conn.createStatement();
// This SQL is guaranteed to succeed over a good connection
// to an Oracle DBMS
ResultSet rs = stmt.executeQuery("select 1 from dual");
if (rs != null) {
while (rs.next()) {;}
}
rs.close();
stmt.close();
conn.close();
}
catch(SQLException e) {
// We cast the JDBC Connection as a
// weblogic.jdbc.t3.Connection
// and call the refresh() method on it
((weblogic.jdbc.t3.Connection)conn).refresh();
}
Setting up ACLs for connection pools in the WebLogic Realm
WebLogic controls access to internal resources like JDBC connection pools through ACLs set up in the WebLogic Realm. Entries for ACLs in the WebLogic Realm are listed as properties in the weblogic.properties file.
You can set the Permissions "reserve," "reset," and "shrink" for JDBC connections in a connection pool by entering a property in the properties file. Setting a Permission for the ACL "weblogic.jdbc.connectionPool" limits access to all connection pools. Add Permissions for other users by adding an entry for the ACL name "weblogic.jdbc.connectionPool.poolID," which controls access to the connection pool poolID. The special user system always has Permissions "reserve," "reset," and "shrink" for every ACL, no matter what other Permissions have been set.
Example:
weblogic.allow.reserve.weblogic.jdbc.connectionPool.eng=
margaret,joe,mary weblogic.allow.reset.weblogic.jdbc.connectionPool.eng=
sysMonitor weblogic.allow.shrink.weblogic.jdbc.connectionPool.eng=
sysMonitor
For backwards compatibility, you can also use the old-style property syntax to grant permission for "reserve" by setting a userlist for the property weblogic.jdbc.connectionPool.poolID=allow=. It is recommended that you upgrade your properties file as soon as possible to reflect the new usage, since WebLogic cannot guarantee how long it will support old-style properties.
Using a Connection Pool in an Application
Server-side Applications
For database access from server-side applications, such as HTTP servlets, use the WebLogic Pool or JTS driver. For database access using Enterprise JavaBeans (EJB) running in WebLogic Server, use the WebLogic JTS driver. The JTS driver provides transactional support for database operations.You can also access connection pools using the Java Naming and Directory Interface (JNDI) and a DataSource object. For more information, see the following documents:
BEA offers two possibilities for client-side, multitier JDBC. The WebLogic JDBC/RMI driver has the advantage of providing a standards-based approach using the Java 2 Enterprise Edition (J2EE) specifications, while the WebLogic T3 driver uses the proprietary WebLogic T3 client service. For new deployments, BEA recommends that you use the JDBC/RMI driver; the T3 client services are scheduled for deprecation in a future release of WebLogic Server.
The WebLogic JDBC/RMI driver is a type-3 JDBC driver using RMI and a DataSource object to create database connections. This driver also provides for clustered JDBC, leveraging the load balancing and failover features of WebLogic Clusters. DataSource objects may be defined to enable transactional support or not.
Describes using the WebLogic T3 driver for multi-tier JDBC. and the use of connection pools.
Note: The T3 client services are scheduled for deprecation in a future release of WebLogic Server.
|
|
|
Copyright © 2000 BEA Systems, Inc. All rights reserved.
|