涉及表包含 testcycl、test、cycl_fold 等

主体代码 1 
package com.sunyard.DBStatistics;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import com.broada.spring.DB.DbProxoolUtil;
import com.broada.spring.DB.DbProxoolUtilTestDB;
import com.sunyard.commons.Html2StringUtil;

public class testCycl2excel {
    static String projectstr ="渠道产品开发中心_移0";
    /**
    *根据测试实验室进行案例导出。
    *module为要导出的目录结构，module.get(0),为要统计的根目录，需要确保唯一性！！！
    *
    */
    public static void main(String[] args){     

        ArrayList<String> module=new ArrayList<String>();
        //设置要统计的底层目录

        module.add("树根--0");
        module.add("树枝--1");
        //获取需要统计的目录（）
        List<ArrayList> cfItemPaths = (ArrayList) DbProxoolUtilTestDB.query("select  cf_item_path from "+projectstr +".td.cycl_fold where cf_item_name like '"+module.get(0)+"'", 0);


        String cfItemPath=cfItemPaths.get(0).get(0).toString();//测试目录路径 



        //清空数据库
        DbProxoolUtil.delete("truncate table test.mobile_test_data");

        // 找到test表中，可以与all_lists表关联查询的id，
        List<ArrayList> alldatas = (ArrayList) DbProxoolUtilTestDB
                .query("select mm.ts_test_id,mm.cf_item_name+'/'+mm.cy_cycle ,mm.ts_name,  case when tdd.DS_DESCRIPTION is null then mm.ts_description else tdd.DS_DESCRIPTION end steps ,tdd.DS_EXPECTED, mm.cf_item_id ,mm.tc_status,mt. st_actual  from "
                        + projectstr
                        + ".td.dessteps tdd ,(select  tt.TS_TEST_ID,tt.TS_NAME,tt.ts_description,tcf.cf_item_name ,tc.cy_cycle,tcf.cf_item_id,ttc.tc_status from "
                        + projectstr
                        + ".td.cycle tc,"
                        + projectstr
                        + ".td.cycl_fold tcf ,"
                        + projectstr
                        + ".td.testcycl ttc,"
                        + projectstr
                        + ".td.test  tt where  ttc.TC_TEST_ID=tt.ts_test_id and ttc.tc_cycle_id=tc.cy_cycle_id and tc.cy_folder_id=tcf.cf_item_id and tcf.cf_item_path like '"+cfItemPath+"%' and tc.cy_cycle like'%精选商品%') mm   ,(select  te.ts_test_id,st.st_status,st.st_actual  from "+ projectstr
                        + ".td.step st,"
                        + projectstr
                        + ".td.test te  where te.ts_test_id =st.st_test_id)  mt   where tdd.DS_TEST_ID=mm.TS_TEST_ID  and mt.ts_test_id=mm.ts_test_id",
                        0);

        for (ArrayList shopRole2 : alldatas) {
            String id = shopRole2.get(0).toString();// test—id
            String catalogue = shopRole2.get(1).toString();// 测试实验室目录
            String testName = shopRole2.get(2).toString();// 测试名
            String description = null;// 测试描述
            String expecations = null;// 测试预期



            List<ArrayList> catalogues =(ArrayList) DbProxoolUtilTestDB.query(  "with subqry(cf_item_id,cf_item_name,cf_father_id) as (select cf_item_id,cf_item_name,cf_father_id from "
                    + projectstr 
                    + ".td.cycl_fold  where cf_item_id = "
                + shopRole2.get(5)
                    + " union all select "
                    + projectstr 
                    + ".td.cycl_fold.cf_item_id, "
                    + projectstr
                    + ".td.cycl_fold.cf_item_name,"
                    + projectstr
                    + ".td.cycl_fold.cf_father_id from "
                    + projectstr
                    + ".td.cycl_fold,subqry  where "
                    + projectstr
                    + ".td.cycl_fold.cf_item_id = subqry.cf_father_id ) select  cf_item_name from subqry order by cf_item_id asc;"
                    , 0);

            //转换ArrayList<ArrayList>为ArrayList<String>
            ArrayList<String> tempList=new ArrayList<String>();
            for(ArrayList l:catalogues){    

                tempList.add(l.get(0).toString());

            }
            if(tempList.containsAll(module)){

            StringBuffer fathernames=new StringBuffer();
            int size = tempList.size();
            for(int i = 0;i<size;i++){
                if(i<size-1){
                    fathernames.append(tempList.get(i).toString()+"/");
                }else{
                    fathernames.append(tempList.get(i).toString());
                }
            }   
            // 处理description html标签
            description = Html2StringUtil.getToString(shopRole2.get(3)
                    .toString());
            // 处理expecations html标签
            expecations = Html2StringUtil.getToString(shopRole2.get(4)
                    .toString());
            String actualResult = Html2StringUtil.getToString(shopRole2.get(7));
             String tcStatus=shopRole2.get(6).toString();
            // 将目录、案例名称等放到表中
            String insertSql2 = "insert into test.mobile_test_data(id,catalogue,main_points,description,expecations,tc_status,actual_result) values('"
                    + id + // id
                    "','" + fathernames + // 目录
                    "','" + testName + // 测试要点
                    "','" + description + // 步骤描述
                    "','" + expecations + // 预期结果
                    "','" + tcStatus +//执行结果
                    "','" + actualResult +//执行结果
                    "')";

            DbProxoolUtil.insert(insertSql2);


        }

    }
    }
}

