Path: csiph.com!aioe.org!.POSTED!not-for-mail
From: =?UTF-8?Q?Arne_Vajh=c3=b8j?= <arne@vajhoej.dk>
Newsgroups: comp.lang.java.programmer
Subject: Re: Is this the way to work with try with resources
Date: Wed, 9 Aug 2017 20:53:09 -0400
Organization: Aioe.org NNTP Server
Lines: 259
Message-ID: <omgapf$dlf$1@gioia.aioe.org>
References: <87poc6wb9n.fsf@munus.decebal.nl> <omdob4$cif$1@gioia.aioe.org> <omdovg$d4o$1@gioia.aioe.org> <87k22dux5g.fsf@munus.decebal.nl>
NNTP-Posting-Host: PBuS014lhzVyHjLbXmo/ZQ.user.gioia.aioe.org
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@aioe.org
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:52.0) Gecko/20100101 Thunderbird/52.2.1
X-Notice: Filtered by postfilter v. 0.8.2
Content-Language: en-US
Xref: csiph.com comp.lang.java.programmer:37121

On 8/9/2017 1:29 AM, Cecil Westerhof wrote:
> I now have:
>          try (ResultSet rsTotal  = conn.createStatement().executeQuery(COUNT_TOTAL_PROV);
>               ResultSet rsUnused = conn.createStatement().executeQuery(COUNT_UNUSED_PROV);
>               ResultSet rsUsed   = conn.createStatement().executeQuery(COUNT_USED_PROV)) {
> 
> That could go wrong?

Lots.

See previous posts.

>                   If so: is there a way to verify if it goes OK, or
> wrong?

Interesting question.

There are probably many approaches.

But here is one.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBCode {
     public static void main(String[] args) throws SQLException {
         Connection con = 
DriverManager.getConnection("jdbc:mysql://localhost/Test", "root", "");
         con = Reveal.wrap(con); // <----
         Statement stmt = con.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT f1,f2 fROM t1");
         while(rs.next()) {
             System.out.printf("%d %s\n",  rs.getInt(1), rs.getString(2));
         }
         //rs.close();
         //stmt.close();
         PreparedStatement pstmt = con.prepareStatement("SELECT f1,f2 
fROM t1 WHERE f1 > ?");
         pstmt.setInt(1, 2);
         ResultSet prs = pstmt.executeQuery();
         while(prs.next()) {
             System.out.printf("%d %s\n",  prs.getInt(1), prs.getString(2));
         }
         //prs.close();
         //pstmt.close();
         //con.close();
         Reveal.dumpOpen(System.out); // <----
     }
}

Output:

Unclosed: Connection#1
Unclosed: ResultSet#2 from PreparedStatement#1 (SELECT f1,f2 fROM t1 
WHERE f1 > ?) from Connection#1
Unclosed: Statement#1 from Connection#1
Unclosed: PreparedStatement#1 (SELECT f1,f2 fROM t1 WHERE f1 > ?) from 
Connection#1
Unclosed: ResultSet#1 (SELECT f1,f2 fROM t1) from Statement#1 from 
Connection#1

The "magic" sauce:

import java.io.PrintStream;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;

public class Reveal {
     public static interface TrackableResource {
         public String getId() throws SQLException;
     }
     private static Set<TrackableResource> reslst = new 
HashSet<TrackableResource>();
     public static class ResultSetWrapper implements InvocationHandler, 
TrackableResource {
         private static int count = 0;
         private int num;
         private ResultSet rs;
         private String parent;
         private String sql;
         public ResultSetWrapper(ResultSet rs, String parent) {
             this.rs = rs;
             this.parent = parent;
             this.sql = null;
             synchronized(reslst) {
                 count++;
                 num = count;
                 reslst.add(this);
             }
         }
         public ResultSetWrapper(ResultSet rs, String parent, String sql) {
             this.rs = rs;
             this.parent = parent;
             this.sql = sql;
             synchronized(reslst) {
                 count++;
                 num = count;
                 reslst.add(this);
             }
         }
         public Object invoke(Object proxy, Method method, Object[] 
args) throws Throwable {
             if(method.getName().equals("close")) {
                 reslst.remove(this);
                 return method.invoke(rs, args);
             } else {
                 return method.invoke(rs, args);
             }
         }
         public String getId() throws SQLException {
             if(sql == null) {
                 return "ResultSet#" + num + " from " + parent;
             } else {
                 return "ResultSet#" + num + " (" + sql + ") from " + 
parent;
             }
         }
     }
     public static class PreparedStatementWrapper implements 
InvocationHandler, TrackableResource {
         private static int count = 0;
         private int num;
         private Statement stmt;
         private String parent;
         private String sql;
         public PreparedStatementWrapper(Statement stmt, String parent, 
String sql) {
             this.stmt = stmt;
             this.parent = parent;
             this.sql = sql;
             synchronized(reslst) {
                 count++;
                 num = count;
                 reslst.add(this);
             }
         }
         public Object invoke(Object proxy, Method method, Object[] 
args) throws Throwable {
             if(method.getName().equals("close")) {
                 reslst.remove(this);
                 return method.invoke(stmt, args);
             } else if(method.getName().equals("executeQuery")) {
                 return 
(ResultSet)Proxy.newProxyInstance(getClass().getClassLoader(), new 
Class<?>[] { ResultSet.class },
                                                          new 
ResultSetWrapper((ResultSet)method.invoke(stmt, args), getId()));
             } else {
                 return method.invoke(stmt, args);
             }
         }
         public String getId() throws SQLException {
             return "PreparedStatement#" + num + " (" + sql + ") from " 
+ parent;
         }
     }
     public static class StatementWrapper implements InvocationHandler, 
TrackableResource {
         private static int count = 0;
         private int num;
         private Statement stmt;
         private String parent;
         public StatementWrapper(Statement stmt, String parent) {
             this.stmt = stmt;
             this.parent = parent;
             synchronized(reslst) {
                 count++;
                 num = count;
                 reslst.add(this);
             }
         }
         public Object invoke(Object proxy, Method method, Object[] 
args) throws Throwable {
             if(method.getName().equals("close")) {
                 reslst.remove(this);
                 return method.invoke(stmt, args);
             } else if(method.getName().equals("executeQuery")) {
                 return 
(ResultSet)Proxy.newProxyInstance(getClass().getClassLoader(), new 
Class<?>[] { ResultSet.class },
                                                          new 
ResultSetWrapper((ResultSet)method.invoke(stmt, args), getId(), 
(String)args[0]));
             } else {
                 return method.invoke(stmt, args);
             }
         }
         public String getId() throws SQLException {
             return "Statement#" + num + " from " + parent;
         }
     }
     public static class ConnectionWrapper implements InvocationHandler, 
TrackableResource {
         private static int count = 0;
         private int num;
         private Connection con;
         public ConnectionWrapper(Connection con) {
             this.con = con;
             synchronized(reslst) {
                 count++;
                 num = count;
                 reslst.add(this);
             }
         }
         public Object invoke(Object proxy, Method method, Object[] 
args) throws Throwable {
             if(method.getName().equals("close")) {
                 reslst.remove(this);
                 return method.invoke(con, args);
             } else if(method.getName().equals("createStatement")) {
                 return 
(Statement)Proxy.newProxyInstance(con.getClass().getClassLoader(), new 
Class<?>[] { Statement.class },
                                                          new 
StatementWrapper((Statement)method.invoke(con, args), getId()));
             } else if(method.getName().equals("prepareStatement")) {
                 return 
(PreparedStatement)Proxy.newProxyInstance(con.getClass().getClassLoader(), 
new Class<?>[] { PreparedStatement.class },
                                                                  new 
PreparedStatementWrapper((PreparedStatement)method.invoke(con, args), 
getId(), (String)args[0]));
             } else {
                 return method.invoke(con, args);
             }
         }
         public String getId() throws SQLException {
             return "Connection#" + num;
         }
     }
     public static Connection wrap(Connection con) {
         return 
(Connection)Proxy.newProxyInstance(con.getClass().getClassLoader(), new 
Class<?>[] { Connection.class },
                                                   new 
ConnectionWrapper(con));
     }
     public static void dumpOpen(PrintStream ps) throws SQLException {
         for(TrackableResource obj : reslst.toArray(new 
TrackableResource[0])) {
             System.out.println("Unclosed: " + obj.getId());
         }
     }
}

Arne