II. Java Persistence and Java SQL Data Access
by Kuassi Mensah
Oracle Database Programming using Java and Web Services
- Copyright
- Oracle Database Related Book Titles:
- Foreword
- Preface
- Introduction
- Acknowledgments
- I. Java in the Database
- 1. Stored Procedures as Database Programming Model
- 1.1. Rationale for Stored Procedures
- 1.1.1. Simplifying Database Programming
- 1.1.2. Centrally Managed Data Logic
- 1.1.3. Performance: Run JDBC Applications Faster in the Database
- 1.1.4. Encapsulation
- 1.1.5. Security: Advanced Data Access Control
- 1.1.6. Resource Optimization
- 1.1.7. Low-Cost Deployment
- 1.1.8. Fully Utilize Database Capabilities
- 1.2. Obstacles to the Adoption of Stored Procedures
- 1.3. Languages for Stored Procedures
- 1.3.1. Proprietary Languages
- 1.3.2. Java for Stored Procedures
- Complete Programming Language
- Secure Language
- Type Safety
- Robustness
- Productivity: Rapid Design Features
- Using Java as a Procedural Language
- Standard Specifications for Java Stored Procedures
- POJO-like Programming Model
- Stored Procedures and O/R Mapping
- Cross-Database Portability
- Huge Class Library and Tools: Reduced Development Time and Costs
- Skills Reuse
- Java Database Connectivity and SQL Data Access
- Starting with Java
- 1.3.3. .NET Languages
- 1.4. PL/SQL or Java
- 1.1. Rationale for Stored Procedures
- 2. OracleJVM: Under the Hood
- 2.1. Design Goals and Architecture
- 2.1.1. Tight Integration with the RDBMS
- 2.1.2. J2SE Compatibility
- 2.1.3. How Is Java Stored in the Database?
- 2.1.4. Class Sharing
- 2.1.5. Interapplication Isolation (JSR 121)
- 2.1.6. Contrasting OracleJVM with the JDK VM
- 2.1.7. Resource Control
- 2.1.8. SQL Data Access from Java in the Database
- 2.1.9. DBMS_JAVA: The All-Purpose Tool for Administering OracleJVM
- 2.2. Java Memory Management
- 2.2.1. Key Memory Structures of the Oracle Database
- 2.2.2. Java Memory Allocation Techniques
- 2.2.3. Garbage Collection Techniques
- 2.2.4. Java Memory Areas
- 2.2.5. Shared Servers versus Dedicated Processes
- 2.2.6. The Javapool
- 2.2.7. Top-Level Calls and Recursive Calls
- 2.2.8. State Preservation across Calls and End-of-Call Migration
- 2.2.9. End-of-Call, VM Termination, and Session Termination
- 2.3. Security in OracleJVM
- 2.4. Java VM Life Cycle
- 2.5. Java Execution in the Database
- 2.6. The Native Java Compiler (NCOMP)
- 2.7. Conclusion
- 2.1. Design Goals and Architecture
- 3. Developing and Running Java in the Database
- 3.1. Developing Java in the Database
- 3.1.1. Turning JDBC Applications into Java Stored Procedures
- 3.1.2. Creating or Loading Java in the Database
- Method 1: Create Java Sources in the Database, Interactively
- Method 2: Uploading Client-Side Java Files into the Database
- The loadjava Utility
- Method 3: Uploading Server-Side Java Files into the Database
- Method 4: Using JDeveloper and Third-party IDE
- Method 5: Creating Java Objects from an Existing CLOB/BLOB/BFILE
- Method 6: Creating Java Objects from XML (XDB folder)
- Dumping the Java Class Bytecode
- Checking the Status of Java Classes in Your Schema
- 3.1.3. Removing Java Sources, Classes, and Resources from the Database
- 3.1.4. Setting/Querying Environment Variable and System Properties
- 3.1.5. The Java Compiler within the Database
- 3.2. Turning Java in the Database into Stored Procedures
- 3.3. Mapping SQL and PL/SQL Types to/from Java Types
- 3.3.1. Mapping Matrix
- 3.3.2. Code Segments for Mapping
- Setup
- Mapping java.sql.Date to/from SQL DATE
- Mapping oracle.sql.DATE to/from SQL DATE
- Mapping java.lang.Integer to/from SQL NUMBER
- Mapping oracle.sql.NUMBER to SQL NUMBER
- Mapping oracle.sql.CHAR to/from SQL CHAR
- Mapping oracle.sql.RAW to/from SQL RAW
- Setup for CLOB, BLOB, and BFILE Mapping
- Steps
- Mapping CLOB Locator to/from java.sql.Clob
- Mapping java.sql.Blob to/from SQL BLOB
- Mapping oracle.sql.BFILE to/from SQL BFILE
- Mapping User-Defined Object Types to oracle.sql.STRUCT
- Mapping REF of ADT Table Types
- Mapping REF Cursors to java.sql.ResultSet
- Mapping VARRAY of Scalar SQL Types
- Mapping Nested Tables of Scalar SQL Types
- Mapping SQLJ Object Types
- 3.4. Invoking Java in the Database
- 3.5. Managing Java in the Database
- 3.1. Developing Java in the Database
- 4. Pragmatic Applications Using Java in the Database
- 4.1. CNXO: Secure Credit Card Processing with Oracle and JSSE
- 4.2. Using J2EE and Java in the Database Together
- 4.3. JDBC Call-Out to Non-Oracle Databases
- 4.4. SAP Java Connector: Accessing the SAP System from the Oracle Database
- 4.5. Excel-like Expression Parser in the Database
- 4.5.1. Rationales for Custom Parsers in the Database
- 4.5.2. What Is the Mini-Parser?
- 4.5.3. Implementing the Mini-Parser
- Step 1: Describing the Mini-Parser
- The Functional Structure of the Mini-Parser
- Code Outline
- Step 2: Generating the Mini-Parser
- Generating a Lexical Analyzer
- Generating the Parser
- Step 3: Deploying the Mini-Parser in the Database
- Step 4: Making the Mini-Parser Invocable through SQL and PL/SQL
- Step 5: Use It
- 5. Database Scripting Using Non-Java Languages
- 5.1. Why Contemplate Non-Java Languages for the Database?
- 5.2. Database Scripting with OracleJVM—Just for Fun!
- 5.2.1. Proof of Concept #1: Running TCL (JACL) Scripts in the Database
- 5.2.2. Proof of Concept #2: Running Jython (Python) in the Database
- 5.2.3. Proof of Concept #3: Running Kawa (Scheme) in the Database
- 5.2.4. Proof of Concept #4: Running Groovy in the Database
- What Is Groovy?
- Groovy Enabling Your Database
- Loading the Groovy Runtime in the Database
- Running Groovy Scripts in the Database
- Running Groovy Scripts as a Dynamically Loaded Class
- Storing Groovy Scripts in Database Columns
- Integrating Groovy with SQL
- Conclusion
- II. Java Persistence and Java SQL Data Access
- 6. Introducing the JDBC Technology and Oracle’s Implementation
- 6.1. JDBC Primer
- 6.1.1. First Steps in JDBC
- 6.1.2. JDBC within J2SE and J2EE Environments
- 6.2. Overview of JDBC Specifications
- 6.2.1. Overview of JDBC 1.22 Specification (Where Things Started!)
- 6.2.2. Overview of JDBC 2.0 Specification (A Major Spec!)
- 6.2.3. Overview of JDBC 3.0 Specification
- 6.2.4. Overview of Upcoming JDBC 4.0 Specification
- 6.2.5. JDBC Standards Support in the Oracle JDBC Drivers
- Oracle 8i Release 8.1.5 JDBC Drivers
- Oracle 8i Release 8.1.6 JDBC Drivers
- Oracle 8i Release 8.1.7 JDBC Drivers
- Oracle 9i Release 1 (9.0.1) JDBC Drivers
- Oracle 9i Release 2 ( 9.2.0.x) JDBC Drivers
- Oracle Database 10g Release 1 (10.1.0.x) JDBC Drivers
- Oracle Database 10g Release 2 (10.2.0.x) JDBC Drivers
- 6.3. Architecture and Packaging of Oracle JDBC Drivers
- 6.1. JDBC Primer
- 7. URL, DataSource, Connection, and Statements
- 7.1. JDBC URL
- 7.2. DataSources
- 7.3. Connections and Connection Services
- 7.3.1. JDBC Connections and Oracle Extensions
- 7.3.2. Connection Caching: Implicit Connection Cache
- Implicit Access to the Cache
- Saving the Values of Connection Attributes
- Caching Heterogeneously Authenticated Connections
- Connection Retrieval Based on User-Defined Attributes
- Applying Connection Attributes to a Cached Connection
- Setting Unmatched Attribute Values
- Connection Retrieval Based on Attributes and Weights
- List of System and Connection Properties
- Implicit Connection Cache Code Sample
- 7.3.3. The Connection Cache Manager
- 7.3.4. RAC Events and Fast Application Notification
- 7.3.5. High Availability: Fast Connection Failover
- 7.3.6. Scalability: Connection Load Balancing
- 7.3.7. JDBC Support for Transparent Application Fail-over
- 7.3.8. Proxy Authentication
- 7.3.9. Connection Wrapping
- 7.3.10. JDBC Connections in Grid Environment
- 7.4. JDBC Statements and Oracle Extensions
- 8. SQL Data Access and Manipulation
- 8.1. Key Metadata in JDBC
- 8.2. Manipulating Oracle Data Types with JDBC
- Example:
- 8.2.1. Manipulating SQL Null Data
- 8.2.2. Manipulating Character Data Types
- 8.2.3. Oracle JDBC Support for Number Data Types
- 8.2.4. JDBC Support for Long and Raw Data Types
- 8.2.5. JDBC Support for SQL Datetime Data Types
- 8.2.6. JDBC Support for LOB Datatypes
- 8.2.7. JDBC Support for ROWID
- 8.2.8. JDBC Support for OPAQUE Type
- 8.2.9. JDBC Support for XMLType
- 8.2.10. JDBC Support for SQL Object Types and References Types
- 8.2.11. JDBC Support for User-Defined Collections
- 8.2.12. JDBC Support for Spatial Types
- 8.2.13. Unsupported Types
- 8.3. Result Set Support in Oracle JDBC
- 8.4. RowSet
- 8.4.1. Introducing the RowSet API
- 8.4.2. JDBCRowSet and OracleJDBCRowSet
- 8.4.3. CachedRowSet and OracleCachedRowSet
- 8.4.4. WebRowSet and OracleWebRowSet
- 8.4.5. FilteredRowSet and OracleFilteredRowSet
- 8.4.6. JoinRowSet and OracleJoinRowSet
- 8.5. Conclusion
- 9. JDBC Quality of Services and Best Practices
- 9.1. Transaction Services
- 9.2. Security Services
- 9.2.1. Oracle JDBC Support for SSL
- 9.3. Tips and Best Practices
- 9.4. Conclusion
- 6. Introducing the JDBC Technology and Oracle’s Implementation
- III. Oracle Database Programming with SQLJ
- 10. Introducing the SQLJ Technology and Oracle’s Implementation
- 11. The SQLJ Language and Oracle Extensions
- 11.1. Declaration Statements
- 11.2. Executable Statements
- 11.3. Expressions in SQLJ
- 11.4. Interoperability: Using SQLJ and JDBC Together
- 11.5. Conclusion
- 12. SQLJ Data Access and Best Practices
- 12.1. Manipulating Oracle SQL and PL/SQL Data Types with SQLJ
- 12.1.1. Oracle SQLJ Type-Mapping Summary
- 12.1.2. Column Definitions
- 12.1.3. Manipulating SQL Null Data with SQLJ
- 12.1.4. Manipulating Character Data Types with SQLJ
- 12.1.5. Oracle SQLJ Support for Number Data Types
- 12.1.6. SQLJ Streams, LONG, and RAW Data Types
- 12.1.7. SQLJ Support for SQL Datetime Data Types
- 12.1.8. SQLJ Support for SQL LOB Data Types
- Overview
- Using the DBMS_LOB Package
- Using Standard JDBC LOB API and oracle.sql LOB Extensions
- Using SQLJ Streaming
- LOB and BFILE Stored Function Results
- LOB and BFILE Host Variables ( SELECT INTO Targets)
- LOBs and BFILEs in Iterator Declarations
- LOB and BFILE HostVariables as Named Iterator
- LOB and BFILE Host Variables as Positional Iterator (FETCH INTO Targets)
- 12.1.9. SQLJ Support for Oracle SQL ROWID
- 12.1.10. SQLJ Support for OPAQUE Types
- 12.1.11. SQLJ Support for SQL Object Types and SQL References Types
- 12.1.12. Serialized Java Objects
- 12.1.13. SQLJ Support for User-Defined SQL Collections
- 12.1.14. PL/SQL Associative Array
- 12.1.15. Unsupported Types
- 12.2. SQLJ Best Practices
- 12.3. Conclusion
- 12.1. Manipulating Oracle SQL and PL/SQL Data Types with SQLJ
- IV. Oracle Database Programming with JPublisher
- 13. Abridged Oracle JPublisher
- 13.1. Why JPublisher?
- 13.2. Overview
- 13.3. JPublisher In Action
- 13.3.1. User-Defined SQL Object Types
- 13.3.2. SQL Object Reference Types (REF types)
- 13.3.3. REF Cursor Types and Subclassing
- 13.3.4. User-Defined SQL Collection Types
- 13.3.5. User-Defined OPAQUE Types
- 13.3.6. XMLType
- 13.3.7. PL/SQL Conversion Functions
- 13.3.8. PL/SQL RECORD Types
- 13.3.9. PL/SQL Table or Scalar Index-by-Table
- 13.3.10. Oracle Streams AQ
- 13.3.11. Java in the Database
- 13.4. Conclusion
- 13. Abridged Oracle JPublisher
- V. Programming the Oracle Database with Web Services
- 14. Web Services and SOA for DBA, Data Architects, and Others
- 15. Database as Web Services Provider Service
- 15.1. Rationales for Database as Web Services Provider
- 15.2. How Does Database as Web Services Provider Work?
- 15.3. Turning Oracle Database Functionality into Web Services
- 15.4. Assembling Database Web Services Using the Command-Line Tool
- Introducing the Web Services Assembler
- Generic Steps for Publishing Database Functionality Using Web Services Assembler
- 15.4.1. Assembling PL/SQL Web Services Using Web Services Assembler
- 15.4.2. Assembling Java in the Database as a Web Service
- 15.4.3. Assembling SQL Queries or SQL DML Statements as Web Services
- 15.4.4. Assembling Oracle Streams AQ as Web Services
- 15.5. Data Type Restrictions
- 15.6. Conclusion
- 16. Database as Web Services Consumer
- VI. Putting Everything Together
- 17. 360-Degree Programming the Oracle Database
- 17.1. TECSIS Systems: Custom Enterprise Integration Framework
- 17.2. Oracle interMedia
- 17.2.1. What Is Oracle interMedia?
- 17.2.2. How Does It Work?
- 17.2.3. Rationales for Storing Media Data in the Database
- 17.2.4. interMedia Powered by the Oracle Database Extensibility Framework
- 17.2.5. interMedia Powered by Java in the Database
- 17.2.6. Developing Feature-Rich Multimedia Applications Using interMedia
- A PL/SQL Example
- Creating a Table with an ORDImage Column
- Importing Images
- Selecting and Viewing Image Properties
- Creating Thumbnails and Changing Formats
- Exporting Images with ORDImage.export()
- A Java Example
- Establishing a Connection
- Uploading from a File
- Setting Properties
- Getting and Displaying Properties
- Processing an Image
- Downloading to a File
- A PL/SQL Example
- 17.3. British Columbia: Online Corporate Registration
- 17.4. Information Retrieval Using Oracle Text
- 17.5. Database-Driven Content Management System (DBPrism CMS)
- 17.6. Conclusion
- 17. 360-Degree Programming the Oracle Database
- 1. Stored Procedures as Database Programming Model
In Part I, we covered in depth Java in the database, including the rationale for using Java in the database, an overview of the Oracle implementation, and the various application types enabled by an embedded Java virtual machine in the database. However, database programming using Java goes way beyond running Java in the database; in fact, the largest part of database programming using Java happens in the middle tier (i.e., J2EE, J2SE, object to relational mapping iconified by Toplink, and database-related Java design patterns).[1]
The relational database (RDBMS) world and the Java world do not share the same model or semantics. On one hand, we have the relational technology, which includes the SQL language, the RDBMS semantics (i.e., schemas, tables, primary keys, foreign keys, views, stored procedures), and a set of proprietary and open languages for building stored procedures, functions, and packages on top of SQL. On the other hand, we have the Java technology and platform, which includes the object-oriented Java language, the design patterns, a rich set of JCP-sanctioned APIs and enterprise services, and a growing number of open source frameworks and programming models. The mapping between database semantics (SQL data) and Java semantics (i.e., Java state) is achieved through numerous Java APIs, design patterns, and frameworks.
We can group these into two categories:
The do-it-yourself persistence technologies and APIs, such as JDBC, SQLJ, and vendor-specific tools/utilities such as Oracle JPublisher. These technologies explicitly manipulate SQL statements, stored procedures to access or persist Java states into relational tables columns, under the control of the application developer.
The transparent persistence (O/R mapping) technologies, which include frameworks such as Toplink, JavaBeans, JDO, Enterprise JavaBeans, POJOs, and so on. These technologies shield the Java developer from the hassle of doing it by transparently mapping Java objects states to database tables/columns. Ultimately, these technologies use JDBC or SQLJ or stored procedures under the covers.
In Part II, we’ll focus on the do-it-yourself Java API for accessing SQL data and persisting Java states in the Oracle database, including JDBC and Oracle’s implementation, SQLJ and Oracle’s implementation, and Oracle’s JPublisher (which complements JDBC and SQLJ for dealing with advanced/complex Oracle Database types but also enables Database Web Services, covered in Part III).
Enterprise computing is not conceivable without RDBMS access. It’s no surprise then that the JDBC API remains by far the most-used Java API either directly or under the covers. Part II will give you a condensed but hopefully comprehensive and pragmatic coverage of Oracle JDBC. We first introduce the JDBC technology, review the various JDBC specifications, and then look at the implementation of Oracle JDBC, including its architecture, packaging, the level of support for standard APIs, the Oracle extensions, transaction handling, security handling, manageability, and advanced JDBC connection pooling mechanisms for scalability, load balancing, fail-over, and troubleshooting. Most of the features described apply to the server-side JDBC, which is used by Java in the database.
-
No Comment