• LOGIN
  • No products in the cart.

Configuring JDBC in Oracle WebLogic Server

WebLogic Server provides database connectivity using data sources. A data source is a pool of database connections from which a connection may be obtained. A data source may be configured separately or combined with other data sources as a multiDataSource. A multi data source is like a pool of data sources configured to supply failover and/or load-balancing across DBMSes (Database Management Systems). A data source is configured with a Java Naming and Directory Interface (JNDI) binding. A dataSource object represents a data source and is obtained using a JNDI lookup. A connection object may be obtained from a dataSource object using the getConnection() method. WebLogic Server provides the Administration Console to configure a data source. WebLogic Server 9.x and later includes Type 4 Java Database Connection (JDBC) drivers from DataDirect. These are branded OEM versions of DataDirect drivers for DB2, Informix, MS SQL Server, Sybase and Oracle databases, as well as the DBMS vendor drivers from Sybase and Oracle. JDBC drivers for other databases may be incorporated in the server by including the Java Archive (JAR) files for the JDBC drivers in the server’s classpath.

What will you learn…
• In this article, you will learn how to configure Java Database Connectivity (JDBC) in Oracle WebLogic Server.

What should you know…
• Familiarity with JDBC including JDBC driver and connection URL is required,
• Installation of Oracle WebLogic Server is a pre-requisite.

 


Deepak Vohra is a Sun Certified Java Programmer, Sun Certified Web Component Developer and Oracle Certified Associate, Oracle Database 10g. The article is an extract from the book JDBC 4.0 and Oracle JDeveloper for J2EE Development with some additions. You can get the book at Amazon http://www.amazon.com/JDBC-Oracle-JDeveloper-J2EE-Development/dp/1847194303/ref=sr_1_1?ie=UTF8&qid=1385392497&sr=8-1&keywords=jdbc+4.0.


 

Each new version of WebLogic Server adds new JDBC features. New JDBC features in WebLogic server 9.0 include support for JDBC 3.0, multiple JNDI names for a data source, and support for “Logging Last Resource Transaction” option. “SQL Statement Timeout” has been added to the connection pool configuration. “SecondsToTrustAnIdlePoolConnection” and “PinnedToThread” connection pool properties, which shall be discussed later in the article, have also been added to improve data source performance. The “MultiDataSource Failover” feature has been improved. Statistics collection has been added to the different connection parameters for performance diagnostics. Identity Based connection pooling was added to WebLogic Server 9.1. “Transaction”, “Diagnostic” and “Security” tabs have been added to the Administration console for configuring a data source. WebLogic Server 9.2 added the support for BEA WebLogic Type 4 JDBC MS SQL Server driver. Some of the other new JDBC features in later versions of WebLogic Server are listed in Table 1.

Table 1. Some of the other new JDBC features in later versions of WebLogic Server are listed
WebLogic Server            Description
  10.3.1 Support for connecting data sources to Oracle RAC Services has been added. The following Sybase JDBC drivers have been removed: Sybase jConnect 4.5 (jConnect.jar), Sybase jConnect 5.5 (jconn2.jar) and Sybase jConnect 6.0 (jconn3.jar).
  10.3.6 Support for application scoped JDBC drivers has been added. The “capacityIncrement” attribute is set to a fixed value and is not configurable. The “Keep Connection After Global Transaction” attribute has been added too, it maintains a connection after committing or rolling back. Both the provision to set database credentials on a connection and support for connection labeling have been added.
  12.1.2 Support for JDBC 4.1 specification with Java 7. Global Data Services (GDS) in a distributed database environment has been added which includes fault tolerance, load balancing and resource utilization. Java DB support has been added.

 

In this article, the following topics will be covered:
•     Create a Data Source in WebLogic Server.
•     Configure Connection Pool Properties for a Data Source.
•     Create a Multi-Data Source in WebLogic Server.
•     Performance Tune WebLogic Server Connections.

Setting the Environment

Install the database requiring a JDBC connection. If a driver for the DBMS has not been pre-installed in WebLogic, download the JDBC driver for the database. In this article, we shall configure JDBC connectivity with the Oracle 10g Database. The Oracle database JDBC driver JAR file, ojdbc14.jar file, is already included in the C:\wls\weblogic\samples\domains\wl_server\lib directory. JAR files in the server lib directory get automatically added to server’s classpath on server startup. If a later version is required, replace it in the lib directory. Alternately, the \samples\domains\wl_server\bin\startWebLogic script can be edited to add any driver jar to the CLASSPATH argument constructed by those scripts. Put the JDBC jar ahead of weblogic.jar in the classpath. weblogic is the directory in which WebLogic Server 9.1 is installed. Double-click on the weblogic\samples\domains\wl_server\startWebLogicEx command script to start the WebLogic examples server.

