Sample Illustrating Statement Caching Support in Oracle9i JDBC Drivers.

Table Of Contents 

• Overview of the Sample Application
• Notations used
• Required Software
• Application Set-up and Configuration
• Running the Application 
• Using Oracle9i JDeveloper 
• From command line
• Description of Sample Files

Statement caching prevents the overhead of repeated statement parsing and cursor creation by caching the statement state and meta data. There are two schemes available for statement caching

Implicit Statement Caching :

This scheme automatically caches all Prepared and Callable statements. Each time a statement is created, the cache is searched with the SQL string and statement type( Prepared or Callable), if any match is found the statement is returned from the cache, else a new statement is created. When the user calls the close() method on the statement, it is logically closed and put in the cache(for further use). The whole statement caching process is completely transparent to the developer.

Explicit Statement Caching :

In this scheme, a key(any Java String) is associated with each statement and this key is used to refer to the statement. When a Prepared or Callable statement is closed using stmt.closeWithKey(key), the statement is associated with this key and put in the cache. when the statement has to be created again, OracleConnection.getStatementWithKey(key) is used to retrieve the statement associated with key. Explicit caching has a performance edge over implicit caching since all the details of the statement are cached( statement data, state and meta data).

This sample illustrates the benefits and use of statement caching  with a "Employee Search Application". Consider an employee search application in  which a user can search for employees based on First Name, Department and Salary. Since the skeleton of the statement remains the same except the parameters, it would be ideal to use statement caching( instead of preparing the statement each time).

For more information refer to Oracle9i JDBC Developer's Guide and Reference.

Here is the code explaining Statement Caching. You can find more details of the code in StmtCacheSample.java file under src\oracle\otnsamples\oracle9ijdbc\stmtcache folder. Look into Description of Sample Files section for folder and file details.

Implicit Statement Caching // Set the Statement cache size to 5 ((OracleConnection) conn).setStatementCacheSize(5); // Enable Implicit caching ((OracleConnection) conn).setImplicitCachingEnabled(true); // Set start time start = System.currentTimeMillis(); // Loop, prepare and execute the query 10 times for (int i = 1; i <= 10; i++) { // The statement is searched in the cache, if a match is found, returns // the statement from cache else creates a new statement opstmt = (OraclePreparedStatement) conn.prepareStatement(mysql); opstmt.setString(1, empName); opstmt.setInt(2, Integer.parseInt(deptId[selectedDept])); opstmt.setInt(3, lowerLimit); opstmt.setInt(4, upperLimit); // Execute query rset = opstmt.executeQuery(); // Do not process ResultSet // Close the ResultSet rset.close(); // The statement is not closed physically, but put in the cache. opstmt.close(); } ....... Explicit Statement Caching // Set Statement Cache size to 5 ((OracleConnection) conn).setStatementCacheSize(5); // Enable Explicit caching ((OracleConnection) conn).setExplicitCachingEnabled(true); // Set start time start = System.currentTimeMillis(); // Loop, prepare and execute the query 10 times for (int i = 1; i <= 10; i++) { // Retrieve the statement from Explicit cache using key. opstmt = (OraclePreparedStatement) ((OracleConnection) conn).getStatementWithKey("searchEmployee"); // If statement not in Explicit cache, create one. if (opstmt == null) { // This step is reached only when the function is called for the first time // from the next time the statement would be retrieved from cache. opstmt = (OraclePreparedStatement) conn.prepareStatement(mysql); } opstmt.setString(1, empName); opstmt.setInt(2, Integer.parseInt(deptId[selectedDept])); opstmt.setInt(3, lowerLimit); opstmt.setInt(4, upperLimit); // Execute query rset = opstmt.executeQuery(); // Do not process results // Close the ResultSet rset.close(); // Close the statement with a key(any Java String), this key would be used // to search for this statement in the explicit cache. opstmt.closeWithKey("searchEmployee"); .....

This following notations are used through out this document  

Notation	Description
<SAMPLE_HOME>	Folder where the StmtCacheSample will be unzipped. For example, C:\OTNSamples
<JAVA_HOME>	Folder where JAVA is installed. For example, C:\jdk1.2
<ORACLE_HOME>	Folder where Oracle Database or the Client is installed. For example, C:\ora9i
<JDBC_HOME>	Folder where the Oracle JDBC driver is installed. For example, C:\ora9i\jdbc\lib

• Oracle9i JDeveloper ( Note: Oracle9i JDeveloper is Oracle's Visual Java Development Tool and can be downloaded from here )
  or JDK1.2.x or above This can be downloaded from here .
• Oracle9i Database or higher running SQL*Net TCP/IP listener. This can be downloaded from here .
  
• Oracle9i JDBC Driver. The JDBC driver is available at <ORACLE_HOME>/jdbc/lib or <OC4J_HOME>/jdbc/lib.

• Unjar the provided StmtCacheSample.jar using the following command 

> jar xvf StmtCacheSample.jar

Note: You will find jar.exe in <JAVA_HOME>\bin. Ensure <JAVA_HOME>\bin is present in your system path.  For setting up environment variables in different platforms, please refer environment set up readme document

This creates a folder StmtCacheSample with all the source files.

• Edit Connection.properties file in <SAMPLE_HOME>\StmtCacheSample directory
  Change the hostname, port number, and database name to those of the database instance you want to connect to.

  Note : The Employees table required by this sample is part of 'HR' schema.

This section describes the steps required to run this application using Oracle9i JDeveloper .

• Open Oracle9i JDeveloper and use File/Open option to select the StmtCacheSample.jws from the StmtCacheSample directory.
• Set CLASSPATH to Oracle9i JDBC drivers. It can be done as follows:
  • Select StmtCacheSample.jpr and click Project/Project Settings, it will display the Project Settings window.
  • The options will be displayed in a tree format. Under Configurations, view the currently active configuration. Go to the Libraries option under the currently active configuration.
  • To add JDBC class libraries, click New button. It will display a window New Library
  • Give some name(Oracle JDBC jars) in the Library Name field shown.
  • Click the Edit button at Class Path field, this will display a new window.
  • Click Add Entry. This will display directory browsing window
  • Browse for <ORACLE_HOME>/jdbc/lib/classes12.jar and <ORACLE_HOME>/jdbc/lib/nls_charset12.jar file.
  • Click Select button
  • Click OK till you return to the Libraries option.
    
    Note: If JDK 1.4 is used, then ojdbc14.jar (<ORACLE_HOME>/jdbc/lib) must be used instead of classes12.jar
• Next, select Project/Make StmtCacheSample.jpr from main menu.
• Select the StmtCacheSample.java file and select Run/Run StmtCacheSample.java from JDeveloper main menu to run the application.