主体代码2 消除html标签

package com.sunyard.commons;

public final class Html2StringUtil {



    public static String getToString(Object object){

        String ReturnhtmlString=null;
        String tmp1=null;   
        if(object== null){
            ReturnhtmlString = "空";
        }else{

            tmp1 = object.toString().replaceAll("[']", "''").replaceAll(";", ".");
            String a = tmp1.replace("<br>", "\n");
//          String b = a.replaceAll("[<]+[a-z]+[>]", "");
//          String buzhou2 = buzhou.replaceAll("<[^</]*>", "");
//          String buzhou3 = buzhou2.replaceAll("</[^<]*>", "");
            String b = a.replaceAll("<div [^>]*>", "");

            String c = b.replaceAll("[</]+[a-z]+[>]", "");
            String c2 = c.replaceAll("<font [^>]*>", "");
            String c3 = c2.replaceAll("<span [^>]*>", "");
            ReturnhtmlString = c3.replaceAll("<br *+/>", "");

        }

        return ReturnhtmlString.trim();

    }

}


主体代码 3 db



package com.broada.spring.DB;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.broada.spring.DB.DbConnectionNoContainer;


public class DbProxoolUtil 
{
       private static Log log = LogFactory.getLog(DbProxoolUtil.class);

    /**
     * 执行sql更新语句
     * @since 2013.06.25
     * @param sql 语句 sql
     * @return int 成功:返回更新的记录数目;失败:-1
     **/
    public static int update(String sql)
    {       
        Connection connection = null;
        Statement SQLStament = null;
        try 
        {           
            connection = DbConnectionNoContainer.getConnection(true);
            SQLStament = connection.createStatement();
            return SQLStament.executeUpdate(sql);
        } 
        catch (SQLException e) 
        {
            log.error("更新数据库表记录时发生异常,SQL语句为："+sql, e);            
            return -1;
        }
        catch(Exception e)
        {
            log.error( "更新数据库表记录时发生异常,SQL语句为："+sql, e);           
            return -1;
        }
        finally
        {
            DbConnectionNoContainer.closeStatement(SQLStament);
            DbConnectionNoContainer.releaseConnection(connection);
        } 
    }

    /**
     * 执行sql删除语句
     * @since 2013.06.25
     * @param sql 语句
     * @return int 成功:返回删除的记录数目;失败:-1
     **/
    public static int delete(String sql)
    {       
        Connection connection = null;
        Statement SQLStament = null;
        try 
        {           
            connection = DbConnectionNoContainer.getConnection(true);
            SQLStament = connection.createStatement();
            return SQLStament.executeUpdate(sql);
        } 
        catch (SQLException e) 
        {
            log.error( "删除数据库表记录时发生异常,SQL语句为："+sql, e);           
            return -1;
        }
        catch(Exception e)
        {
            log.error( "删除数据库表时记录发生异常,SQL语句为："+sql, e);           
            return -1;
        }
        finally
        {
            DbConnectionNoContainer.closeStatement(SQLStament);
            DbConnectionNoContainer.releaseConnection(connection);
        } 
    }

