Connection, Statement, PreparedStatement, resultset note the memo.

Record a few test results first

Test environment

Mysql:mysql-connector-java-5.1.29.jar

java:1.7.0_67

Test A

Code:

        PreparedStatement pstmt = conn                 .preparestatement ("Select  id from world.city limit 3 ");         Resultset rs = pstmt.executequery ();        while  (Rs.next ())  {            system.out.println ( Rs.getstring (1));        }         system.out.println ();        preparedstatement pstmt2  = conn                . Preparestatement ("select countrycode from world.city limit 3");         reSultset rs2 = pstmt2.executequery ();        while  (Rs2.next ())  {            system.out.println ( Rs2.getstring (1));        }         system.out.println (pstmt.isclosed ());         system.out.println ( Rs.isclosed ());         system.out.println (pstmt2.isClosed ());         system.out.println (rs2.isclosed ());         system.out.println (conn.isclosed ());         System.out.println ();         conn.close ();         system.out.println (pstmt.isclosed ());         System.out.println (rs.isclosed());         system.out.println (pstmt2.isclosed ());         system.out.println (rs2.isclosed ());         system.out.println (conn.isclosed ());         system.out.println () ;

Results:

Falsefalsefalsefalsefalsetruetruetruetruetrue

Conclusion:

The associated resultset and PreparedStatement are closed when the connection is closed.

Test b

Code:

        PreparedStatement pstmt = null;         ResultSet rs1 = null;         ResultSet rs2 = null;                 Statement st = null;         resultset rs3 = null;        resultset  rs4 = null;                 try {            pstmt  = conn.preparestatement (                      "select name from world.city where  Name like ? limit 3 ");             pstmt.setstring (1,  "a%");             rs1 = pstmt.executequery ();             rs2 = null;             while  (Rs1.next ())  {                 system.out.println (rs1.getString (1));                  Pstmt.setstring (1,  "b%");                 rs2 = pstmt.executequery ();                 while  (Rs2.next ())  {             &nbSp;       system.out.println (rs2.getstring (1));                 }             }        }         catch  (exception e)  {             e.printstacktrace ();        }         try {             st = conn.createstatement ();             rs3 = st.executequery (                      "Select name from world.city where  name like  ' A% '  limit 3 ");             rs4 =  null;            while  (Rs3.next ())  {                 System.out.println (rs3.getstring (1));                 rs4 = st.executequery (                          "Select name  from world.city where name like  ' b% '  limit 3 ");                 while  (Rs4.next ())  {                      system.out.println (Rs4.getString (1));                 }             }         }        catch  (exception e)  {             e.printstacktrace ();         }        system.out.println (pstmt.isClosed ());         system.out.println (rs1.isclosed ());         system.out.println (rs2.isclosed ());         System.out.println (st.isclosed ());         system.out.println ( Rs3.isclosed ());         system.out.println (rs4.isClosed ());        &nbsP System.out.println (conn.isclosed ());         system.out.println ();

Results:

Java.sql.SQLException:Operation not allowed after ResultSet closedjava.sql.SQLException:Operation not allowed after Res Ultset closed Falsetruefalsefalsetruefalsefalse

Conclusion:

Only one active resultset can be associated with the same statement or PreparedStatement object.

Test C

Code:

        PreparedStatement pstmt = conn                 .preparestatement ("Select  id from world.city limit 3 ");         Resultset rs = pstmt.executequery ();        while  (Rs.next ())  {            system.out.println ( Rs.getstring (1));        }         system.out.println ();                 PreparedStatement pstmt_old = pstmt;         pstmt = conn.preparestatement ("Select name from world.city where  name like ? limit&nBsp;3 ");         pstmt.setstring (1, " a% ");         resultset rs2 = pstmt.executequery ();         while  (Rs2.next ())  {             system.out.println (rs2.getstring (1));        }         system.out.println (pstmt.isclosed ());         system.out.println (rs.isclosed ());         System.out.println (pstmt_old.isclosed ());         system.out.println ( Rs2.isclosed ());         system.out.println (conn.isClosed ());         system.out.println ();               &nBsp; pstmt.close ();         system.out.println (pstmt.isClosed ());         system.out.println (rs.isclosed ());         system.out.println (pstmt_old.isclosed ());         system.out.println (rs2.isclosed ());         system.out.println ( Conn.isclosed ());         system.out.println ();         conn.close ();         system.out.println ( Pstmt.isclosed ());         system.out.println (rs.isClosed ());         system.out.println (pstmt_old.isclosed ());         system.out.println (rs2.isclosed ());         System.out.println (conn.isclosed ());        system.out.println (); 

Results:

Falsefalsefalsefalsefalsetruefalsefalsetruefalsetruetruetruetruetrue

Conclusion:

If the PreparedStatement object is not closed and the other preparedstatement are associated, the old PreparedStatement and resultset are not closed until connection is closed. In the case of connection pooling, memory leaks can occur.

A better approach:

1. The use of PreparedStatement instead of statement, on the one hand, several times to improve the efficiency of the execution of statements, on the other hand, prevent splicing SQL may cause injection attacks

2. Be careful to turn off PreparedStatement, especially if you are using connection pooling. Java7 above suggest using Try-with-resource statement, concise. Java7 the following attention to Try-catch when closed, and as far as possible according to ResultSet, PreparedStatement, connection in order to close.

3. Do not pass resultset, use rowset.
   

This article from "Do not accumulate kuibu, not even thousands of Miles" blog, please be sure to keep this source http://wangzhichao.blog.51cto.com/2643325/1720656

Connection, Statement, PreparedStatement, resultset note the memo.