PreparedStatement Overview

JDBC for Oracle - Herong's Tutorial Examples

∟PreparedStatement Overview

This section provides an overview of PreparedStatement.

If you have a SQL statement that needs to be executed multiple times, it is more efficient to use a JDBC PreparedStatement object to run it. JDBC PreparedStatement class supports the following main features:

SQL statements PreparedStatement objects are pre-compiled on the database server side.
IN parameters are supported in SQL statements in PreparedStatement objects.
Batch execution mode is supported to run the run SQL statement multiple times in a single transaction.

A PreparedStatement object should be created from a Connection object with the prepareStatement() method and executed like a regular Statement object as shown in the following program:

/* OraclePreparedSelect.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
import java.sql.*;
public class OraclePreparedSelect {
  public static void main(String [] args) {
    Connection con = null;
    try {
      oracle.jdbc.pool.OracleDataSource ds
        = new oracle.jdbc.pool.OracleDataSource();
      ds.setDriverType("thin");
      ds.setServerName("localhost");
      ds.setPortNumber(1521);
      ds.setDatabaseName("XE");
      ds.setUser("Herong");
      ds.setPassword("TopSecret");
      con = ds.getConnection();

// PreparedStatement for SELECT statement
      PreparedStatement sta = con.prepareStatement(
  "SELECT * FROM Profile WHERE ID = 2");

// Execute the PreparedStatement as a query
      ResultSet res = sta.executeQuery();

// Get values out of the ResultSet
      res.next();
      String firstName = res.getString("FirstName");
      String lastName = res.getString("LastName");
      System.out.println("User ID 2: "+firstName+' '+lastName);

// Close ResultSet and PreparedStatement
      res.close();
      sta.close();

      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

The output looks correct:

herong> javac -cp .;ojdbc11.jar OraclePreparedSelect.java

herong> java -cp .;ojdbc11.jar
   OraclePreparedSelect

User ID 2: Janet Gates

Table of Contents

About This Book

JDBC (Java Database Connectivity) Introduction

JDK (Java SE) Installation

Oracle Express Edition Installation on Windows

Oracle JDBC Drivers

Oracle - Reference Implementation of JdbcRowSet

►Oracle - PreparedStatement

►PreparedStatement Overview

PreparedStatement with Parameters

PreparedStatement in Batch Mode

Performance of Inserting Rows with a PreparedStatement

Performance of Inserting Rows with a Regular Statement

Performance of Inserting Rows with a ResultSet

Oracle - JBDC CallableStatement

Oracle CLOB (Character Large Object) - TEXT

Oracle BLOB (Binary Large Object) - BLOB

Using Connection Pool with JDBC

Archived Tutorials

References

Full Version in PDF/EPUB