c3p0在javaApp项目中的操作

ゝ一纸荒年。 2024-02-17 22:36 26阅读 0赞

1. c3p0-config.properties配置文件

jdbcUrl=jdbc:mysql://192.168.1.2:3306/imsi  
driverClass=com.mysql.jdbc.Driver  
user=imsi  
password=imsi  
\#连接池中保留的最小连接数  
minPoolSize=100  
\#连接池中保留的最大连接数。Default: 15  
maxPoolSize=1000  
\#初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间。Default: 3  
initialPoolSize=20  
\#最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0  
maxIdleTime=1800  
\#当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3  
acquireIncrement=10  
\#连接关闭时默认将所有未提交的操作回滚。Default: false  
autoCommitOnClose=false  
\#JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements  
\#属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。  
\#如果maxStatements与maxStatementsPerConnection均为0，则缓存被关闭。Default: 0  
maxStatements=0  
\#maxStatementsPerConnection定义了连接池内单个连接所拥有的最大缓存statements数。Default: 0  
maxStatementsPerConnection=0  
\#c3p0是异步操作的，缓慢的JDBC操作通过帮助进程完成。扩展这些操作可以有效的提升性能  
\#通过多线程实现多个操作同时被执行。Default: 3  
numHelperThreads=5  
\#定义在从数据库获取新连接失败后重复尝试的次数。Default: 30  
acquireRetryAttempts=3  
\#两次连接中间隔时间，单位毫秒。Default: 1000  
acquireRetryDelay=1000  
\#如果设为true那么在取得连接的同时将校验连接的有效性。Default: false  
testConnectionOnCheckin=true  
\#c3p0将建一张名为Test的空表，并使用其自带的查询语句进行测试。如果定义了这个参数那么  
\#属性preferredTestQuery将被忽略。你不能在这张Test表上进行任何操作，它将只供c3p0测试  
\#使用。Default: null  
automaticTestTable=c3p0TestTable  
\#每60秒检查所有连接池中的空闲连接。Default: 0  
idleConnectionTestPeriod=3000  
\#当连接池用完时客户端调用getConnection()后等待获取新连接的时间，超时后将抛出  
\#SQLException,如设为0则无限期等待。单位毫秒。Default: 0  
checkoutTimeout=100000

2. C3p0DBUtils.java

/\*\*  
\*  
\*/  
package com.dh.imsi.utils;  
  
  
import java.io.File;  
import java.sql.Connection;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.ResultSetMetaData;  
import java.sql.SQLException;  
import java.util.ArrayList;  
import java.util.HashMap;  
import java.util.List;  
import java.util.Map;  
  
  
import com.mchange.v2.c3p0.ComboPooledDataSource;  
  
  
/\*\*  
\* @title : C3p0DBUtils.java  
  
