wood burning stoves 2.0*
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 Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Using Prepared Statements" Watch "Using Prepared Statements" New topic
Author

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:

Pseudo-Code:


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!

-Nate
David O'Meara
Rancher

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.

Dave
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!

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