Cover image for Java Programming with Oracle JDBC

Book description

JDBC is the key Java technology for relational database access. Oracle is arguably the most widely used relational database platform in the world. In this book, Donald Bales brings these two technologies together, and shows you how to leverage the full power of Oracle's implementation of JDBC.

You begin by learning the all-important mysteries of establishing database connections. This can be one of the most frustrating areas for programmers new to JDBC, and Donald covers it well with detailed information and examples showing how to make database connections from applications, applets, Servlets, and even from Java programs running within the database itself.

Next comes thorough coverage of JDBC's relational SQL features. You'll learn how to issue SQL statements and get results back from the database, how to read and write data from large, streaming data types such as BLOBs, CLOBs, and BFILEs, and you'll learn how to interface with Oracle's other built-in programming language, PL/SQL.

If you're taking advantage of the Oracle's relatively new ability to create object tables and column objects based on user-defined datatypes, you'll be pleased with Don's thorough treatment of this subject. Don shows you how to use JPublisher and JDBC to work seamlessly with Oracle database objects from within Java programs. You'll also learn how to access nested tables and arrays using JDBC.

Donald concludes the book with a discussion of transaction management, locking, concurrency, and performance--topics that every professional JDBC programmer must be familiar with. If you write Java programs to run against an Oracle database, this book is a must-have.

