Do I need different objects of PreparedStatement for each different SQL Query?

Question

I am working on a java application and using Derby Database.

I have one object of PreparedStatement named pstmt.

1) Can I use same object (here pstmt) for every SELECT Statements like these and others Statements (Would it be an efficient way) ?

For Ex:

pstmt = conn.prepareStatement("SELECT NAME FROM TABLE1 ORDER BY NAME");
pstmt = conn.prepareStatement("SELECT * FROM TABLE1 ORDER BY NAME");
pstmt = conn.prepareStatement("SELECT * FROM TABLE1 WHERE .....");

or I have to create different objects per different SQL Statement like below..

pstmt1 = conn.prepareStatement("SELECT NAME FROM TABLE1 ORDER BY NAME");
pstmt2 = conn.prepareStatement("SELECT * FROM TABLE1 ORDER BY NAME");
pstmt3 = conn.prepareStatement("SELECT * FROM TABLE1 WHERE .....");

2) And what about other Statements like CREATE, INSERT, UPDATE etc. Can I use a common object for each type of operation (like one object for every SELECT Statements, one for every INSERT, one for UPDATE Statements and so on..)

I know the advantages of it but I am confused with efficient usage of it.

Answer 1

When you call conn.prepareStatement you are creating a new object (assuming the Connection object isn't some sort of caching factory pattern, which is probably a safe assumption to make). But from your question you aren't asking about using the same object, but reusing the same variable, pstmt. There's nothing wrong with doing that, just make sure to close() the previous one, otherwise you'd be leaking cursors:

pstmt = conn.prepareStatement("SELECT NAME FROM TABLE1 ORDER BY NAME");
// Missing pstmt.close();
pstmt = conn.prepareStatement("SELECT * FROM TABLE1 ORDER BY NAME");

The second assingment loses the reference to the previous pstmt, without close() being called on it. Hopefully, it will be closed when the garbage collector picks it up (depending on the database-specific implementation of the JDBC driver). But even if it does, you're leaving a dangling cursor open until the garbage collector finally gets round to it.

Answer 2

It is perfectly fine to reuse same PreparedStatement. I would rather say that it is more efficient way than creating multiple PreparedStatement objects. For further details, you can go through this link http://www.jooq.org/doc/3.2/manual/sql-execution/reusing-statements/#N129B1

About the second doubt, instead of using different PrepareStatement objects for CREATE, INSERT and UPDATE, (if the use doesn't overlap) you can even reuse single PreparedStatement object for all the statements.