Creating a Data Source

A data source is a pool of JDBC connections from which a connection may be obtained with the getConnection() method of a DataSource object. In this section, we shall create a data source in the WebLogic Server Administration Console. Access the Administration Console with this URL http://localhost:7001/console. In the Administration Console, select the node Services>JDBC>DataSources as shown in Figure 1.

Figure 1. Creating a new JDBC Data Source

Click on Lock & Edit to activate the Data Sources page buttons.
To create a new JDBC data source, click on New in the Data Sources table as shown in Figure 2.

Figure 2. Data Source Table

In the “Create a New JDBC Data Source” window, specify a data source name and a JNDI name for the data source. A data source is bound to a JNDI naming service with a JNDI name.
Select a Database Type. A data source with the Oracle database will be created as an example. Select Oracle as the Database Type.
Select Oracle’s Driver (Thin) as the Database Driver and click on Next as shown in Figure 3.

Figure 3. Specifying DataSource Properties

A data source may be configured with any of the commonly used databases. WebLogic Server provides Type 4 JDBC drivers from DataDirect for DB2, Informix, Oracle, SQL Server, and Sybase. BEA-branded DataDirect, Sybase jConnect, and Oracle thin drivers are pre-installed in the /server/lib directory. The different JDBC Type 4 drivers included with WebLogic Server are listed in Table 2.

 

Table 2. WebLogic Type 4 JDBC Drivers
Database Versions Supported Driver Classes Connection URL
DB2 UDB 7.x, 8.1 and 8.2 on Linux, Unix, and Windows. XA-weblogic.jdbcx. DB2DataSource

Non XA-weblogic.jdbc. db2.DB2Driver

jdbc:bea:db2: //db2_server_name: port; DatabaseName= database
Informix 9.4 and later versions. XA- weblogic.jdbcx. informix. InformixDataSource

Non XA- weblogic.jdbc. informix.InformixDriver

jdbc:bea:informix: //dbserver1:1543;

informixServer=

dbserver1; databaseName= dbname

MS SQL Server 7.0, SQL Server 2000(SP1, SP2, and SP3a) and SQL Server 2005. XA- weblogic. jdbcx.sqlserver. SQLServerDataSource

Non XA- weblogic.jdbc.sqlserver.

SQLServerDriver

jdbc:bea:sqlserver: //dbserver:port
Oracle 9i (R1 and R2) and Oracle 10g. XA- weblogic.jdbcx .oracle. OracleDataSource

Non XA- weblogic.jdbc.

oracle.OracleDriver

jdbc:bea:oracle:

//dbserver:port

Sybase Adaptive Server 11.5, 11.9, 12.0, 12.5 and 15. XA- weblogic.jdbcx.sybase. SybaseDataSource

Non XA- weblogic.jdbc. sybase.SybaseDriver

jdbc:bea:sybase: //dbserver:port

Databases, other than those having a JDBC driver already installed, may also be selected as shown in Figure 4. If a JDBC driver other than those included with WebLogic is selected, either add the driver zip/JAR file to the CLASSPATH variable in the startWebLogic script or the JAR/zip file to the server’s lib directory. Any JDBC driver to any DBMS can be used by specifying ‘Other’ and entering the driver’s class name manually to the console. Any driver can be added. It needs not be included in the server\lib directory. However, it must be manually added to the classpath in the start-weblogic scripts.

Figure 4. Supported Databases

In the Transaction Options window, the transaction attributes of the data source are specified. If a XA driver is selected, global transactions are automatically supported with Two-Phase commit transaction protocol. A global (or distributed, or XA) transaction is one that involves multiple DBMSes or resources, and requires a 2-Phase commit protocol to ensure an all-or-nothing resolution of the transaction. A global transaction is managed by a Transaction Manager using the Java Transaction Application Interface (JTA). For a non-XA data source to support global transactions, check the Global Transactions checkbox. Select the protocol for which global transactions are supported. The different transaction protocols are listed in Table 3.

