In this three part series of posts we will look at fetching data from an Oracle Database using Java and JDBC. In this second post we look at the PreparedStatement interface and how to process simple queries.
Note: The example programs presented in this series of post make use of the HR demonstration schema.
The PreparedStatement Interface extends the Statement Interface and provides the ability execute SQL statements with or without input parameters (bind variables). Like the Statement, the PreparedStatement executes within the context of a Connection.
You may wonder why there is a need for another Statement interface. Consider the case of an Employee lookup screen that will display employee information based upon the Employee ID entered by the user. Given what we know about the Statement interface we will need to “build” the SQL dynamically to append the new literal value in response to user input.
OracleConnection ocon; Statement stmt; Resultset rset; String sqlStr; ... sqlStr = "select first_name, last_name, hire_date from employees where employee_id = " + userInput; stmt = ocon.createStatement(); rset = stmt.executeQuery(sqlStr); ...
Processing SQL in this manner is inefficient and causes unnecessary CPU utilization and contention due to hard parsing. Not to mention possible application bugs and security issues. What would happened if the user input was “102 OR 103”?
Using a PreparedStatement literal values can be changed and SQL can be re-used by Oracle and not require a hard parse for each execution. Below is a re-write of the example presented earlier to use the PreparedStatement.
OracleConnection ocon; PreparedStatement pstmt; Resultset rset; String sqlStr; ... sqlStr = "select first_name, last_name, hire_date from employees where employee_id = ?”; pstmt = ocon.prepareStatement(sqlStr); pstmt.setInt(1,userInput); rset = pstmt.executeQuery(); ...
One of the first things to note is the string that makes up the SQL statement. The “?” is a place holder for the value to be supplied before execution. The Connection.prepareStatement() method is used to create the PreparedStatement. Unlike the Statement object the SQL is needed at the time of creation.
Next we bind the value to the SQL statement using the setInt method. The first parameter indicates the placeholder to be set and the second is the value to be set. There are setXXX methods for all of the common data types.
And finally just like the Statement object we execute the SQL using the executeQuery() method.
At the end of the post Introduction to Fetching Data from an Oracle Database using Java and JDBC part 1 a program was presented showing how executing multiple SQL statements that differ only in literal values resulted in multiple hard parses. Below is a re-write of that program that uses a PreparedStatement.
import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class PreparedStmtExmpl {
public static void main(String[] args) {
try {
// Create the Oracle DataSource and set the connection URL
OracleDataSource ods = new OracleDataSource();
ods.setURL(/java/introduction-to-fetching-data-from-an-oracle-database-using-java-and-jdbc-part-2/"jdbc:oracle:thin:hr/hr@ora1:1521/orcl");
// connect to the database and turn off auto commit
OracleConnection ocon = (OracleConnection)ods.getConnection();
ocon.setAutoCommit(false);
// create the prepared statement
PreparedStatement pstmt = ocon.prepareStatement("select employee_id, first_name, last_name, hire_date from employees where employee_id = ?");
ResultSet rset;
for(int i = 100; i <= 206; i++) {
// bind the employee_id value
pstmt.setInt(1,i);
//execute the new query
rset = pstmt.executeQuery();
// process the result set
while(rset.next()) {
System.out.println(rset.getInt("EMPLOYEE_ID") + ", " +
rset.getString("FIRST_NAME") + ", " +
rset.getString("LAST_NAME") + ", " +
rset.getDate("HIRE_DATE"));
}
}
} catch(SQLException e) {
System.out.println(e.getMessage());
}
}
}
After running the program above the database shows that the query was parsed one time and executed 107 times.
SQL> select executions, parse_calls
2 from v$sql
3 where sql_text like 'select employee_id, first_name, last_name%';
EXECUTIONS PARSE_CALLS
---------- -----------
107 1
SQL>
The PreparedStatement example resulted in 106 few hard parses than the same example using the Statement. But how do those savings translate to the user experience?
Below is the Statement based program again with a few modifications. A new table was created in the HR schema that is simply a copy of DBA_OBJECTS with a unique index on the OBJECT_ID column. The query has been changed to use this new table. Note that the program does not print out the results of the query. Two variables startTime and elapsedTime are used to capture the execution of the time of the loop.
import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.concurrent.TimeUnit;
public class StmtSpeedTest {
public static void main(String[] args) {
try {
// create the Oracle DataSource and set the URL
OracleDataSource ods = new OracleDataSource();
ods.setURL(/java/introduction-to-fetching-data-from-an-oracle-database-using-java-and-jdbc-part-2/"jdbc:oracle:thin:hr/hr@ora1:1521/orcl");
// connect to the database and turn off auto commit
OracleConnection ocon = (OracleConnection)ods.getConnection();
ocon.setAutoCommit(false);
// create the statement
Statement stmt = ocon.createStatement();;
ResultSet rset;
String sqlStr;
long startTime = System.currentTimeMillis();
for(int i = 1; i <= 78874; i++) {
// build the query
sqlStr = "select object_name from speedtest where object_id = " + i;
// execute the new query not going to process the result set.
rset = stmt.executeQuery(sqlStr);
}
long elapsedTime = System.currentTimeMillis() - startTime;
System.out.println(String.format("Loop executed in %d mins, %d sec",
TimeUnit.MILLISECONDS.toMinutes(elapsedTime),
TimeUnit.MILLISECONDS.toSeconds(elapsedTime) -
TimeUnit.MINUTES.toSeconds(TimeUnit.MILLISECONDS.toMinutes(elapsedTime))));
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}
The loop in the above program executed 0 minutes and 43 seconds.
The same modifications were made to the PreparedStatement example below.
import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.concurrent.TimeUnit;
public class PstmtSpeedTest {
public static void main(String[] args) {
try {
// Create the Oracle DataSource and set the connection URL
OracleDataSource ods = new OracleDataSource();
ods.setURL(/java/introduction-to-fetching-data-from-an-oracle-database-using-java-and-jdbc-part-2/"jdbc:oracle:thin:hr/hr@ora1:1521/orcl");
// connect to the database and turn off auto commit
OracleConnection ocon = (OracleConnection)ods.getConnection();
ocon.setAutoCommit(false);
// create the prepared statement
PreparedStatement pstmt = ocon.prepareStatement("select object_name from speedtest where object_id = ?");
ResultSet rset;
long startTime = System.currentTimeMillis();
for(int i = 1; i <= 78874; i++) {
// bind the employee_id value
pstmt.setInt(1,i);
// execute the new query not going to process the result set.
rset = pstmt.executeQuery();
}
long elapsedTime = System.currentTimeMillis() - startTime;
System.out.println(String.format("Loop executed in %d mins, %d sec",
TimeUnit.MILLISECONDS.toMinutes(elapsedTime),
TimeUnit.MILLISECONDS.toSeconds(elapsedTime) -
TimeUnit.MINUTES.toSeconds(TimeUnit.MILLISECONDS.toMinutes(elapsedTime))));
} catch(SQLException e) {
System.out.println(e.getMessage());
}
}
}
The loop in the program above executed in 0 minutes and 9 seconds. Both programs executed 78,874 SQL statements but one hard parsed every statement and one performed one hard parse. By parsing only one time we saved 33 seconds.