| |
| |
| Acknowledgments | |
| |
| |
| Introduction | |
| |
| |
| |
| Basic JDBC Programming | |
| |
| |
| |
| Introduction to JDBC | |
| |
| |
| Software Requirements | |
| |
| |
| The Oracle Client Software | |
| |
| |
| Configuration | |
| |
| |
| Setting the ORACLE_HOME Environment Variable | |
| |
| |
| Setting the JAVA_HOME Environment Variable | |
| |
| |
| Setting the PATH Environment Variable | |
| |
| |
| Setting the CLASSPATH Environment Variable | |
| |
| |
| Setting the LD_LIBRARY_PATH Environment Variable on Unix or Linux | |
| |
| |
| Your First JDBC Program | |
| |
| |
| Example Program: FirstExample.java | |
| |
| |
| Compiling and Running FirstExample.java | |
| |
| |
| Oracle JDeveloper | |
| |
| |
| Creating a New Workspace and Project | |
| |
| |
| Adding FirstExample.java to the Project | |
| |
| |
| Adding the Oracle JDBC Library to the Project and Compiling and Running FirstExample.java | |
| |
| |
| Debugging FirstExample.java | |
| |
| |
| |
| Introduction to Databases and Oracle | |
| |
| |
| What Is a Relational Database? | |
| |
| |
| Structured Query Language (SQL) | |
| |
| |
| SQL *Plus | |
| |
| |
| Running the store_user.sql Script | |
| |
| |
| Data Definition Language (DDL) Statements | |
| |
| |
| Data Manipulation Language (DML) Statements | |
| |
| |
| Oracle PL/SQL | |
| |
| |
| Block Structure | |
| |
| |
| Variables and Types | |
| |
| |
| Conditional Logic | |
| |
| |
| Loops | |
| |
| |
| Cursors | |
| |
| |
| Procedures | |
| |
| |
| Functions | |
| |
| |
| Packages | |
| |
| |
| |
| The Basics of JDBC Programming | |
| |
| |
| The Oracle JDBC Drivers | |
| |
| |
| The Thin Driver | |
| |
| |
| The OCI Driver | |
| |
| |
| The Server-Side Internal Driver | |
| |
| |
| The Server-Side Thin Driver | |
| |
| |
| Importing the JDBC Packages | |
| |
| |
| Registering the Oracle JDBC Drivers | |
| |
| |
| Opening a Database Connection | |
| |
| |
| Connecting to the Database Using the getConnection() Method of the DriverManager Class | |
| |
| |
| Connecting to the Database Using an Oracle Data Source | |
| |
| |
| Creating a JDBC Statement Object | |
| |
| |
| Retrieving Rows from the Database | |
| |
| |
| |
| Create and Populate a ResultSet Object | |
| |
| |
| |
| Read the Column Values from the ResultSet Object | |
| |
| |
| |
| Close the ResultSet Object | |
| |
| |
| Adding Rows to the Database | |
| |
| |
| Modifying Rows in the Database | |
| |
| |
| Deleting Rows from the Database | |
| |
| |
| Handling Numbers | |
| |
| |
| Handling Database Null Values | |
| |
| |
| Controlling Database Transactions | |
| |
| |
| Performing Data Definition Language Statements | |
| |
| |
| Handling Exceptions | |
| |
| |
| Closing Your JDBC Objects | |
| |
| |
| Example Program: BasicExample1.java | |
| |
| |
| Prepared SQL Statements | |
| |
| |
| The Oracle JDBC Extensions | |
| |
| |
| The oracle.sql Package | |
| |
| |
| The oracle.jdbc Package | |
| |
| |
| Example Program: BasicExample3.java | |
| |
| |
| |
| Advanced JDBC Programming | |
| |
| |
| |
| Advanced Result Sets | |
| |
| |
| Scrollable Result Sets | |
| |
| |
| Navigating a Scrollable Result Set | |
| |
| |
| Determining the Position in a Scrollable Result Set | |
| |
| |
| Example Program: AdvResultSetExample1.java | |
| |
| |
| Updatable Result Sets | |
| |
| |
| Updating a Row | |
| |
| |
| Deleting a Row | |
| |
| |
| Conflicts When Updating and Deleting Rows Using an Updatable Result Set | |
| |
| |
| Inserting a Row | |
| |
| |
| Example Program: AdvResultSetExample2.java | |
| |
| |
| What Database Changes does a Result Set "See"? | |
| |
| |
| The refreshRow() Method | |
| |
| |
| When Are Changes Visible? | |
| |
| |
| Scrollable Sensitive Result Sets | |
| |
| |
| Example Program: AdvResultSetExample4.java | |
| |
| |
| Meta Data | |
| |
| |
| Example Program: MetaDataExample.java | |
| |
| |
| |
| PL/SQL and JDBC | |
| |
| |
| Calling PL/SQL Procedures | |
| |
| |
| Calling PL/SQL Functions | |
| |
| |
| Example Program: PLSQLExample1.java | |
| |
| |
| Using PL/SQL Packages and REF CURSORs | |
| |
| |
| |
| Database Objects | |
| |
| |
| Creating Object Types | |
| |
| |
| Using Object Types to Define Column Objects and Object Tables | |
| |
| |
| Object References and Object Identifiers | |
| |
| |
| Performing DML on the products Table | |
| |
| |
| Inserting Rows into the products Table | |
| |
| |
| Selecting Rows from the products Table | |
| |
| |
| Updating a Row in the products Table | |
| |
| |
| Deleting a Row from the products Table | |
| |
| |
| Performing DML on the object_products Table | |
| |
| |
| Inserting Rows into the object_products Table | |
| |
| |
| Selecting Rows from the object_products Table | |
| |
| |
| Updating a Row in the object_products Table | |
| |
| |
| Deleting a Row from the object_products Table | |
| |
| |
| Performing DML on the object_customers Table | |
| |
| |
| Inserting Rows into the object_customers Table | |
| |
| |
| Selecting Rows from the object_customers Table | |
| |
| |
| Performing DML on the purchases Table | |
| |
| |
| Inserting a Row into the purchases Table | |
| |
| |
| Selecting a Row from the purchases Table | |
| |
| |
| Updating a Row in the purchases Table | |
| |
| |
| Oracle9i Database Type Inheritance | |
| |
| |
| NOT INSTANTIABLE Object Types | |
| |
| |
| Accessing Database Objects Using Weakly Typed Java Objects | |
| |
| |
| Inserting a Database Object Using a STRUCT | |
| |
| |
| Selecting Database Objects into a STRUCT | |
| |
| |
| Updating a Database Object Using a STRUCT | |
| |
| |
| Deleting an Object | |
| |
| |
| Example Program: ObjectExample1.java | |
| |
| |
| Weakly Typed Object References | |
| |
| |
| Example Program: ObjectExample2.java | |
| |
| |
| Strongly Typed Interfaces and Custom Classes | |
| |
| |
| Main Differences Between SQLData and ORAData | |
| |
| |
| Generating Custom Classes Using JPublisher | |
| |
| |
| Running JPublisher from the Command Line | |
| |
| |
| Running JPublisher from JDeveloper | |
| |
| |
| Using Custom Classes that Implement SQLData | |
| |
| |
| Type Maps for SQLData Implementations | |
| |
| |
| Inserting a Database Object Using a Custom Java Object | |
| |
| |
| Selecting Database Objects into a Custom Java Object | |
| |
| |
| Updating a Database Object Using a Custom Java Object | |
| |
| |
| Example Program: ObjectExample3.java | |
| |
| |
| Using Custom Classes that Implement ORAData | |
| |
| |
| Generating Custom Classes That Implement ORAData Using JPublisher | |
| |
| |
| Inserting, Selecting, and Updating Database Objects Using Custom Java Objects | |
| |
| |
| Example Program: ObjectExample4.java | |
| |
| |
| Strongly Typed Object References | |
| |
| |
| |
| Collections | |
| |
| |
| Varrays | |
| |
| |
| Creating a Varray Type | |
| |
| |
| Using a Varray Type to Define a Column in a Table | |
| |
| |
| Populating a Varray with Elements | |
| |
| |
| Selecting Varray Elements | |
| |
| |
| Modifying Varray Elements | |
| |
| |
| Nested Tables | |
| |
| |
| Creating a Nested Table Type | |
| |
| |
| Using a Nested Table Type to Define a Column in a Table | |
| |
| |
| Populating a Nested Table with Elements | |
| |
| |
| Selecting Nested Table Elements | |
| |
| |
| Modifying Nested Table Elements | |
| |
| |
| Oracle9i Multilevel Collection Types | |
| |
| |
| Accessing Collections Using Weakly Typed Objects | |
| |
| |
| Inserting a Collection Using an ARRAY | |
| |
| |
| Selecting Collections into an ARRAY | |
| |
| |
| Updating a Collection Using an ARRAY | |
| |
| |
| Example Program: CollectionExample1.java | |
| |
| |
| Accessing Collections Using Strongly Typed Objects | |
| |
| |
| Generating the Custom Classes Using JPublisher | |
| |
| |
| Inserting a Collection Using a Custom Java Object | |
| |
| |
| Selecting Collections into a Custom Java Object | |
| |
| |
| Updating a Collection Using a Custom Java Object | |
| |
| |
| Example Program: CollectionExample3.java | |
| |
| |
| |
| Large Objects | |
| |
| |
| The Example Files | |
| |
| |
| Large Objects (LOBs) | |
| |
| |
| The Example Tables | |
| |
| |
| The Put, Get, and Stream Methods | |
| |
| |
| Using the Put Methods to Write to CLOB and BLOB Columns | |
| |
| |
| Using Streams to Write to CLOB and BLOB Columns | |
| |
| |
| Storing Pointers to External Files Using BFILE Columns | |
| |
| |
| Example Program: LobExample1.java | |
| |
| |
| Using the Get Methods to Read from CLOB and BLOB Columns | |
| |
| |
| Using Streams to Read from CLOB and BLOB Columns | |
| |
| |
| Reading External Files Using BFILE Pointers | |
| |
| |
| Example Program: LobExample2.java | |
| |
| |
| LONG and LONG RAW Columns | |
| |
| |
| Writing to LONG and LONG RAW Columns | |
| |
| |
| Example Program: LongExample1.java | |
| |
| |
| Reading from LONG and LONG RAW Columns | |
| |
| |
| |
| Advanced Transaction Control | |
| |
| |
| ACID Transaction Properties | |
| |
| |
| Transaction Isolation | |
| |
| |
| A Worked Example Using JDBC | |
| |
| |
| Example Program: AdvTransExample1.java | |
| |
| |
| Distributed Transactions | |
| |
| |
| Example Program: AdvTransExample2.java | |
| |
| |
| |
| Deploying Java | |
| |
| |
| |
| Java Stored Procedures and Triggers | |
| |
| |
| The Oracle JVM Architecture | |
| |
| |
| Features of Java Stored Programs | |
| |
| |
| Using the Default Database Connection | |
| |
| |
| Output | |
| |
| |
| A Worked Example | |
| |
| |
| Publishing and Calling Java Methods Using PL/SQL Packages | |
| |
| |
| Calling Java Stored Programs from PL/SQL Procedures | |
| |
| |
| Calling Java Stored Programs from Java | |
| |
| |
| Loading and Publishing Java Stored Programs Using JDeveloper | |
| |
| |
| Database Triggers | |
| |
| |
| |
| Oracle9iAS Containers for J2EE (OC4J) | |
| |
| |
| Overview of OC4J | |
| |
| |
| Installing OC4J | |
| |
| |
| Starting, Restarting, and Shutting Down OC4J | |
| |
| |
| Starting OC4J | |
| |
| |
| Restarting OC4J | |
| |
| |
| Shutting Down OC4J | |
| |
| |
| Defining a Data Source | |
| |
| |
| Servlets | |
| |
| |
| Deploying the Servlet | |
| |
| |
| Invoking the Servlet | |
| |
| |
| JavaServer Pages (JSP) | |
| |
| |
| Enterprise JavaBeans (EJB) | |
| |
| |
| An Example Session Bean | |
| |
| |
| Using the Bean in a Stand-Alone Java Program | |
| |
| |
| Using the Bean in a Servlet | |
| |
| |
| |
| Performance | |
| |
| |
| |
| Connection Pooling and Caching | |
| |
| |
| Connection Pooling and Caching Packages | |
| |
| |
| Connection Pooling with the Thin Driver | |
| |
| |
| Example Program: ConnectionPoolExample1.java | |
| |
| |
| Connection Caching | |
| |
| |
| Creating a Connection Cache | |
| |
| |
| Requesting, Using, and Closing a Connection Instance | |
| |
| |
| Closing a Connection Cache | |
| |
| |
| Example Program: ConnectionCacheExample1.java | |
| |
| |
| Controlling the Number of PooledConnection Objects | |
| |
| |
| Example Program: ConnectionCacheExample2.java | |
| |
| |
| Connection Pooling with the OCI Driver | |
| |
| |
| Creating an OCI Connection Pool | |
| |
| |
| Requesting, Using, and Closing a Connection Instance | |
| |
| |
| Closing the OCI Connection Pool | |
| |
| |
| Example Program: ConnectionPoolExample2.java | |
| |
| |
| |
| Performance Tuning | |
| |
| |
| The perf_test Table | |
| |
| |
| Disabling Auto-Commit Mode | |
| |
| |
| Example Program: AutoCommitExample.java | |
| |
| |
| Batching | |
| |
| |
| Standard Update Batching | |
| |
| |
| Oracle Update Batching | |
| |
| |
| Row Prefetching | |
| |
| |
| Standard Row Prefetching | |
| |
| |
| Oracle Row Prefetching | |
| |
| |
| Defining the Types of Result Set Columns | |
| |
| |
| The defineColumnType() Method | |
| |
| |
| Defining the Length for a Column | |
| |
| |
| Structured Objects, Object References, and Array Columns | |
| |
| |
| Example Program: DefineColumnTypeExample.java | |
| |
| |
| Statement Caching | |
| |
| |
| Enabling Statement Caching and Setting the Statement Cache Size | |
| |
| |
| Types of Statement Caching | |
| |
| |
| Using Implicit Statement Caching | |
| |
| |
| Using Explicit Statement Caching | |
| |
| |
| Tuning SQL Statements | |
| |
| |
| Use a WHERE Clause to Restrict the Rows | |
| |
| |
| Add Additional Indexes | |
| |
| |
| A Final Note | |
| |
| |
| |
| Appendixes | |
| |
| |
| |
| Oracle and Java Type Mappings | |
| |
| |
| JDBC 2.0 Type Mappings | |
| |
| |
| Oracle Extended JDBC Type Mappings | |
| |
| |
| Java Wrapper Type Mappings | |
| |
| |
| |
| Oracle Java Tools Reference | |
| |
| |
| JPublisher | |
| |
| |
| loadjava | |
| |
| |
| dropjava | |
| |
| |
| ncomp | |
| |
| |
| deploync | |
| |
| |
| statusnc | |
| |
| |
| |
| Selected JDBC Interface and Class Reference | |
| |
| |
| Get and Update Methods of the java.sql.ResultSet Interface | |
| |
| |
| The Get Methods of java.sql.ResultSet | |
| |
| |
| The Update Methods of java.sql.ResultSet | |
| |
| |
| Get and Update Methods of the oracle.jdbc.OracleResultSet Interface | |
| |
| |
| The Get Methods of oracle.jdbc.OracleResultSet | |
| |
| |
| The Update Methods of oracle.jdbc.OracleResultSet | |
| |
| |
| Set Methods of the java.sql.PreparedStatement Interface | |
| |
| |
| Set Methods of the oracle.jdbc.OraclePreparedStatement Interface | |
| |
| |
| Integer Constants of the java.sql.Types Class | |
| |
| |
| Integer Constants of the oracle.jdbc.OracleTypes Class | |
| |
| |
| |
| JNDI and Data Sources | |
| |
| |
| Binding a Data Source to JNDI | |
| |
| |
| |
| Create an OracleDataSource Object | |
| |
| |
| |
| Set the Attributes for the OracleDataSource Object | |
| |
| |
| |
| Create a Properties Object | |
| |
| |
| |
| Add the JNDI Properties to the Properties Object | |
| |
| |
| |
| Create a JNDI Context Object | |
| |
| |
| |
| Bind the OracleDataSource Object to JNDI Using the Context Object | |
| |
| |
| Example Program: JNDIExample1.java | |
| |
| |
| Looking Up a Data Source Using JNDI | |
| |
| |
| |
| Look Up the Data Source Using JNDI | |
| |
| |
| Example Program: JNDIExample2.java | |
| |
| |
| Index | |