c3p0在javaApp项目中的操作

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


}