涉及表包含 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;
}
}
}