File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes question regarding PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "question regarding PreparedStatement" Watch "question regarding PreparedStatement" New topic

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

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: 15084

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:

Java Beginners FAQ - JavaRanch SCJP FAQ - The Java Tutorial - Java SE 8 API documentation
I agree. Here's the link:
subject: question regarding PreparedStatement
It's not a secret anymore!