wood burning stoves 2.0*
The moose likes JDBC and the fly likes Prepared Statements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Prepared Statements" Watch "Prepared Statements" New topic
Author

Prepared Statements

Drew Lane
Ranch Hand

Joined: May 13, 2001
Posts: 296
Could someone please explain when to use a Prepared Statement vs. a Statement in Java?
What are the advantages of using a Prepared Statement?
Thank you,
Drew
Dave Vick
Ranch Hand

Joined: May 10, 2001
Posts: 3244
Drew
A PreparedStatement is sent to the database and precompiled then when it is reused the precompiled statement is used instead of having to recompile it like a normal Statement that is sent and compiled each time it is used.
Using a PreparedStatement lets you use the same base statement and just change certain parameters with in it each time you use it. Say, for example, you need to loop through a list of names and get their birthdays from a database. In a normal Statement you would get the first name then create a statement and execute the query, then do the same for each name in turn. With a PreparedStatement you would create the initial query using ? to indicate where you will place the search criteria (the names in this example). Then each time you have a new name to search on you would just set the parameter and re-execute the statement. It is generally faster to use the PreparedStatement in this case.
hope that answers it for you.


Dave
Drew Lane
Ranch Hand

Joined: May 13, 2001
Posts: 296
Thanks, that is helpful.
I'm still not perfectly clear on the use of the ? in a PreparedStatement, however.
Could you show me the SQL syntax using your example of names and birthdates?
Regards,
Drew
Syam Veerakumar
Ranch Hand

Joined: Sep 20, 2001
Posts: 49
? in PreparedStatement is used to set the parameters, so if you want to get the birthdates of names in database you can give like this,
String sql = "SELECT BIRTHDATE FROM TABLE WHERE NAME = ?";
PreparedStatement pstmt = conn.prepareStament(sql);
pstmt.setString(1,DrewLane);
ResultSet rs = pstmt.exequteQuery();
Here ? takes Drewlane as parameter and retrives the relevant birthdate.
[ July 23, 2002: Message edited by: Syam Veerakumar ]
[ July 23, 2002: Message edited by: Syam Veerakumar ]

A Quitter Never Wins<br />A Winner Never Quits
Dave Vick
Ranch Hand

Joined: May 10, 2001
Posts: 3244
Just to clarify...
The DrewLane variable would be a String like this:
String DrewLane = "Drew Lane";
Then, as Syam showed, you're just filling in the missing parameter (the ?) with the value contained in the variable. So that when the query is actualy executed it executes as if it looked like this:
SELECT BIRTHDATE FROM TABLE WHERE NAME = "Drew Lane"
One of the nicest things about PReparedStatements is that you dont have to worry about the enclosing quotes for Strings nor do you ahve to worry about escaping quates or other special characters in the string your using. So if you have a String that might contain special characters that you need to insert into a database you can use a PreparedStatement to insert it without having to escape anything.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Prepared Statements