Oracle Database JDBC Developer’s Guide and Reference

[Pages:100]Oracle? Database

JDBC Developer's Guide and Reference 10g Release 1 (10.1)

Part No. B10979-02

June 2004 This book describes how to use the Oracle JDBC drivers to develop powerful Java database applications.

Oracle Database JDBC Developer's Guide and Reference 10g Release 1 (10.1)

Part No. B10979-02

Copyright ? 1999, 2004, Oracle. All rights reserved.

Primary Author: Elizabeth Hanes Perry, Brian Wright, Thomas Pfaeffle

Contributing Author: Brian Martin

Contributor: Kuassi Mensah, Magdi Morsi, Ron Peterson, Ekkehard Rohwedder, Ashok Shivarudraiah, Catherine Wong, Ed Shirk, Tong Zhou, Longxing Deng, Jean de Lavarene, Rosie Chen, Sunil Kunisetty, Joyce Yang, Mehul Bastawala, Luxi Chidambaran, Srinath Krishnaswamy, Rajkumar Irudayaraj, Scott Urman, Jerry Schwarz, Steve Ding, Soulaiman Htite, Douglas Surber, Anthony Lai, Paul Lo, Prabha Krishna, Ellen Siegal, Susan Kraft, Sheryl Maring, Angie Long

The Programs (which include both the software and documentation) contain proprietary information; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly, or decompilation of the Programs, except to the extent required to obtain interoperability with other independently created software or as specified by law, is prohibited.

The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. This document is not warranted to be error-free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose.

