Sanko M., Wright B., Pfaeffle T.
Table of contents :
Contents……Page 3
Send Us Your Comments……Page 19
Preface……Page 21
1 Overview……Page 29
JDBC versus SQLJ……Page 30
Advantages of SQLJ over JDBC for Static SQL……Page 31
General Guidelines for Using JDBC and SQLJ……Page 32
Overview of the Oracle JDBC Drivers……Page 33
Common Features of Oracle JDBC Drivers……Page 34
JDBC Thin Driver……Page 35
JDBC OCI Driver……Page 36
JDBC Server-Side Thin Driver……Page 37
Choosing the Appropriate Driver……Page 38
Applets and Security……Page 40
Package oracle.jdbc……Page 41
Connecting to the Database……Page 43
JNI and Java Environments……Page 44
JDBC and IDEs……Page 45
2 Getting Started……Page 47
Requirements and Compatibilities for Oracle JDBC Drivers……Page 48
Check Installed Directories and Files……Page 51
Check the Environment Variables……Page 52
Make Sure You Can Compile and Run Java……Page 53
Testing JDBC and the Database Connection: JdbcCheckup……Page 54
3 Basic Features……Page 59
Import Packages……Page 60
Open a Connection to a Database……Page 61
Understanding the Forms of getConnection()……Page 62
Specifying a Database URL That Includes User Name and Password……Page 63
Specifying a Database URL and Properties Object……Page 64
Using Roles for Sys Logon……Page 65
Opening a Connection for the JDBC OCI Driver……Page 67
Opening a Connection for the JDBC Thin Driver……Page 68
Process the Result Set……Page 69
Make Changes to the Database……Page 70
Commit Changes……Page 71
Close the Connection……Page 72
Sample: Connecting, Querying, and Processing the Results……Page 73
Table of Mappings……Page 74
Regarding NUMBER Types……Page 76
Streaming LONG or LONG RAW Columns……Page 77
LONG Data Conversions……Page 78
Streaming Example for LONG RAW Data……Page 79
Streaming CHAR, VARCHAR, or RAW Columns……Page 82
Streaming Example with Multiple Columns……Page 83
Bypassing Streaming Data Columns……Page 84
Streaming BLOBs and CLOBs……Page 85
Streaming Data Precautions……Page 86
Streaming and Row Prefetching……Page 88
PL/SQL Stored Procedures……Page 89
Java Stored Procedures……Page 90
Retrieving Error Information……Page 91
Printing the Stack Trace……Page 92
4 Overview of JDBC 2.0 Support……Page 95
Introduction……Page 96
Datatype Support……Page 97
Standard Feature Support……Page 98
Migration from JDK 1.1.x to JDK 1.2.x……Page 99
Overview of JDBC 2.0 Features……Page 101
5 Overview of Oracle Extensions……Page 103
Introduction to Oracle Extensions……Page 104
Support for Oracle Datatypes……Page 105
Support for Oracle Objects……Page 106
Support for Schema Naming……Page 107
OCI Extensions……Page 108
Classes of the oracle.sql Package……Page 109
General oracle.sql.* Datatype Support……Page 111
Overview of Class oracle.sql.STRUCT……Page 112
Overview of Class oracle.sql.REF……Page 113
Overview of Classes oracle.sql.BLOB, oracle.sql.CLOB, oracle.sql.BFILE……Page 114
Classes oracle.sql.TIMESTAMP, oracle.sql.TIMESTAMPTZ, and oracle.sql.TIMESTAMPLTZ……Page 115
Overview of Class oracle.sql.ROWID……Page 116
Class oracle.sql.OPAQUE……Page 117
Package oracle.jdbc……Page 118
Class oracle.jdbc.OracleDriver……Page 119
Interface oracle.jdbc.OracleConnection……Page 120
Interface oracle.jdbc.OraclePreparedStatement……Page 121
Interface oracle.jdbc.OracleCallableStatement……Page 122
Interface oracle.jdbc.OracleResultSet……Page 123
Class oracle.jdbc.OracleTypes……Page 124
Method getJavaSqlConnection()……Page 127
Package oracle.jdbc2 (for JDK 1.1.x only)……Page 128
SQL NCHAR Datatypes……Page 130
Class oracle.sql.CHAR……Page 131
oracle.sql.CHAR Objects and Character Sets……Page 132
Constructing an oracle.sql.CHAR Object……Page 133
oracle.sql.CHAR Conversion Methods……Page 134
Oracle ROWID Type……Page 135
Oracle REF CURSOR Type Category……Page 136
Support for Oracle Extensions in 8.0.x and 7.3.x JDBC Drivers……Page 138
6 Accessing and Manipulating Oracle Data……Page 141
Converting SQL NULL Data……Page 142
Result Set and Statement Extensions……Page 143
Oracle getOracleObject() Method……Page 144
Summary of getObject() and getOracleObject() Return Types……Page 146
Return Types and Input Parameter Types of getXXX() Methods……Page 147
Special Notes about getXXX() Methods……Page 149
Casting Your get Method Return Values……Page 150
Standard setObject() and Oracle setOracleObject() Methods……Page 151
Other setXXX() Methods……Page 152
Input Parameter Types of setXXX() Methods……Page 153
Setter Method Size Limitations on Oracle8 and Oracle7……Page 155
Setter Methods That Take Additional Input……Page 156
Method setFixedCHAR() for Binding CHAR Data into WHERE Clauses……Page 157
Limitations of the Oracle 8.0.x and 7.3.x JDBC Drivers……Page 158
Using Result Set Meta Data Extensions……Page 159
7 Working with LOBs and BFILEs……Page 161
Oracle Extensions for LOBs and BFILEs……Page 162
Retrieving BLOB and CLOB Locators……Page 163
Passing BLOB and CLOB Locators……Page 165
Reading and Writing BLOB and CLOB Data……Page 166
Creating and Populating a BLOB or CLOB Column……Page 170
Populating a BLOB or CLOB Column in a New Table……Page 171
Accessing and Manipulating BLOB and CLOB Data……Page 172
Additional BLOB Methods……Page 173
Additional CLOB Methods……Page 175
Creating Empty LOBs……Page 176
Working With Temporary LOBs……Page 178
Using Open and Close With LOBs……Page 179
Retrieving BFILE Locators……Page 180
Passing BFILE Locators……Page 181
Reading BFILE Data……Page 182
Creating a BFILE Column in a New Table……Page 183
Populating a BFILE Column……Page 184
Accessing and Manipulating BFILE Data……Page 185
Additional BFILE Features……Page 186
8 Working with Oracle Object Types……Page 189
Mapping Oracle Objects……Page 190
Standard java.sql.Struct Methods……Page 191
Steps in Creating StructDescriptor and STRUCT Objects……Page 192
Using StructDescriptor Methods……Page 193
Serializable STRUCT Descriptors……Page 194
Retrieving an Oracle Object as an oracle.sql.STRUCT Object……Page 195
Binding STRUCT Objects into Statements……Page 196
STRUCT Automatic Attribute Buffering……Page 197
Creating and Using Custom Object Classes for Oracle Objects……Page 198
Understanding Type Maps for SQLData Implementations……Page 199
Creating a Type Map Object and Defining Mappings for a SQLData Implementation……Page 200
Adding Entries to an Existing Type Map……Page 201
Creating a New Type Map……Page 202
Understanding the SQLData Interface……Page 203
Implementing readSQL() and writeSQL() Methods……Page 204
Reading SQLData Objects from a Result Set……Page 205
Passing SQLData Objects to a Callable Statement as an IN Parameter……Page 207
Writing Data to an Oracle Object Using a SQLData Implementation……Page 208
Understanding ORAData Features……Page 209
Retrieving and Inserting Object Data……Page 210
Reading Data from an Oracle Object Using a ORAData Implementation……Page 211
Writing Data to an Oracle Object Using a ORAData Implementation……Page 213
Additional Uses for ORAData……Page 214
The Deprecated CustomDatum Interface……Page 216
Creating Subtypes……Page 217
Use of ORAData for Type Inheritance Hierarchy……Page 218
Use of SQLData for Type Inheritance Hierarchy……Page 222
Using Default Mapping……Page 225
Using SQLData Mapping……Page 226
Using ORAData Mapping……Page 227
Creating Subtype Objects……Page 228
Subtype Data Fields from the getAttribute() Method……Page 229
Subtype Data Fields from the getOracleAttribute() Method……Page 230
Inheritance Meta Data Methods……Page 231
JPublisher Type Mappings……Page 233
Type-Mapping Modes……Page 234
Mapping Attribute Types to Java……Page 235
Summary of SQL Type Categories and Mapping Settings……Page 236
Functionality for Getting Object Meta Data……Page 237
Steps for Retrieving Object Meta Data……Page 238
SQLJ Object Types……Page 240
Creating a Java Class Definition for a SQLJ Object Type……Page 241
Creating the SQLJ Object Type in the Database……Page 243
Inserting an Instance of a SQLJ Object Type……Page 247
Retrieving a SQLJ Object Type Instance Through Database Queries……Page 248
Meta Data Methods for SQLJ Object Types……Page 249
SQLJ Object Types and Custom Object Types Compared……Page 250
9 Working with Oracle Object References……Page 253
Oracle Extensions for Object References……Page 254
Key REF Class Methods……Page 256
Retrieving an Object Reference from a Result Set……Page 258
Retrieving an Object Reference from a Callable Statement……Page 259
Passing an Object Reference to a Prepared Statement……Page 260
Accessing and Updating Object Values through an Object Reference……Page 261
Custom Reference Classes with JPublisher……Page 262
10 Working with Oracle Collections……Page 265
Choices in Materializing Collections……Page 266
Creating Collections……Page 267
Creating Multi-Level Collection Types……Page 268
Array Getter and Setter Methods……Page 269
ARRAY Class Methods……Page 270
Accessing oracle.sql.ARRAY Elements as Arrays of Java Primitive Types……Page 272
ARRAY Automatic Indexing……Page 273
Steps in Creating ArrayDescriptor and ARRAY Objects……Page 275
Creating Multi-Level Collections……Page 277
Using ArrayDescriptor Methods……Page 278
Retrieving an Array and Its Elements……Page 279
Data Retrieval Methods……Page 280
Comparing the Data Retrieval Methods……Page 281
Retrieving Elements of a Structured Object Array According to a Type Map……Page 282
Retrieving Array Elements into an oracle.sql.Datum Array……Page 283
Accessing Multi-Level Collection Elements……Page 285
Passing an Array to a Prepared Statement……Page 286
Passing an Array to a Callable Statement……Page 287
Using a Type Map to Map Array Elements……Page 289
Custom Collection Classes with JPublisher……Page 291
11 Result Set Enhancements……Page 293
Scrollability, Positioning, and Sensitivity……Page 294
Result Set Types for Scrollability and Sensitivity……Page 295
Summary of Result Set Categories……Page 296
Oracle JDBC Implementation for Result Set Updatability……Page 297
Implementing a Custom Client-Side Cache for Scrollability……Page 298
Specifying Result Set Scrollability and Updatability……Page 300
Result Set Limitations……Page 302
Result Set Downgrade Rules……Page 303
Verifying Result Set Type and Concurrency Type……Page 304
Methods for Moving to a New Position……Page 305
Processing a Scrollable Result Set……Page 307
Backward versus Forward Processing……Page 308
Presetting the Fetch Direction……Page 309
Performing a DELETE Operation in a Result Set……Page 310
Performing an UPDATE Operation in a Result Set……Page 311
Performing an INSERT Operation in a Result Set……Page 313
Update Conflicts……Page 315
Setting the Fetch Size……Page 316
Use of Standard Fetch Size versus Oracle Row-Prefetch Setting……Page 317
Refetching Rows……Page 318
Seeing Internal Changes……Page 319
Seeing External Changes……Page 320
Visibility versus Detection of External Changes……Page 321
Oracle Implementation of Scroll-Sensitive Result Sets……Page 322
New Result Set Methods……Page 324
New Database Meta Data Methods……Page 327
12 Performance Extensions……Page 329
Oracle Model versus Standard Model……Page 330
Types of Statements Supported……Page 331
Oracle Update Batching……Page 332
Setting the Connection Batch Value……Page 333
Setting the Statement Batch Value……Page 334
Overriding the Batch Value……Page 335
Committing the Changes in Oracle Batching……Page 336
Example: Oracle Update Batching……Page 337
Standard Update Batching……Page 338
Adding Operations to the Batch……Page 339
Executing the Batch……Page 341
Clearing the Batch……Page 342
Example: Standard Update Batching……Page 343
Error Handling in the Oracle Implementation of Standard Batching……Page 344
Intermixing Batched Statements and Non-Batched Statements……Page 345
Premature Batch Flush……Page 346
Setting the Oracle Prefetch Value……Page 348
Defining Column Types……Page 351
DatabaseMetaData TABLE_REMARKS Reporting……Page 354
Considerations for getProcedures() and getProcedureColumns() Methods……Page 355
13 Statement Caching……Page 357
Implicit Statement Caching……Page 358
Comparing Implicit and Explicit Statement Caching……Page 360
Enabling and Disabling Statement Caching……Page 362
Physically Closing a Cached Statement……Page 363
Disabling Implicit Statement Caching for a Particular Statement……Page 364
Using Explicit Statement Caching……Page 365
Retrieving an Explicitly Cached Statement……Page 366
14 Connection Pooling and Caching……Page 369
A Brief Overview of Oracle Data Source Support for JNDI……Page 370
Data Source Interface and Oracle Implementation……Page 371
Data Source Properties……Page 372
Creating a Data Source Instance and Connecting (without JNDI)……Page 375
Initialize Connection Properties……Page 376
Register the Data Source for the Oracle9i JNDI……Page 377
Logging and Tracing……Page 379
Connection Pooling Concepts……Page 381
Connection Pool Data Source Interface and Oracle Implementation……Page 382
Pooled Connection Interface and Oracle Implementation……Page 383
Summary of Imports for Oracle Connection Pooling……Page 384
Oracle Connection Pooling Code Sample……Page 385
Basics of Setting Up a Connection Cache……Page 386
Basics of Opening Connections……Page 387
Implementation Scenarios……Page 388
General Steps in Opening a Connection……Page 389
General Steps in Closing a Connection……Page 391
Oracle Connection Cache Specification: OracleConnectionCache Interface……Page 392
Instantiating OracleConnectionCacheImpl and Setting Properties……Page 393
Setting a Maximum Number of Pooled Connections……Page 394
Schemes for Creating New Pooled Connections in the Oracle Implementation……Page 395
Additional OracleConnectionCacheImpl Methods……Page 396
Oracle Connection Event Listener Methods……Page 397
15 Distributed Transactions……Page 399
Distributed Transaction Components and Scenarios……Page 400
Distributed Transaction Concepts……Page 401
Oracle XA Packages……Page 403
XA Data Source Interface and Oracle Implementation……Page 404
XA Connection Interface and Oracle Implementation……Page 405
XA Resource Interface and Oracle Implementation……Page 406
XA Resource Method Functionality and Input Parameters……Page 407
XA ID Interface and Oracle Implementation……Page 411
XA Exception Classes and Methods……Page 413
XA Error Handling……Page 414
Oracle XA Optimizations……Page 415
Oracle XA Code Sample……Page 416
16 JDBC OCI Extensions……Page 423
OCI Driver Connection Pooling……Page 424
OCI Driver Connection Pooling and MTS Compared……Page 425
Defining an OCI Connection Pool……Page 426
Importing the oracle.jdbc.pool and oracle.jdbc.oci Packages……Page 427
Creating an OCI Connection Pool……Page 428
Setting the OCI Connection Pool Parameters……Page 429
Checking the OCI Connection Pool Status……Page 430
Connecting to an OCI Connection Pool……Page 431
Statement Handling and Caching……Page 432
Types of Statement Caching used with the OCI Connection Pool……Page 433
JNDI and the OCI Connection Pool……Page 434
Middle-Tier Authentication Through Proxy Connections……Page 435
Failover Type Events……Page 438
Java TAF Callback Interface……Page 439
Handling the FO_ABORT Event……Page 440
HeteroRM XA Code Example……Page 441
Overview……Page 443
Binding IN Parameters……Page 444
Registering the OUT Parameters……Page 446
Accessing the OUT Parameter Values……Page 447
17 Java Transaction API……Page 453
Global and Local Transactions……Page 454
UserTransaction Interface……Page 455
Enlisting Resources……Page 457
Two-Phase Commit……Page 459
Timeouts……Page 460
Methods for Enlisting Database Resources……Page 461
Summary of Single-Phase and Two-Phase Commit……Page 462
Binding Transactional Objects in the Namespace……Page 463
Bind UserTransaction Object in the Namespace……Page 464
Bind DataSource Object in the Namespace……Page 466
JTA Client-Side Demarcation……Page 467
Developing the Client Application……Page 469
JTA Server-Side Database Enlistment……Page 470
Configuring Two-Phase Commit Engine……Page 473
Creating DataSource Objects Dynamically……Page 477
JDBC Restrictions……Page 478
18 Advanced Topics……Page 479
JDBC and Globalization Support……Page 480
JDBC OCI Driver and Globalization Support……Page 481
Server-Side Internal Driver and Globalization Support……Page 482
Globalization Support and Object Types……Page 483
Role of the Globalization Support Ratio……Page 484
Globalization Support Ratios and Calculated Size Restrictions for Common Character Sets……Page 485
OCI Driver Support for Oracle Advanced Security……Page 487
JDBC Support for Login Authentication……Page 488
JDBC Support for Data Encryption and Integrity……Page 489
OCI Driver Support for Encryption and Integrity……Page 490
Thin Driver Support for Encryption and Integrity……Page 491
Setting Encryption and Integrity Parameters in Java……Page 492
Connecting to the Database through the Applet……Page 494
Using the Oracle8 Connection Manager……Page 496
Using Signed Applets……Page 498
Using Applets with Firewalls……Page 499
Configuring a Firewall for Applets that use the JDBC Thin Driver……Page 500
Writing a Connect String to Connect through a Firewall……Page 501
Packaging Applets……Page 502
CODEBASE……Page 503
ARCHIVE……Page 504
Connecting to the Database with the Server-Side Internal Driver……Page 505
Connecting with the OracleDriver Class defaultConnection() Method……Page 506
Exception-Handling Extensions for the Server-Side Internal Driver……Page 507
Testing JDBC on the Server……Page 509
Loading Class Files into the Server……Page 511
Server-Side Character Set Conversion of oracle.sql.CHAR Data……Page 512
19 Coding Tips and Troubleshooting……Page 513
JDBC and Multithreading……Page 514
Disabling Auto-Commit Mode……Page 518
Standard and Oracle Update Batching……Page 519
Memory Leaks and Running Out of Cursors……Page 520
Opening More Than 16 OCI Connections for a Process……Page 521
Client-Side Tracing……Page 523
Server-Side Tracing……Page 524
Third Party Debugging Tools……Page 525
Transaction Isolation Levels and Access Modes……Page 526
20 Sample Applications……Page 527
Listing Names from the EMP Table—Employee.java……Page 528
Inserting Names into the EMP Table—InsertExample.java……Page 529
Calling PL/SQL Stored Procedures—PLSQLExample.java……Page 532
Executing Procedures in PL/SQL Blocks—PLSQL.java……Page 533
Accessing a PL/SQL Index-by Table from JDBC—PLSQLIndexTab.java……Page 537
Streams—StreamExample.java……Page 544
Multithreading—JdbcMTSample.java……Page 546
BLOBs and CLOBs—LobExample.java……Page 551
Weakly Typed Objects—PersonObject.java……Page 556
Weakly Typed Object References—StudentRef.java……Page 559
Weakly Typed Arrays—ArrayExample.java……Page 561
REF CURSORs—RefCursorExample.java……Page 564
BFILEs—FileExample.java……Page 566
SQLData Implementation—SQLDataExample.java……Page 569
Custom Object Class—SQLData Implementation……Page 570
Sample Application Using SQLData Custom Object Class……Page 571
Custom Object Class—CustomDatum Implementation……Page 573
Sample Application Using CustomDatum Custom Object Class……Page 575
Positioning in a Result Set—ResultSet2.java……Page 578
Inserting and Deleting Rows in a Result Set—ResultSet3.java……Page 581
Updating Rows in a Result Set—ResultSet4.java……Page 585
Scroll-Sensitive Result Set—ResultSet5.java……Page 587
Refetching Rows in a Result Set—ResultSet6.java……Page 590
Standard Update Batching—BatchUpdates.java……Page 595
Oracle Update Batching with Implicit Execution—SetExecuteBatch.java……Page 597
Oracle Update Batching with Explicit Execution—SendBatch.java……Page 599
Oracle Row Prefetching Specified in Connection—RowPrefetch_connection.java……Page 600
Oracle Row Prefetching Specified in Statement—RowPrefetch_statement.java……Page 602
Oracle Column Type Definitions—DefineColumnType.java……Page 604
Implicit Statement Caching—StmtCache1.java……Page 605
Explicit Statement Caching—StmtCache2.java……Page 608
Data Source without JNDI—DataSource.java……Page 611
Data Source with JNDI—DataSourceJNDI.java……Page 612
Pooled Connection—PooledConnection.java……Page 615
OCI Connection Pool—OCIConnectionPool.java……Page 616
Middle-Tier Authentication—NtierAuth.java……Page 619
JDBC OCI Application Failover Callbacks—OCIFailOver.java……Page 623
Oracle Connection Cache (dynamic)—CCache1.java……Page 627
Oracle Connection Cache (“fixed with no wait”)—CCache2.java……Page 629
XA with Suspend and Resume—XA2.java……Page 631
XA with Two-Phase Commit Operation—XA4.java……Page 635
HeteroRM XA—XA6.java……Page 642
HTML Page—JdbcApplet.htm……Page 646
Applet Code—JdbcApplet.java……Page 647
SQL Program to Create Tables and Objects……Page 650
JDBC Version of the Sample Code……Page 652
Maintaining JDBC Programs……Page 655
SQLJ Version of the Sample Code……Page 656
Coding Requirements of the SQLJ Version……Page 657
21 Reference Information……Page 659
Valid SQL-JDBC Datatype Mappings……Page 660
Supported SQL and PL/SQL Datatypes……Page 663
Date Literals……Page 668
Time Literals……Page 669
Scalar Functions……Page 670
Outer Joins……Page 671
SQL92 to SQL Syntax Example……Page 672
PL/SQL TABLE, BOOLEAN, and RECORD Types……Page 674
Bind by Name……Page 675
Java Technology……Page 677
A Row Set……Page 679
Introduction……Page 680
Row Set Setup and Configuration……Page 682
Runtime Properties for Row Set……Page 683
Row Set Listener……Page 684
Traversing Through the Rows……Page 686
Cached Row Set……Page 687
CachedRowSet Constraints……Page 691
JDBC Row Set……Page 693
B JDBC Error Messages……Page 695
General Structure of JDBC Error Messages……Page 696
JDBC Messages Sorted by ORA Number……Page 697
JDBC Messages Sorted Alphabetically……Page 701
HeteroRM XA Messages Sorted Alphabetically……Page 706
TTC Messages Sorted by ORA Number……Page 708
TTC Messages Sorted Alphabetically……Page 709
Index……Page 713
Reviews
There are no reviews yet.