For SQL statements that are executed repeatedly, using a PreparedStatement object would almost always be faster than using a Statement object. This is because creating a PreparedStatement object by explicitly giving the SQL statement causes the statement to be precompiled within the database immediately. Thus, when the PreparedStatement is later executed, the DBMS does not have to recompile the SQL statement and prepared an execution plan - it simply runs the statement.
Typically, PreparedStatement objects are used for SQL statements that take parameters. However, they can also be used with repeatedly executed SQL statements that do not accept parameters.
Prepared Statements aren't actually compiled, but they are bound by the
JDBC driver. Depending on the driver, Prepared Statements can be a lot faster - if you re-use them. Some drivers bind the columns you request in the SQL statement. When you execute Connection.prepareStatement(), all the columns bindings take place, so the binding overhead does not occur each time you run the Prepared Statement.
Example:
import java.sql.*;
import java.util.Properties;
public class JDBC_PreparedStatement {
public static void main(java.lang.String[] args)
throws Exception
{
String[] values = {"VAL1", "VAL2", "VAL3"};
Connection conn = null;
ResultSet rs = null;
try {
// Register JDBC Driver and connect to DB.
Class.forName("oracle.jdbc.driver.OracleDriver");
c = DriverManager.getConnection("jdbc
racle:thin:@150.180.120.200:1500:myDB");
// Example using simple Statement objects:
Statement s = conn.createStatement();
// Each statement gets prepared and executed each time.
for (int i = 0; i < values.length; i++) {
rs = s.executeQuery("SELECT COUNT(*) FROM TABLE1" +
" WHERE TABLE_OWNER = '" + values[i] + "'");
if (rs.next())
System.out.println(values[i] + " owns " + rs.getInt(1) + " tables.");
else
System.out.println(values[i] + " doesn't own any tables on the system.");
}
// Same example using a PreparedStatement
// The statement gets prepared at the time that it is constructed.
PreparedStatement ps = conn.prepareStatement("SELECT COUNT(*) FROM TABLE1S" +
" WHERE TABLE_OWNER = ?");
// Only the execution phase is done later. The prepare work is reused.
for (int i = 0; i < values.length; i++) {
ps.setString(1, values[i]);
rs = ps.executeQuery();
if (rs.next())
System.out.println(values[i] + " owns " + rs.getInt(1) + " tables.");
else
System.out.println(values[i] + " doesn't own any tables on the system.");
}
} finally {
if (conn != null)
conn.close(); // Connection close will close the statements as well.
}
}
}
Good Luck.