PreparedStatement vs Statement
There seems to be a lot of confusion about when to actually use a PreparedStatement vs a Statement object.
PreparedStatement objects are best used when you will be executing a large number of identical queries with different values. If you are going to be
looping through code and adding in or updating rows in bulk, go for the PreparedStatement, otherwise, Statement is your answer.
Too many times I see this:
| Code: | PreparedStatement pstmt = conn.prepareStatement("insert into table (column2) values ("My Value") where id = 1000");
pstmt.execute();
|
or even this:
| Code: | PreparedStatement pstmt = conn.prepareStatement("insert into table (column2) values (?) where id = ?");
pstmt.setString("My Value");
pstmt.setInt(1000);
pstmt.execute();
|
OK, the first one is blatantly wrong but what's wrong with the second one you ask? It's being executed every time you run through the code. Why is
it bad to do it this way? You are DOUBLING your number of calls to the database.
When you call conn.prepareStatement(String) you are sending a message to the database to pre-compile the sql string. You then send another message to
the database when you call execute() after you set the variables. The correct way of using prepared statement would be in a situation like this:
| Code: | PreparedStatement pstmt = conn.prepareStatement("insert into table (column2) values (?) where id = ?");
while (true) // some kind of terminating loop here not just while true
{
pstmt.setString(valueVar);
pstmt.setInt(intVar);
pstmt.execute();
}
|
However, there is a large speed difference with the first 50-60 records being sent. If you are doing less that 50-60 iterations of this query it is
still faster to use Statements rather than a PreparedStatement. However, it is twice as fast to use PreparedStatements once you have iterated through
it about 1000 times.
Statements are good for one time insert/updates and also for sending in batches of several different inserts/updates. Example:
| Code: | String sql1 = "insert into...";
String sql2 = "update table set ...";
Statement st = conn.createStatement();
st.addBatch(sql1);
st.addBatch(sql2);
int[] returnRows = st.executeBatch();
|
Hopefully this will help you optimize any JDBC communication you have to do with your database. Just make smart decisions about when to use each type
of Statement and if unsure, test it and see which way will work faster for you.
Ben Steiner
Software Engineer
|
![[*]](./images/corporate/default_icon.gif){kind=icon}
posted on 26-1-2005 at 04:00 PM