\* @author : tao  
\* @date : 2017-6-8 下午2:21:20  
\* @version V1.0  
\*/  
public class C3p0DBUtils \{  
  
private Connection conn; // 数据库连接  
private PreparedStatement preStat; // 预编译  
private ResultSet rstSet; // 结果集  
  
private static C3p0DBUtils instance;  
  
  
private ComboPooledDataSource dataSource;  
  
static\{  
instance = new C3p0DBUtils();  
\}  
  
  
private C3p0DBUtils() \{  
try \{  
dataSource = new ComboPooledDataSource();  
/\* Properties prop = new Properties();  
InputStream in = C3p0DBUtils.class.getClassLoader().getResourceAsStream("db.properties");  
prop.load(in);\*/  
  
String path = System.getProperty("user.dir");  
String confPath = path + File.separator + "bin" + File.separator + "c3p0-config.properties";  
Map<String, String> map = PropertiesUtils.readProperties(confPath);  
if (map != null && map.size() > 0) \{  
dataSource.setJdbcUrl(CommonFunc.checkNull(map.get("jdbcUrl")));  
dataSource.setDriverClass(CommonFunc.checkNull(map  
.get("driverClass")));  
dataSource.setUser(CommonFunc.checkNull(map.get("user")));  
dataSource  
.setPassword(CommonFunc.checkNull(map.get("password")));  
dataSource.setMinPoolSize(CommonFunc.checkNum(  
CommonFunc.checkNull(map.get("minPoolSize")), 3));  
dataSource.setMaxPoolSize(CommonFunc.checkNum(  
CommonFunc.checkNull(map.get("maxPoolSize")), 70));  
dataSource.setInitialPoolSize(CommonFunc.checkNum(  
CommonFunc.checkNull(map.get("initialPoolSize")), 3));  
dataSource.setMaxIdleTime(CommonFunc.checkNum(  
CommonFunc.checkNull(map.get("maxIdleTime")), 30));  
dataSource.setAcquireIncrement(CommonFunc.checkNum(  
CommonFunc.checkNull(map.get("acquireIncrement")), 10));  
dataSource.setAutoCommitOnClose(Boolean.parseBoolean(CommonFunc  
.checkNull(map.get("autoCommitOnClose"))));  
dataSource.setMaxStatements(CommonFunc.checkNum(  
CommonFunc.checkNull(map.get("maxStatements")), 75));  
dataSource.setMaxStatementsPerConnection(CommonFunc.checkNum(  
CommonFunc.checkNull(map  
.get("maxStatementsPerConnection")), 100));  
dataSource.setNumHelperThreads(CommonFunc.checkNum(  
CommonFunc.checkNull(map.get("numHelperThreads")), 5));  
dataSource.setAcquireRetryAttempts(CommonFunc.checkNum(  
CommonFunc.checkNull(map.get("acquireRetryAttempts")),  
3));  
dataSource.setAcquireRetryDelay(CommonFunc.checkNum(  
CommonFunc.checkNull(map.get("acquireRetryDelay")),  
2 \* 1000));  
dataSource.setTestConnectionOnCheckin(Boolean  
.parseBoolean(CommonFunc.checkNull(map  
.get("testConnectionOnCheckin"))));  
dataSource.setAutomaticTestTable(CommonFunc.checkNull(map  
.get("automaticTestTable")));  
dataSource.setIdleConnectionTestPeriod(CommonFunc.checkNum(  
CommonFunc.checkNull(map  
.get("idleConnectionTestPeriod")), 30));  
dataSource.setCheckoutTimeout(CommonFunc.checkNum(  
CommonFunc.checkNull(map.get("checkoutTimeout")),  
10 \* 1000));  
\}  
\} catch (Exception e) \{  
e.printStackTrace();  
\}  
\}  
  
public static C3p0DBUtils getInstance()\{  
return instance;  
\}  
  
public void getConnection() throws SQLException \{  
conn = dataSource.getConnection();  
\}  
  
/\*\*  
\* 释放  
\* @param flag  
\*/  
public void freeAll(boolean flag) \{  
try \{  
if (flag && conn != null) \{  
conn.commit();  
\} else if (conn != null) \{  
conn.rollback();  
\}  
if (rstSet != null) \{  
rstSet.close();  
rstSet = null;  
\}  
if (preStat != null) \{  
preStat.close();  
preStat = null;  
\}  
if (conn != null) \{  
conn.close();  
conn = null;  
\}  
\} catch (Exception exception1) \{  
\}  
\}  
  
/\*\*  
\* 查询  
\* @param sql  
\* @param values  
\* @return  
\*/  
public List<Map<String, Object>> query(String sql, Object\[\] values) \{  
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();  
try \{  
if (conn == null) \{  
C3p0DBUtils.getInstance().getConnection();  
\}  
// 初始化PreparedStatement  
preStat = conn.prepareStatement(sql);  
for (int i = 0; i < values.length; i++) \{  
preStat.setObject((i + 1), values\[i\]);  
\}  
rstSet = preStat.executeQuery();  
/\*  
\* 遍历结果集  
\*/  
if (rstSet != null) \{  
ResultSetMetaData rsmd = rstSet.getMetaData();  
int columnCount = rsmd.getColumnCount(); // 列数  
while (rstSet.next()) \{  
Map<String, Object> map = new HashMap<String, Object>();  
/\*  
\* 根据列数遍历  
\*/  
for (int j = 1; j < columnCount + 1; j++) \{  
Object obj = rstSet.getObject(j);  
if (obj != null) \{  
map.put(rsmd.getColumnName(j).toLowerCase(), obj);  
\} else \{  
map.put(rsmd.getColumnName(j).toLowerCase(), null);  
\}  
\}  
list.add(map);  
\}  
\}  
\} catch (Exception e) \{  
e.printStackTrace();  
\} finally \{  
freeAll(false);  
\}  
return list;  
\}  
  
  
public static void main(String\[\] args)\{  
try \{  
String sql = "select \* from sys\_user where USERACC=? ";  
Object\[\] values = new Object\[\]\{"00000000"\};  
List<Map<String, Object>> list = C3p0DBUtils.getInstance().query(sql, values);  
Map<String, Object> map = list.get(0);  
System.out.println("useracc = " + CommonFunc.checkNull(map.get("useracc")));  
  
\} catch (Exception e) \{  
e.printStackTrace();  
\}  
\}  
  
\}

3. PropertiesUtils.java

