mysql数据库连接工具类C3P0
1 package com.dl.network_flow.db; 2 3 import java.sql.Connection; 4 import java.sql.PreparedStatement; 5 import java.sql.ResultSet; 6 import java.sql.ResultSetMetaData; 7 import java.sql.SQLException; 8 import java.sql.Statement; 9 import java.sql.Timestamp; 10 import java.util.ArrayList; 11 import java.util.Date; 12 import java.util.HashMap; 13 import java.util.List; 14 import java.util.Map; 15 16 import org.apache.log4j.Logger; 17 18 /*oracle和mysql的分页区别： 19 * 1、语句的记录起始部分不一样，oralce是“记录查询结果的结束行”和“查询结果的开始行”，mysql是从“记录起始行”和“该页显示的条数” 20 * 2、数据基数：oracle是从1开始，mysql是从0开始 21 * */ 22 public class BaseDao { 23 24 protected Logger log = Logger.getLogger(this.getClass()); 25 26 /** 27 * 执行新增和修改的数据库操作,不用处理返回的ResultSet结果集 28 * 29 * @param sql 30 * sql语句 31 * @param params 32 * 参数，若为日期，需要特别处理 33 * @return 34 */ 35 public int executeSql(String sql, Object[] params) { 36 Connection connection = null; 37 PreparedStatement preparedStatement = null; 38 ResultSet resultSet = null; 39 40 try { 41 connection = ConnectionFactory.getInstance().getConnection(); 42 preparedStatement = connection.prepareStatement(sql); 43 // log.debug("executeSql sql = " + sql); 44 // log.debug("params = " + params); 45 if (params != null) { 46 // 设置sql语句参数 47 for (int i = 0; i < params.length; i++) { 48 // log.debug("params[i] = " + params[i]); 49 if (params[i] != null) { 50 if (params[i] instanceof java.util.Date) { 51 preparedStatement 52 .setTimestamp(i + 1, new Timestamp( 53 ((Date) params[i]).getTime())); 54 } else { 55 preparedStatement.setObject(i + 1, params[i]); 56 } 57 } else { 58 preparedStatement.setString(i + 1, ""); 59 } 60 } 61 } 62 return preparedStatement.executeUpdate(); 63 } catch (SQLException e) { 64 log.error(e.getMessage() + "code = " + e.getErrorCode()+",sql:"+sql); 65 // /throw new RuntimeException(e.getMessage() + "code = " + 66 // e.getErrorCode()); 67 return -1; 68 } finally { 69 ConnectionFactory.getInstance().closeConnection(connection, 70 preparedStatement, resultSet); 71 } 72 73 } 74 75 /* 76 * 批量执行sql语句 paramsArr是个2维数组，第一维度表示各条记录，第二维度表示各条记录里的各个parameter值 77 */ 78 public int[] executeBatchSql(String sql, Object[][] paramsArr) { 79 Connection connection = null; 80 PreparedStatement preparedStatement = null; 81 ResultSet resultSet = null; 82 try { 83 connection = ConnectionFactory.getInstance().getConnection(); 84 preparedStatement = connection.prepareStatement(sql); 85 86 if (paramsArr != null) { 87 for (int s = 0; s < paramsArr.length; s++) { 88 Object[] params = paramsArr[s]; 89 if (params != null) { 90 // 设置sql语句参数 91 for (int i = 0; i < params.length; i++) { 92 if (params[i] != null) { 93 if (params[i] instanceof java.util.Date) { 94 preparedStatement.setTimestamp( 95 i + 1, 96 new Timestamp(((Date) params[i]) 97 .getTime())); 98 } else { 99 preparedStatement.setObject(i + 1, 100 params[i]); 101 } 102 } else { 103 preparedStatement.setString(i + 1, ""); 104 } 105 } 106 preparedStatement.addBatch();// /批量增加1条 107 } 108 } 109 } 110 return preparedStatement.executeBatch();// /批量执行 111 } catch (SQLException e) { 112 e.printStackTrace(); 113 // log.error(e.getMessage() + "code = " + e.getErrorCode()); 114 log.error(e.getMessage() + "code = " + e.getErrorCode()+",sql:"+sql); 115 } finally { 116 ConnectionFactory.getInstance().closeConnection(connection,preparedStatement, resultSet); 117 } 118 return null; 119 } 120 121 /** 122 * 批量执行不同的sql语句 不包含查询 123 * executeBatchSql 124 * @time 2015年9月23日下午4:23:16 125 * @packageName com.dl.ios6 126 * @param sql 多个sql语句的数组 127 * @return 128 */ 129 public int[] executeBatchSql(String[] sql){ 130 Connection connection = null; 131 PreparedStatement preparedStatement = null; 132 ResultSet resultSet = null; 133 134 connection = ConnectionFactory.getInstance().getConnection(); 135 Statement state = null; 136 try { 137 if(sql!=null&&sql.length>0){ 138 boolean autoCommit = connection.getAutoCommit(); 139 connection.setAutoCommit(false); 140 state = connection.createStatement(); 141 for (int i = 0; i < sql.length; i++) { 142 state.addBatch(sql[i]); 143 } 144 int j[] = state.executeBatch(); 145 connection.commit(); 146 connection.setAutoCommit(autoCommit); 147 state.close(); 148 ConnectionFactory.getInstance().closeConnection(connection, preparedStatement, resultSet); 149 return j; 150 } 151 } catch (SQLException e) { 152 state = null; 153 ConnectionFactory.getInstance().closeConnection(connection, preparedStatement, resultSet); 154 } 155 return null; 156 } 157 158 159 /* 160 * 批量执行sql语句 paramsArr是个2维数组，第一维度表示各条记录，第二维度表示各条记录里的各个parameter值 161 */ 162 public int[] executeBatchSql(String sql, List<Object[]> paramsList) { 163 Connection connection = null; 164 PreparedStatement preparedStatement = null; 165 ResultSet resultSet = null; 166 try { 167 168 connection = ConnectionFactory.getInstance().getConnection(); 169 preparedStatement = connection.prepareStatement(sql); 170 171 if (paramsList == null){ 172 return null; 173 } 174 // /遍历所有记录 175 for (int i = 0; i < paramsList.size(); i++) { 176 Object[] tObj = paramsList.get(i); 177 if (tObj == null) { 178 continue; 179 } 180 // /遍历记录中的每个字段 181 for (int j = 0; j < tObj.length; j++) { 182 Object curObj = tObj[j]; 183 if (curObj != null) { 184 if (curObj instanceof java.util.Date) { 185 preparedStatement.setTimestamp(j + 1, 186 new Timestamp(((java.util.Date) curObj).getTime())); 187 } else { 188 preparedStatement.setObject(j + 1, curObj); 189 } 190 } else{ 191 preparedStatement.setString(j + 1, ""); 192 } 193 }// /遍历记录中的每个字段 194 preparedStatement.addBatch();// /添加一条记录 195 }// /遍历所有记录 196 197 return preparedStatement.executeBatch();// /批量执行 198 } catch (SQLException e) { 199 e.printStackTrace(); 200 log.error(e.getMessage() + "code = " + e.getErrorCode()+",sql:"+sql); 201 } finally { 202 ConnectionFactory.getInstance().closeConnection(connection, 203 preparedStatement, resultSet); 204 } 205 return null; 206 } 207 208 /* 209 * 执行sql操作，把sql和params结合成一个sql语句 210 * 执行sql查询的结果集交给sqlExecute这个接口函数处理，处理后封装的对象放到List里 211 */ 212 public List<Map<String, Object>> queryForList(String sql, Object[] params) { 213 Connection connection = null; 214 PreparedStatement preparedStatement = null; 215 ResultSet resultSet = null; 216 217 try { 218 connection = ConnectionFactory.getInstance().getConnection(); 219 preparedStatement = connection.prepareStatement(sql); 220 // 设置sql语句参数 221 if (params != null) { 222 for (int i = 0; i < params.length; i++) { 223 // log.debug("params[i] = " + params[i]); 224 preparedStatement.setObject(i + 1, params[i]); 225 } 226 } 227 resultSet = preparedStatement.executeQuery(); 228 ResultSetMetaData md = resultSet.getMetaData(); // 得到结果集(rs)的结构信息，比如字段数、字段名等 229 int columnCount = md.getColumnCount(); 230 List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); 231 Map<String, Object> rowData = new HashMap<String, Object>(); 232 while (resultSet.next()) { 233 rowData = new HashMap<String, Object>(columnCount); 234 for (int i = 1; i <= columnCount; i++) { 235 rowData.put(md.getColumnLabel(i), resultSet.getObject(i)); 236 } 237 list.add(rowData); 238 } 239 240 return list; 241 } catch (SQLException e) { 242 // log.error(e.getMessage()); 243 log.error(e.getMessage() + "code = " + e.getErrorCode()+",sql:"+sql); 244 } finally { 245 ConnectionFactory.getInstance().closeConnection(connection, 246 preparedStatement, resultSet); 247 } 248 return null; 249 } 250 251 252 public Map<String, Object> queryForMap(String sql, Object[] params) { 253 Connection connection = null; 254 PreparedStatement preparedStatement = null; 255 ResultSet resultSet = null; 256 257 try { 258 connection = ConnectionFactory.getInstance().getConnection(); 259 preparedStatement = connection.prepareStatement(sql); 260 // 设置sql语句参数 261 if (params != null) { 262 for (int i = 0; i < params.length; i++) { 263 // log.debug("params[i] = " + params[i]); 264 preparedStatement.setObject(i + 1, params[i]); 265 } 266 } 267 resultSet = preparedStatement.executeQuery(); 268 ResultSetMetaData md = resultSet.getMetaData(); // 得到结果集(rs)的结构信息，比如字段数、字段名等 269 int columnCount = md.getColumnCount(); 270 Map<String, Object> rowData = new HashMap<String, Object>(); 271 while (resultSet.next()) { 272 rowData = new HashMap<String, Object>(columnCount); 273 for (int i = 1; i <= columnCount; i++) { 274 rowData.put(md.getColumnLabel(i), resultSet.getObject(i)); 275 } 276 break; 277 } 278 279 return rowData; 280 } catch (SQLException e) { 281 log.error(e.getMessage() + "code = " + e.getErrorCode()+",sql:"+sql); 282 } finally { 283 ConnectionFactory.getInstance().closeConnection(connection,preparedStatement, resultSet); 284 } 285 return null; 286 } 287 288 }
1 package com.dl.network_flow.db; 2 3 import java.beans.PropertyVetoException; 4 import java.sql.Connection; 5 import java.sql.PreparedStatement; 6 import java.sql.ResultSet; 7 import java.sql.SQLException; 8 import com.dl.network_flow.utils.PropertyUtils; 9 import com.mchange.v2.c3p0.ComboPooledDataSource; 10 /*使用C3P0连接池 11 * 从C3P0连接池ComboPooledDataSource中获得Connection 12 * 关闭Connection 13 * */ 14 public class ConnectionFactory 15 { 16 17 // private static Logger log = Logger.getLogger(ConnectionFactory.class); 18 19 public static ConnectionFactory connectionFactory = new ConnectionFactory(); 20 21 private ComboPooledDataSource ds; ///C3P0连接池 22 23 ///初始化数据库连接池的参数：5个 24 ///给C3P0连接池指定DriverClass、JdbcUrl、User、Password、InitialPoolSize、MaxPoolSize、CheckoutTimeout 25 private ConnectionFactory() { 26 ds = new ComboPooledDataSource(); 27 try { 28 String DriverClass = PropertyUtils.getValue("DriverClass"); 29 //log.debug("DriverClass = " + DriverClass); 30 if (DriverClass != null) { 31 ds.setDriverClass(DriverClass); 32 } 33 } 34 catch (PropertyVetoException e) { 35 } 36 String JdbcUrl = PropertyUtils.getValue("JdbcUrl"); 37 //log.debug("JdbcUrl = " + JdbcUrl); 38 if (JdbcUrl != null) { 39 ds.setJdbcUrl(JdbcUrl); 40 } 41 42 43 String User = PropertyUtils.getValue("User"); 44 //log.debug("User = " + User); 45 if (User != null) { 46 ds.setUser(User); 47 } 48 49 String Password = PropertyUtils.getValue("Password"); 50 //log.debug("Password = " + Password); 51 if (Password != null) { 52 ds.setPassword(Password); 53 } 54 55 String InitialPoolSize = PropertyUtils.getValue("InitialPoolSize"); 56 //log.debug("InitialPoolSize = " + InitialPoolSize); 57 if (InitialPoolSize != null) { 58 ds.setInitialPoolSize(Integer.parseInt(InitialPoolSize)); 59 } 60 61 String MaxPoolSize = PropertyUtils.getValue("MaxPoolSize"); 62 //log.debug("MaxPoolSize = " + MaxPoolSize); 63 if (MaxPoolSize != null) { 64 ds.setMaxPoolSize(Integer.parseInt(MaxPoolSize)); 65 } 66 67 String CheckoutTimeout = PropertyUtils.getValue("CheckoutTimeout"); 68 //log.debug("CheckoutTimeout = " + CheckoutTimeout); 69 if (CheckoutTimeout != null) { 70 ds.setCheckoutTimeout(Integer.parseInt(CheckoutTimeout)); 71 } 72 73 String MaxIdleTime = PropertyUtils.getValue("MaxIdleTime"); 74 if(MaxIdleTime != null){ 75 ds.setMaxIdleTime(Integer.parseInt(MaxIdleTime)); 76 } 77 78 } 79 80 public static ConnectionFactory getInstance() { 81 return connectionFactory; 82 } 83 84 /** 85 * 获得数据库连接 86 * 87 * @return 88 */ 89 public Connection getConnection() { 90 try { 91 92 return ds.getConnection(); 93 } catch (SQLException e) { 94 throw new RuntimeException(e.getMessage() + "code = " + e.getErrorCode()); 95 } 96 } 97 98 /** 99 * 关闭数据库连接 100 * 101 * @param connection 102 * @param prepareStatement 103 * @param resultSet 104 */ 105 public void closeConnection(Connection connection, PreparedStatement prepareStatement, ResultSet resultSet) { 106 107 try { 108 if (resultSet != null) { 109 resultSet.close(); 110 } 111 if (prepareStatement != null) { 112 prepareStatement.close(); 113 } 114 if (connection != null) { 115 connection.close(); 116 } 117 } catch (SQLException e) { 118 throw new RuntimeException(e.getMessage() + "code = " + e.getErrorCode()); 119 } 120 } 121 122 public static void main(String[] args) 123 { 124 125 } 126 }
1 package com.dl.network_flow.utils; 2 3 4 import java.io.BufferedReader; 5 import java.io.IOException; 6 import java.io.InputStream; 7 import java.io.InputStreamReader; 8 import java.util.Properties; 9 10 /** 11 * Created by IntelliJ IDEA. 12 * User: Administrator 13 * Date: 2010-4-23 14 * Time: 18:02:11 15 * To change this template use File | Settings | File Templates. 16 */ 17 public class PropertyUtils { 18 private Properties pro = new Properties(); 19 private static PropertyUtils propertyUtils = new PropertyUtils(); 20 21 private PropertyUtils() { 22 InputStream in = null; 23 try { 24 // pro.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties")); 25 in = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"); 26 BufferedReader bf = new BufferedReader(new InputStreamReader(in)); 27 pro.load(bf); 28 } catch (IOException e) { 29 throw new RuntimeException(e.getMessage()); 30 } finally { 31 try { 32 if(in!=null){ 33 in.close(); 34 } 35 } catch (IOException e) { 36 } 37 } 38 } 39 40 public static PropertyUtils getInstance(){ 41 return propertyUtils; 42 } 43 44 public static String getValue(String key) { 45 return (String)getInstance().pro.get(key); 46 } 47 48 public static void main(String[] args) throws Exception { 49 System.out.println(getValue("abcd")); 50 } 51 52 }
db.properties 文件内容
#本地
JdbcUrl=jdbc:mysql://IP地址:3306/数据库名称?autoReconnect=true&autoReconnectForPools=true
DriverClass=com.mysql.jdbc.Driver
User=用户名
Password=数据库密码
#初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间。Default: 3 initialPoolSize
InitialPoolSize=15
#连接池中保留的最大连接数。Default: 15 maxPoolSize
MaxPoolSize=30
#sp计费信息实时入库，每次入库的数量
spsynCacheSize=30
#最大空闲时间
MaxIdleTime=120
