org.apache.commons.dbcp2.DelegatingPreparedStatement with address: "NULL" is closed

Question

I'm trying to implement one Connection pooling in order to have a multithread application (but without a UOW).

I got the same problem every time when I'm trying to call any methods after login.

I got this error: org.apache.commons.dbcp2.DelegatingPreparedStatement with address: "NULL" is closed.

My prepareStatements are stocked in a Map in order to be used again. They are never closed.

My Services class:

public class ServicesImpl implements BackendServices, Services {
private ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();
private ThreadLocal<String> connectionState = new ThreadLocal<String>();

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();
    }
  }
}

There is a exemple of DAO in order to understand how it works:

public class DocumentDaoImpl implements DocumentDao {

BackendServices bs;
BizFactory fac;
private ChoixMobiliteDao cmdao;
private String schema;

private Map<String, PreparedStatement> mapPs = new HashMap<String, PreparedStatement>();
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());
  }
}
}

Every UCC methods call OpenConnection then CloseConnection.

Could you help me to fix my problem please ?

Answer 1

When you close a connection, it is returned to the pool and any statements created from it are closed (or at least the proxy given to you is closed). This behavior is mandated by the JDBC specification.

If you want to have statement pooling, then you should use the functionality provided by the connection pool, not try to roll your own.

For DBCP see BasicDataSource Configuration Parameters, specifically settings poolPreparedStatements and maxOpenPreparedStatements.