Table 3. Transaction Protocols
Transaction Protocol Description
Logging Last Resource (LLR) LLR optimization provides better performance than a XA JDBC driver for insert, update, and delete operations. For read operations performance is better with a XA driver. Recommended over two-phase commit.
Two-Phase Commit Emulates participation in a global transaction using JTA.
One-Phase Commit The default setting. With one-phase commit only one resource may participate in the global transaction.

 

In the “Transaction Options” page, click on Next.
In the “Connection Properties” window, specify the Database Name as ORCL. Specify Host Name as localhost, Port as 1521 and user name as OE. Specify the password for the OE username.
Click on Next as shown in Figure 5.

Figure 5. Specifying Connection Properties

In the Test Database Connection window, the driver’s class name, connection URL, and user name for the Oracle database are specified. Click on Test Database Configuration to test the connection to the database as shown in Figure 6.

Figure 6. Testing Connection

A message is displayed indicating if a connection has been established. If the database does not connect, an error message is displayed.
Click on Next.
In the “Select Targets” window, select a server to which the data source is to be deployed. To deploy to the examples server, select the examplesServer and click on Finish as shown in Figure 7.

Figure 7. Deploying Data Source to a Server

A data source is configured and is added to the Data Sources table as shown in Figure 8. The JNDI name with which the data source is registered with the naming service and the target server to which the data source is deployed are also listed in the data sources table.

Figure 8. New Data Source

To make the data source available to applications in the server, click on the “Activate Changes” button. Until the “Activate Changes” or “Undo All Changes” button is selected, a web application does not get deployed to WebLogic Server.

Configuring a Data Source

In this section, the data source created in the previous section shall be configured. Select the data source to configure from the “Data Sources” table. Select the “Configuration” tab (selected by default) as shown in Figure 9. In the “Configurations” window, the data source JNDI name may be modified.

Figure 9. Configuring Data Source Properties

Other Data Source configuration options are listed in Table 4.

Table 4. Data Source Configuration Options
Data Source Setting Description
Row Prefetch Row prefetch fetches multiple rows from the server to the client in a single server access, thus improving performance.
Row Prefetch Size If row prefetching is enabled, it specifies the number of rows to fetch with row prefetching. Optimal size depends on the query.
Stream Chunk Size Specifies the data chunk size for streaming data types.

 

To configure the connection pool associated with a data source, select the “Connection Pool” link. Initial capacity, maximum capacity, and capacity increment may be set in the connection pool configuration. Advanced connection pool properties may be set with the “Advanced” link. Some of the connection pool settings are listed in Table 5.

Table 5. Connection Pool Settings
Connection Pool Setting     Description
Initial Capacity Initial number of connections in the connection pool. Also specifies the minimum number of available connections in the connection pool.
Maximum Capacity Maximum number of connections in the connection pool.
Capacity Increment Number of connections added to the connection pool in a connection increment.
Statement Cache Type Specifies the algorithm used to cache prepared statements. If a value is LRU, the least recently used statement in the cache is replaced when a new statement is created. If a value is FIXED, the first statements that populate the cache stay there indefinitely.
Statement Cache Size Specifies the number of prepared and callable statements in the cache. The WebLogic Server performance increases by reusing statements in the cache. However, having too many statements in the cache can lead to the limit on open cursors being exceeded.
Test Connections on Reserve If Test Connections on Reserve checkbox is selected, connections are tested before being given to the client. A test is required for connection pools in a multi data source created with the Failover algorithm. If Test Connections on Reserve is selected, Test Table Name should also be specified.
Test Frequency Specifies seconds interval to test unused connections in a connection pool. If the test fails, the connection is closed and reopened. If the test fails again, the connection is closed. If a Test Frequency of more than 0 is specified, a Test Table Name should also be stated.
Test Table Name It’s a database table used to test connections. To improve testing, specify a table with few or no rows. The SQL query to test connection may be specified with: SQL <query>. <query> is used to test a database connection.
Init SQL Specifies an SQL statement to be used in initializing a connection with a database. The SQL statement is specified with: SQL <sql statement>. <sql statement> is used to test a connection. A database table may be specified by not specifying ‘SQL’ at the start of the field. If a database table is specified, a database connection is tested with the SQL statement “SELECT count(*) from InitSQL”.
Shrink Frequency Specifies the wait time for reducing connection pool size to a pre-incremented value.
Connection Creation Retry Frequency Specifies the number of seconds between attempts to establish a connection with a database.
Inactive Connection Timeout Specifies the number of seconds after which an unused connection is returned to the connection pool.
Login Delay Specifies the number of seconds to delay before establishing a connection with a database. Used for database servers that cannot handle successive connection requests.
Maximum Waiting for Connection Specifies the maximum number of connection requests waiting to establish a connection from the connection pool.
Connection Reserve Timeout Specifies the number of seconds after which a connection request shall time out.
Statement Timeout Specifies number of seconds after which a statement shall time out.

 

