Programming WebLogic JDBC

Introduction and Roadmap

Document Scope and Audience

Guide to this Document

JDBC Samples and Tutorials

Avitek Medical Records Application (MedRec) and Tutorials

JDBC Examples in the WebLogic Server Distribution

New and Changed JDBC Features in This Release

Using WebLogic JDBC in an Application

Getting a Database Connection from a DataSource Object

Importing Packages to Access DataSource Objects

Obtaining a Client Connection Using a DataSource

Possible Exceptions When a Connection Request Fails

Pooled Connection Limitation

Getting a Connection from an Application-Scoped Data Source

Performance Tuning Your JDBC Application

WebLogic Performance-Enhancing Features

How Pooled Connections 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 (Deprecated)

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

Limitations When Using Row Caching

Limitations When Using Global Transactions

Using the WebLogic JTS Driver (Deprecated)

Sample Client Code for Using the JTS Driver

Using the WebLogic Pool Driver (Deprecated)

Using Third-Party Drivers with WebLogic Server

Getting a Connection with Your Third-Party Driver

Using Data Sources with a Third-Party Driver

Using a JNDI Lookup to Obtain the Connection

Getting a Physical Connection from a Data Source

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

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

Types of RowSets

Programming with RowSets

CachedRowSets

Characteristics

Special Programming Considerations and Limitations for CachedRowSets

Code Example

Importing Classes and Interfaces for a CachedRowSet

Creating a CachedRowSet

Setting CachedRowSet Properties

Database Connection Options

Populating a CachedRowSet

Setting CachedRowSet MetaData

Working with Data in a CachedRowSet

Synchronizing RowSet Changes with the Database

RowSet MetaData Settings for Database Updates

WebLogic RowSet Extensions for Working with MetaData

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

Reusing a WebLogic RowSet After Completing a Transaction

FilteredRowSets

FilteredRowSet Characteristics

Special Programming Considerations

FilteredRowSet Code Example

Importing Classes and Interfaces for FilteredRowSets

Creating a FilteredRowSet

Setting FilteredRowSet Properties

Database Connection Options for a FilteredRowSet

Populating a FilteredRowSet

Setting FilteredRowSet MetaData

Setting the Filter for a FilteredRowSet

Working with Data in a FilteredRowSet

WebRowSets

Special Programming Considerations

JoinRowSets

JDBCRowSets

Handling SyncProviderExceptions with a SyncResolver

RowSet Data Synchronization Conflict Types

SyncResolver Code Example

Getting a SyncResolver Object

Navigating in a SyncResolver Object

Setting the Resolved Value for a RowSet Data Synchronization Conflict

Synchronizing Changes

WLCachedRowSets

SharedRowSets

SortedRowSets

SQLPredicate, a SQL-Style RowSet Filter

What is SQLPredicate?

SQLPredicate Grammar

Code Example

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

Performance Options

JDBC Batching

Oracle Batching Limitations

Group Deletes

Troubleshooting JDBC

Problems with Oracle on UNIX

Thread-related Problems on UNIX

Closing JDBC Objects

Abandoning JDBC Objects

Using Microsoft SQL with Nested Triggers

Exceeding the Nesting Level

Using Triggers and EJBs