/\*\*  
\*  
\*/  
package com.dh.imsi.utils;  
  
  
import java.io.BufferedInputStream;  
import java.io.File;  
import java.io.FileInputStream;  
import java.io.FileOutputStream;  
import java.io.IOException;  
import java.io.InputStream;  
import java.io.OutputStream;  
import java.util.Enumeration;  
import java.util.HashMap;  
import java.util.Iterator;  
import java.util.Map;  
import java.util.Properties;  
import java.util.Set;  
  
  
  
  
/\*\*  
\* @title : PropertiesUtils.java  
  
\* @author : tao  
\* @date : 2017-6-8 上午10:03:22  
\* @version V1.0  
\*/  
public class PropertiesUtils \{  
  
  
/\*\*  
\* 读取Properties文件中key的值  
\* @param filePath  
\* @param key  
\* @return  
\*/  
public static String readValue(String filePath, String key) \{  
Properties props = new Properties();  
String value = "";  
try \{  
File file = new File(filePath);  
if (file.exists()) \{  
InputStream in = new BufferedInputStream(new FileInputStream(  
filePath));  
props.load(in);  
value = props.getProperty(key);  
// System.out.println(key+"="+value);  
in.close(); // 关闭流  
\}  
\} catch (Exception e) \{  
e.printStackTrace();  
\}  
return value;  
\}  
  
  
/\*\*  
\* 读取Properties文件中key的值  
\* @param props  
\* @param key  
\* @return  
\*/  
public static String readValue(Properties props, String key) \{  
String value = "";  
try \{  
if (props != null) \{  
value = CommonFunc.checkNull(props.getProperty(key));  
// System.out.println(key+"="+value);  
\}  
\} catch (Exception e) \{  
e.printStackTrace();  
\}  
return value;  
\}  
  
  
/\*\*  
\*  
\* @param filePath  
\* @return  
\*/  
@SuppressWarnings("rawtypes")  
public static Map<String, String> readProperties(String filePath) \{  
Properties props = null;  
HashMap<String, String> map = new HashMap<String, String>();  
try \{  
File file = new File(filePath);  
if (file.exists()) \{  
props = new Properties();  
InputStream in = new BufferedInputStream(new FileInputStream(  
filePath));  
props.load(in);  
Enumeration en = props.propertyNames();  
while (en.hasMoreElements()) \{  
String key = (String) en.nextElement();  
String value = props.getProperty(key);  
map.put(key, CommonFunc.checkNull(value));  
// System.out.println(key+Property);  
\}  
in.close();  
\}  
\} catch (Exception e) \{  
e.printStackTrace();  
\}  
return map;  
\}  
  
  
/\*\*  
\* 向Properties配置文件中写数据  
\* @param filePath  
\* @param map  
\*/  
public static void writeProperties(String filePath,  
HashMap<String, String> map) \{  
Properties prop = new Properties();  
Set<Map.Entry<String, String>> set = map.entrySet();  
Iterator<Map.Entry<String, String>> it = set.iterator();  
try \{  
InputStream fis = new FileInputStream(filePath);  
prop.load(fis);  
OutputStream fos = new FileOutputStream(filePath);  
while (it.hasNext()) \{  
Map.Entry<String, String> me = it.next();  
prop.setProperty(me.getKey(), me.getValue());  
prop.store(fos, "Update '" + me.getKey() + "' value");  
\}  
\} catch (IOException e) \{  
// System.err.println("Visit "+filePath+" for updating "+parameterName+" value error");  
\}  
\}  
  
\}

4. CommonFunc.java

/\*\*  
\*  
\*/  
package com.dh.imsi.utils;  
  
  
/\*\*  
\* @title : CommonFunc.java  
\* @description : \[接口/类的作用，示例：\*\*\*功能模块服务接口\]  
\* @Company : 杭州达恒科技有限公司  
\* @author : tao  
\* @date : 2017-6-8 上午10:05:18  
\* @version V1.0  
\*/  
public class CommonFunc \{  
  
  
/\*\*  
\* 判空字符串  
\*  
\* @param obj  
\* @return  
\*/  
public static String checkNull(Object obj) \{  
if (obj == null) \{  
return "";  
\}  
return obj.toString().trim();  
\}  
  
/\*\*  
\* 判断字符串是否为大于等于0的整数  
\* @param str 待操作的字符串  
\* @param defaultVal 默认值  
\* @return  
\*/  
public static int checkNum(String str, int defaultVal)\{  
if("".equals(checkNull(str)))\{  
return defaultVal;  
\}  
int rstVal = defaultVal;  
String pattern = "^\[0-9\]+$"; // 匹配大于等于0的整数  
if(str.matches(pattern))\{  
rstVal = Integer.parseInt(str);  
\}  
return rstVal;  
\}  
  
public static void main(String\[\] args)\{  
String str = "a123sf";  
System.out.println("str == " + checkNum(str, -1));  
\}  
  
  
\}