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 PLEASE REPLY TO JSC@ELOI.NILDRAM.CO.UK AS I AM AWAY FROM MY USUAL PC.
John Coleman, MSTA<br />Sun Certified Programmer for the Java� 2 Platform<br />email@example.com<br />Eurobase banking solutions<br /><a href="http://www.eurobase-international.com/banking" target="_blank" rel="nofollow">http://www.eurobase-international.com/banking</a>
John, 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 Kareem