/** Creates a Pooled connection and adds it to the connection pool. */
private void installConnection() throws EmanagerDatabaseException {
logger.debug("enter");
PooledConnection connection;
try {
connection = poolDataSource.getPooledConnection();
connection.addConnectionEventListener(this);
connection.getConnection().setAutoCommit(false);
synchronized (connectionPool) {
connectionPool.add(connection);
logger.debug("Database connection added.");
}
} catch (SQLException ex) {
logger.fatal("exception caught while obtaining database " + "connection: ex = " + ex);
SQLException ex1 = ex.getNextException();
while (ex1 != null) {
logger.fatal("chained sql exception ex1 = " + ex1);
SQLException nextEx = ex1.getNextException();
ex1 = nextEx;
}
String logString;
EmanagerDatabaseException ede;
logString =
EmanagerDatabaseStatusCode.DatabaseConnectionFailure.getStatusCodeDescription()
+ ex.getMessage();
logger.fatal(logString);
ede =
new EmanagerDatabaseException(
EmanagerDatabaseStatusCode.DatabaseConnectionFailure, logString);
throw ede;
}
}
/**
* @param arg0
* @roseuid 3F4E5F400123
*/
public void connectionClosed(ConnectionEvent event) {
logger.debug("Enter: " + event.getSource());
synchronized (connectionPool) {
if (!SHUTTING_DOWN) {
if (connectionPool.size() < connectionPoolSize) {
logger.debug("Reading Connection: " + event.getSource());
connectionPool.add(event.getSource());
}
}
}
}
public List<PromoterRowGateway> findAll() {
List result = new ArrayList();
List promoters = jdbcTemplate.queryForList(findAllStatement);
for (int i = 0; i < promoters.size(); i++)
result.add(PromoterRowGateway.load(dataSource, (Map) promoters.get(i)));
return result;
}
public static List<Movie> getAllMovies() {
String query = "SELECT * FROM movie";
List<Movie> movies = new LinkedList<>();
try (Connection connection = Config.getConnection(); // step 1
Statement statement = connection.createStatement(); // step 2
ResultSet result = statement.executeQuery(query); ) { // step 3 and 4
while (result.next()) { // step 5
Movie movie = new Movie();
// you should be validating the following,
// this is just an example to get you started
movie.setName(result.getString(1));
movie.setYear(result.getDate(2).getYear());
movie.setUrl(result.getString(3));
movies.add(movie);
}
} catch (SQLException e) {
System.err.println(e.getMessage());
System.err.println(e.getStackTrace());
}
return movies;
}
public List<Customer> findCustomers(String id, String match, int limit) {
Connection connection = null;
CallableStatement callableStatement = null;
ResultSet resultSet = null;
boolean hasResults;
List<Customer> customers = new ArrayList<Customer>();
try {
connection = dataSource.getConnection(); // get connection from dataSource
connection.setTransactionIsolation(
Connection.TRANSACTION_READ_COMMITTED); // prevent dirty reads
callableStatement = connection.prepareCall(listCustomersSql); // prepare callable statement
if (id == null) {
callableStatement.setNull(1, Types.INTEGER);
} else {
callableStatement.setInt(1, Integer.parseInt(id));
}
if (match == null) {
callableStatement.setNull(2, Types.VARCHAR);
} else {
callableStatement.setString(2, match);
}
callableStatement.setInt(3, limit);
callableStatement.setNull(4, Types.INTEGER);
hasResults = callableStatement.execute();
if (hasResults) {
resultSet = callableStatement.getResultSet();
if (resultSet.isBeforeFirst()) { // customers have been returned
while (resultSet.next()) {
customers.add(
new Customer(
resultSet.getString("id"),
resultSet.getString("first_name"),
resultSet.getString("last_name"),
resultSet.getString("street_address"),
resultSet.getString("apt_address"),
resultSet.getString("city"),
resultSet.getString("state"),
resultSet.getString("zip"),
resultSet.getString("phone"),
resultSet.getString("email"),
resultSet.getString("notes")));
}
} else {
log.debug("No customers returned.");
}
} else {
log.debug("No customers returned.");
}
} catch (SQLException se) {
log.error("SQL error: ", se);
return null;
} finally {
try {
resultSet.close();
} catch (Exception se) {
log.error("Unable to close resultSet: ", se);
}
try {
callableStatement.close();
} catch (Exception se) {
log.error("Unable to close callableStatement: ", se);
}
try {
connection.close();
} catch (Exception se) {
log.error("Unable to close connection: ", se);
}
}
return customers;
}
