This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes question regarding PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "question regarding PreparedStatement" Watch "question regarding PreparedStatement" New topic
Author

question regarding PreparedStatement

Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
Hi all,
I wonder what is the difference between the following 2 codes:





Both return the same result but I guess (and correct me if I�m wrong) that it is better to use the prepared statement (efficiency�but how, why?)

Thanks for any thoughts
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

When you pass a query into a database, it is just a string, the databse then does its own compilation to turn it into the DB query, performs a bunch of optimisation stuff and other database magic, then runs it.

When the Driver and database both support PreparedStatements, it links the query to the compiled and optimised bit in the database so that it doesn't need to happen all the time. I point this out since some Drivers mimic PS behaviour because it isn't supported in all DBs.

It is actually true that PreparedStatements are better most of the time, but it isn't really worth worrying about the other parts, it isn't enough of a difference to stop using them. This is because the database cannot fully optimise the query due to the unbound parts. I have seen stats showing that a single query with no variables can be more efficient, but this effect disappears as soon as you need to run the same query with a different value. Like I said, nothing to worry about.
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 14074
    
  16

The first piece of code, where you construct an SQL statement by concatenating strings together, may introduce a security leak in your program - it may make your program vulnerable to SQL injection.

See this thread: http://www.coderanch.com/t/302647/JDBC/java/construct-SQL-statement


Java Beginners FAQ - JavaRanch SCJP FAQ - The Java Tutorial - Java SE 7 API documentation
Scala Notes - My blog about Scala
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: question regarding PreparedStatement
 
Similar Threads
help with application variables to store the list of names code
SELECT * FROM TESTTABLE WHERE ID IN (?)
Select max id and calling method from another class
ORA-01461: can bind a LONG value only for insert into a LONG column
Accessing One table with multiple Threads and Inserting Values