java 数据库 Connection 事宜

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();
            }

        }

    }