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 Prepared Statements 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 "Prepared Statements" Watch "Prepared Statements" New topic

Prepared Statements

John Coleman
Ranch Hand

Joined: Jul 24, 2001
Posts: 65
In order to improve access time to an oracle database, I want my application to prepare all its statements ahead of when they may be used. There may be as many as 50 statements to prepare.
From the API I don't understand the full implications.
When do database and JDBC resources get committed, is it only after the query is executed, or is it when the statement is compiled? When I close the statment, what happens when I want to run the query with it again, will it open automatically?
I also want to cache my resultset objects for future reuse, is this feasible, and is it okay to use a CachedRowset object for small result sets?

John Coleman, MSTA<br />Sun Certified Programmer for the Java� 2 Platform<br /><br />Eurobase banking solutions<br /><a href="" target="_blank" rel="nofollow"></a>
Kareem Qureshi
Ranch Hand

Joined: Mar 14, 2002
Posts: 102
What is important between selecting a statement or a preparedstatement is the reusability, that is for different values for the same statement.
Here is what I think happens,
first time when u make a call to the statement or preparedstatement it goes to DBMS and gets compiled and is remains there and when the execute method is called the SQL gets executed and result is set in resultset object.
In case of statement everytime u make a call it is compiled as new but in case of preparedstatement it is already compiled the first time so now you just replace the values and call the execute method and result is a resultset object.
what I have to balance here is the number of times(with different values) for which I want to execute my preparedstatement if the number is very small then it is betterof with statement because it would save on methods I call for setting the parameters.
2. When do database and JDBC resources get committed -
I can do it two ways one it set the autoCommit to true then as soon as the execute statement has executed without any error the values will commited to the database as far as the java program is concerned, it also depends on the underlying database on how it gets commited.
secondly if I set the autoCommit to false then I can commit whenever I am sure of results.
3. When I close the statment, what happens when I want to run the query with it again, will it open automatically?
Whenever you close the preparedstatement essentially you have to create a new preparedstatement. but from my experience before creating a new one, check to see whether your preparedstatement is still lying in the DBMS buffer if it lying there then you dont have to create a new one you can use that.
Please correct me
Hope this helps
I agree. Here's the link:
subject: Prepared Statements
It's not a secret anymore!