File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Servlets and the fly likes prepare statement or just statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Java » Servlets
Bookmark "prepare statement or just statement" Watch "prepare statement or just statement" New topic

prepare statement or just statement

anand chawla
Ranch Hand

Joined: Oct 19, 2000
Posts: 91
I have had always this curiousity that why people use prepare statement..can anyone tell me.
is it bcoz during large transactions it reduces the overhead.
can anyone give me example distinguishing between them.
Please help.
Randall Twede
Ranch Hand

Joined: Oct 21, 2000
Posts: 4351

I cant remember very well because it was a while ago now. But I had a problem and using PreparedStatement worked but Statement couldnt do it. If I remember correctly. I think you have seen my code. It is in CheckoutServlet that I had to use PreparedStatement. Sorry I cant remember more right now.
Oh, I used both Statement and PreparedStatement in the same servlet.
[This message has been edited by Randall Twede (edited March 07, 2001).]

Visit my download page
David Cole

Joined: Mar 07, 2001
Posts: 5
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.
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.
c = DriverManager.getConnection("jdbc racle:thin:@");

// 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 (
System.out.println(values[i] + " owns " + rs.getInt(1) + " tables.");
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" +

// 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 (
System.out.println(values[i] + " owns " + rs.getInt(1) + " tables.");
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.
anand chawla
Ranch Hand

Joined: Oct 19, 2000
Posts: 91
That was very intersting david.
I agree. Here's the link:
subject: prepare statement or just statement
It's not a secret anymore!