/**
* 获得一个可用连接.若没有可用连接,且已有连接数小于最大连接数限制, 则创建并返回新连接.否则,在指定的时间内等待其它线程释放连接.
*
* @param name 连接池名字
* @param time 以毫秒计的等待时间
* @return Connection 可用连接或null
*/
public Connection getConnection(int id, long time) {
DBConnectionPool pool = (DBConnectionPool) pools.get(new Integer(id));
if (pool != null) {
return pool.getConnection(time);
}
return null;
}
// Open Connection을 얻음. 현재 열린 커넥션이 없고 최대 커넥션 개수가
// 사용 중이 아닐 때는 새로운 커넥션을 생성. 현재 열린 커넥션이 없고
// 최대 커넥션 개수가 사용 중일 때 기본 대기 시간을 기다림
// @param name : Pool Name
// @return Connection : The connection or null
public Connection getConnection(String name) {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
while (pool != null) {
return pool.getConnection(10);
}
return null;
}
/**
* 获得一个空闲的连接.若没有空闲的连接,且已有连接数小于最大连接数限制, 则创建并返回新连接.否则,在指定的时间内等待其它线程释放连接.
*/
public Connection getConnection(String name, long time) {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
return pool.getConnection(time);
}
return null;
}
/**
* 获得一个可用的(空闲的)连接.如果没有可用连接,且已有连接数小于最大连接数 限制,则创建并返回新连接
*
* @param name 在属性文件中定义的连接池名字
* @return Connection 可用连接或null
*/
public Connection getConnection(int id) {
DBConnectionPool pool = (DBConnectionPool) pools.get(new Integer(id));
if (pool != null) {
// return pool.getConnection();
return pool.returnConnection();
}
return null;
}
/**
* 将连接对象返回给由名字指定的连接池
*
* @param name 在属性文件中定义的连接池ID名字
* @param con 连接对象
*/
public void freeConnection(int id, Connection con) {
DBConnectionPool pool = (DBConnectionPool) pools.get(new Integer(id));
if (pool != null) {
pool.freeConnection(con);
} else {
System.out.println("pool ==null");
}
clients--;
}
public int delete(int no) {
Connection con = null;
PreparedStatement stmt = null;
try {
con = dbPool.getConnection();
stmt = con.prepareStatement("DELETE FROM board2 WHERE bno=?");
stmt.setInt(1, no);
return stmt.executeUpdate();
} catch (SQLException e) {
throw new DaoException(e);
} finally {
try {
stmt.close();
} catch (Exception e) {
}
dbPool.returnConnection(con);
}
}
public List<BoardVo> selectList() {
ArrayList<BoardVo> list = new ArrayList<BoardVo>();
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = dbPool.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT bno,title,cre_date,views FROM board2");
BoardVo board = null;
while (rs.next()) {
board = new BoardVo();
board.setNo(rs.getInt("bno"));
board.setTitle(rs.getString("title"));
board.setCreateDate(rs.getDate("cre_date"));
board.setViews(rs.getInt("views"));
list.add(board);
}
return list;
} catch (SQLException e) {
throw new DaoException(e);
} finally {
try {
rs.close();
} catch (Exception e) {
}
try {
stmt.close();
} catch (Exception e) {
}
dbPool.returnConnection(con);
}
}
public BoardVo select(int no) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
con = dbPool.getConnection();
stmt =
con.prepareStatement("SELECT bno,title,content,cre_date,views FROM board2 WHERE bno=?");
stmt.setInt(1, no);
rs = stmt.executeQuery();
if (rs.next()) {
BoardVo board = new BoardVo();
board.setNo(rs.getInt("bno"));
board.setTitle(rs.getString("title"));
board.setContent(rs.getString("content"));
board.setCreateDate(rs.getDate("cre_date"));
board.setViews(rs.getInt("views"));
return board;
} else {
return null;
}
} catch (SQLException e) {
throw new DaoException(e);
} finally {
try {
rs.close();
} catch (Exception e) {
}
try {
stmt.close();
} catch (Exception e) {
}
dbPool.returnConnection(con);
}
}
// 关闭所有连接,撤销驱动程序的注册
public synchronized void release() {
// 等待直到最后一个客户程序调用
if (--clients != 0) {
return;
}
Enumeration allPools = pools.elements();
while (allPools.hasMoreElements()) {
DBConnectionPool pool = (DBConnectionPool) allPools.nextElement();
pool.release();
}
Enumeration allDrivers = drivers.elements();
while (allDrivers.hasMoreElements()) {
Driver driver = (Driver) allDrivers.nextElement();
try {
DriverManager.deregisterDriver(driver);
log("撤销JDBC驱动程序 " + driver.getClass().getName() + "的注册");
} catch (SQLException e) {
log(e, "无法撤销下列JDBC驱动程序的注册: " + driver.getClass().getName());
}
}
}
public void insert(final BoardVo board) {
Connection con = null;
PreparedStatement stmt = null;
try {
con = dbPool.getConnection();
stmt = con.prepareStatement("INSERT INTO board2 (title,content,cre_date) VALUES (?,?,now())");
stmt.setString(1, board.getTitle());
stmt.setString(2, board.getContent());
stmt.executeUpdate();
} catch (SQLException e) {
throw new DaoException(e);
} finally {
try {
stmt.close();
} catch (Exception e) {
}
dbPool.returnConnection(con);
}
}
public int update(BoardVo board) {
Connection con = null;
PreparedStatement stmt = null;
try {
con = dbPool.getConnection();
stmt = con.prepareStatement("UPDATE board2 SET title=?, content=?" + " WHERE bno=?");
stmt.setString(1, board.getTitle());
stmt.setString(2, board.getContent());
stmt.setInt(3, board.getNo());
return stmt.executeUpdate();
} catch (SQLException e) {
throw new DaoException(e);
} finally {
try {
stmt.close();
} catch (Exception e) {
}
dbPool.returnConnection(con);
}
}
// Release the given database resources, if non-null.
private static void releaseDatabaseResources(Connection dbConn, PreparedStatement ps) {
if (ps != null) {
try {
ps.close();
} catch (Exception e) {
Debug.warning("Failed to close the prepared statement:\n" + e.toString());
}
}
if (dbConn != null) {
try {
DBConnectionPool.getInstance().releaseConnection(dbConn);
} catch (Exception e) {
Debug.warning("Failed to release database connection back to the pool:\n" + e.toString());
}
}
}
@Override
public Connection getConnection() throws SQLException {
return ds.getConnection();
}
// 将连接对象返回给由名字指定的连接池
public void freeConnection(String name, Connection con) {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
pool.freeConnection(con);
}
}
/**
* Update the given event as indicated.
*
* @param event The event to update.
* @param eventStatus The event delivery status.
* @exception FrameworkException Thrown on errors.
*/
public void update(Event event, EventStatus eventStatus) throws FrameworkException {
if (Debug.isLevelEnabled(Debug.MSG_STATUS))
Debug.log(
Debug.MSG_STATUS,
"QUEUE OPERATION: Updating database queue using event status ["
+ eventStatus.name
+ "] ...");
// If no consumers were available, the event delivery wasn't attempted so leave
// the queue in its current state.
if (eventStatus == EventStatus.NO_CONSUMERS_AVAILABLE) {
Debug.log(
Debug.MSG_STATUS, "Skipping queue update, as no consumers were available to process it.");
return;
}
Connection dbConn = null;
PreparedStatement ps = null;
long startTime = -1;
if (Debug.isLevelEnabled(Debug.BENCHMARK)) startTime = System.currentTimeMillis();
try {
dbConn = DBConnectionPool.getInstance().acquireConnection();
if (eventStatus == EventStatus.DELIVERY_SUCCESSFUL) {
// If the event was successfully delivered, update status in database to delivered.
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(Debug.DB_DATA, "\n" + LINE + "\nExecuting SQL:\n" + UPDATE_EVENT_SUCCESS_SQL);
ps = dbConn.prepareStatement(UPDATE_EVENT_SUCCESS_SQL);
java.sql.Timestamp ts = new java.sql.Timestamp(System.currentTimeMillis());
ps.setTimestamp(1, ts);
ps.setString(2, event.channelName);
ps.setInt(3, event.id);
} else if (eventStatus == EventStatus.DELIVERY_FAILED) {
// If the event delivery failed, we mark it as failed in the database.
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(Debug.DB_DATA, "\n" + LINE + "\nExecuting SQL:\n" + UPDATE_EVENT_ERROR_SQL);
// Truncate error message if it's larger than database column.
if ((event.lastErrorMessage != null)
&& (event.lastErrorMessage.length() > MAX_ERROR_MESSAGE_LENGTH))
event.lastErrorMessage = event.lastErrorMessage.substring(0, MAX_ERROR_MESSAGE_LENGTH);
event.lastErrorTime = new java.sql.Timestamp(System.currentTimeMillis());
ps = dbConn.prepareStatement(UPDATE_EVENT_ERROR_SQL);
ps.setTimestamp(1, event.lastErrorTime);
ps.setString(2, event.lastErrorMessage);
ps.setString(3, event.channelName);
ps.setInt(4, event.id);
} else {
throw new FrameworkException(
"ERROR: Invalid event update type [" + eventStatus.name + "].");
}
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(Debug.DB_DATA, "Event being operated on in database:\n" + event.describe());
int numRows = ps.executeUpdate();
if (numRows > 1) {
String errMsg = "Execution of update SQL statement affected [" + numRows + "] rows.";
Debug.error(errMsg);
throw new FrameworkException(errMsg);
}
DBConnectionPool.getInstance().commit(dbConn);
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(Debug.DB_DATA, "Successfully committed SQL operation.\n" + LINE);
// At this point, the event should be removed from the in-memory buffer of events as well,
// irrespective of processing outcome.
if (Debug.isLevelEnabled(Debug.MSG_STATUS))
Debug.log(
Debug.MSG_STATUS,
"Removing event [" + event.describe() + "] from in-memory queue buffer.");
boolean removed = queue.remove(event);
if (Debug.isLevelEnabled(Debug.MSG_STATUS))
Debug.log(
Debug.MSG_STATUS,
"Event removed? ["
+ removed
+ "]. In-memory queue buffer size ["
+ queue.size()
+ "].");
} catch (SQLException sqle) {
throw new DatabaseException(
"ERROR: Could not execute SQL statement:\n" + DBInterface.getSQLErrorMessage(sqle));
} catch (Exception e) {
throw new DatabaseException("ERROR: Could not execute SQL statement:\n" + e.toString());
} finally {
releaseDatabaseResources(dbConn, ps);
if (Debug.isLevelEnabled(Debug.BENCHMARK) && (startTime > 0)) {
long stopTime = System.currentTimeMillis();
Debug.log(
Debug.BENCHMARK,
"ELAPSED TIME ["
+ (stopTime - startTime)
+ "] msec: "
+ "SQL: Time to update event in PersistentEvent database table.");
}
}
}
/**
* Add the event to the end of queue.
*
* @param event The event to add to the queue.
* @exception FrameworkException Thrown on errors.
*/
public void add(Event event) throws FrameworkException {
Debug.log(Debug.MSG_STATUS, "QUEUE OPERATION: Adding event to database queue ...");
Connection dbConn = null;
PreparedStatement ps = null;
long startTime = -1;
if (Debug.isLevelEnabled(Debug.BENCHMARK)) startTime = System.currentTimeMillis();
try {
event.id = PersistentSequence.getNextSequenceValue(SEQUENCE_NAME);
dbConn = DBConnectionPool.getInstance().acquireConnection();
event.arrivalTime = new java.sql.Timestamp(System.currentTimeMillis());
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(Debug.DB_DATA, "\n" + LINE + "\nExecuting SQL:\n" + INSERT_EVENT_SQL);
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(Debug.DB_DATA, "Event being inserted into database:\n" + event.describe());
if (Debug.isLevelEnabled(Debug.MSG_DATA))
Debug.log(Debug.MSG_DATA, "Event contents:\n" + event.message);
ps = dbConn.prepareStatement(INSERT_EVENT_SQL);
ps.setString(1, event.channelName);
ps.setInt(2, event.id);
DBLOBUtils.setCLOB(ps, 3, event.message);
ps.setTimestamp(4, event.arrivalTime);
int numRows = ps.executeUpdate();
if (numRows != 1) {
String errMsg =
"Execution of SQL statement ["
+ INSERT_EVENT_SQL
+ "] affected ["
+ numRows
+ "] rows.";
Debug.error(errMsg);
throw new FrameworkException(errMsg);
}
DBConnectionPool.getInstance().commit(dbConn);
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(Debug.DB_DATA, "Successfully committed SQL operation.\n" + LINE);
// NOTE: We don't add the item just inserted into the database into the in-memory
// queue, as we want it loaded by the separate dequeueing thread.
} catch (SQLException sqle) {
throw new DatabaseException(
"ERROR: Could not execute SQL statement:\n" + DBInterface.getSQLErrorMessage(sqle));
} catch (Exception e) {
throw new DatabaseException("ERROR: Could not execute SQL statement:\n" + e.toString());
} finally {
releaseDatabaseResources(dbConn, ps);
if (Debug.isLevelEnabled(Debug.BENCHMARK) && (startTime > 0)) {
long stopTime = System.currentTimeMillis();
Debug.log(
Debug.BENCHMARK,
"ELAPSED TIME ["
+ (stopTime - startTime)
+ "] msec: "
+ "SQL: Time to insert event into PersistentEvent database table.");
}
}
}
/**
* Reset any events meeting the given criteria so that they can be retried.
*
* @param criteria An event containing the event-selection criteria.
* @return The number of events reset.
* @exception FrameworkException Thrown on errors.
*/
protected static int reset(Event criteria) throws FrameworkException {
Debug.log(
Debug.MSG_STATUS, "QUEUE OPERATION: Resetting events in database for database queue ...");
if (!StringUtils.hasValue(criteria.channelName)) {
throw new FrameworkException(
"ERROR: Event channel name is a required queue search criteria.");
}
Connection dbConn = null;
PreparedStatement ps = null;
long startTime = -1;
if (Debug.isLevelEnabled(Debug.BENCHMARK)) startTime = System.currentTimeMillis();
try {
dbConn = DBConnectionPool.getInstance().acquireConnection();
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(
Debug.DB_DATA, "Criteria used to reset events in database:\n" + criteria.describe());
// If no identifier was given that uniquely identifies a single event ...
if (criteria.id == 0) {
// Use last error time and error count, if available.
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(Debug.DB_DATA, "\n" + LINE + "\nExecuting SQL:\n" + UPDATE_EVENT_RETRY_SQL);
ps = dbConn.prepareStatement(UPDATE_EVENT_RETRY_SQL);
ps.setString(1, criteria.channelName);
if (criteria.lastErrorTime == null) ps.setNull(2, Types.DATE);
else ps.setTimestamp(2, criteria.lastErrorTime);
if (criteria.errorCount < 1) ps.setNull(3, Types.INTEGER);
else ps.setInt(3, criteria.errorCount);
} else {
// An Id was given which should uniquely identify a single event, so we should
// skip using any other qualifying criteria, if present.
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(
Debug.DB_DATA, "\n" + LINE + "\nExecuting SQL:\n" + UPDATE_EVENT_RETRY_BY_ID_SQL);
ps = dbConn.prepareStatement(UPDATE_EVENT_RETRY_BY_ID_SQL);
ps.setString(1, criteria.channelName);
ps.setInt(2, criteria.id);
}
int numRows = ps.executeUpdate();
DBConnectionPool.getInstance().commit(dbConn);
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(
Debug.DB_DATA, "Committed SQL execution affected [" + numRows + "] rows.\n" + LINE);
return numRows;
} catch (SQLException sqle) {
throw new DatabaseException(
"ERROR: Could not execute SQL statement:\n" + DBInterface.getSQLErrorMessage(sqle));
} catch (Exception e) {
throw new DatabaseException("ERROR: Could not execute SQL statement:\n" + e.toString());
} finally {
releaseDatabaseResources(dbConn, ps);
if (Debug.isLevelEnabled(Debug.BENCHMARK) && (startTime > 0)) {
long stopTime = System.currentTimeMillis();
Debug.log(
Debug.BENCHMARK,
"ELAPSED TIME ["
+ (stopTime - startTime)
+ "] msec: "
+ "SQL: Time to reset event(s) in PersistentEvent database table.");
}
}
}
/**
* Load any available events from the database up to the configured maximum.
*
* @param criteria An event containing the event-selection criteria.
* @return The next available event on the queue.
* @exception FrameworkException Thrown on errors.
*/
private void loadFromDatabase(Event criteria) throws FrameworkException {
Debug.log(Debug.MSG_STATUS, "QUEUE OPERATION: Loading events from database into queue ...");
if (!StringUtils.hasValue(criteria.channelName)) {
throw new FrameworkException(
"ERROR: Event channel name is a required queue search criteria.");
}
Connection dbConn = null;
PreparedStatement ps = null;
long startTime = -1;
if (Debug.isLevelEnabled(Debug.BENCHMARK)) startTime = System.currentTimeMillis();
try {
dbConn = DBConnectionPool.getInstance().acquireConnection();
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(Debug.DB_DATA, "\n" + LINE + "\nExecuting SQL:\n" + QUERY_EVENT_SQL);
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(
Debug.DB_DATA, "Criteria used in query against database:\n" + criteria.describe());
ps = dbConn.prepareStatement(QUERY_EVENT_SQL);
ps.setString(1, criteria.channelName);
ResultSet rs = ps.executeQuery();
for (int counter = 0; (counter < maxDatabaseEventLoadSize) && rs.next(); counter++) {
Event event = new Event();
event.channelName = rs.getString(CHANNEL_NAME_COL);
event.id = rs.getInt(ID_COL);
event.message = DBLOBUtils.getCLOB(rs, MESSAGE_COL);
if (Debug.isLevelEnabled(Debug.MSG_LIFECYCLE))
Debug.log(Debug.MSG_LIFECYCLE, "Event contents:\n" + event.message);
event.arrivalTime = rs.getTimestamp(ARRIVAL_TIME_COL);
event.errorStatus = rs.getString(ERROR_STATUS_COL);
event.errorCount = rs.getInt(ERROR_COUNT_COL);
event.lastErrorMessage = rs.getString(LAST_ERROR_MESSAGE_COL);
event.lastErrorTime = rs.getTimestamp(LAST_ERROR_TIME_COL);
// Add item to in-memory buffer.
if (Debug.isLevelEnabled(Debug.MSG_STATUS))
Debug.log(
Debug.MSG_STATUS,
"Adding event [" + event.describe() + "] to in-memory queue buffer.");
queue.add(event);
if (Debug.isLevelEnabled(Debug.MSG_STATUS))
Debug.log(Debug.MSG_STATUS, "In-memory queue buffer size [" + queue.size() + "].");
}
if (Debug.isLevelEnabled(Debug.DB_DATA)) Debug.log(Debug.DB_DATA, "\n" + LINE);
} catch (SQLException sqle) {
throw new DatabaseException(
"ERROR: Could not execute SQL statement:\n" + DBInterface.getSQLErrorMessage(sqle));
} catch (Exception e) {
throw new DatabaseException("ERROR: Could not execute SQL statement:\n" + e.toString());
} finally {
releaseDatabaseResources(dbConn, ps);
if (Debug.isLevelEnabled(Debug.BENCHMARK) && (startTime > 0)) {
long stopTime = System.currentTimeMillis();
Debug.log(
Debug.BENCHMARK,
"ELAPSED TIME ["
+ (stopTime - startTime)
+ "] msec: "
+ "SQL: Time to load event(s) from PersistentEvent database table.");
}
}
}
