title: org.apache.commons.dbcp2.DelegatingPreparedStatement with address: "NULL" is closed
body: <p>I'm trying to implement one Connection pooling in order to have a multithread application (but without a UOW).</p>
  
  <p>I got the same problem every time when I'm trying to call any methods after login.</p>
  
  <p>I got this error: org.apache.commons.dbcp2.DelegatingPreparedStatement with address: "NULL" is closed.</p>
  
  <p>My prepareStatements are stocked in a Map in order to be used again. They are never closed.</p>
  
  <p>My Services class:</p>
  
  <pre><code>public class ServicesImpl implements BackendServices, Services {
  private ThreadLocal&lt;Connection&gt; threadLocal = new ThreadLocal&lt;Connection&gt;();
  private ThreadLocal&lt;String&gt; connectionState = new ThreadLocal&lt;String&gt;();
  
  public static final int TIMEOUT = 0;
  
  private String url = "";
  private String usr = "";
  private String pwd = "";
  
  
  /**
   * Constructeur.
   * 
   * @param url connexion
   * @param usr login
   * @param pwd mdp
   * @throws DALException
   */
  public ServicesImpl(String url, String usr, String pwd) {
    this.url = url;
    this.usr = usr;
    this.pwd = pwd;
    connectionState.set("Empty");
    openDataAccess();
  }
  
  public ServicesImpl() {
  
  }
  
  /**
   * Méthode permettant de bloquer la table en BD.
   * 
   * @throws DALException
   **/
  @Override
  public void beginTransaction() {
    try {
      threadLocal.get().setAutoCommit(false);
    } catch (SQLException e) {
      throw new DALException(e.getMessage());
    }
  }
  
  /**
   * Méthode permettant de valider une transaction en BD.
   * 
   * @throws DALException
   **/
  @Override
  public void commit() {
    try {
      threadLocal.get().commit();
      threadLocal.get().setAutoCommit(true);
    } catch (SQLException e) {
      throw new DALException(e.getMessage());
    }
  }
  
  /**
   * Méthode permettant d'annuler une transaction en BD.
   * 
   * @throws DALException
   **/
  @Override
  public void rollback() {
    try {
      threadLocal.get().rollback();
      threadLocal.get().setAutoCommit(true);
    } catch (SQLException e) {
      throw new DALException(e.getMessage());
    }
  }
  
  /**
   * Méthode permettant d'ouvrir la connexion.
   * 
   * @throws DALException
   * @throws SQLException
   * @throws ClassNotFoundException
   **/
  @Override
  public void openDataAccess() {
    try {
      Class.forName("org.postgresql.Driver");
      try {
        threadLocal.set(DataSource.getInstance().getConnection());
      } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      } catch (PropertyVetoException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
      testConnection();
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      throw new DALException(e.getMessage());
    }
  }
  
  /**
   * Méthode permettant de fermer la connexion.
   * 
   * @throws DALException
   **/
  @Override
  public void closeDataAccess() {
    try {
      testConnection();
      threadLocal.get().setAutoCommit(false);
      threadLocal.get().rollback();
      threadLocal.get().close();
      threadLocal.remove();
    } catch (SQLException e) {
      throw new DALException(e.getMessage());
    }
  }
  
  /**
   * Méthode permettant de tester si la connexion est libre.
   * 
   * @throws DALException
   **/
  private void testConnection() {
  
    try {
      if (!threadLocal.get().isValid(TIMEOUT)) {
        throw new DALException("Connexion non valide !");
      }
    } catch (SQLException e) {
      throw new DALException(e.getMessage());
    }
  
  }
  
  /**
   * Méthode permettant de distribuer des PS sur la connexion.
   * 
   * @param la requete
   * @return PreparedStatement
   * @throws DALException
   **/
  @Override
  public PreparedStatement getPreparedStatement(String requete) {
    try {
      return threadLocal.get().prepareStatement(requete);
    } catch (SQLException e) {
      // throw new DALException(e.getMessage());
      e.printStackTrace();
      return null;
    }
  }
  
  
    public void openConnection(String query) {
      if (connectionState.get() == null) {
        connectionState.set("Empty");
      }
      if (connectionState.get().equals("Empty")) {
        connectionState.set(query);
        try {
          threadLocal.set(DataSource.getInstance().getConnection());
        } catch (SQLException | IOException | PropertyVetoException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
        this.openDataAccess();
      }
    }
  
    @Override
    public void closeConnection(String query) {
      if (connectionState.get().equals(query)) {
        connectionState.set("Empty");
        this.closeDataAccess();
      }
    }
  }
  </code></pre>
  
  <p>There is a exemple of DAO in order to understand how it works:</p>
  
  <pre><code>public class DocumentDaoImpl implements DocumentDao {
  
  BackendServices bs;
  BizFactory fac;
  private ChoixMobiliteDao cmdao;
  private String schema;
  
  private Map&lt;String, PreparedStatement&gt; mapPs = new HashMap&lt;String, PreparedStatement&gt;();
  private PreparedStatement ps;
  
  private enum ColonneDb {
    ID, MOBILITE_ID, CONTRAT_BOURSE, CONVENTION_STAGE, CHARTE_ETUDIANT, PREUVE_TEST_LINGUISTIQUE_BEFORE, DOCUMENT_ENGAGEMENT, ATTESTATION_SEJOUR, RELEVE_NOTE, CERTIFICAT_STAGE, RAPPORT_FINAL, PREUVE_TEST_LINGUISTIQUE_AFTER
  }
  
  private String getDocument;
  private String getDocumentById;
  private String getDocumentsByMobilite;
  private String addDocument;
  private String updateDocument;
  
  /**
   * Constructeur
   * 
   * @param dal
   * @param fac
   * @param ap
   * @param cmdao
   * @param tdao
   */
  public DocumentDaoImpl(Services dal, BizFactory fac, AppContext ap, ChoixMobiliteDao cmdao,
      TypeDao tdao) {
    this.bs = (BackendServices) dal;
    this.schema = ap.getProperty("DocumentDAOSchema");
    this.fac = fac;
    this.cmdao = cmdao;
  
    String getDocument = "SELECT " + ColonneDb.ID + "," + ColonneDb.MOBILITE_ID + ","
        + ColonneDb.CONTRAT_BOURSE + "," + ColonneDb.CONVENTION_STAGE + ","
        + ColonneDb.CHARTE_ETUDIANT + "," + ColonneDb.PREUVE_TEST_LINGUISTIQUE_BEFORE + ","
        + ColonneDb.DOCUMENT_ENGAGEMENT + "," + ColonneDb.ATTESTATION_SEJOUR + ","
        + ColonneDb.RELEVE_NOTE + "," + ColonneDb.CERTIFICAT_STAGE + "," + ColonneDb.RAPPORT_FINAL
        + "," + ColonneDb.PREUVE_TEST_LINGUISTIQUE_AFTER + " FROM " + schema + "";
  
    getDocumentById = getDocument + " WHERE " + ColonneDb.ID + " =?";
    getDocumentsByMobilite = getDocument + " WHERE " + ColonneDb.MOBILITE_ID + " =?";
    addDocument = "INSERT INTO " + schema + " VALUES (DEFAULT,?,?,?,?,?,?,?,?,?,?,?) returning "
        + ColonneDb.ID;
    updateDocument =
        " UPDATE " + schema + " SET " + ColonneDb.MOBILITE_ID + " =? ," + ColonneDb.CONTRAT_BOURSE
            + " =? ," + ColonneDb.CONVENTION_STAGE + " =? ," + ColonneDb.CHARTE_ETUDIANT + " =? ,"
            + ColonneDb.PREUVE_TEST_LINGUISTIQUE_BEFORE + " =? ," + ColonneDb.DOCUMENT_ENGAGEMENT
            + " =? ," + ColonneDb.ATTESTATION_SEJOUR + " =? ," + ColonneDb.RELEVE_NOTE + " =? ,"
            + ColonneDb.CERTIFICAT_STAGE + " =? ," + ColonneDb.RAPPORT_FINAL + " =? ,"
            + ColonneDb.PREUVE_TEST_LINGUISTIQUE_AFTER + " =? WHERE " + ColonneDb.ID + " =?";
  
  }
  
  /**
   * Méthode qui va chercher le DTO en DB et qui l'initialise
   * 
   * @param rs
   * @return un DTO complété
   * @throws SQLException
   */
  public DocumentDto getDocumentDto(final ResultSet rs) throws SQLException {
    DocumentDto result = fac.getDocumentDto();
    result.setId(rs.getInt(1));
    result.setChoixMobiliteDto(cmdao.getChoixMobiliteById(rs.getInt(2)));
    result.setContratBourse(rs.getBoolean(3));
    result.setConventionStage(rs.getBoolean(4));
    result.setCharteEtudiant(rs.getBoolean(5));
    result.setPreuveTestLinguistiqueBefore(rs.getBoolean(6));
    result.setDocumentEngagement(rs.getBoolean(7));
    result.setAttestationSejour(rs.getBoolean(8));
    result.setReleveNote(rs.getBoolean(9));
    result.setCertificatStage(rs.getBoolean(10));
    result.setRapportFinal(rs.getBoolean(11));
    result.setPreuveTestLinguistiqueAfter(rs.getBoolean(12));
  
    return result;
  }
  
  @Override
  public DocumentDto getDocumentById(int id) {
    DocumentDto areturn = null;
    try {
      mapPs.putIfAbsent("getDocumentById", bs.getPreparedStatement(getDocumentById));
      ps = mapPs.get("getDocumentById");
      ps.setInt(1, id);
      try (ResultSet rs = ps.executeQuery()) {
        if (!rs.next()) {
          rs.close();
          return null;
        }
        areturn = this.getDocumentDto(rs);
        return areturn;
      }
    } catch (final SQLException ex) {
      throw new DALException(ex.getMessage());
    }
  }
  
  @Override
  public DocumentDto getDocumentsByMobilite(int id) {
    try {
      DocumentDto tmp = null;
      mapPs.putIfAbsent("getDocumentsByMobilite", bs.getPreparedStatement(getDocumentsByMobilite));
      ps = mapPs.get("getDocumentsByMobilite");
      ps.setInt(1, id);
      try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
          tmp = getDocumentDto(rs);
        }
        return tmp;
      }
    } catch (final SQLException ex) {
      ex.printStackTrace();
      throw new DALException(ex.getMessage());
    }
  }
  
  @Override
  public DocumentDto addDocument(DocumentDto ddao) {
    DocumentDto areturn = null;
    mapPs.putIfAbsent("addDocument", bs.getPreparedStatement(addDocument));
    ps = mapPs.get("addDocument");
  
    try {
      ps.setInt(1, ddao.getMobiliteDto().getId());
      ps.setBoolean(2, ddao.getContratBourse());
      ps.setBoolean(3, ddao.getConventionStage());
      ps.setBoolean(4, ddao.getCharteEtudiant());
      ps.setBoolean(5, ddao.getPreuveTestLinguistiqueBefore());
      ps.setBoolean(6, ddao.getDocumentEngagement());
      ps.setBoolean(7, ddao.getAttestationSejour());
      ps.setBoolean(8, ddao.getReleveNote());
      ps.setBoolean(9, ddao.getCertificatStage());
      ps.setBoolean(10, ddao.getRapportFinal());
      ps.setBoolean(11, ddao.getPreuveTestLinguistiqueAfter());
      try (ResultSet rs = ps.executeQuery()) {
        if (rs.next()) {
          areturn = getDocumentById(rs.getInt(1));
        }
        return areturn;
      }
    } catch (SQLException ex) {
      ex.printStackTrace();
      return null;
    }
  }
  
  @Override
  public void updateDocument(DocumentDto ddto) {
    mapPs.putIfAbsent("updateDocument", bs.getPreparedStatement(updateDocument));
    ps = mapPs.get("updateDocument");
    try {
      ps.setInt(1, ddto.getMobiliteDto().getId());
      ps.setBoolean(2, ddto.getContratBourse());
      ps.setBoolean(3, ddto.getConventionStage());
      ps.setBoolean(4, ddto.getCharteEtudiant());
      ps.setBoolean(5, ddto.getPreuveTestLinguistiqueBefore());
      ps.setBoolean(6, ddto.getDocumentEngagement());
      ps.setBoolean(7, ddto.getAttestationSejour());
      ps.setBoolean(8, ddto.getReleveNote());
      ps.setBoolean(9, ddto.getCertificatStage());
      ps.setBoolean(10, ddto.getRapportFinal());
      ps.setBoolean(11, ddto.getPreuveTestLinguistiqueAfter());
      ps.setInt(12, ddto.getId());
      System.out.println(ps);
      ps.executeUpdate();
    } catch (SQLException ex) {
      throw new DALException(ex.getMessage());
    }
  }
  }
  </code></pre>
  
  <p>Every UCC methods call OpenConnection then CloseConnection.</p>
  
  <p>Could you help me to fix my problem please ?</p>