Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using Prepared Statements

 
Nate Leech
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks! I'm grateful for the help!

-Nate
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic