This week's giveaway is in the EJB and other Java EE Technologies forum.
We're giving away four copies of EJB 3 in Action and have Debu Panda, Reza Rahman, Ryan Cuprak, and Michael Remijan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Using Prepared Statements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Using Prepared Statements" Watch "Using Prepared Statements" New topic

Using Prepared Statements

Nate Leech
Ranch Hand

Joined: Feb 07, 2005
Posts: 43
Thanks to Jeanne for helping me with my last question!

I have set up my pools now, and everything is working, but I'm still worried about some of my code. I'm a newbie to setting up databases to work with java code, and I'm having a hard time thinking up the most efficient way to access data in my web application.

I'm using Prepared Statements in a Java class. Here is how it is set up:


Then I'll call the method in another class when I need to get certain data.
My question is:
Should I create the PreparedStatement EVERY TIME I call the method? Or should the PreparedStatement be created only ONCE when the application is executed, then I can just re-set the parameters and execute the query when I need to. If it's the second one, what is a good way to go about doing this?

Thanks for any help, and sorry for any confusion!

David O'Meara

Joined: Mar 06, 2001
Posts: 13459

Either way is fine, but it is usually easier and safer to create a new PreparedStatement each time you run a query.

PreparedStatements get cached in the database against the query string, so as long as the string is the same (in Oracle and possibly other DBs it is case-sensetive) you'll be getting the full benefit of using PreparedStatements.

I typically only re-use them when I'm executing batches of statements.

David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
As well, each Statement is attached to the Connection that created it, so I don't think you can reuse them when using pooling. Many connection pools will cache them behind the scenes for you now so you don't need to bother.

As David said, the time that can pay off is if you are going to execute the statement inside a single transaction (or at least using the same Connection) in a loop.
Nate Leech
Ranch Hand

Joined: Feb 07, 2005
Posts: 43
Thanks! I'm grateful for the help!

I agree. Here's the link:
subject: Using Prepared Statements
Similar Threads
best programming practises of JDBC
java.sql.SQLException: Closed Connection
Use Multiple Insert Statements in Prepared Statements
Statement, Prepared Statement, and CallableStatement
What exactly we mean by Precompiled SQL Statement