    /**
     * 执行sql插入语句
     * @since 2013.06.25
     * @param sql 语句 sql
     * @return int 成功:返回插入的记录数目;失败:-1
     **/
    public static int insert(String sql)
    {       
        Connection connection = null;
        Statement SQLStament = null;
        try 
        {           
            connection = DbConnectionNoContainer.getConnection(true);
            SQLStament = connection.createStatement();
            return SQLStament.executeUpdate(sql);
        } 
        catch (SQLException e) 
        {
            log.error( "插入数据库表记录时发生异常,SQL语句为："+sql, e);           
            return -1;
        }
        catch(Exception e)
        {
            log.error( "插入数据库表时记录发生异常,SQL语句为："+sql, e);           
            return -1;
        }
        finally
        {
            DbConnectionNoContainer.closeStatement(SQLStament);
            DbConnectionNoContainer.releaseConnection(connection);
        } 
    }

    /**
     * 批量执行sql插入语句
     * @since 2013.06.25
     * @param sqlList 语句集 sqlList
     * @return int 成功:返回插入的记录数目;失败:-1。一条插入失败所有都失败
     **/
    public static int batchInsert(List<String> sqlList)
    {       
        Connection connection = null;
        Statement SQLStament = null;
        try 
        {

            connection = DbConnectionNoContainer.getConnection(false);
            SQLStament = connection.createStatement();
            for(String sql:sqlList)
            {               
                SQLStament.executeUpdate(sql);
            }
            connection.commit();
            return sqlList.size();
        } 
        catch (SQLException e) 
        {
            DbConnectionNoContainer.rollback(connection);
            log.error( "批量插入数据库表时发生异常", e);           
            return -1;
        }
        catch(Exception e)
        {
            DbConnectionNoContainer.rollback(connection);
            log.error( "批量插入数据库表时发生异常", e);           
            return -1;
        }

        finally
        {
            DbConnectionNoContainer.closeStatement(SQLStament);
            DbConnectionNoContainer.releaseConnection(connection);
        } 
    }

    /**
     * 执行sql查询语句
     * @since 2013.06.25
     * @param sql 语句
     * @param returnType 返回结果集类型,0:返回list结果集;1:返回hashmap结果集;其他同0 returnType
     * @return List<Object> 查询结果集 resultList元素为list<value>或HashMap<ColName,value>
     **/
    public static List<Object> query(String sql,int returnType)
    {       
        Connection connection = null;
        Statement SQLStament = null;
        ResultSet set = null;
        try 
        {           
            connection = DbConnectionNoContainer.getConnection(true);
            //log.error("12334");
            SQLStament = connection.createStatement();
            set = SQLStament.executeQuery(sql);

            ResultSetMetaData rsmd=set.getMetaData();
            int colCount=rsmd.getColumnCount();
            List<Object> resultList=new ArrayList<Object>();
            if(returnType==0)
            {
                while(set!=null&&set.next())
                {
                    List<Object> temp=new ArrayList<Object>();
                    for(int i=0;i<colCount;i++)
                    {
                        temp.add(set.getObject(i+1));
                    }
                    resultList.add(temp);
                }
            }
            else
            {
                while(set!=null&&set.next())
                {
                    HashMap<String, Object> temp=new HashMap<String,Object>();
                    for(int i=0;i<colCount;i++)
                    {
                        temp.put(rsmd.getColumnName(i+1).toUpperCase(),set.getObject(i+1));
                    }
                    resultList.add(temp);
                }
            }
            return resultList;
        } 
        catch (SQLException e) 
        {
            log.error( "查询数据库表时发生异常,SQL语句为："+sql, e);         
            return null;
        }
        catch(Exception e)
        {
            log.error( "查询数据库表时发生异常,SQL语句为："+sql, e);         
            return null;
        }       
        finally
        {
            //System.out.println("1233ww4");
            DbConnectionNoContainer.closeResultSet(set);
            DbConnectionNoContainer.closeStatement(SQLStament);
            DbConnectionNoContainer.releaseConnection(connection);
        } 
    }

