5
PreparedStatment ps = null;
public void executeQueries(){
    try{
        ps = conn.prepareStatement(Query1);
        // Execute Query1 here and do the processing.           

        ps = conn.prepareStatement(Query2);
        // Execute Query2 here and do the processing.

        //... more queries
    }catch(){}
    finally{
        ps.close(); // At this point would the caching of queries in DB be lost?
    }
}

In my Application, I call the method executeQueries() frequently.

My question is, If I close the PreparedStatement in the finally block inside the method (that I use frequently), would the database system remove the caching? If YES, can I make a global PreparedStatement for the entire application as there are loads of JAVA CLASSES in my application that query the database.

Thank you!

Update : The question has been marked duplicate but the linked thread does not answer my question at all. AFAIK, the database system stores the executed queries in the cache memory. It also stores their execution plan. This is where PreparedStatement perfoms better than Statement. However, I am not very sure if the information related to the query is removed once the PreparedStatement is closed.

TBag
  • 63
  • 6
  • why do you think it would? – Scary Wombat Jan 21 '19 at 06:29
  • Read the answer by @dogbane which addresses your question. – Tim Biegeleisen Jan 21 '19 at 06:32
  • @TimBiegeleisen Please refer the update in the question. – TBag Jan 21 '19 at 06:48
  • @TBag, I am very interested to know why you think that closing a JDBC will cause a DB (of many different flavours) to flush their internal caches. – Scary Wombat Jan 21 '19 at 06:50
  • @Thilo Maybe you should comment why you reopened this. It is certainly a duplicate. – Tim Biegeleisen Jan 21 '19 at 06:52
  • @ScaryWombat It is not a completely unreasonable question. Some database resources are tied to open client connections, for example cursors. – Thilo Jan 21 '19 at 06:53
  • @TimBiegeleisen It is not a duplicate. The other question asks if it is necessary to close PreparedStatements or if closing the Connection is enough to avoid resource leaks. This new questions asks if there is a benefit to keeping PreparedStatements open as long as possible to avoid the server-side caching being lost (to the extent of even thinking about having a "pool" of these statements kept alive client-side) – Thilo Jan 21 '19 at 06:55
  • @ScaryWombat I haven't deep dived into this topic. Perhaps, there could be an internal command that gets executed by the jdbc that informs the DBS to flush the cache memory. I understand the possibility is remote but still I wanted to be sure. – TBag Jan 21 '19 at 06:55
  • @Thilo If you close the statement, you lose your `ResultSet`, period. So the question is a moot point IMO. – Tim Biegeleisen Jan 21 '19 at 06:58
  • 1
    @Thilo *It is not a completely unreasonable question.* = was not meaning to suggest it was. I was more interested in any documentation which showed that this happens. – Scary Wombat Jan 21 '19 at 07:01
  • 1
    @TimBiegeleisen I cannot speak for MySQL, but in the case of Oracle for example, if you prepare a statement it caches a compiled execution plan for it, and that will survive your database connection (so that another connection for the same user with the same SQL statement will benefit from it). – Thilo Jan 21 '19 at 07:02
  • @Thilo OK...I get your thinking now. I don't know the answer to this. – Tim Biegeleisen Jan 21 '19 at 07:03

1 Answers1

3

Specifically with regard to MySQL, according to

8.10.3 Caching of Prepared Statements and Stored Programs

The server maintains caches for prepared statements and stored programs on a per-session basis. Statements cached for one session are not accessible to other sessions. When a session ends, the server discards any statements cached for it.

So closing a PreparedStatement would not remove the statement(s) from the cache, but closing the Connection presumably would.

... unless the application uses a connection pool, in which case closing the Connection may not necessarily end the database session; it may keep the session open and just return the connection to the pool.

Then there's also the question of whether the statements are actually being PREPAREd on the server. That is controlled by the useServerPrepStmts connection string attribute. IIRC, by default, server-side prepared statements are not enabled.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • which means I can close PreparedStatements without worrying about application's efficiency. Can you please tell me, where exactly to put the close() command, after each query execution or directly in the finally block (the way it is mentioned in the given example)? What would be more suitable considering the statements are being executed frequently? – TBag Jan 22 '19 at 09:50
  • It is usually considered best practice to close ResultSets and Statements as soon as they have finished their work. Since Java 7 we can use a "try with resources" block to ensure that the items get closed. – Gord Thompson Jan 22 '19 at 13:37