/** * 获得一个可用连接.若没有可用连接,且已有连接数小于最大连接数限制, 则创建并返回新连接.否则,在指定的时间内等待其它线程释放连接. * * @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."); } } }