    /**
     * 执行sql查询语句
     * @since 2013.06.25
     * @param sql 语句 sql
     * @param returnType 返回结果集类型,0:返回list结果集;1:返回hashmap结果集;其他同0 returnType
     * @return List<Object>  查询结果集 resultList元素为list<value>或HashMap<ColName,value>
     **/
    public static List<Object> query(String sql,int returnType ,String parm)
    {       
        Connection connection = null;
        PreparedStatement SQLStament = null;
        ResultSet set = null;
        try 
        {           
            connection = DbConnectionNoContainer.getConnection(true);
            SQLStament = connection.prepareStatement(sql);
            SQLStament.setString(1, parm);
            set = SQLStament.executeQuery();

            ResultSetMetaData rsmd=set.getMetaData();
            int colCount=rsmd.getColumnCount();
            List<Object> resultList=new ArrayList<Object>();
            if(returnType==0)
            {
                while(set!=null&&set.next())
                {
                    List<Object> temp=new ArrayList<Object>();
                    for(int i=0;i<colCount;i++)
                    {
                        temp.add(set.getObject(i+1));
                    }
                    resultList.add(temp);
                }
            }

            return resultList;
        } 
        catch (SQLException e) 
        {
            log.error( "查询数据库表时发生异常,SQL语句为："+sql, e);         
            return null;
        }
        catch(Exception e)
        {
            log.error( "查询数据库表时发生异常,SQL语句为："+sql, e);         
            return null;
        }
        finally
        {
            DbConnectionNoContainer.closeResultSet(set);
            DbConnectionNoContainer.closeStatement(SQLStament);
            DbConnectionNoContainer.releaseConnection(connection);
        } 
    }

    /**
     * 执行sql查询语句
     * @since 2013.06.25
     * @param sql 语句 sql
     * @param returnType 返回结果集类型,0:返回list结果集;1:返回hashmap结果集;其他同0 returnType
     * @return  List<Object> 查询结果集 resultList元素为list<value>或HashMap<ColName,value>
     **/
    public static List<Object> query(String sql,int returnType ,int parm)
    {       
        Connection connection = null;
        PreparedStatement SQLStament = null;
        ResultSet set = null;
        try 
        {           
            connection = DbConnectionNoContainer.getConnection(true);
            SQLStament = connection.prepareStatement(sql);
            SQLStament.setInt(1, parm);
            set = SQLStament.executeQuery();

            ResultSetMetaData rsmd=set.getMetaData();
            int colCount=rsmd.getColumnCount();
            List<Object> resultList=new ArrayList<Object>();
            if(returnType==0)
            {
                while(set!=null&&set.next())
                {
                    List<Object> temp=new ArrayList<Object>();
                    for(int i=0;i<colCount;i++)
                    {
                        temp.add(set.getObject(i+1));
                    }
                    resultList.add(temp);
                }
            }

            return resultList;
        } 
        catch (SQLException e) 
        {
            log.error( "查询数据库表时发生异常,SQL语句为："+sql, e);         
            return null;
        }
        catch(Exception e)
        {
            log.error( "查询数据库表时发生异常,SQL语句为："+sql, e);         
            return null;
        }
        finally
        {
            DbConnectionNoContainer.closeResultSet(set);
            DbConnectionNoContainer.closeStatement(SQLStament);
            DbConnectionNoContainer.releaseConnection(connection);
        } 
    }

