Programming WebLogic JDBC

Introduction to WebLogic JDBC

Overview of JDBC

Using JDBC Drivers with WebLogic Server

Types of JDBC Drivers

Table of WebLogic Server JDBC Drivers

Selecting a JDBC Driver

WebLogic Server JDBC Drivers

WebLogic jDriver for Oracle (Deprecated)

BEA WebLogic Type 4 JDBC Driver for Microsoft SQL Servers

WebLogic jDriver for Microsoft SQL Server (Deprecated)

WebLogic Server Wrapper Drivers

WebLogic RMI Driver

WebLogic Pool Driver

WebLogic JTS Driver

Third-Party JDBC Drivers

Oracle Thin Driver

Overview of Connection Pools

Using Connection Pools with Server-side Applications

Using Connection Pools with Client-side Applications

Overview of MultiPools

Overview of Clustered JDBC

Overview of DataSources

JDBC API

JDBC 2.0

Platforms

Configuring and Using WebLogic JDBC

Configuring and Using Connection Pools

Advantages to Using Connection Pools

Creating a Connection Pool at Startup

Avoiding Server Lockup with the Correct Number of Connections

Database Passwords in Connection Pool Configuration

SQL Statement Timeout Enhancements for Pooled JDBC Connections

JDBC Connection Pool Testing Enhancements

Minimizing Connection Test Delay After Database Connectivity Loss

Minimizing Connection Request Delay After Connection Test Failures

Minimizing Connection Request Delay with Seconds to Trust an Idle Pool Connection

Creating a Connection Pool Dynamically

Dynamic Connection Pool Sample Code

Import Packages

Look Up the Administration MBeanHome

Get the Server MBean

Create the Connection Pool MBean

Set the Connection Pool Properties

Add the Target

Create a DataSource

Removing a Dynamic Connection Pool and DataSource

Configuring and Using DataSources

Importing Packages to Access DataSource Objects

Obtaining a Client Connection Using a DataSource

Possible Exceptions When a Connection Request Fails

Connection Pool Limitation

Managing Connection Pools

Getting Status and Statistics for a Connection Pool

Enabling Connection Creation Retries

Initializing Connections with a SQL Query

Testing Connection Pools and Database Connections

Enabling Connection Requests to Wait for a Connection

Connection Reserve Timeout

Limiting the Number of Waiting Connection Requests

Configuring and Managing the Statement Cache for a Connection Pool

Configuring the Statement Cache

Deprecated Statement Cache Configuration Options

Clearing the Statement Cache for a Connection Pool

Clearing the Statement Cache for a Single Connection

Shrinking a Connection Pool

Resetting a Connection Pool

Suspending a Connection Pool

Resuming a Connection Pool

Configuring and Using Application-Scoped JDBC Connection Pools

Configuring Application-Scoped Connection Pools

Required Elements Within the jdbc-connection-pool Element

Encrypting the Database Password in weblogic-application.xml

Deprecated Statement Cache Configuration Options for Application-Scoped Connection Pools

Getting a Connection from an Application-Scoped Connection Pool

Configuring and Using MultiPools

Configuring MultiPools

Choosing the MultiPool Algorithm

High Availability

Load Balancing

Transaction Support in JDBC MultiPools

Transaction Failover Processing for MultiPools

MultiPool Failover Enhancements

Connection Request Routing Enhancements When a Connection Pool Fails

Automatic Re-enablement on Recovery of a Failed Connection Pool within a MultiPool

Enabling Failover for Busy Connection Pools in a MultiPool

Controlling MultiPool Failover with a Callback

Controlling MultiPool Failback with a Callback

MultiPool Fail-Over Limitations and Requirements

Test Connections on Reserve to Enable Fail-Over

By Default, No Fail-Over When All Connections are In Use

Do Not Enable Connection Creation Retries

No Fail-Over for In-Use Connections

Performance Tuning Your JDBC Application

WebLogic Performance-Enhancing Features

How Connection Pools Enhance Performance

Caching Statements and Data

Designing Your Application for Best Performance

