JDBC 3.0 feature: Named Parameters - Using Oracle Database 10g JDBC APIs in PreparedStatement

Date: 14-Jan-2004

Objective

Table of Contents

Introduction
Prerequisites
Software Requirements
Binding the parameters of a PreparedStatement using Oracle Specific APIs in JDBC
Running the Sample Java Application
Useful References

Introduction

JDBC (Java Database Connectivity) is a standard Java interface for connecting from Java to relational databases. The JDBC standard was defined by Sun Microsystems, allowing independent providers to implement and extend the standard with their own.

In addition to supporting the standard JDBC API, Oracle drivers have extensions to support Oracle-specific datatypes and to enhance performance. This document introduces the new Oracle JDBC 10g specific API, for setting Named Parameters of a PreparedStatement object in a JDBC class. For more information about Oracle JDBC, refer the Useful References section in this document.

Prerequisites

To work your way through this HowTo, it is necessary to have a basic understanding of the fundamental concepts of JDBC, and how to use the JDBC APIs to connect to the database for the basic CRUD(Create, Read, Update, and Delete)- the essential database operations. In addition, you need to know the basic Oracle database concepts.

Software Requirements

• Oracle Database 10g. You can download the database here.
• Oracle Database 10g JDBC Driver. This can be downloaded here.
• JDK1.4.x or later that can be downloaded here.

Note: JDBC driver classes are available with an Oracle Client or a Database installation and need not be downloaded separately.

Binding the parameters of a PreparedStatement using Oracle Specific APIs in JDBC

Description

• set<datatype>(String, ...)
• set<datatype>AtName((String, ...)

SQL> CREATE TABLE product_information(PRODUCT_ID           NUMBER(6)      NOT NULL,
                                      PRODUCT_NAME         VARCHAR2(50)   NOT NULL,
                                      PRODUCT_DESCRIPTION  VARCHAR2(2000) NOT NULL, 
                                      MIN_PRICE            NUMBER(8,2),
                                      LIST_PRICE           NUMBER(8,2));
SQL> INSERT INTO product_information VALUES(2400, 'DIMM - 512 MB', 
        '512 MB DIMM memory. Improved memory upgrade granularity', 380, 448);
SQL> INSERT INTO product_information VALUES(2401, 'DIMM - 64MB',
        'Memory DIMM: RAM, 64MB (100-MHz Unregistered ECC SDRAM)', 202, 247);
SQL> INSERT INTO product_information VALUES(2402, 'EDO - 32MB',
        'Memory EDO SIM: RAM, 32 MB (100-MHz Unregistered ECC SDRAM)', 149, 179);

Following is the code snippet that demonstrates the usage of the Oracle Database 10g JDBC APIs for a 'SELECT' operation. Please refer the UsingOracleNamedParameters.java file for the full runnable Java class.

     ..........

     //Database Connection object
     Connection conn = null;

     // OraclePreparedStatement object
     OraclePreparedStatement pstmt = null;

     // ResultSet object
     ResultSet rst = null;
     .......

      // Create SQL query statement to retrieve records from product_information
      // table in the database.
      String sqlString = "SELECT product_id, product_name, product_description,"
                       + "list_price, min_price FROM product_information "
                       + "WHERE product_name LIKE :pname AND "
                       + "list_price <= :price AND min_price <= :price";

      // Connect to the database.
      ......
 
      // Use OraclePreparedStatement
      pstmt = (OraclePreparedStatement)conn.prepareStatement(sqlString);

      String val = "%"+name+"%";

      // Use Oracle Specific APIs to set the values for the bind parameters
      // in the SQL statement. Note that price is bound only once.
      pstmt.setStringAtName("pname",val);
      pstmt.setIntAtName("price", priceVal);

      // Execute the query.
      rst = pstmt.executeQuery();
      ............
	  

Running the Sample Java Application

• Copy the class file UsingOracleNamedParameters.java from here to a directory say for example, UsingOracleNamedParameters
• From the UsingOracleNamedParameters directory, include the JDBC driver class in the CLASSPATH.
  • Use ojdbc14.jar with JDK 1.4.x.
  • Also include, the current directory(.) to the CLASSPATH.
• Make sure you have created the database table 'product_information' with few records. Refer the SQL Script for more details.
• Edit the file UsingOracleNamedParameters.java to set your database details. Change the following lines in the file:
  
  // Set database URL details.
String url = "jdbc:oracle:thin:@localhost:1521:orcl";

// Get the database connection.
conn = DriverManager.getConnection(url,"oe","oe");

Mention the username and password under which the table was created. Save the file.
  
  
• Compile the Java file using:
  javac -d . UsingOracleNamedParameters.java
• Run the class using:
  java oracle.otnsamples.jdbc.UsingOracleNamedParameters
  
  This will display the output as follows:
  
  name: DIMM - 512 MB listprice: 448 minprice: 380
  name: DIMM - 64MB listprice: 247 minprice: 202

Useful References

• Oracle9iJDBC Developer's Guide and Reference
• JDBC API Documentation by Sun
• JDBC How To documents on OTN
• Basic JDBC samples on OTN
• JDBC WhitePaper
• JDBC Technology Page