|
|
|
|
|
|
| | | |
Configuring WebLogic JDBC Features
The following sections describe how to program the JDBC connectivity components:
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. Also see Overview of Connection Pools.
Advantages to Using Connection Pools
Connection Pools provide numerous performance and application design advantages:
The attributes for configuring a connection pool are defined in the Administration Console Online Help. There is also an API that you can use to programmatically create connection pools in a running WebLogic Server; see Creating a Connection Pool Dynamically. You can also use the command line; see the Web Logic Server Command-Line Interface Reference in the Administration Guide.
Connection Pool Fail-Over Requirements
WebLogic Server cannot provide fail-over for connections that fail while being used by an application. Any failure while using a connection requires that you restart the transaction and provide code to handle such a failure.
Creating a Connection Pool at Startup
You set attributes in the in the Administration Console to create a startup (static) connection pool. The WebLogic Server opens JDBC connections to the database during the startup process and adds the connections to the pool.
Here is a list with descriptions of the connection pool attributes. For more information see "Managing JDBC Connectivity" in the Administration Guide and the Administration Console Online Help.
Connection Pool Attributes
Note: If you set a value for this attribute when AllowShrinking is set to false, WebLogic Server ignores the false setting and allows shrinking according to the value in ShrinkPeriodMins.
Notes About Refreshing Connections in a JDBC Connection Pool
When the refresh process finds a bad database connection that it cannot replace, the process stops its current cycle. It does not delete remaining broken connections from the connection pool. They remain in the connection pool until they can be replaced by new connections. This behavior was designed to avoid degrading performance by using system cycles to refresh database connections when the DBMS is inaccessible.
The refresh process cannot test or refresh connections currently being used by application code. It will only test connections that are not currently reserved. Thus a refresh cycle, even if it is able to replace any bad connections it finds, may never test all connections in the connection pool if applications are requesting connections.
Because the refresh process can only test connections not in use, it's possible that some connections will never be tested. A client will always run the risk of getting a broken connection unless testConnsOnReserve is enabled. In fact, even if the connection is tested before being given to an application, the connection could go bad immediately after the successful test.
Set the permissions for creating dynamic connection pools in the Administration Console.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.
If you define an ACL for connection pools, access is restricted to exactly what is defined in the ACL. For example, before you have any ACLs for connection pools in your fileRealm.properties file, everyone has unrestricted access to all connection pools in your domain. However, if you add the following line to the file, access becomes very restricted:
acl.reset.weblogic.jdbc.connectionPool=Administrators
This line grants reset privileges to Administrators on all connection pools and it prohibits all other actions by all other users. By adding an ACL, file realm protection for connection pools is activated. WebLogic Server enforces the ACLs defined in fileRealm.properties and only allows access specifically granted in the file. If your intent in adding the ACL was to restrict resets only on connection pools, you must specifically grant privileges for other actions to everyone or to specific roles or users. For example:
acl.reserve.weblogic.jdbc.connectionPool=everyone acl.shrink.weblogic.jdbc.connectionPool=everyone acl.admin.weblogic.jdbc.connectionPool=everyone
Table 4-1 lists the ACLs that you can use in fileRealm.properties to secure connection pools.
For information on how to modify ACLs, see Defining ACLs in the Managing Security section of the Administration Guide.
When using connection pools, it is possible to execute DBMS-specific SQL code that will alter the database connection properties and that WebLogic Server and the JDBC driver will not be unaware of. When the connection is returned to the connection pool, the characteristics of the connection may not be set back to a valid state. For example, with a Sybase DBMS, if you use a statement such as set rowcount 3 select * from y, the connection will only ever return a maximum of 3 rows. When the connection is returned to the connection pool and then reused, the client will still only get 3 rows returned, even if the table they are selecting against has 500 rows. In most cases, there is standard (non-DBMS-specific) SQL code that can accomplish the same result and for which WebLogic Server or the JDBC driver will reset the connection. In this example, you could use setMaxRows() instead of set rowcount.
If you use DBMS-specific SQL code that alters the connection, you must set the connection back to an acceptable state before returning it to the connection pool.
Creating a Connection Pool Dynamically
A JNDI-based API 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. Access to dynamic connection pools requires a JTS or Pool driver.
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.
To define a specific property for your connection pool, be sure that you duplicate the key's exact spelling and case. You pair these types (keys) along with their values, shown in the table below, in a java.utils.Properties object that is used when creating the pool.
Dynamic Connection Pool Sample Code
The following sample code shows how to create a connection pool programmatically.
Note: The following code samples cannot be used in a clustered environment. As a work-around, you can create connection pools and data sources in the Administration Console as described in the Administration Console Online Help, and target the connection pools and data sources to a cluster.
Import Packages
Import the following packages:
import java.util.Properties import weblogic.common.*; import weblogic.jdbc.common.JdbcServices; import weblogic.jdbc.common.Pool;
Use JNDI to Retrieve the JdbcServices Object
The object reference allows you to access all the methods needed to create the dynamic pool. First, get an initial JNDI context to the WebLogic JNDI provider, and then look up "weblogic.jdbc.common.JdbcServices."
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");
Set up the java.utils.properties object that defines the attributes of your pool. See Table 4-2 Connection Pool Properties in Properties
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 Administration Console, 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".
String thePoolName = "eng2"; Properties poolProps = null; Pool myPool = null; weblogic.jdbc.common.Pool pool = null
poolProps = new Properties();
// Set properties for the ConnectionPool.
poolProps.put("poolName", thePoolName);
poolProps.put("url", "jdbc:weblogic:oracle");
poolProps.put("driver", "weblogic.jdbc.oci.Driver");
poolProps.put("props", "user=scott;password=tiger;server=demo");
poolProps.put("password", "tiger");
poolProps.put("initialCapacity", "1");
poolProps.put("maxCapacity", "10");
poolProps.put("capacityIncrement", "1");
poolProps.put("aclName", "weblogic.jdbc.connectionPool.dynapool");
poolProps.put("allowShrinking", "true");
poolProps.put("shrinkPeriodMins", "5");
poolProps.put("refreshPeriod", "10");
poolProps.put("testConnectionsOnReserve", "true");
poolProps.put("testConnectionsOnRelease", "false");
poolProps.put("testTableName", "dual");
poolProps.put("loginDelaySecs", "1");
Create the pool by passing in the newly defined Properties object to the JdbcServices object previously retrieved from JNDI. An exception is thrown if there is a problem creating the pool, such as a new pool with the same name as an existing pool.
// create our pool
try {
myJdbc.createPool(poolProps);
} catch (Exception e) {
System.out.println(thePoolName
+ " can't be created ..");
System.exit(666);
}
Retrieve the pool handle from the newly created pool.You use the pool handle to manipulate the pool during the course of the application.
weblogic.jdbc.common.Pool myPool = null;
// get our pool, we would like to do something with it...
try {
theNewPool = myJdbc.getPool(thePoolName);
} catch (Exception e) {
System.out.println("Cannot retrieve pool: "
+ thePoolName);
System.exit(666);
}
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.
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.
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.
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.
Use any of the following methods to reset a connection pool:
$ 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.
The last case requires the most work for you, but also gives you flexibility. To reset a pool using the reset() method:
A MultiPool is a "pool of pools." MultiPools contain a configurable algorithm for determining the connection pool from which a connection is returned to an application: either high availability or connection pool load balancing.
MultiPools differ from connection pools in that all the connections in a particular connection pool are created identically with a single database, single user, and the same connection attributes. However, the connection pools within a MultiPool may be associated with different users or DBMSs.
Figure 4-1 MultiPool Architecture
Note that although a Multipool can return connections from multiple databases or with different users, WebLogic Server does not provide any means to integrate or handle the contents of disparate databases. Your application or DBMS environment must handle the synchronization or data integration so that your application will work transparently and successfully when it receives a connection from any of the underlying connection pools.
Choosing the MultiPool Algorithm
Before you set up a MultiPool, you need to determine the primary purpose of the MultiPool—high availability or load balancing. You can choose the algorithm that corresponds with your requirements.
The High Availability algorithm provides an ordered list of connection pools. Normally, every connection request to this kind of MultiPool is served by the first pool in the list. If the first pool loses connectivity to the database, then a connection is sought sequentially from the next pool on the list.
Notes: You must set TestConnectionsOnReserve=true for the connection pools within the MultiPool so that the MultiPool can determine when to fail over to the next connection pool in the list.
If all connections in a connection pool are being used, a MultiPool with the High Availability algorithm will not attempt to provide a connection from the next pool in the list. This is by design so that you can set the capacity for a connection pool. MultiPool fail-over takes effect only if loss of database connectivity has occurred. To avoid this situation, you should increase the maximum number of connections in the connection pool.
Load Balancing
Connection requests to a load balancing MultiPool are served from any connection pool in the list. Pools are added without any attached ordering and are accessed using a round-robin scheme. When switching connections, the connection pool just after the last pool accessed is selected.
MultiPool Fail-Over Limitations and Requirements
WebLogic Server provides the High Availability algorithm for MultiPools so that if a connection pool fails (for example, if the database management system crashes), your system can continue to operate.
Connection pools rely on the TestConnectionsOnReserve feature to know when database connectivity is lost. Connections are not automatically tested before being reserved by an application. You must set TestConnectionsOnReserve=true for the connection pools within the MultiPool. After turning on this feature, WebLogic Server will test each connection before returning it to an application, which is crucial to the High Availability algorithm operation. With the High Availability algorithm, the MultiPool uses the results from testing connections on reserve to determine when to fail over to the next connection pool in the MultiPool. After a test failure, the connection pool attempts to recreate the connection. If that attempt fails, the MultiPool fails over to the next connection pool.
It is possible for a connection to fail after being reserved, in which case your application must handle the failure. WebLogic Server cannot provide fail-over for connections that fail while being used by an application. Any failure while using a connection requires that you restart the transaction and provide code to handle such a failure.
Guidelines to Setting Wait for Connection Times
Setting wait for connection times is a property of the connection attempt. If you are familiar with setting waiting time to pool connections, the wait for connection property applies to every connection tapped in a given connection attempt.
You can add any connection pool to a MultiPool. However, you optimize your resources depending on how you set the wait for connection time when you configure your connection pools.
Users may request information regarding the connection pool from which the connection originated.
Exceptions are posted to the JDBC log under these circumstances:
In a high availability scenario, the fact that the first pool in the list is busy does not trigger an attempt to get a connection from the next pool in the list.
Configuring and Using DataSources
As with Connection Pools and MultiPools, you can create DataSource objects in the Administration Console or using the WebLogic Management API. DataSource objects can be defined with or without transaction services. You configure connection pools and MultiPools before you define the pool name attribute for a DataSource.
DataSource objects, along with the JNDI, provide access to connection pools for database connectivity. Each DataSource can refer to one connection pool or MultiPool. However, you can define multiple DataSources that use a single connection pool. This allows you to define both transaction and non-transaction-enabled DataSource objects that share the same database.
WebLogic Server supports two types of DataSource objects:
If your application meets any of the following criteria, you should use a TxDataSource in WebLogic Server:
For more information about when to use a TxDataSource and how to configure a TxDataSource, see JDBC Configuration Guidelines for Connection Pools, MultiPools, and DataSources.
If you want applications to use a DataSource to get a database connection from a connection pool (the preferred method), you should define the DataSource in the Administration Console before running your application. For instructions to create a DataSource, see the Administration Console Online Help. For instructions to create a TxDataSource, see the Administration Console Online Help.
Importing Packages to Access DataSource Objects
To use the DataSource objects, import the following classes in your client code:
import java.sql.*; import java.util.*; import javax.naming.*;
Obtaining a Client Connection Using a DataSource
To obtain a connection from a JDBC client, use a Java Naming and Directory Interface (JDNI) lookup to locate the DataSource object, as shown in this code fragment:
Context ctx = null;
Hashtable ht = new Hashtable();
ht.put(Context.INITIAL_CONTEXT_FACTORY,
"weblogic.jndi.WLInitialContextFactory");
ht.put(Context.PROVIDER_URL,
"t3://hostname:port");
try {
ctx = new InitialContext(ht);
javax.sql.DataSource ds
= (javax.sql.DataSource) ctx.lookup ("myJtsDataSource");
java.sql.Connection conn = ds.getConnection();
// You can now use the conn object to create // Statements and retrieve result sets:
Statement stmt = conn.createStatement();
stmt.execute("select * from someTable");
ResultSet rs = stmt.getResultSet();
// Close the statement and connection objects when you are finished:
stmt.close();
conn.close();
}
catch (NamingException e) {
// a failure occurred
}
finally {
try {ctx.close();}
catch (Exception e) {
// a failure occurred
}
}
(Substitute the correct hostname and port number for your WebLogic Server.)
Note: The code above uses one of several available procedures for obtaining a JNDI context. For more information on JNDI, see Programming WebLogic JNDI.
See the DataSource code example in the samples/examples/jdbc/datasource directory of your WebLogic Server installation.
|
|
|
|
|
|
Copyright © 2001 BEA Systems, Inc. All rights reserved.
|