1. Process as Much Data as Possible Inside the Database

2. Use Built-in DBMS Set-based Processing

3. Make Your Queries Smart

4. Make Transactions Single-batch

5. Never Have a DBMS Transaction Span User Input

6. Use In-place Updates

7. Keep Operational Data Sets Small

8. Use Pipelining and Parallelism

Using WebLogic Wrapper Drivers

Using the WebLogic RMI Driver

Setting Up WebLogic Server to Use the WebLogic RMI Driver

Sample Client Code for Using the RMI Driver

Import the Required Packages

Get the Database Connection

Using a JNDI Lookup to Obtain the Connection

Using Only the WebLogic RMI Driver to Obtain a Database Connection

Row Caching with the WebLogic RMI Driver

Important Limitations for Row Caching with the WebLogic RMI Driver

Using the WebLogic JTS Driver

Sample Client Code for Using the JTS Driver

Using the WebLogic Pool Driver

Using Third-Party Drivers with WebLogic Server

Overview of Third-Party JDBC Drivers

Using Third-Party JDBC Drivers Installed with WebLogic Server

Using Third-Party JDBC Drivers not Installed with WebLogic Server

Using the Oracle Thin Driver

Updating the Oracle 10g Driver

Using the Oracle 9.2 Driver

Package Change for Oracle Thin Driver 9.x and 10g

Character Set Support with nls_charset12.zip

Using the Oracle Thin Driver in Debug Mode

Updating the Sybase jConnect Driver

Installing and Using the IBM DB2 Type 2 JDBC Driver

Connection Pool Attributes when using the IBM DB2 Type 2 JDBC Driver

Installing and Using the SQL Server 2000 Driver for JDBC from Microsoft

Installing the MS SQL Server JDBC Driver on a Windows System

Installing the MS SQL Server JDBC Driver on a Unix System

Connection Pool Attributes when using the Microsoft SQL Server Driver for JDBC

Installing and Using the IBM Informix JDBC Driver

Connection Pool Attributes when using the IBM Informix JDBC Driver

Programming Notes for the IBM Informix JDBC Driver

Getting a Connection with Your Third-Party Driver

Using Connection Pools with a Third-Party Driver

Creating the Connection Pool and DataSource

Using a JNDI Lookup to Obtain the Connection

Getting a Physical Connection from a Connection Pool

Opening a Connection

Closing a Connection

Limitations for Using a Physical Connection

Using Vendor Extensions to JDBC Interfaces

Sample Code for Accessing Vendor Extensions to JDBC Interfaces

Import Packages to Access Vendor Extensions

Get a Connection

Cast the Connection as a Vendor Connection

Use Vendor Extensions

Using Oracle Extensions with the Oracle Thin Driver

Limitations When Using Oracle JDBC Extensions

Sample Code for Accessing Oracle Extensions to JDBC Interfaces

Programming with ARRAYs

Import Packages to Access Oracle Extensions

Establish the Connection

Getting an ARRAY

Updating ARRAYs in the Database

Using Oracle Array Extension Methods

Programming with STRUCTs

Getting a STRUCT

Using OracleStruct Extension Methods

Getting STRUCT Attributes

Using STRUCTs to Update Objects in the Database

Creating Objects in the Database

Automatic Buffering for STRUCT Attributes

Programming with REFs

Getting a REF

Using OracleRef Extension Methods

Getting a Value

Updating REF Values

Creating a REF in the Database

Programming with BLOBs and CLOBs

Query to Select BLOB Locator from the DBMS

Declare the WebLogic Server java.sql Objects

Begin SQL Exception Block

Updating a CLOB Value Using a Prepared Statement

Programming with Oracle Virtual Private Databases

Oracle VPD with WebLogic Server 8.1SP2

Support for Vendor Extensions Between Versions of WebLogic Server Clients and Servers

Tables of Oracle Extension Interfaces and Supported Methods

Using RowSets with WebLogic Server

About RowSets

Creating RowSets

Working with Data in a RowSet

Populating a RowSet

Populating a RowSet from an Existing ResultSet

Populating a RowSet from a DataSource and Query