If the Programs are delivered to the United States Government or anyone licensing or using the Programs on behalf of the United States Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the Programs, including documentation and technical data, shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement, and, to the extent applicable, the additional rights set forth in FAR 52.227-19, Commercial Computer Software--Restricted Rights (June 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065

The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup, redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and we disclaim liability for any damages caused by such use of the Programs.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

The Programs may provide links to Web sites and access to content, products, and services from third parties. Oracle is not responsible for the availability of, or any content provided on, third-party Web sites. You bear all risks associated with the use of such content. If you choose to purchase any products or services from a third party, the relationship is directly between you and the third party. Oracle is not responsible for: (a) the quality of third-party products or services; or (b) fulfilling any of the terms of the agreement with the third party, including delivery of products or services and warranty obligations related to purchased products or services. Oracle is not responsible for any loss or damage of any sort that you may incur from dealing with any third party.

Portions of this software are copyrighted by MERANT, 1991-2001.

Contents

Send Us Your Comments ...................................................................................................................... xxi

Preface ............................................................................................................................................................. xxiii

Intended Audience.................................................................................................................................. xxiii Documentation Accessibility ................................................................................................................. xxiii Structure ................................................................................................................................................... xxiv Related Documents ................................................................................................................................. xxv Conventions ............................................................................................................................................ xxvii

1 Overview

What is JDBC?........................................................................................................................................... 1-1 Overview of the Oracle JDBC Drivers ................................................................................................. 1-1

Common Features of Oracle JDBC Drivers .................................................................................... 1-2 JDBC Thin Driver ............................................................................................................................... 1-3 JDBC OCI Driver ................................................................................................................................ 1-3 JDBC Server-Side Thin Driver.......................................................................................................... 1-4

About Permission for the Server-Side Thin Driver................................................................ 1-4 JDBC Server-Side Internal Driver .................................................................................................... 1-4 Choosing the Appropriate Driver.................................................................................................... 1-5 Overview of Application and Applet Functionality ......................................................................... 1-5 Applet Basics....................................................................................................................................... 1-5

Applets and Security .................................................................................................................. 1-5 Applets and Firewalls ................................................................................................................ 1-6 Packaging and Deploying Applets........................................................................................... 1-6 Oracle Extensions ............................................................................................................................... 1-6 Server-Side Basics .................................................................................................................................... 1-6 Session and Transaction Context ..................................................................................................... 1-6 Connecting to the Database .............................................................................................................. 1-7 Environments and Support .................................................................................................................... 1-7 Supported JDK and JDBC Versions................................................................................................. 1-7 Backward Compatibility ............................................................................................................ 1-7 Forward Compatibility .............................................................................................................. 1-7 JNI and Java Environments .............................................................................................................. 1-7 JDBC and IDEs.................................................................................................................................... 1-7 Changes At This Release ........................................................................................................................ 1-8

iii

New Features ...................................................................................................................................... 1-8 Deprecated Features ....................................................................................................................... 1-10 Desupported Features .................................................................................................................... 1-10 Interface Changes............................................................................................................................ 1-11

2 Getting Started

Compatibilities for Oracle JDBC Drivers............................................................................................ 2-1 Backward Compatibility ................................................................................................................... 2-1 Forward Compatibility...................................................................................................................... 2-1

Verifying a JDBC Client Installation.................................................................................................... 2-2 Check Installed Directories and Files .............................................................................................. 2-3 Check the Environment Variables ................................................................................................... 2-4 JDBC OCI Driver......................................................................................................................... 2-5 JDBC Thin Driver........................................................................................................................ 2-6 Make Sure You Can Compile and Run Java .................................................................................. 2-6 Determine the Version of the JDBC Driver .................................................................................... 2-7 Testing JDBC and the Database Connection: JdbcCheckup ........................................................ 2-8

3 Datasources and URLs

Datasources................................................................................................................................................ 3-1 A Brief Overview of Oracle Datasource Support for JNDI .......................................................... 3-1 Datasource Features and Properties................................................................................................ 3-2 DataSource Interface and Oracle Implementation................................................................. 3-2 DataSource Properties ................................................................................................................ 3-2 Creating a Datasource Instance and Connecting (without JNDI)............................................... 3-6 Creating a Datasource Instance, Registering with JNDI, and Connecting ................................ 3-6 Initialize Connection Properties ............................................................................................... 3-6 Register the Datasource ............................................................................................................. 3-7 Open a Connection ..................................................................................................................... 3-7 Logging and Tracing.......................................................................................................................... 3-7

Database URLs and Database Specifiers............................................................................................. 3-8 Database Specifiers ............................................................................................................................ 3-8 Thin-style Service Name Syntax ............................................................................................... 3-9 TNSNames Alias Syntax ......................................................................................................... 3-10

4 Basic Features

First Steps in JDBC .................................................................................................................................. 4-1 Importing Packages ........................................................................................................................... 4-2 Opening a Connection to a Database .............................................................................................. 4-2 Specifying a Database URL, User Name, and Password ...................................................... 4-2 Specifying a Database URL That Includes User Name and Password ............................... 4-3 Supported Connection Properties ............................................................................................ 4-3 Using Roles for Sys Logon......................................................................................................... 4-6 Configuring To Permit Use of sysdba .............................................................................. 4-6 Bequeath Connection and Sys Logon ...................................................................................... 4-6 Remote Connection..................................................................................................................... 4-7

iv

Properties for Oracle Performance Extensions ....................................................................... 4-8 Example................................................................................................................................. 4-8

Creating a Statement Object ............................................................................................................. 4-8 Executing a Query and Returning a Result Set Object ................................................................. 4-9 Processing the Result Set................................................................................................................... 4-9 Closing the Result Set and Statement Objects................................................................................ 4-9 Making Changes to the Database ................................................................................................. 4-10 Committing Changes...................................................................................................................... 4-10 Closing the Connection .................................................................................................................. 4-11 Sample: Connecting, Querying, and Processing the Results........................................................ 4-11 Datatype Mappings............................................................................................................................... 4-12 Table of Mappings .......................................................................................................................... 4-12 Notes Regarding Mappings........................................................................................................... 4-14

Regarding User-Defined Types ............................................................................................. 4-14 Regarding NUMBER Types ................................................................................................... 4-14 Java Streams in JDBC ........................................................................................................................... 4-14 Streaming LONG or LONG RAW Columns ............................................................................... 4-15 LONG RAW Data Conversions ............................................................................................. 4-15 LONG Data Conversions ........................................................................................................ 4-15 Streaming Example for LONG RAW Data........................................................................... 4-16

Getting a LONG RAW Data Column with getBinaryStream() .................................. 4-16 Getting a LONG RAW Data Column with getBytes()................................................. 4-17 Avoiding Streaming for LONG or LONG RAW................................................................. 4-18 Streaming CHAR, VARCHAR, or RAW Columns .................................................................... 4-18 Data Streaming and Multiple Columns ....................................................................................... 4-18 Streaming Example with Multiple Columns ....................................................................... 4-19 Bypassing Streaming Data Columns..................................................................................... 4-19 Streaming LOBs and External Files .............................................................................................. 4-20 Streaming BLOBs and CLOBs................................................................................................ 4-20 Streaming BFILEs..................................................................................................................... 4-20 Closing a Stream.............................................................................................................................. 4-20 Notes and Precautions on Streams ............................................................................................... 4-21 Streaming Data Precautions ................................................................................................... 4-21 Using Streams to Avoid Limits on setBytes() and setString() ........................................... 4-22 Streaming and Row Prefetching ............................................................................................ 4-22 Stored Procedure Calls in JDBC Programs....................................................................................... 4-22 PL/SQL Stored Procedures ........................................................................................................... 4-22 Java Stored Procedures................................................................................................................... 4-23 Processing SQL Exceptions ................................................................................................................. 4-23 Retrieving Error Information ........................................................................................................ 4-24 Printing the Stack Trace ................................................................................................................. 4-24

5 JDBC Standards Support

Introduction............................................................................................................................................... 5-1 JDBC 2.0 Support: JDK 1.2.x and Higher Versions ............................................................................ 5-2

Datatype Support ............................................................................................................................... 5-2 Standard Feature Support................................................................................................................. 5-2

v

Extended Feature Support ................................................................................................................ 5-2 Standard versus Oracle Performance Enhancement APIs ........................................................... 5-2 Migration from JDK 1.1.x .................................................................................................................. 5-3 JDBC 3.0 Support: JDK 1.4 and Previous Releases............................................................................ 5-3 Overview of Supported JDBC 3.0 Features ......................................................................................... 5-3 Unsupported JDBC 3.0 Features ...................................................................................................... 5-4 Transaction Savepoints............................................................................................................................ 5-4 Creating a Savepoint.......................................................................................................................... 5-4 Rolling back to a Savepoint .............................................................................................................. 5-5 Releasing a Savepoint ........................................................................................................................ 5-5 Checking Savepoint Support............................................................................................................ 5-5 Savepoint Notes.................................................................................................................................. 5-5 Savepoint Interfaces........................................................................................................................... 5-5 Pre-JDK1.4 Savepoint Support ......................................................................................................... 5-6 JDBC 3.0 LOB Interface Methods.......................................................................................................... 5-6

6 Statement Caching

About Statement Caching....................................................................................................................... 6-1 Basics of Statement Caching ............................................................................................................. 6-1 Implicit Statement Caching .............................................................................................................. 6-2 Explicit Statement Caching............................................................................................................... 6-2

Using Statement Caching ....................................................................................................................... 6-3 Enabling and Disabling Statement Caching................................................................................... 6-3 Enabling and Disabling Implicit Statement Caching............................................................. 6-3 Enabling and Disabling Explicit Statement Caching ............................................................. 6-4 Checking for Statement Creation Status ......................................................................................... 6-4 Physically Closing a Cached Statement .......................................................................................... 6-5 Using Implicit Statement Caching ................................................................................................... 6-5 Allocating a Statement for Implicit Caching........................................................................... 6-5 Disabling Implicit Statement Caching for a Particular Statement ....................................... 6-5 Implicitly Caching a Statement................................................................................................. 6-6 Retrieving an Implicitly Cached Statement ............................................................................ 6-6 Using Explicit Statement Caching ................................................................................................... 6-6 Allocating a Statement for Explicit Caching ........................................................................... 6-7 Explicitly Caching a Statement ................................................................................................. 6-7 Retrieving an Explicitly Cached Statement............................................................................. 6-7

7 Implicit Connection Caching

The Implicit Connection Cache ............................................................................................................. 7-1 Using the Connection Cache .................................................................................................................. 7-3

Turning Caching On .......................................................................................................................... 7-3 Opening a Connection....................................................................................................................... 7-3 Setting Connection Cache Name ..................................................................................................... 7-4 Setting Connection Cache Properties .............................................................................................. 7-4 Closing A Connection........................................................................................................................ 7-4 Implicit Connection Cache Example ............................................................................................... 7-4 Connection Attributes ............................................................................................................................. 7-5

vi

Getting Connections .......................................................................................................................... 7-5 Attribute Matching Rules .......................................................................................................... 7-6

Setting Connection Attributes .......................................................................................................... 7-6 Checking a Returned Connection's Attributes .............................................................................. 7-7 Connection Attribute Example ........................................................................................................ 7-7 Connection Cache Properties ................................................................................................................. 7-7 Limit Properties .................................................................................................................................. 7-7

InitialLimit ................................................................................................................................... 7-8 MaxLimit ...................................................................................................................................... 7-8 MaxStatementsLimit................................................................................................................... 7-8 MinLimit....................................................................................................................................... 7-8 Timeout Properties............................................................................................................................. 7-8 InactivityTimeout........................................................................................................................ 7-8 TimeToLiveTimeout ................................................................................................................... 7-8 AbandonedConnectionTimeout ............................................................................................... 7-9 PropertyCheckInterval............................................................................................................... 7-9 Other Properties ................................................................................................................................. 7-9 AttributeWeights......................................................................................................................... 7-9 ClosestConnectionMatch ........................................................................................................... 7-9 ConnectionWaitTimeout............................................................................................................ 7-9 LowerThresholdLimit ................................................................................................................ 7-9 ValidateConnection .................................................................................................................... 7-9 Connection Property Example ......................................................................................................... 7-9 Connection Cache Manager API ........................................................................................................ 7-10 createCache ...................................................................................................................................... 7-10 removeCache ................................................................................................................................... 7-11 reinitializeCache .............................................................................................................................. 7-11 existsCache ....................................................................................................................................... 7-11 enableCache ..................................................................................................................................... 7-11 disableCache .................................................................................................................................... 7-12 refreshCache .................................................................................................................................... 7-12 purgeCache ...................................................................................................................................... 7-12 getCacheProperties ......................................................................................................................... 7-12 getCacheNameList .......................................................................................................................... 7-12 getNumberOfAvailableConnections............................................................................................ 7-12 getNumberOfActiveConnections ................................................................................................. 7-12 setConnectionPoolDataSource ...................................................................................................... 7-13 Example Of ConnectionCacheManager Use ............................................................................... 7-13 Advanced Topics.................................................................................................................................... 7-13 Attribute Weights And Connection Matching............................................................................ 7-14 ClosestConnectionMatch ........................................................................................................ 7-14 AttributeWeights...................................................................................................................... 7-14 Connection Cache Callbacks ......................................................................................................... 7-14

8 Fast Connection Failover

Introduction............................................................................................................................................... 8-1 What Can Fast Connection Failover Do?........................................................................................ 8-1

vii

Using Fast Connection Failover............................................................................................................. 8-1 Fast Connection Failover Prerequisites........................................................................................... 8-1 Configuring ONS For Fast Connection Failover ........................................................................... 8-2 ONS Configuration File ............................................................................................................. 8-2 Client-side ONS Configuration................................................................................................. 8-3 Using the oncstl Command................................................................................................ 8-3 Server-side ONS Configuration Using racgons...................................................................... 8-4 Other Uses of racgons ......................................................................................................... 8-4 Enabling Fast Connection Failover.................................................................................................. 8-4 Querying Fast Connection Failover Status..................................................................................... 8-5

Understanding Fast Connection Failover ............................................................................................ 8-5 What The Application Sees............................................................................................................... 8-5 What's Happening ............................................................................................................................. 8-6

Comparison of Fast Connection Failover and TAF............................................................................ 8-6

9 Distributed Transactions

Overview.................................................................................................................................................... 9-1 Distributed Transaction Components and Scenarios ................................................................... 9-2 Distributed Transaction Concepts ................................................................................................... 9-2 Switching Between Global and Local Transactions ...................................................................... 9-4 Mode Restrictions On Operations ............................................................................................ 9-4 Oracle XA Packages ........................................................................................................................... 9-5

XA Components........................................................................................................................................ 9-5 XA Datasource Interface and Oracle Implementation.................................................................. 9-5 XA Connection Interface and Oracle Implementation ................................................................. 9-6 XA Resource Interface and Oracle Implementation ..................................................................... 9-7 XA Resource Method Functionality and Input Parameters......................................................... 9-8 Start ............................................................................................................................................... 9-8 End ............................................................................................................................................. 9-10 Prepare....................................................................................................................................... 9-10 Commit ...................................................................................................................................... 9-11 Roll back .................................................................................................................................... 9-11 Forget ......................................................................................................................................... 9-11 Recover ...................................................................................................................................... 9-11 Check for same RM.................................................................................................................. 9-12 XA ID Interface and Oracle Implementation .............................................................................. 9-12

Error Handling and Optimizations.................................................................................................... 9-13 XA Exception Classes and Methods ............................................................................................. 9-13 Mapping between Oracle Errors and XA Errors ........................................................................ 9-14 XA Error Handling.......................................................................................................................... 9-14 Oracle XA Optimizations ............................................................................................................... 9-14

Implementing a Distributed Transaction ......................................................................................... 9-15 Summary of Imports for Oracle XA ............................................................................................. 9-15 Oracle XA Code Sample................................................................................................................. 9-15

10 Oracle Extensions

Introduction to Oracle Extensions ..................................................................................................... 10-1

viii

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download