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