Table of Contents

  1. Java Programming with Oracle JDBC
    1. Preface
      1. Why I Wrote This Book
      2. This Book’s Intended Audience
      3. Structure of This Book
      4. Conventions Used in This Book
      5. Software and Versions
      6. Comments and Questions
      7. Acknowledgments
    2. I. Overview
      1. 1. Introduction to JDBC
        1. The JDBC API
        2. Clients
          1. What Is Client/Server?
          2. Types of Clients
        3. Using SQL
    3. II. Connections
      1. 2. Application Database Connections
        1. JDBC Drivers
          1. Driver Types
          2. Oracle’s JDBC Drivers
          3. Guidelines for Choosing a Driver
          4. Versions
          5. Oracle Class Files
        2. Installation
          1. Installing the OCI Driver
            1. Install the Oracle Client
            2. Setting environment variables
          2. Installing the Thin Driver
            1. Install the Thin driver class files
            2. Setting environment variables
          3. Using Sun’s JDBC-ODBC Bridge
        3. Connecting to a Database
          1. Package Imports
          2. Registering a JDBC Driver
          3. Formulating a Database URL
            1. Using a database URL with a username and password
            2. Using only a database URL
            3. Using a database URL and a Properties object
            4. Mistakes to watch for
          4. Application Examples
            1. An OCI driver example
            2. A Thin driver example
        4. Handling Exceptions
          1. Java Exception Handling
            1. Try blocks
            2. Try block nesting behavior
          2. SQLException Methods
      2. 3. Applet Database Connections
        1. Oracle Drivers and JDK Versions
        2. It’s an Applet’s Life
        3. Packaging Your Applet
          1. A Development Packaging Cycle
          2. Production Packaging Cycles
          3. Oracle NLS Support
        4. Getting Around the Sandbox
          1. Using Connection Manager
            1. Installing Connection Manager
            2. Formulating a database URL for Connection Manager
          2. Getting Socket Permissions
            1. Java 2 security policies
            2. Setting up a SocketPermissions policy
            3. An adaptive applet tag
            4. An applet to test our SocketPermissions policy
        5. Establishing a Connection Through a Firewall
          1. Configuring a Firewall for Net8
          2. Formulating a Firewall Database URL
          3. Net8-Compliant Firewalls
        6. Guidelines for Choosing a Workaround
      3. 4. Servlet Database Connections
        1. Oracle Driver Selection
        2. Servlet Connection Strategies
          1. A Per-Transaction Connection
          2. A Dedicated Connection
          3. A Session Connection
            1. Creating a session-bound wrapper for connections
            2. Using the session bound wrapper class
          4. A Cached Connection
            1. A class to wrap cached connections
            2. A class to load drivers and create connections
            3. A class to manage cached connections
            4. A servlet that uses cached connections
        3. Guidelines for Choosing a Connection Strategy
      4. 5. Internal Database Connections
        1. Server-Side Driver Types
        2. Using the Server-Side Internal Driver
          1. An Internal Driver Example
            1. Loading a class file into a database
            2. Publishing a class
            3. Executing a Java stored procedure
          2. Internal-Connection Considerations
            1. You have only one connection
            2. Closing one of your connections closes all of your connections
            3. Auto-commit is not supported
            4. Additional methods are available for use in exception handlers
        3. Using the Server-Side Thin Driver
          1. A Server-Side Thin Driver Example
          2. Database SocketPermission Policies
        4. JServer Program Support
      5. 6. Oracle Advanced Security
        1. Authentication
        2. Data Encryption
          1. Enabling Encryption on a Server
          2. Enabling Encryption on a Client
          3. Negotiating the Use of Encryption
          4. Negotiating the Type of Encryption
        3. Data Integrity
          1. Enabling Data Integrity on a Server
          2. Enabling Data Integrity on a Client
          3. Negotiating the Use of Data Integrity
        4. A Data Encryption and Integrity Example
        5. Secure Sockets Layer
      6. 7. JNDI and Connection Pooling
        1. DataSources
          1. OracleDataSources
          2. Getting a Connection from a DataSource
          3. Using a JNDI DataSource
          4. Caveats
        2. Oracle’s Connection Cache
          1. ConnectionPoolDataSources
          2. Connection Cache Implementation
            1. The OracleConnectionCache interface
            2. The OracleConnectionCacheImpl class
          3. A Connection Caching Example
            1. Creating and binding a ConnectionPoolDataSource
            2. Creating the connection manager
            3. Testing our connection cache
    4. III. Relational SQL
      1. 8. A Relational SQL Example
        1. Relational Database Analysis
          1. Identifying Entities
          2. Identifying Primary Keys
          3. Determining Relationships Between Entities
        2. Refining the Analysis
          1. Defining Dumb Primary Keys
          2. Reanalysis of the Person Entity
        3. Relational Database Design
          1. Selecting Data Types
          2. DDL Coding Conventions
          3. Writing the DDL
      2. 9. Statements
        1. Creating a Statement Object
        2. The execute( ) Method
          1. Executing DDL
          2. Creating the HR Tables
        3. The executeUpdate( ) Method
          1. Executing an INSERT, UPDATE, or DELETE Statement
          2. Auto-Commit
          3. Oracle and SQL92 Escape Syntax
            1. Handling ticks
            2. Converting numbers
            3. Converting date and time values
            4. Using Oracle’s built-in TO_DATE( ) function
            5. Using SQL92 syntax with dates
            6. An escape syntax example
          4. More on SQL92 Escape Syntax
            1. SQL92 LIKE escape syntax
            2. Outer join escape syntax
            3. Function escape syntax
            4. Unsupported SQL92 syntax
          5. Batching
        4. The executeQuery( ) Method
          1. Executing a SELECT Statement
          2. Defining Columns
          3. Row Prefetch
        5. OracleStatement Implements Statement
      3. 10. Result Sets
        1. Basic Cursor Positioning
        2. Data Types
        3. Accessor Methods
          1. Handling NULL Values
            1. Avoiding the use of primitive data types
            2. Using wrapper classes
            3. Representing NULL with a special value
          2. ResultSetMetaData
            1. Getting the ResultSetMetaData object
            2. Getting column information
            3. Getting column values
            4. A ResultSetMetaData example
        4. Scrollable, Updateable Result Sets
          1. Eligible SELECT Statement Rules
            1. Downgrade rules
            2. Verifying the ResultSet category
          2. Scrollability
          3. Updateability
            1. Inserting a new row into a result set
            2. Updating a row in a result set
            3. Deleting a row in a result set
            4. Update visibility and detection
          4. Reasons Not to Use Updateable Result Sets
        5. ResultSet Is an OracleResultSet
      4. 11. Prepared Statements
        1. A Prepared Statement Versus a Statement
        2. Formulating SQL Statements
          1. Accessor Methods
            1. SQL type constants
            2. NULL values
            3. Dynamic input
            4. Dynamic input using the Oracle data types
            5. Fixed-length CHAR columns
            6. A prepared statement example
          2. Limits
          3. Defining Parameter Types
        3. Batching
          1. Standard Batching Implementation
            1. Adding rows to a batch
            2. Executing a batch
            3. Canceling a batch
            4. Dependencies
            5. A standard batching example
          2. Oracle’s Batching Implementation
            1. Setting a default batch size for a connection
            2. Setting a batch size for a specific statement
            3. Forcing batch execution
            4. An Oracle batching example
        4. PreparedStatement Is an OraclePreparedStatement
      5. 12. Streaming Data Types
        1. BLOBs
          1. An Example LOB Table
          2. Inserting a BLOB Using oracle.sql.BLOB
            1. Creating a locator
            2. Retrieving a locator
            3. Using the locator to insert BLOB data
            4. An example that inserts a BLOB using an output stream
            5. A nonstreaming alternative for small BLOBs
            6. An example that inserts a BLOB using the putBytes( ) method
          3. Inserting a BLOB Using java.sql.PreparedStatement
            1. Using setBinaryStream( ) to insert a BLOB
            2. Using setBytes( ) to insert a BLOB
            3. Using setObject( ) to insert a BLOB
          4. Updating a BLOB
            1. Using oracle.sql.BLOB to update a BLOB
            2. Using java.sql.PreparedStatement to update a BLOB
          5. Deleting a BLOB
          6. Selecting a BLOB
            1. Using getBinaryStream( ) to retrieve BLOB data
            2. An example using getBinaryStream( )
          7. Oracle BLOB Methods
        2. CLOBs
          1. Inserting a CLOB Using oracle.sql.CLOB
            1. Using getCharacterOutputStream( ) to insert a CLOB
            2. Using getAsciiOutputStream( )
          2. Inserting a CLOB Using java.sql.PreparedStatement
            1. Using setCharacterStream( )
            2. Using setAsciiStream( )
          3. Updating a CLOB
            1. Using oracle.sql.CLOB to update a CLOB
            2. Using java.sql.PreparedStatement
          4. Deleting a CLOB
          5. Selecting a CLOB
          6. Oracle CLOB Methods
        3. BFILEs
          1. Creating a Directory Object
          2. Creating a BFILE Table
          3. Inserting a BFILE
          4. Updating a BFILE
          5. Deleting a BFILE
          6. Selecting BFILEs
          7. BFILE Methods
        4. LONG RAWs
          1. Creating a Table with a LONG RAW
          2. Inserting a LONG RAW
          3. Updating a LONG RAW
          4. Deleting a LONG RAW
          5. Selecting a LONG RAW
        5. LONGs
          1. Inserting or Updating a LONG
          2. Selecting a LONG
      6. 13. Callable Statements
        1. Understanding Stored Procedures
          1. Stored Procedure Signatures
            1. Standalone functions
            2. Standalone procedures
            3. Packages
          2. Describing Signatures
          3. Granting Execute Rights
        2. Calling Stored Procedures
          1. Formulating a Callable Statement
            1. SQL92 escape syntax
            2. Oracle syntax
          2. Creating a CallableStatement
          3. Handling Errors
          4. Registering OUT Parameters
          5. Setting IN Parameters
          6. Handling NULL Values
          7. Executing a Stored Procedure
          8. Getting OUT Parameter Values
          9. Putting It All Together
        3. CallableStatement Is an OracleCallableStatement
    5. IV. Object-Relational SQL
      1. 14. An Object-Relational SQL Example
        1. From Relational Tables to Object Views
          1. Transforming Entities into Objects
          2. Creating Object Views
            1. Creating user-defined data types
            2. Selecting a reference value
            3. Creating an object view
            4. Creating INSTEAD OF triggers
        2. Object Tables
      2. 15. Weakly Typed Object SQL
        1. Accessing Objects as Relational Tables
        2. Structs
          1. Inserting Object Values
            1. Creating a StructDescriptor
            2. Creating an Object array
            3. Creating a Struct object
            4. Inserting a Struct using java.sql.PreparedStatement
          2. Retrieving Object Values
            1. Formulating a SELECT statement
            2. Retrieving an object value as a Struct
            3. Casting the returned object attributes
          3. Updating Object Values
          4. Deleting Object Values
        3. Arrays
          1. Creating an Array
            1. Creating an ArrayDescriptor
            2. Creating an Object array
            3. Creating an Array object
        4. Refs
          1. Retrieving a Reference
          2. Materializing Object Values Using a Ref
          3. Updating Object Values Using a Ref
          4. Deleting Object Values Using a Ref
        5. Calling Object Methods
        6. Putting It All Together
        7. Oracle’s Implementations
          1. ArrayDescriptor
          2. ARRAY Implements Array
          3. StructDescriptor
          4. STRUCT Implements Struct
          5. REF Implements Ref
      3. 16. Strongly Typed Object SQL
        1. JPublisher
          1. Command-Line Options
          2. Property File Syntax
          3. Input File Syntax
          4. Writing a Class That Extends a Generated Class
        2. The SQLData Interface
          1. Hand-Coding a SQLData Implementation
          2. Using JPublisher to Generate SQLData Classes
            1. Creating database objects
            2. Creating a mapping file for SQLData
            3. Creating a properties file for SQLData
            4. Executing JPublisher
            5. Examining JPublisher’s output
            6. Extending a generated superclass
            7. Database versus client method execution
          3. Adding Classes to a Type Map
            1. Getting an existing type map
            2. Adding mapping entries
            3. Setting the updated type map
          4. Using getObject( ) with a Type Map
          5. Inserting an Object
            1. Creating a new instance of a custom Java class
            2. Formulating an INSERT statement
            3. Creating a prepared statement object
            4. Set the object value
            5. Execute the prepared statement
          6. Retrieving an Object
            1. Creating a variable to hold your database object
            2. Formulating a SELECT statement
            3. Creating a statement object
            4. Executing the SELECT statement
            5. Getting an object value from a result set
          7. Updating an Object
          8. Deleting an Object
          9. A SQLData Example
        3. Oracle’s CustomDatum Interface
          1. Creating an Input File for CustomDatum
          2. Creating a Properties File for CustomDatum
          3. Compiling SQLJ Files
          4. A CustomDatum Example
    6. V. Essentials
      1. 17. Transactions
        1. Manual Transactions
        2. Transaction Scope
        3. Implicit Locking and Visibility
        4. Isolation Levels
        5. Distributed Transactions
          1. XA Data Sources
          2. XA Connections
          3. XA IDs
          4. XA Resources
          5. XA Exceptions
          6. classpath and imports
          7. An XA Example
      2. 18. Detection and Locking
        1. Oracle’s Locking Mechanisms
          1. Implicit Locking
          2. Row Locking
          3. Table Locking
          4. Locks Alone Don’t Solve the Problem
        2. Detection
          1. Using an Updatestamp
          2. Comparing All Columns or Attributesto Their Original Values
          3. Comparing Modified Columns or Attributes to Their Original Values
        3. Data Integrity Solutions
          1. Pessimistic Data Integrity Solutions
            1. SELECT FOR UPDATE NOWAIT
            2. Pessimistic detection and implicit locking
          2. An Optimistic Data Integrity Solution
          3. Which Approach to Use?
      3. 19. Performance
        1. A Testing Framework
        2. Auto-Commit
        3. SQL92 Token Parsing
        4. Statement Versus PreparedStatement
          1. The OCI Driver
          2. The Thin Driver
        5. Batching
          1. The OCI Driver
          2. The Thin Driver
        6. Predefined SELECT Statements
        7. CallableStatements
        8. OCI Versus Thin Drivers
      4. 20. Troubleshooting
        1. The “Gotchas”
          1. Class XXX Not Found
          2. A Missing JDBC Jar File
          3. A Bad Database URL
          4. Explicitly Closing JDBC Objects
          5. Running Out of Connections
          6. Boolean Parameters in PL/SQL
          7. The Evil CHAR Data Type
        2. Unsupported Features
          1. Named Cursors
          2. SQL92 Join Syntax
          3. PL/SQL Boolean, Table, and Record Types
          4. IEEE 754 Floating-Point Compliance
          5. Catalog Arguments to DatabaseMetadata
          6. SQLWarning
        3. Debugging
          1. Handling SQLExceptions
          2. Logging DriverManager Connections
          3. Logging DataSource Connections
        4. Net8 Tracing
          1. Client-Side Tracing
          2. Server-Side Tracing
        5. Wait for the Cure
          1. Oracle8i Release 3 (Version 8.1.7) Features
          2. Oracle9i Features
    7. Index
    8. Colophon