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