Retrieving Data from a RowSet

Updating Data in a RowSet

Deleting Data from a RowSet

Inserting Data into a RowSet

Flushing Changes to the Database

RowSet Meta Data

Optimistic Concurrency Policies

VERIFY_READ_COLUMNS

VERIFY_MODIFIED_COLUMNS

VERIFY_SELECTED_COLUMNS

VERIFY_NONE

VERIFY_AUTO_VERSION_COLUMNS

VERIFY_VERSION_COLUMNS

Optimistic Concurrency Control Limitations

Choosing an Optimistic Policy

MetaData Settings for RowSet Updates

executeAndGuessTableName and executeAndGuessTableNameAndPrimaryKeys

Setting Table and Primary Key Information Using the MetaData Interface

Setting the Write Table

RowSets and Transactions

Integrating with JTA Global Transactions

Behavior of Rowsets Using Global Transactions

Using Local Transactions

Behavior of Rowsets Using Local Transactions

Performance Options

JDBC Batching

Oracle Batching Limitations

Group Deletes

RowSets and XML

Writing a RowSet Instance as XML

Populating a RowSet from an XML Document

JDBC Type to XML Schema Type Mapping

XML Schema Type to JDBC Type Mapping

Multi-table RowSet Mapping

Multi-Table RowSet Example

Testing JDBC Connections and Troubleshooting

Monitoring JDBC Connectivity

Validating a DBMS Connection from the Command Line

Syntax

Arguments

Examples

Troubleshooting JDBC

JDBC Connections

Windows

UNIX

Codeset Support

Thread-related Problems on UNIX

Closing JDBC Objects

Abandoning JDBC Objects

Troubleshooting Problems with Shared Libraries on UNIX

WebLogic jDriver for Oracle

Solaris

HP-UX

Incorrectly Set File Permissions

Incorrect SHLIB_PATH

Using Microsoft SQL with Nested Triggers

Exceeding the Nesting Level

Using Triggers and EJBs

Using WebLogic Server with Oracle RAC

Overview of Oracle Real Application Clusters

Oracle RAC Scalability with WebLogic Server

Oracle RAC Availability with WebLogic Server

Oracle RAC Load Balancing with WebLogic Server

Oracle RAC Failover with WebLogic Server

Environment

Hardware Requirements

WebLogic Server Cluster

Oracle RAC Cluster

Shared Storage

Software Requirements

Configuration Considerations for Oracle

Configuring the Listener Process for Each Oracle RAC Instance

Disabling Remote Listeners

Configuration Options in WebLogic Server with Oracle RAC

Choosing a WebLogic Server Configuration for Use with Oracle RAC

Required JDBC Drivers

Configuration Considerations for Failover

MultiPool-Managed Failover

Connect-Time Failover

Delays During Failover

Failure Handling Walkthrough for Global Transactions

Using MultiPools with Oracle RAC

Attributes of a MultiPool

Using MultiPools with Global Transactions

Rules for Connection Pools within a MultiPool Using Global Transactions

Required Attributes of Connection Pools within a MultiPool Using Global Transactions

Sample config.xml Code

Using MultiPools without Global Transactions

Attributes of Connection Pools within a MultiPool Not Using Global Transactions

Sample config.xml Code

Using Connect-Time Failover with Oracle RAC

Using Connect-Time Failover without Global Transactions

Attributes of a Connect-Time Failover Configuration without Global Transactions

Sample config.xml Code

Using Connect-Time Failover with Global Transactions

Attributes of a Connect-Time Failover Configuration with Global Transactions

Sample config.xml Code

XA Considerations and Limitations with Oracle 9i RAC

Required JDBC Driver Configuration for Use with XA

Oracle 9i RAC XA Requirements

A Global Transaction Must Be Initiated, Prepared, and Concluded in the Same Instance of the RAC Cluster

Transaction IDs Must Be Unique Within the RAC Cluster

Known Limitations When Using Oracle RAC with WebLogic Server

Potential for Inconsistent Transaction Completion (Data Loss) in Some Failure Conditions