title: Connection closing abruptly
body: <p>I am working on a Java program which will get the total row count of all the tables in source end and destination end for data count validation.
  At the moment, I have written code to get the count from Source tables.
  Below is the connection class:</p>
  
  <pre><code>public static Connection getGpConnection() {
          try {
              Class.forName("org.postgresql.Driver");
              if(gpConnection == null) {
                  gpConnection = DriverManager.getConnection("hostname", "username", "password");
                  return gpConnection;
              } else {
              return gpConnection;
              }
          } catch (ClassNotFoundException e) {
              e.printStackTrace();
              return null;
          } catch (SQLException e) {
              e.printStackTrace();
              return null;
          }
      }
  </code></pre>
  
  <p>Method that gets the row count of all the source tables:</p>
  
  <pre><code>public Map&lt;String,Long&gt; getGpTableCount() throws SQLException {
          Map&lt;String, String&gt; gpTableMap = getScopeTableList();
          Set&lt;String&gt; gpTableSet = gpTableMap.keySet();
          gpCountMap = new HashMap&lt;String, Long&gt;();
          Iterator&lt;String&gt; keySetIterator_gpTables = gpTableSet.iterator();
          Connection gpAnalyticsCon = (Connection) DbManager.getGpConnection();
          while(keySetIterator_gpTables.hasNext()) {
              String gpSchemaTableName = keySetIterator_gpTables.next();  // Ex: schema.tablename
              String[] gpparts = gpSchemaTableName.split("\\.");  // schema, tablename
              String gpTable = gpparts[1];
              String gpCountQuery = "select '" + gpTable + "' as TableName, count(*) as Count, source_system_name from " + gpSchemaTableName + " group by source_system_name order by source_system_name limit 30";
              // resultSet(1): TableName, resultSet(2): count, resultSet(3): source_system_name
              try {
                  gpAnalyticsCon.setAutoCommit(false);
                  PreparedStatement gp_pstmnt = gpAnalyticsCon.prepareStatement(gpCountQuery);
                  ResultSet gpCountRs = gp_pstmnt.executeQuery();
                  while(gpCountRs.next()) {
                      gpCountMap.put(gpTable + ": " + gpCountRs.getString(3), gpCountRs.getLong(2));
                  }
              } catch(SQLException e) {
                  e.printStackTrace();
              } catch(Exception e) {
                  e.printStackTrace();
              } finally {
                  if(gpAnalyticsCon != null) {
                  //  gpAnalyticsCon.setAutoCommit(true);
                      gpAnalyticsCon.commit();
                      System.out.println("Closing the connection: Divya");
                      gpAnalyticsCon.close();
                  }
              }
          }
          return gpCountMap;
      }
  </code></pre>
  
  <p>The problem is, after it executes for one table in all the source systems, the connection closes with the following exception:</p>
  
  <pre><code>Tablename: table1 Source System Name: source1, Count: 4
  Tablename: table1 Source System Name: source2, Count: 9
  Tablename: table1 Source System Name: source3, Count: 1
  Tablename: table1 Source System Name: source4, Count: 88
  Tablename: table1 Source System Name: source5, Count: 15
  Tablename: table1 Source System Name: source6, Count: 2
  Tablename: table1 Source System Name: source7, Count: 1521
  Tablename: table1 Source System Name: source8, Count: 1288
  Tablename: table1 Source System Name: source9, Count: 24569
  Exception in thread "main" org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled.
      at org.postgresql.jdbc.PgConnection.commit(PgConnection.java:756)
      at com.recordcount.dao.GpHiveRecords.getGpTableCount(GpHiveRecords.java:82)
      at com.recordcount.entry.StartCount.main(StartCount.java:12)
  </code></pre>
  
  <p>I understand I am using the connection wrongly, but I could identify the mistake.
  Can anyone let me know what is the mistake I am doing here. </p>