Cover image for Oracle9i JDBC programming
Title:
Oracle9i JDBC programming
Author:
Price, Jason.
Personal Author:
Publication Information:
Berkeley, Calif. : McGraw-Hill/Osborne, [2002]

©2002
Physical Description:
xxi, 598 pages : illustrations ; 24 cm
General Note:
"Oracle Press."

Includes index.
Language:
English
Title Subject:
ISBN:
9780072222548
Format :
Book

Available:*

Library
Call Number
Material Type
Home Location
Status
Item Holds
Searching...
QA76.9.D3 P72967 2002 Adult Non-Fiction Central Closed Stacks
Searching...

On Order

Summary

Summary

This text covers how to use JDBC programs with the Oracle8i and 9i databases, including details on the Oracle extensions to JDBC. It demonstrates accessing a database from JDBC using examples and introduces JDeveloper, which provides a productive environment for developing Java programs.


Table of Contents

Acknowledgmentsp. xv
Introductionp. xvii
Part I Basic JDBC Programming
1 Introduction to JDBCp. 3
Software Requirementsp. 4
The Oracle Client Softwarep. 6
Configurationp. 6
Setting the ORACLE_HOME Environment Variablep. 7
Setting the JAVA_HOME Environment Variablep. 8
Setting the PATH Environment Variablep. 9
Setting the CLASSPATH Environment Variablep. 9
Setting the LD_LIBRARY_PATH Environment Variable on Unix or Linuxp. 10
Your First JDBC Programp. 11
Example Program: FirstExample.javap. 11
Compiling and Running FirstExample.javap. 16
Oracle JDeveloperp. 18
Creating a New Workspace and Projectp. 18
Adding FirstExample.java to the Projectp. 20
Adding the Oracle JDBC Library to the Project and Compiling and Running FirstExample.javap. 23
Debugging FirstExample.javap. 25
2 Introduction to Databases and Oraclep. 29
What Is a Relational Database?p. 30
Structured Query Language (SQL)p. 31
SQL *Plusp. 32
Running the store_user.sql Scriptp. 32
Data Definition Language (DDL) Statementsp. 33
Data Manipulation Language (DML) Statementsp. 41
Oracle PL/SQLp. 67
Block Structurep. 68
Variables and Typesp. 69
Conditional Logicp. 70
Loopsp. 70
Cursorsp. 72
Proceduresp. 77
Functionsp. 80
Packagesp. 82
3 The Basics of JDBC Programmingp. 85
The Oracle JDBC Driversp. 86
The Thin Driverp. 86
The OCI Driverp. 87
The Server-Side Internal Driverp. 87
The Server-Side Thin Driverp. 88
Importing the JDBC Packagesp. 88
Registering the Oracle JDBC Driversp. 88
Opening a Database Connectionp. 89
Connecting to the Database Using the getConnection() Method of the DriverManager Classp. 89
Connecting to the Database Using an Oracle Data Sourcep. 92
Creating a JDBC Statement Objectp. 95
Retrieving Rows from the Databasep. 96
Step 1 Create and Populate a ResultSet Objectp. 97
Step 2 Read the Column Values from the ResultSet Objectp. 98
Step 3 Close the ResultSet Objectp. 101
Adding Rows to the Databasep. 102
Modifying Rows in the Databasep. 103
Deleting Rows from the Databasep. 103
Handling Numbersp. 104
Handling Database Null Valuesp. 105
Controlling Database Transactionsp. 108
Performing Data Definition Language Statementsp. 109
Handling Exceptionsp. 109
Closing Your JDBC Objectsp. 111
Example Program: BasicExample1.javap. 113
Prepared SQL Statementsp. 120
The Oracle JDBC Extensionsp. 123
The oracle.sql Packagep. 124
The oracle.jdbc Packagep. 128
Example Program: BasicExample3.javap. 133
Part II Advanced JDBC Programming
4 Advanced Result Setsp. 139
Scrollable Result Setsp. 140
Navigating a Scrollable Result Setp. 143
Determining the Position in a Scrollable Result Setp. 145
Example Program: AdvResultSetExample1.javap. 146
Updatable Result Setsp. 149
Updating a Rowp. 150
Deleting a Rowp. 152
Conflicts When Updating and Deleting Rows Using an Updatable Result Setp. 152
Inserting a Rowp. 153
Example Program: AdvResultSetExample2.javap. 154
What Database Changes does a Result Set "See"?p. 157
The refreshRow() Methodp. 158
When Are Changes Visible?p. 160
Scrollable Sensitive Result Setsp. 160
Example Program: AdvResultSetExample4.javap. 161
Meta Datap. 164
Example Program: MetaDataExample.javap. 167
5 PL/SQL and JDBCp. 171
Calling PL/SQL Proceduresp. 172
Calling PL/SQL Functionsp. 175
Example Program: PLSQLExample1.javap. 178
Using PL/SQL Packages and REF CURSORsp. 182
6 Database Objectsp. 185
Creating Object Typesp. 186
Using Object Types to Define Column Objects and Object Tablesp. 188
Object References and Object Identifiersp. 189
Performing DML on the products Tablep. 190
Inserting Rows into the products Tablep. 190
Selecting Rows from the products Tablep. 191
Updating a Row in the products Tablep. 192
Deleting a Row from the products Tablep. 192
Performing DML on the object_products Tablep. 192
Inserting Rows into the object_products Tablep. 192
Selecting Rows from the object_products Tablep. 193
Updating a Row in the object_products Tablep. 194
Deleting a Row from the object_products Tablep. 194
Performing DML on the object_customers Tablep. 194
Inserting Rows into the object_customers Tablep. 194
Selecting Rows from the object_customers Tablep. 195
Performing DML on the purchases Tablep. 196
Inserting a Row into the purchases Tablep. 196
Selecting a Row from the purchases Tablep. 197
Updating a Row in the purchases Tablep. 197
Oracle9i Database Type Inheritancep. 198
NOT INSTANTIABLE Object Typesp. 199
Accessing Database Objects Using Weakly Typed Java Objectsp. 200
Inserting a Database Object Using a STRUCTp. 201
Selecting Database Objects into a STRUCTp. 204
Updating a Database Object Using a STRUCTp. 207
Deleting an Objectp. 209
Example Program: ObjectExample1.javap. 209
Weakly Typed Object Referencesp. 216
Example Program: ObjectExample2.javap. 220
Strongly Typed Interfaces and Custom Classesp. 226
Main Differences Between SQLData and ORADatap. 227
Generating Custom Classes Using JPublisherp. 227
Running JPublisher from the Command Linep. 228
Running JPublisher from JDeveloperp. 234
Using Custom Classes that Implement SQLDatap. 234
Type Maps for SQLData Implementationsp. 235
Inserting a Database Object Using a Custom Java Objectp. 236
Selecting Database Objects into a Custom Java Objectp. 237
Updating a Database Object Using a Custom Java Objectp. 238
Example Program: ObjectExample3.javap. 240
Using Custom Classes that Implement ORADatap. 245
Generating Custom Classes That Implement ORAData Using JPublisherp. 245
Inserting, Selecting, and Updating Database Objects Using Custom Java Objectsp. 250
Example Program: ObjectExample4.javap. 252
Strongly Typed Object Referencesp. 258
7 Collectionsp. 265
Varraysp. 267
Creating a Varray Typep. 267
Using a Varray Type to Define a Column in a Tablep. 267
Populating a Varray with Elementsp. 268
Selecting Varray Elementsp. 268
Modifying Varray Elementsp. 268
Nested Tablesp. 269
Creating a Nested Table Typep. 269
Using a Nested Table Type to Define a Column in a Tablep. 269
Populating a Nested Table with Elementsp. 270
Selecting Nested Table Elementsp. 270
Modifying Nested Table Elementsp. 271
Oracle9i Multilevel Collection Typesp. 272
Accessing Collections Using Weakly Typed Objectsp. 274
Inserting a Collection Using an ARRAYp. 274
Selecting Collections into an ARRAYp. 278
Updating a Collection Using an ARRAYp. 279
Example Program: CollectionExample1.javap. 283
Accessing Collections Using Strongly Typed Objectsp. 288
Generating the Custom Classes Using JPublisherp. 289
Inserting a Collection Using a Custom Java Objectp. 293
Selecting Collections into a Custom Java Objectp. 294
Updating a Collection Using a Custom Java Objectp. 296
Example Program: CollectionExample3.javap. 299
8 Large Objectsp. 305
The Example Filesp. 306
Large Objects (LOBs)p. 307
The Example Tablesp. 308
The Put, Get, and Stream Methodsp. 309
Using the Put Methods to Write to CLOB and BLOB Columnsp. 311
Using Streams to Write to CLOB and BLOB Columnsp. 317
Storing Pointers to External Files Using BFILE Columnsp. 321
Example Program: LobExample1.javap. 322
Using the Get Methods to Read from CLOB and BLOB Columnsp. 328
Using Streams to Read from CLOB and BLOB Columnsp. 331
Reading External Files Using BFILE Pointersp. 333
Example Program: LobExample2.javap. 336
LONG and LONG RAW Columnsp. 343
Writing to LONG and LONG RAW Columnsp. 344
Example Program: LongExample1.javap. 346
Reading from LONG and LONG RAW Columnsp. 350
9 Advanced Transaction Controlp. 355
ACID Transaction Propertiesp. 356
Transaction Isolationp. 357
A Worked Example Using JDBCp. 358
Example Program: AdvTransExample1.javap. 362
Distributed Transactionsp. 366
Example Program: AdvTransExample2.javap. 367
Part III Deploying Java
10 Java Stored Procedures and Triggersp. 373
The Oracle JVM Architecturep. 374
Features of Java Stored Programsp. 376
Using the Default Database Connectionp. 377
Outputp. 377
A Worked Examplep. 379
Publishing and Calling Java Methods Using PL/SQL Packagesp. 397
Calling Java Stored Programs from PL/SQL Proceduresp. 398
Calling Java Stored Programs from Javap. 399
Loading and Publishing Java Stored Programs Using JDeveloperp. 402
Database Triggersp. 413
11 Oracle9iAS Containers for J2EE (OC4J)p. 421
Overview of OC4Jp. 422
Installing OC4Jp. 423
Starting, Restarting, and Shutting Down OC4Jp. 424
Starting OC4Jp. 424
Restarting OC4Jp. 424
Shutting Down OC4Jp. 425
Defining a Data Sourcep. 425
Servletsp. 426
Deploying the Servletp. 429
Invoking the Servletp. 430
JavaServer Pages (JSP)p. 430
Enterprise JavaBeans (EJB)p. 434
An Example Session Beanp. 435
Using the Bean in a Stand-Alone Java Programp. 447
Using the Bean in a Servletp. 451
Part IV Performance
12 Connection Pooling and Cachingp. 457
Connection Pooling and Caching Packagesp. 458
Connection Pooling with the Thin Driverp. 459
Example Program: ConnectionPoolExample1.javap. 463
Connection Cachingp. 465
Creating a Connection Cachep. 466
Requesting, Using, and Closing a Connection Instancep. 467
Closing a Connection Cachep. 469
Example Program: ConnectionCacheExample1.javap. 469
Controlling the Number of PooledConnection Objectsp. 472
Example Program: ConnectionCacheExample2.javap. 474
Connection Pooling with the OCI Driverp. 477
Creating an OCI Connection Poolp. 478
Requesting, Using, and Closing a Connection Instancep. 480
Closing the OCI Connection Poolp. 481
Example Program: ConnectionPoolExample2.javap. 481
13 Performance Tuningp. 485
The perf_test Tablep. 486
Disabling Auto-Commit Modep. 486
Example Program: AutoCommitExample.javap. 487
Batchingp. 490
Standard Update Batchingp. 491
Oracle Update Batchingp. 498
Row Prefetchingp. 504
Standard Row Prefetchingp. 505
Oracle Row Prefetchingp. 510
Defining the Types of Result Set Columnsp. 515
The defineColumnType() Methodp. 515
Defining the Length for a Columnp. 516
Structured Objects, Object References, and Array Columnsp. 517
Example Program: DefineColumnTypeExample.javap. 517
Statement Cachingp. 519
Enabling Statement Caching and Setting the Statement Cache Sizep. 520
Types of Statement Cachingp. 521
Using Implicit Statement Cachingp. 521
Using Explicit Statement Cachingp. 528
Tuning SQL Statementsp. 534
Use a WHERE Clause to Restrict the Rowsp. 534
Add Additional Indexesp. 534
A Final Notep. 534
Part V Appendixes
A Oracle and Java Type Mappingsp. 537
JDBC 2.0 Type Mappingsp. 538
Oracle Extended JDBC Type Mappingsp. 539
Java Wrapper Type Mappingsp. 540
B Oracle Java Tools Referencep. 541
JPublisherp. 542
loadjavap. 543
dropjavap. 549
ncompp. 551
deployncp. 554
statusncp. 555
C Selected JDBC Interface and Class Referencep. 557
Get and Update Methods of the java.sql.ResultSet Interfacep. 558
The Get Methods of java.sql.ResultSetp. 559
The Update Methods of java.sql.ResultSetp. 561
Get and Update Methods of the oracle.jdbc.OracleResultSet Interfacep. 563
The Get Methods of oracle.jdbc.OracleResultSetp. 563
The Update Methods of oracle.jdbc.OracleResultSetp. 565
Set Methods of the java.sql.PreparedStatement Interfacep. 567
Set Methods of the oracle.jdbc.OraclePreparedStatement Interfacep. 569
Integer Constants of the java.sql.Types Classp. 570
Integer Constants of the oracle.jdbc.OracleTypes Classp. 571
D JNDI and Data Sourcesp. 573
Binding a Data Source to JNDIp. 574
Step 1 Create an OracleDataSource Objectp. 575
Step 2 Set the Attributes for the OracleDataSource Objectp. 575
Step 3 Create a Properties Objectp. 575
Step 4 Add the JNDI Properties to the Properties Objectp. 576
Step 5 Create a JNDI Context Objectp. 576
Step 6 Bind the OracleDataSource Object to JNDI Using the Context Objectp. 576
Example Program: JNDIExample1.javap. 577
Looking Up a Data Source Using JNDIp. 578
Step 4 Look Up the Data Source Using JNDIp. 579
Example Program: JNDIExample2.javap. 579
Indexp. 583