首页 / MYSQL / mysql数据库连接工具类C3P0
mysql数据库连接工具类C3P0
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql数据库连接工具类C3P0，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含18583字，纯文字阅读大概需要27分钟。
内容图文
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
mysql数据库连接工具类C3P0
标签：
本文系统来源：http://www.cnblogs.com/zfy0098/p/5502620.html
内容总结
以上是互联网集市为您收集整理的mysql数据库连接工具类C3P0全部内容，希望文章能够帮你解决mysql数据库连接工具类C3P0所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】