Steve's Blog Technical Information on Oracle Middleware.

The advantage of using bind variables within your Java application, it that provides greater application performance and uses less of the shared pool from the database.

This is illustrated in the following 2 examples.

Example 1: Using Bind variables.

package bindvariables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import bindvariables.JDBCUtil;

public class Bind {

    public static void main(String[] args) throws SQLException {

        ResultSet rs = null;
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            // Get Connection
            conn = JDBCUtil.getConnection();
            // Create statement
            pstmt = conn.prepareStatement("SELECT ? FROM dual");
            System.out.println("Start: " + new Date());
            for (int i = 1; i < 100000; i++) {
                // Use bind variable for subsitution
                pstmt.setString(1, String.valueOf(i));
                // Execute the query
                rs = pstmt.executeQuery();
                //if (i % 1000 == 1)
                  //  System.out.println("Print: " + i);
            }
            System.out.println("End: " + new Date());
           
        } catch (SQLException se) {
            System.out.println("SQL Exception:");
            se.printStackTrace();
        }
        finally {
            // Close the result set, statement and the connection
            JDBCUtil.close(rs,pstmt,conn);
        }
    }
}

Running this application returns the following:

Start: Mon Mar 31 11:36:04 EST 2008
End: Mon Mar 31 11:36:51 EST 2008

It shows the application being executed within less than a minute.

Now the second example does not use bind variables and force the use of Hard Parse on the database.

Example 2: Not using bind variables.

package bindvariables;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class NonBind {

    public static void main(String[] args) throws SQLException {

        ResultSet rs = null;
        Connection conn = null;
        Statement stmt = null;

        try {
            // Get Connection            
            stmt = (JDBCUtil.getConnection()).createStatement();
            System.out.println("Start: " + new Date());
            for (int i = 0; i < 100000; i++) {
                // Execute Statement
                rs = stmt.executeQuery("SELECT " + i + " FROM dual");
                //if (i % 1000 == 1)
                  //  System.out.println("Print: " + i);
            }
            System.out.println("End: " + new Date());           
        } catch (SQLException se) {
            System.out.println("SQL Exception:");
            se.printStackTrace();
        }
        finally {
            // Close the result set, pstatement and the connection
            JDBCUtil.close(rs,stmt,conn);
        }
    }
}

Running this application returns the following:

Start: Mon Mar 31 11:31:45 EST 2008
End: Mon Mar 31 11:35:34 EST 2008

It shows the application being executed over 4 minutes.

As you can see, ensure you use bind variables for performance when coding your application.

My JDBCUtil class method look like:

...

    public static void close(ResultSet resultSet, Statement statement,
                             Connection connection) {
        try {
            if (resultSet != null)
                close(resultSet);
            if (statement != null)
                close(statement);
            if (connection != null)
                close(connection);
        }  catch (Exception e) {
            e.printStackTrace();
        }
    }
   
    public static void close(ResultSet resultSet, PreparedStatement pstatement,
                             Connection connection) {
        try {
            if (resultSet != null)
                close(resultSet);
            if (pstatement != null)
                close(pstatement);
            if (connection != null)
                close(connection);
        }  catch (Exception e) {
            e.printStackTrace();
        }
    }
   
    public static Connection getConnection() throws SQLException {
        String username = "scott";
        String password = "tiger";
        String thinConn =
            "jdbc:oracle:thin:@sracanov-au2.au.oracle.com:1522:orcl";
        DriverManager.registerDriver(new OracleDriver());
        Connection conn =
            DriverManager.getConnection(thinConn, username, password);
        conn.setAutoCommit(false);
        return conn;
    }
}

...