    /**
     * 执行sql查询语句
     * @since 2013.06.25
     * @param sql 语句 sql
     * @param returnType 返回结果集类型,0:返回list结果集;1:返回hashmap结果集;其他同0 returnType
     * @return List<Object> 查询结果集 resultList元素为list<value>或HashMap<ColName,value>
     **/
    public static List<Object> query(String sql,int returnType ,Object x,Object y)
    {       
        Connection connection = null;
        PreparedStatement SQLStament = null;
        ResultSet set = null;
        try 
        {           
            connection = DbConnectionNoContainer.getConnection(true);
            SQLStament = connection.prepareStatement(sql);
            SQLStament.setObject(1, x);
            SQLStament.setObject(2, y);
            set = SQLStament.executeQuery();

            ResultSetMetaData rsmd=set.getMetaData();
            int colCount=rsmd.getColumnCount();
            List<Object> resultList=new ArrayList<Object>();
            if(returnType==0)
            {
                while(set!=null&&set.next())
                {
                    List<Object> temp=new ArrayList<Object>();
                    for(int i=0;i<colCount;i++)
                    {
                        temp.add(set.getObject(i+1));
                    }
                    resultList.add(temp);
                }
            }

            return resultList;
        } 
        catch (SQLException e) 
        {
            log.error( "查询数据库表时发生异常,SQL语句为："+sql, e);         
            return null;
        }
        catch(Exception e)
        {
            log.error( "查询数据库表时发生异常,SQL语句为："+sql, e);         
            return null;
        }
        finally
        {
            DbConnectionNoContainer.closeResultSet(set);
            DbConnectionNoContainer.closeStatement(SQLStament);
            DbConnectionNoContainer.releaseConnection(connection);
        } 
    }

    /**
     * 执行sql查询语句
     * @since 2013.06.25
     * @param sql 语句 sql
     * @param list 返回结果集类型,0:返回list结果集;1:返回hashmap结果集;其他同0 returnType
     * @return 查询结果集 resultList元素为list<value>或HashMap<ColName,value>
     **/
    public static List<Object> query(String sql,int returnType ,List list)
    {       
        Connection connection = null;
        PreparedStatement SQLStament = null;
        ResultSet set = null;
        try 
        {           
            connection = DbConnectionNoContainer.getConnection(true);
            SQLStament = connection.prepareStatement(sql);
            for(int i=0;i<list.size();i++){
                SQLStament.setString(i+1, list.get(i)==null?"":list.get(i).toString());
            }
            set = SQLStament.executeQuery();

            ResultSetMetaData rsmd=set.getMetaData();
            int colCount=rsmd.getColumnCount();
            List<Object> resultList=new ArrayList<Object>();
            if(returnType==0)
            {
                while(set!=null&&set.next())
                {
                    List<Object> temp=new ArrayList<Object>();
                    for(int i=0;i<colCount;i++)
                    {
                        temp.add(set.getObject(i+1));
                    }
                    resultList.add(temp);
                }
            }

            return resultList;
        } 
        catch (SQLException e) 
        {
            log.error( "查询数据库表时发生异常,SQL语句为："+sql, e);         
            return null;
        }
        catch(Exception e)
        {
            log.error( "查询数据库表时发生异常,SQL语句为："+sql, e);         
            return null;
        }
        finally
        {
            DbConnectionNoContainer.closeResultSet(set);
            DbConnectionNoContainer.closeStatement(SQLStament);
            DbConnectionNoContainer.releaseConnection(connection);
        } 
    }


    /**
     * 获取当前可插入表中的记录编号
     * @since 2013.06.25
     * @param table  表名
     * @param idName 记录主键
     * @param baseId 记录基本值
     * @return 对于原来记录主键为按数字型排序的返回当前最大记录+1，如果为-1表示不能获取最大记录
     * */
    public static int getId(String table,String idName,int baseId)
    {
        if(table==null||table.equals("")||idName==null||idName.equals(""))
        {
            return -1;
        }

        Connection connection = null;
        Statement SQLStament = null;
        ResultSet set = null;
        String sql = null;
        try
        {
            sql="SELECT MAX("+idName+") tempvalue FROM "+table;
            connection = DbConnectionNoContainer.getConnection(true);
            SQLStament = connection.createStatement();
            set = SQLStament.executeQuery(sql);
            if(set!=null&&set.next())
            {
                return Integer.parseInt(set.getString("tempvalue"))+1;
            }
            return baseId+1;
        }       
        catch(Exception e)
        {
            log.error( "获取当前可插入表中的记录编号发生异常,SQL语句为："+sql, e);            
            return -1;
        }       
        finally
        {
            DbConnectionNoContainer.closeResultSet(set);
            DbConnectionNoContainer.closeStatement(SQLStament);
            DbConnectionNoContainer.releaseConnection(connection);
        } 
    }

