I would like your help with the connection below, when I close I have the following error, am I doing it correctly? with regard to opening and closing the connection?
My pool
public class MysqlConnectionPool {
private final DataSource dataSource;
public MysqlConnectionPool() {
MysqlConnectionPoolDataSource pool = new MysqlConnectionPoolDataSource();
pool.setUrl("jdbc:mysql://*********.sa-east-1.rds.amazonaws.com:3306/*********?autoReconnect=true&useSSL=false");
pool.setUser("*********");
pool.setPassword("*********");
this.dataSource = pool;
}
public Connection getConnection() throws SQLException {
Connection connection = dataSource.getConnection();
return connection;
}}
Example of a DAO I have, all others follow the same pattern
public class UsuarioDAO {
ExceptionHandling capturaException = new ExceptionHandling();
GeradorURL geradorURL = new GeradorURL();
MysqlConnectionPool mysqlConnectionPool;
private final Connection connection;
public UsuarioDAO() throws SQLException {
this.connection = new MysqlConnectionPool().getConnection();
}
public List<UsuarioDTO> seleciona() throws SQLException {
List<UsuarioDTO> results = new ArrayList<>();
String sql = "select aqui";
try (PreparedStatement stmt = connection.prepareStatement(sql);
ResultSet resultSet = stmt.executeQuery()) {
while (resultSet.next()) {
results.add(populaConta(resultSet));
}
resultSet.close();
stmt.close();
} catch (SQLException e) {
capturaException.ExceptionHandlerController(e);
throw new RuntimeException(e);
} finally {
connection.close();
}
return results;
}}
error
02-Jan-2018 15: 49: 16.027 SEVERE [http-nio-8084-exec-5] org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service () for servlet [dispatcher] in context with path [/ Head2Head] threw exception [Request processing failed; nested exception is java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after closed connection.] With root cause com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after closed connection.