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
标签：