    /**
     * 从数据库查询到的结果集中获取指定列的值
     * @since 2013.06.25
     * @param resultList 查询结果list 
     * @param rowNum 第几条数据 
     * @param colName 列的名称 
     * @return value 列的值 
     **/
    public static Object getValue(List resultList,int rowNum,String colName)
    {
        try
        {
            HashMap<String, Object> tempMap = (HashMap<String, Object>)resultList.get(rowNum);
            return tempMap.get(colName.toUpperCase());
        }
        catch(Exception e)
        {           
            log.error( "从数据库查询到的结果集中获取指定列的值发生异常，结果集size["+resultList.size()+"]请求行["+rowNum+"]请求列"+colName, e);        
            return null;
        }
    }

    /**
     * 从数据库查询到的结果集中获取指定列的值
     * @since 2013.06.25
     * @param resultList 查询结果list 
     * @param rowNum 第几条数据 
     * @param colNum 第几列 
     * @return value 列的值 
     **/
    public static Object getValue(List resultList,int rowNum,int colNum)
    {
        try
        {
            ArrayList<String> tempList = (ArrayList)resultList.get(rowNum);
            return tempList.get(colNum);
        }
        catch(Exception e)
        {
            log.error( "从数据库查询到的结果集中获取指定列的值发生异常，结果集size["+resultList.size()+"]请求行["+rowNum+"]请求列"+colNum, e);     
            return null;
        }
    }

    /**
     * 从数据库查询到的结果集中获取指定列的值
     * @since 2013.06.25
     * @param resultList 查询结果list 
     * @param colNum 第几列 
     * @return value 列的值 
     **/
    public static String getValue(List resultList,int colNum)
    {
        try
        {
            return getValue(resultList,0,colNum)==null?"":getValue(resultList,0,colNum).toString();
        }
        catch(Exception e)
        {
            log.error( "从数据库查询到的结果集中获取指定列的值发生异常，结果集size["+resultList.size()+"]请求列"+colNum, e);      
            return null;
        }
    }

    public static int preparedInsert(String sql,List list)
    {       
        Connection connection = null;
        PreparedStatement SQLStament = null;
        try 
        {           
            connection = DbConnectionNoContainer.getConnection(true);
            SQLStament = connection.prepareStatement(sql);
            for(int i=0;i<list.size();i++)
            {
                SQLStament.setObject(i+1, list.get(i)==null?"":list.get(i));
            }
            return SQLStament.executeUpdate();
        } 
        catch (SQLException e) 
        {
            log.error( "插入数据库表时发生异常,SQL语句为："+sql, e);         
            return -1;
        }
        catch(Exception e)
        {
            log.error( "插入数据库表时发生异常,SQL语句为："+sql, e);         
            return -1;
        }
        finally
        {
            DbConnectionNoContainer.closeStatement(SQLStament);
            DbConnectionNoContainer.releaseConnection(connection);
        } 
    }



    public static int preparedupdate(String sql,List list,String key)
    {       
        Connection connection = null;
        PreparedStatement SQLStament = null;
        try 
        {           
            connection = DbConnectionNoContainer.getConnection(true);
            SQLStament = connection.prepareStatement(sql);
            for(int i=0;i<list.size();i++)
            {
                SQLStament.setObject(i+1, list.get(i)==null?"":list.get(i));
            }
            SQLStament.setObject(list.size()+1,key);
            return SQLStament.executeUpdate();
        } 
        catch (SQLException e) 
        {
            log.error( "更新数据库表时发生异常,SQL语句为："+sql, e);         
            return -1;
        }
        catch(Exception e)
        {
            log.error( "更新数据库表时发生异常,SQL语句为："+sql, e);         
            return -1;
        }
        finally
        {
            DbConnectionNoContainer.closeStatement(SQLStament);
            DbConnectionNoContainer.releaseConnection(connection);
        } 
    }

