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

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

我收到此错误:地址为“ NULL”的org.apache.commons.dbcp2.DelegatingPreparedStatement已关闭。

我的prepareStatement存放在Map中以便再次使用。他们永远不会关闭。

我的服务班:

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。

你能帮我解决我的问题吗?

最佳答案

关闭连接时,该连接将返回到池中,并且从该连接创建的所有语句都将关闭(或者至少关闭了提供给您的代理)。 JDBC规范要求这种行为。

如果要使用语句池,则应使用连接池提供的功能,而不要尝试扮演自己的角色。

对于DBCP,请参见BasicDataSource Configuration Parameters,特别是设置poolPreparedStatementsmaxOpenPreparedStatements

09-10 06:26
查看更多