This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Prepared Statements

 
Drew Lane
Ranch Hand
Posts: 296
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3244
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Drew Lane
Ranch Hand
Posts: 296
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 49
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
? 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 ]
 
Dave Vick
Ranch Hand
Posts: 3244
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic