title: How do I use SELECT LAST_INSERT_ID() without closing the connection
body: <p>I have table whose only column is an auto-incrementing primary key.  I want to insert a new row into this table, and then get the ID generated by this insert.  </p>
  
  <p>Reading through StackOverflow, LAST_INSERT_ID() appears to be the standard solution.  The caveat given is that it will return the last generated ID per connection.  I wrote a new method to INSERT and then SELECT, all without closing the connection.  Then I started getting "Operation not allowed after ResultSet closed" errors. Reading up on that in StackOverflow leads me to think I should not be preparing multiple statements on the same connection.  </p>
  
  <p>How should I be implementing this insert and select model?  Am I misunderstanding what counts as the database connection concerning LAST_INSERT_ID()?</p>
  
  <p>relevant excerpts:</p>
  
  <pre><code>public class ConceptPortal {   
  
      public static ConceptID createNewConceptID() throws Exception {
          ConceptID returnConceptID = null;
          StringBuilder stmt = new StringBuilder("INSERT INTO " + MysqlDefs.CONCEPT_TABLE + " VALUES ()");
          ArrayList&lt;Object&gt; parameters = new ArrayList&lt;Object&gt;();
          StringBuilder stmt2 = new StringBuilder("SELECT LAST_INSERT_ID()");     
          ResultSet resultSet = MysqlPortal.specialExecuteStatement(stmt.toString(), parameters, stmt2.toString());
          if (resultSet.next()) {
              returnConceptID = new ConceptID(resultSet.getLong(1));           
              resultSet.getStatement().close();
          } else {
              throw new Exception("Wait a minute...I can't create new concepts?!!");
          }
          return returnConceptID;
      }
  }
  
  
  
  public class MysqlPortal {
      private static ConnPool connPool = null;
  
      public static void init(String database, boolean startFromScratch) throws Exception {
          databaseName = database;
          connPool = new ConnPool(database);
      }
  
      /*
       * This one doesn't close right away,
       * so that we can run LAST_INSERT_ID() on it
       */
      static ResultSet specialExecuteStatement(String sqlCommand, ArrayList&lt;Object&gt; parameters, String followUpSelect) throws Exception {
          ResultSet resultSet = null;
          if (parameters == null) {
              System.out.println(sqlCommand + " :: [null]");          
          } else {
              System.out.println(sqlCommand + " :: " + Arrays.deepToString(parameters.toArray()) );
          }
  
          ConnectionImpl connInstance = connPool.aquire();
          try {
              Connection myConn = connInstance.getConnection();
              try (PreparedStatement pstmt = myConn.prepareStatement(sqlCommand)) {
                  if (parameters != null) {
                      int i = 1;
                      for (Object parameter : parameters) {
                          pstmt.setObject(i, parameter);
                          i++;
                      }
                  }
                  pstmt.execute();
              }
              try (PreparedStatement pstmt2 = myConn.prepareStatement(followUpSelect)) {
                  resultSet = pstmt2.executeQuery();
              }
  
              connPool.release(connInstance);
              return resultSet;
          } catch (SQLException e) {
              System.out.println(sqlCommand);
              throw e;
          }
      }
  }
  </code></pre>