java 数据库 Connection 事务
public void deleteDeadDataImprove(String tableName, long updateTime) throws Exception { // 根据报表源判断是sql if(connGrpForDel == null) { logger.error("connGrpForDel is not initialized."); throw new Exception("connGrpForDel is not initialized."); } Connection connection = null; synchronized(this) { connection = this.connGrpForDel.getActiveConnection(); threadLocal.set(new Long(batchNo)); logger.debug(Thread.currentThread().getName() + "ok," + "threadLocal.get()=" + threadLocal.get() + ",batchNo=" + batchNo); } String sql = "";// Statement pstmt = null; PreparedStatement pstmt = null; try { connection.setAutoCommit(false); // 删除主键和索引 String dbType = DBConnectionParams.getDatabaseType(); if(CommonAttrs.DATABASE_TYPE_ORACLE.equals(dbType)) { sql = "drop index I" + tableName; } else if(CommonAttrs.DATABASE_TYPE_SQLSERVER.equals(dbType)) { sql = "drop index " + tableName + ".I" + tableName; } logger.info("sql=" + sql); if(sql != null && sql.length() > 0) {// pstmt = connection.createStatement();// pstmt.executeUpdate(sql); pstmt = connection.prepareStatement(sql); pstmt.executeUpdate(); } // 批量删除数据 // boolean status = true; sql = "delete from " + tableName + " where timestamp <= " + updateTime;// + " and rownum <= " + countOneBatchDel; logger.debug("sql=" + sql); // while(status) // {// int deleteCount = pstmt.executeUpdate(sql); pstmt = connection.prepareStatement(sql); int deleteCount = pstmt.executeUpdate(); logger.info("Remove " + deleteCount +" expired records from " + tableName + " table"); // 分批删,但一次性提交 // if(deleteCount <= 0) // { // status = false; // } // } // 创建主键和索引 sql = "create index I" + tableName + " on " + tableName + "(timestamp ASC, entityId)"; logger.info("sql=" + sql);// pstmt.executeUpdate(sql); pstmt = connection.prepareStatement(sql); pstmt.executeUpdate(); connection.commit(); } catch(SQLException e) { logger.error("SQLException when deleteDeadDataImprove.", e); connection.rollback(); } finally { if(pstmt != null) { pstmt.close(); pstmt = null; } if(!connection.isClosed()) { // 存储结束后,将数据存储自动提交置为true,需要手动提交时再修改为false connection.setAutoCommit(true); // 释放connection this.connGrpForDel.realseConnection(connection); } else { // 如果是connection被关闭了，重新初始化数据池 reInitialize(); } } }