    //增加提交是多个的更新操作
    public static int preparedupdate(String sql,List list,List keyValue)
    {       
        Connection connection = null;
        PreparedStatement SQLStament = null;
        try 
        {           
            connection = DbConnectionNoContainer.getConnection(true);
            SQLStament = connection.prepareStatement(sql);
            for(int i=0;i<list.size();i++)
            {
                SQLStament.setObject(i+1, list.get(i)==null?"":list.get(i));
            }
            for(int j=0;j<keyValue.size();j++)
            {
                SQLStament.setObject(list.size()+j+1, keyValue.get(j)==null?"":keyValue.get(j));
            }
            return SQLStament.executeUpdate();
        } 
        catch (SQLException e) 
        {
            log.error( "更新数据库表时发生异常,SQL语句为："+sql, e);         
            return -1;
        }
        catch(Exception e)
        {
            log.error( "更新数据库表时发生异常,SQL语句为："+sql, e);         
            return -1;
        }
        finally
        {
            DbConnectionNoContainer.closeStatement(SQLStament);
            DbConnectionNoContainer.releaseConnection(connection);
        } 
    }

    public static int preparedupdate(String sql,String key)
    {       
        Connection connection = null;
        PreparedStatement SQLStament = null;
        try 
        {           
            connection = DbConnectionNoContainer.getConnection(true);
            SQLStament = connection.prepareStatement(sql);
            SQLStament.setString(1,key);
            return SQLStament.executeUpdate();
        } 
        catch (SQLException e) 
        {
            log.error( "更新数据库表时发生异常,SQL语句为："+sql, e);         
            return -1;
        }
        catch(Exception e)
        {
            log.error( "更新数据库表时发生异常,SQL语句为："+sql, e);         
            return -1;
        }
        finally
        {
            DbConnectionNoContainer.closeStatement(SQLStament);
            DbConnectionNoContainer.releaseConnection(connection);
        } 
    }
}


主体代码 4  

package com.broada.spring.DB;



/**
 *通过proxool数据库连接池访问数据库
 *
 *@author wury
 *@version  1.0.0
 *@satement
 * 
 * */

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.logicalcobwebs.proxool.ProxoolDataSource;
import org.logicalcobwebs.proxool.ProxoolException;
import org.logicalcobwebs.proxool.ProxoolFacade;
import org.logicalcobwebs.proxool.admin.SnapshotIF;



import com.broada.spring.utils.SilencerProperties;



public class DbConnectionNoContainer 
{

    private static  SilencerProperties silencerProperties = SilencerProperties.getInstance();
    private static Log log = LogFactory.getLog(DbConnectionNoContainer.class);

    private static ProxoolDataSource pool=null;
    private static String activeCountstr = silencerProperties.getString("activeCount"); 
    private static String dbAlias=silencerProperties.getString("dbAlias");
    private static String dbDriver=silencerProperties.getString("dbDriver");
    private static String dbUrl=silencerProperties.getString("dbUrl");
    private static String dbUser=silencerProperties.getString("dbUser");
    private static String dbPwd=silencerProperties.getString("dbPwd");
    private static String minConnstr=silencerProperties.getString("minConn");
    private static String maxConnstr=silencerProperties.getString("maxConn");
    private static int activeCount =Integer.parseInt(activeCountstr);
    private static int minConn = Integer.parseInt(minConnstr);
    private static int maxConn = Integer.parseInt(maxConnstr);


    /**
     * 创建poxool连接池
     * @author wury
     * @since 2013.06.25
     **/
    static 
    {
        if(!connectionAuth())
        {
            log.error("连接数据库失败!");
        }
    }

    public static boolean connectionAuth()
    {
        try 
        {
            pool=new ProxoolDataSource();
            pool.setAlias(dbAlias);
            pool.setDriver(dbDriver);
            pool.setDriverUrl(dbUrl);
            pool.setUser(dbUser);
            pool.setPassword(dbPwd);
            pool.setDelegateProperties("user="+dbUser+",password="+dbPwd);
            pool.setMinimumConnectionCount(minConn);
            pool.setMaximumConnectionCount(maxConn);    
            pool.setHouseKeepingSleepTime(900000);
            pool.setHouseKeepingTestSql("select 1 from dual");
        }
        catch(Exception e) 
        {           
            log.error( "初始化proxool连接池失败:", e);
            return false;
        }
        return true;
    }