The transaction protocol settings may be configured with the “Transaction” link. Monitoring statistics may be collected with the “Diagnostics” link. Data source profile information may be collected with the “Profiles” link. Some of the data source profiles which may be collected are listed in Table 6.

Table 6. Data Source Profiles
Profile Description
Profile Connection Usage Collects profile information about threads currently using connections from the connection pool.
Profile Connection Reservation Wait Collects profile information about threads currently waiting to reserve a connection from the connection pool.
Profile Connection Leak Collects profile information about threads that have reserved a connection from the connection pool and the connection leaked.
Profile Connection Usage Collects profile information about threads currently using connections from the connection pool.
Profile Connection Reservation Failed Collects profile information about threads that fail to reserve a connection from the connection pool.
Profile Statement Cache Entry Collects profile information about callable and prepared statements added to the statement cache and about threads that create the statements.
Profile Statement Usage Collects profile information about threads currently executing statements from the statement cache.
Profile Connection Last Usage Collects profile information about previous thread that last used a connection from the connection pool.
Profile Connection Multithreaded Usage Collects profile information about threads that erroneously use a connection previously obtained by a different thread.
Profile Harvest Frequency Refers to interval in seconds between when WebLogic server harvests for profile data.

 

To monitor a data source, select the “Monitoring” tab.
To administer the WebLogic Server instances to which a data source is deployed, select the “Control” tab. In a deployed server instance, the statement cache may be cleared and the server may be suspended or shutdown. Having too many statements can make the limit on open cursors to be exceeded since the number of open cursors that can be created is directly proportional to the number of statements in the cache.

Creating a Multi Data Source

A multi data source is an abstract group of data sources providing failover and load-balancing around data sources. It has a JNDI binding similar to a data source. To create a multi data source, click on the “Services>JDBC>Multi Data Sources” link in the “Administration Console” as shown in Figure 10.

Figure 10. Creating a Multi Data Source

In the “Multi Data Sources” table, click on the New button to create a new multi data source.
In the “Configure the Multi Data Source” window, specify a data source name and a JNDI name for the data source. Select the algorithm type as “FailOver or Load Balancing” as shown in Figure 11.
Then, click on Next.

Figure 11. Specifying Multi Data Source Attributes

In the “Select Targets” window, select the examplesServer, or another server, to deploy the multi data source to as shown in Figure 12 and click on Next.

Figure 12. Deploying a Multi Data Source

In the “Select Data Source Type” window, select XA Driver for XA data source or select Non-XA Driver for a non-XA data source.
Click on Next.
In the “Add Data Sources” window, add data sources from the “Available” list to the “Chosen” list as shown in Figure 13.
If new data sources are required, click on the Create a New Data Source button.
Click on Finish.

Figure 13. Adding Data Sources to Multi Data Source

A new data source is configured and is added to the Multi Data Sources table as shown in Figure 14. Click on the “Activate Changes” button to make the data source available to applications.

Figure 14. New Multi Data Source

A multi data source may be configured by selecting the multi data source link. The targets to which the multi data source is deployed may be configured with the “Targets” tab. The data sources in the multi data source may be configured with the “Data Sources” link in the “Configuration” tab. The multi data source JNDI name may be modified in the “Configuration>General” window. The “Algorithm Type” specifies the algorithm used to select a data source from which a connection is obtained. If algorithm type is Failover, connection requests are always sent to the first data source in the list. If that data source has lost connectivity to the DBMS, the request will be sent successively to the next data source in the list until a connection is obtained or the end of the data source list is reached. If the algorithm type is Load Balancing, connection request load is distributed evenly over the data sources in the list. If load balancing is selected, connection failover is also provided with connection requests being sent to different data sources in the list until a connection gets established or the end of the data source list is reached.
A multi data source also provides the “Failover Request If Busy”, “Failover Callback Handler”, and “Test Frequency” settings. For a multi data source with failover algorithm if “Failover Request If Busy” is selected, the connection request is sent to the next data source if all the connections in a data source are busy. The “Failover Callback Handler” specifies the application class to handle the callback sent when a multi data source is ready to send a failover connection request to another data source. “Test Frequency” specifies the interval in a number of seconds after which connections are tested. If a connection fails, the connection is closed and reopened. If the connection fails again, the connection is closed.
WebLogic Server also supports “Data Source Factories.” Application-scoped connection pools use “JDBC Data Source Factories” to provide default connection pool values.
To configure a Data Source Factory, click on “Services>JDBC>Data Source Factories” link.
Click on New in the “Data Source Factories” table. In “Create a New JDBC Data Source Factory” window, specify a data source factory name, username and password to login to the database, a connection URL, driver class name and factory name, and click on OK.
A data source factory is created and may be deployed to a target server node. The data source factory name is used in the “Administration Console” and a configuration file config.xml is generated. The “Factory Name” is used in the deployment descriptors.

