org.apache.commons.dbcp2.DemissionatingPreparedStatement 地址："NULL"已关闭

Question

我正在尝试实现一个连接池，以便拥有一个多线程应用程序（但没有UOW）。

每次登录后尝试调用任何方法时，我都会遇到同样的问题。

我收到以下错误：地址为"NULL"的org.apache.commons.dbcp2.DelegatingPreparedStatement已关闭。

我准备的对账单储存在地图中，以便再次使用。它们从不关闭。

我的服务类别：

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

有一个DAO的例子来了解它是如何工作的：

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

每个UCC方法都先调用OpenConnection，然后调用CloseConnection。

你能帮我解决一下我的问题吗？

Answer 1

当您关闭一个连接时，它会返回到池中，并关闭从中创建的任何语句（或者至少关闭给您的代理）。这种行为是JDBC规范强制要求的。

如果您想要语句池，那么您应该使用连接池提供的功能，而不是尝试自己滚动。

有关DBCP，请参见BasicDataSource配置参数，特别是设置poolPreparedStatements和maxOpenPreparedStatements。