    /**
     * 从数据库连接池中获取一个连接
     * @since 2013.06.25
     * @return 成功:数据库连接;失败:null
     **/
    public static Connection getConnection()
    {
        Connection conn=null;

        try 
        {
            conn=pool.getConnection();
        }
        catch(SQLException e) 
        {
            /*异常重新链接*/
            if(connectionAuth())
            {
                try
                {
                    conn=pool.getConnection();
                }
                catch(SQLException ex)
                {
                    log.error( "重新连接时发生异常", e);
                }               
            }
            log.error( "从数据库连接池中获取一个连接时发生异常:", e);
        } 
        return conn;
    }   

    /**
     * 从数据库连接池中获取一个连接
     * @since 2013.06.25
     * @param autoCommit 是否自动commit autoConmmit
     * @return 成功:数据库连接;失败:null
     **/
    public static Connection getConnection( boolean autoCommit ) 
    {   
        Connection conn = null;
        try 
        {
            conn = getConnection();
            conn.setAutoCommit( autoCommit );
        }
        catch( SQLException e )
        {
            log.error( "从数据库连接池中获取一个连接时发生异常:", e);
        }
        return conn;
    }

    /**
     * 数据库连接rollback
     * @since 2013.06.25
     * @param p_connection 数据库连接
     **/
    public static void rollback(Connection p_connection ) 
    {
        try 
        {
            p_connection.rollback();
        }
        catch( SQLException e )
        {
            log.error( "回滚SQL语句异常:", e);
        }
    }

    /**
     * 数据库连接commit
     * @since 2013.06.25
     * @param p_connection 数据库连接 
     **/
    public static void commit( Connection p_connection ) 
    {
        try 
        {
            p_connection.commit();
        }
        catch( SQLException e )
        {
            log.error( "提交数据库SQL语句异常:", e);
        }
    }

    /**
     * 数据库连接release
     * @since 2013.06.25
     * @param p_connection 数据库连接 
     **/
    public static void releaseConnection( Connection p_connection ) 
    {
        try 
        {
            if( p_connection != null ) 
            {
                p_connection.close();
            }
        }
        catch( SQLException e )
        {
            log.error( "释放数据库连接异常:", e);
        }
    }

    /**
     * 关闭resultset
     * @since 2013.06.25
     * @param a_resultSet 结果集 
     **/
    public static void closeResultSet(ResultSet a_resultSet) 
    {
        try
        {
            if (a_resultSet != null)
            {
                a_resultSet.close();
            }
        } 
        catch (SQLException e) 
        {
            log.error( "关闭数据库查数据集异常:", e);
        }
    }

    /**
     * 关闭数据库操作声明(Statement)
     * @since 2013.06.25
     * @param a_pstmSQL 数据库操作声明 
     **/
    public static void closeStatement(Statement a_pstmSQL)
    {
        try 
        {
            if (a_pstmSQL != null)
            {
                a_pstmSQL.close();
            }
        } 
        catch (SQLException e) 
        {
            log.error( "关闭数据库操作声明异常:", e);
        }
    }    

    /**
     * 获取proxool连接池属性并在后台打印(此函数仅供参考)
     * @since 2013.06.25
     **/      
    public static boolean showSnapshotInfo()
    {           
        try
        {           
            SnapshotIF snapshot = ProxoolFacade.getSnapshot(dbAlias,true); 
            int curActiveCount=snapshot.getActiveConnectionCount();//获得活动连接数 
            int availableCount=snapshot.getAvailableConnectionCount();//获得可得到的连接数 
            int maxCount=snapshot.getMaximumConnectionCount() ;//获得总连接数
            if(curActiveCount!=activeCount)//当活动连接数变化时输出的信息 
            {           
                log.error("active DB connect account:"+curActiveCount+"(active)");
                log.error("available connect account:"+availableCount+"(available)");
                log.error("max connect account:"+maxCount+"(max)");

                activeCount=curActiveCount;           
            }   
            else
            {
                return false;
            }
            return true;
        }
        catch(ProxoolException e)
        {           
            log.error(e.getMessage());     
            return false;
        }           
    } 
}