postgresql获取java。朗。反思。从数据库中的每个表获取行计数时调用TargetException

Question

我正在开发一个JavaJDBC程序，它将获取数据库中所有表的行数。为此，我提出了以下代码：

// Get the source(gp) count
public Map<String,Long> getGpTableCount() throws SQLException {
    Map<String, String> gpTableMap = getScopeTableList();   // Key: Sourceschema.tablename, value: Destschema.tablename:sourcesystem1,sourcesystem2...sourcesystemn
    Set<String> gpTableSet = gpTableMap.keySet();           // Gets the Source Table names - Schema.Tablename
    gpCountMap = new HashMap<String, Long>();
    Iterator<String> keySetIterator_gpTables = gpTableSet.iterator();
    Connection gpAnalyticsCon = (Connection) DbManager.getGpConnection();
    while(keySetIterator_gpTables.hasNext()) {
        //getting source system names
        String gpTabSchemakey = keySetIterator_gpTables.next();
        String tablesnSSNs = gpTableMap.get(gpTabSchemakey);
        String[] ssnArray = tablesnSSNs.split(":");
        String sourceSystems = ssnArray[1];
        System.out.println(sourceSystems);
        if(sourceSystems.equals("No_SSNs_available") || sourceSystems == "No_SSNs_available") {
            System.out.println("In the if condition");
            gptableCountWithoutSsn = tblCountWithoutSsn(gpTabSchemakey, gpAnalyticsCon);
            gpTabMapWoSsn.put(gpTabSchemakey, gptableCountWithoutSsn);
        } else {
            System.out.println("In the else condition");
            //get the source system names
            String inSourceSystems = "('" + sourceSystems.replace(",", "','") + "')";
            String gpSchemaTableName = gpTabSchemakey;
            String[] gpparts = gpSchemaTableName.split("\\.");  // schema, tablename
            String gpTable = gpparts[1];
            String gpCountQuery = "select '" + gpTable + "' as TableName, count(*) as Count, source_system_name from " + gpSchemaTableName + " where source_system_name in " + inSourceSystems +" group by source_system_name order by source_system_name";
            // resultSet(1): TableName, resultSet(2): count, resultSet(3): source_system_name
            try {
                PreparedStatement gp_pstmnt = gpAnalyticsCon.prepareStatement(gpCountQuery);
                ResultSet gpCountRs = gp_pstmnt.executeQuery();
                while(gpCountRs.next()) {
                    System.out.println("Tablename: " + gpCountRs.getString(1) + " Source System Name: " + gpCountRs.getString(3) + ", Count: " + gpCountRs.getLong(2));
                    System.out.println(" ");
                    ssn = getSsn(gpCountRs.getString(3));
                    gpCountMap.put(gpTable + ": " + gpCountRs.getString(3), gpCountRs.getLong(2));
                }
            } catch(org.postgresql.util.PSQLException e) {
                System.out.println("Table: " + gpTable + " not found for the source system: " + ssn);
            } catch(SQLException e) {
                e.printStackTrace();
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
    }
    gpAnalyticsCon.close();
    return gpCountMap;
}

代码中有两种表

1. 具有源系统的表
2. 没有任何源系统的表

存在一个if-else条件，其中没有sourcesystem名称的表转到“if”，而那些有源系统的表转到“else”条件。 在“if”中，我正在调用另一个方法：tblCountWithoutSsn，该方法写为：

// Method that returns the row count of tables without any source system name
private Long tblCountWithoutSsn(String tableWithoutSsn, Connection con) throws SQLException {
    String[] woSsn = tableWithoutSsn.split("\\.");
    long count=0;
    String tablename = woSsn[1];
    String query = "select '" + tablename + "' as TableName, count(*) as count from " + tableWithoutSsn;    
    System.out.println("Executing table: " + tablename + " which doesn't have any source system name");
    System.out.println("Query for tables with no source_systems: " + query);
    PreparedStatement gpwoSsn_pstmnt = con.prepareStatement(query);
    ResultSet rs = gpwoSsn_pstmnt.executeQuery();
    while(rs.next()) {
        count = rs.getLong(2);
    }
    System.out.println("TableName: " + tablename + ", count: " + count);
    return count;
}

有41个表没有任何源系统。当我执行jar文件时，我可以看到这样的表的数量：

In the if condition
Executing table: table1withoutSSN which doesn't have any source system name
Query for tables with no source_systems: select 'table1withoutSSN' as TableName, count(*) as count from analytics_view.table1withoutSSN
TableName: table1withoutSSN, count: 1764

In the if condition
Executing table: table2withoutSSN which doesn't have any source system name
Query for tables with no source_systems: select 'table2withoutSSN' as TableName, count(*) as count from ge_hr3.table2withoutSSN
TableName: table2withoutSSN, count: 473376

但一旦它到达表：表3没有SN，代码就会给出异常，连接就会关闭。例外情况如下所示：

In the if condition
Executing table: table3WithoutSSN which doesn't have any source system name
Query for tables with no source_systems: select 'table3WithoutSSN' as TableName, count(*) as count from custSchema.table3WithoutSSN
Exception in thread "main" java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.eclipse.jdt.internal.jarinjarloader.JarRsrcLoader.main(JarRsrcLoader.java:58)
Caused by: org.postgresql.util.PSQLException: This connection has been closed.
        at org.postgresql.jdbc.PgConnection.checkClosed(PgConnection.java:803)
        at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1621)
        at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:415)
        at com.recordcount.dao.GpHiveRecords.tblCountWithoutSsn(GpHiveRecords.java:156)
        at com.recordcount.dao.GpHiveRecords.getGpTableCount(GpHiveRecords.java:72)
        at com.recordcount.entry.StartCount.main(StartCount.java:12)
        ... 5 more

例外情况中的第156行是：

PreparedStatement gpwoSsn_pstmnt = con.prepareStatement(query); from the method: `tblCountWithoutSsn`

DBManager类中的连接方法：

private static Connection gpAnalyticsConnection = null; // Creating gp connection
public static Connection getGpConnection() {
    try {
        Class.forName("org.postgresql.Driver");
        if(gpAnalyticsConnection == null) {
            gpAnalyticsConnection = DriverManager.getConnection("hostname", "username", "password");
            return gpAnalyticsConnection;
        } else {
        return gpAnalyticsConnection;
        }
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
        return null;
    } catch (SQLException e) {
        e.printStackTrace();
        return null;
    }
}

有谁能告诉我我在这里犯了什么错误，我该如何纠正