Performance Tuning JDBC

For the best stability and performance, it is best to configure the pool to create all the connections it will need during startup, and retain them indefinitely. Thus, it is recommended to set the initial capacity to equal the maximum capacity (which should usually be one for each execute-thread). The connection pooling provided by WebLogic Server data sources improves performance by keeping a pool of connections available for JDBC applications. Connections do not have to be opened and closed for each client.
“Test Connections on Reserve” tests connections before making a connection available to a client, but connection testing may reduce performance. To prevent frequent connection testing, set the connection pool attribute “Seconds to Trust an Idle Pool Connection”, which specifies the number of seconds for a connection known to have been successfully used, is not tested with a SQL query.
For JDBC done in external client JVMs, using RMI to do JDBC through WebLogic to the DBMS, data source performance may be improved by selecting “Row Prefetch Enabled” and an optimal prefetch size in configuring a data source. Row prefetching improves performance by prefetching multiple rows from the server to an external client. The optimal prefetch size depends on the query and is usually below 100, the default being 2. Row prefetch is not recommended to external clients through WebLogic. It is much faster to do JDBC in the WebLogic JVM itself, in JSPs, servlete, EJBs, etc.
Caching statements improves performance by reusing statements rather than creating new statements. Statement caching is specified in the connection pool configuration.
Listing 1 illustrates an ideal standard for safe WebLogic JDBC coding style. Pooling is fast and is best used in a quick per-invoke fashion: Listing 1.

 

/* This is how you should make any of your top-level methods
* that will do JDBC work for any of your applications.
*/
public void myTopLevelJDBCMethod()
{
Connection c = null; // All JDBC objects should be *method*
// level objects to ensure thread-safety
// and prevent connection leaking.
// Define the connection object before
// the JDBC ‘try’ block.

try {
// This is *the* JDBC try block for this method. Do
// *all* the JDBC for this method in this block.

// Get the connection *directly from our DataSource*
// in the try block. Do *not* get it from any method
// that has kept a connection and is sharing it for
// repeated use.

c = myDataSource.getConnection();

// Do *all* the JDBC for this method in the scope of this try block...
// You *can* pass the connection or sub-objects to sub-methods
// but none of these methods must expect to keep or use the
// objects they receive after their method call completes...

// (eg)

DoSomethingFancyWith(c);

// Use Prepared/Callable Statements. They are faster usually,
// Especially because we cache them transparently with the pool.
PreparedStatement p = c.prepareStatement(...);
ResultSet rs = p.executeQuery();
ProcessResult(myrs);

// Close JDBC objects in the proper order: resultset,
// then statement, then connection

rs.close(); // Always close result sets ASAP at the level they were created
p.close(); // Always close statements ASAP at the level they were created

// When the JDBC is finished in the try-block, close the con:
c.close(); // always close connection ASAP in the same method
// and block that created/obtained it.
c = null; // set the con to null so the finally block below
// knows it’s been taken care of.
}
catch (Exception e ) {
// Do whatever, according to your needs... you do not have to
// have a catch block if you don’t want it...
}
finally {
// Always have this finally block. A finally block is *crucial*
// to ensure the connection is closed and returned to the pool,
// (not leaked).
// failsafe: Do every individual thing you want to do in the
// finally block in it’s own try block-catch-ignore so everything
// is attempted.

try {if (c != null) c.close();} catch (Exception ignore){}
}
}

Summary

In this article, you learnt how to configure JDBC in Oracle WebLogic Server. The procedure to configure a data source including a JNDI was discussed. The procedure to configure a Multi Data Source and Performance tuning of JDBC was also discoursed.

Leave a Reply

avatar

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
Notify of
© HAKIN9 MEDIA SP. Z O.O. SP. K. 2013