Using Prepared Statements

[Nate Leech]

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:

String getName(parameters){
     open connection;
 
     String sqlStuff = "Select * blah blah blah";
     create PreparedStatement(sqlStuff);
     set parameters;
     execute PreparedStatement;
 
     close connection;
}

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]

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]

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]

Thanks! I'm grateful for the help!

-Nate