aspose file tools*
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
JavaRanch » Java Forums » Java » Servlets
Bookmark "prepare statement or just statement" Watch "prepare statement or just statement" New topic
Author

prepare statement or just statement

anand chawla
Ranch Hand

Joined: Oct 19, 2000
Posts: 91
Hi,
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: 4339
    
    2

anand,
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 http://javaguy.yi.org 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).]


SCJP
Visit my download page
David Cole
Greenhorn

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.
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.
anand chawla
Ranch Hand

Joined: Oct 19, 2000
Posts: 91
Thanks
That was very intersting david.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: prepare statement or just statement
 
Similar Threads
Regarding Prepartion of Scja
OpenSource SCJA Study Guide :D
Preparation Help for SCJP2
ejb3 EBOOKS
new to SCBCD