dbUtils.java(本人使用的是mysql-connector-java 8.0.22)
1 package pers.dbutils;
2
3 import lombok.Data;
4
5 import java.io.IOException;
6 import java.io.InputStream;
7 import java.sql.*;
8 import java.util.*;
9 import java.util.stream.Collectors;
10
11 /**
12 * TODO jdbc常用操作工具类
13 *
14 * @author netyts@163.com
15 * @date 2020/11/6 15:24
16 */
17
18 @Data
19 public class DbUtils {
20 private String driver = "com.mysql.cj.jdbc.Driver";
21 private String url = "jdbc:mysql://localhost:3306/db?serverTimezone=PRC&useUnicode=true&characterEncoding=utf8";
22 private String user = "root";
23 private String password = "";
24 private Connection conn = null;
25 private int currentPage = 1; //当前页
26 private int pageCount = 0; //总页数
27 private int pageSize = 10; //每一页记录的数据量
28 private int recordCount = 0; //总数据量
29
30 public DbUtils() {
31 try {
32 this.conn = DriverManager.getConnection(url, user, password);
33 } catch (SQLException throwables) {
34 throwables.printStackTrace();
35 }
36 }
37
38 public DbUtils(String driver, String host, String port, String dbName, String user, String password) {
39 this.driver = driver;
40 this.url = "jdbc:mysql://" + host + ":" + port + "/" + dbName + "?serverTimezone=PRC&useUnicode=true&characterEncoding=utf8";
41 this.user = user;
42 this.password = password;
43 try {
44 this.conn = DriverManager.getConnection(this.url, this.user, this.password);
45 } catch (SQLException throwables) {
46 throwables.printStackTrace();
47 }
48 }
49
50 //利用db.properties配置文件连接数据库
51 public DbUtils(boolean flag) {
52 InputStream is = DbUtils.class.getClassLoader().getResourceAsStream("db.properties");
53 Properties prop = new Properties();
54 try {
55 prop.load(is);
56 driver = prop.getProperty("db.driver", "com.mysql.cj.jdbc.Driver");
57 url = prop.getProperty("db.url", "jdbc:mysql://localhost:3306/db?serverTimezone=PRC&useUnicode=true&characterEncoding=utf8");
58 user = prop.getProperty("db.user", "root");
59 password = prop.getProperty("db.password", "");
60 pageSize = Integer.parseInt(prop.getProperty("db.pageSize", "10"));
61
62 conn = DriverManager.getConnection(url, user, password);
63 } catch (IOException e) {
64 e.printStackTrace();
65 } catch (SQLException throwables) {
66 throwables.printStackTrace();
67 }
68 }
69
70 /**
71 * 数据库数据语句操作（增、删、改）
72 *
73 * @param sql sql语句
74 * @param params 0个或多个
75 * @return 对数据表产生影响的行数
76 */
77 public int execute(String sql, Object... params) {
78 int row = 0;
79 try {
80 PreparedStatement ps = conn.prepareStatement(sql);
81 int index = 1;
82 for (Object p : params) {
83 ps.setObject(index++, p);
84 }
85 row = ps.executeUpdate();
86 ps.close();
87 } catch (SQLException throwables) {
88 throwables.printStackTrace();
89 }
90 return row;
91 }
92
93 /**
94 * 数据插入
95 *
96 * @param tableName 表名
97 * @param values Map(key,values)键值对
98 * @return 对数据表产生影响的行数
99 */
100 public int insert(String tableName, Map<String, Object> values) {
101 int row = 0;
102 Set<String> set = values.keySet();
103
104 String fn = set.toString().replace(" ", "");
105 fn = fn.substring(1, fn.length() - 1);
106
107 String fv = set.stream().map(m -> "?").collect(Collectors.toList()).toString().replace(" ", "");
108 fv = fv.substring(1, fv.length() - 1);
109
110 String sql = String.format("insert into %s(%s) values(%s)", tableName, fn, fv);
111 try {
112 PreparedStatement ps = conn.prepareStatement(sql);
113 int index = 1;
114 for (String k : set) {
115 ps.setObject(index++, values.get(k));
116 }
117 row = ps.executeUpdate();
118 ps.close();
119 } catch (SQLException throwables) {
120 throwables.printStackTrace();
121 }
122 return row;
123 }
124
125 /**
126 * 数据插入
127 *
128 * @param tableName 表名
129 * @param fieldName 字段名（1个或多个）
130 * @param fieldValues 数据信息（和字段名顺序保持一致）
131 * @return 对数据表产生影响的行数
132 */
133 public int insert(String tableName, String fieldName, Object[] fieldValues) {
134 int row = 0;
135 String fv = Arrays.stream(fieldValues).map(m -> "?").collect(Collectors.toList()).toString().replace(" ", "");
136 fv = fv.substring(1, fv.length() - 1);
137 String sql = String.format("insert into %s(%s) values(%s)", tableName, fieldName, fv);
138 try {
139 PreparedStatement ps = conn.prepareStatement(sql);
140 for (int i = 0; i < fieldValues.length; i++) {
141 ps.setObject(i + 1, fieldValues[i]);
142 }
143 row = ps.executeUpdate();
144 ps.close();
145 } catch (SQLException throwables) {
146 throwables.printStackTrace();
147 }
148 return row;
149 }
150
151 /**
152 * 根据主键删除数据
153 * @param tableName 表名
154 * @param PKValues 主键值（1个或多个）
155 * @return 对数据表产生影响的行数
156 */
157 public int deleteByPK(String tableName, Object... PKValues){
158 int row = 0;
159 Set<Object> set = new HashSet<>();
160 for(Object o : PKValues){
161 set.add(o);
162 }
163 StringBuilder sql = new StringBuilder("delete from "+tableName+" where "+getPK(tableName)+" in(");
164 int index = 1;
165 for(Object o : set){
166 sql.append(o);
167 if(index++ < set.size()) sql.append(",");
168 }
169 sql.append(")");
170 try {
171 PreparedStatement ps = conn.prepareStatement(sql.toString());
172 row = ps.executeUpdate();
173 ps.close();
174 } catch (SQLException throwables) {
175 throwables.printStackTrace();
176 }
177 return row;
178 }
179
180 /**
181 * 根据主键修改数据
182 * @param tableName 表名
183 * @param fieldName 字段名（1个或多个）
184 * @param updateValues 修改后的值
185 * @param PKValues 主键值（1个或多个）
186 * @return 对数据表产生影响的行数
187 */
188 public int updateByPK(String tableName, String fieldName, Object updateValues, Object... PKValues){
189 int row = 0;
190 Set<Object> set = new HashSet<>();
191 for(Object o : PKValues){
192 set.add(o);
193 }
194 StringBuilder sql = new StringBuilder("update "+tableName+" set "+fieldName+"="+updateValues+" where "+getPK(tableName)+" in(");
195 int index = 1;
196 for(Object o : PKValues){
197 sql.append(o);
198 if(index++ < set.size()) sql.append(",");
199 }
200 sql.append(")");
201 try {
202 PreparedStatement ps = conn.prepareStatement(sql.toString());
203 row = ps.executeUpdate();
204 ps.close();
205 } catch (SQLException throwables) {
206 throwables.printStackTrace();
207 }
208 return row;
209 }
210
211 /**
212 * 对某一列（某几个）的值全部增加或减少相同的值
213 *
214 * @param tableName 表名
215 * @param fieldName 字段名（1个或多个）
216 * @param values 要增加或减少的值
217 * @param condition 条件
218 * @return 对数据表产生影响的行数
219 */
220 public int updateInc(String tableName, String fieldName, Object values, String condition) {
221 int row = 0;
222 String sql = String.format("update %s set %2$s=%s+%d %s", tableName, fieldName, values, condition);
223 try {
224 PreparedStatement ps = conn.prepareStatement(sql);
225 row = ps.executeUpdate();
226 ps.close();
227 } catch (SQLException throwables) {
228 throwables.printStackTrace();
229 }
230 return row;
231 }
232
233 /**
234 * 获取主键的字段名
235 * @param tableName 表名
236 * @return 表的主键字段名
237 */
238 public String getPK(String tableName) {
239 String PKName = null;
240 try {
241 DatabaseMetaData dmd = conn.getMetaData();
242 ResultSet rs = dmd.getPrimaryKeys(null, "%", tableName);
243 rs.next();
244 PKName = rs.getString("column_name");
245 rs.close();
246 } catch (SQLException throwables) {
247 throwables.printStackTrace();
248 }
249 // //方法二
250 // String sql = String.format("show index from %s", tableName);
251 // try {
252 // PreparedStatement ps = conn.prepareStatement(sql);
253 // ResultSet rs = ps.executeQuery();
254 // rs.next();
255 // PKName = rs.getString("column_name");
256 // rs.close();
257 // ps.close();
258 // } catch (SQLException throwables) {
259 // throwables.printStackTrace();
260 // }
261 return PKName;
262 }
263
264 /**
265 * 查询操作
266 * @param sql sql语句
267 * @param params 0个或多个
268 * @return list集合
269 */
270 public List<Map<String, Object>> select(String sql, Object... params) {
271 List<Map<String, Object>> list = new LinkedList<>();
272 try {
273 PreparedStatement ps = conn.prepareStatement(sql);
274 int index = 1;
275 for (Object o : params) {
276 ps.setObject(index++, o);
277 }
278 ResultSet rs = ps.executeQuery();
279 ResultSetMetaData rsm = rs.getMetaData();
280 Map<String, Object> m;
281 while (rs.next()) {
282 m = new LinkedHashMap<>();
283 for (int i = 1; i <= rsm.getColumnCount(); i++) {
284 m.put(rsm.getColumnLabel(i), rs.getObject(rsm.getColumnLabel(i)));
285 }
286 list.add(m);
287 }
288 rs.close();
289 ps.close();
290 } catch (SQLException throwables) {
291 throwables.printStackTrace();
292 }
293 return list;
294 }
295
296 /**
297 * 查询操作
298 * @param tableName 表名
299 * @param fieldName 字段名（1个或多个）
300 * @param condition 条件
301 * @return list集合
302 */
303 public List<Map<String, Object>> select(String tableName, String fieldName, String condition) {
304 String sql = String.format("select %s from %s %s", fieldName, tableName, condition);
305 return select(sql);
306 }
307
308 // private int currPage = 1; //当前页
309 // private int pageCount = 0; //总页数
310 // private int pageSize = 10; //每一页的数据量
311 // private int recordCount = 0; //总数据量
312
313 /**
314 * 获取总数据数量
315 * @param tableName 表名
316 * @param condition 条件
317 * @return 总数据量
318 */
319 public int getRecordCount(String tableName, String condition) {
320 String sql = String.format("select count(*) from %s %s", tableName, condition);
321 try {
322 PreparedStatement ps = conn.prepareStatement(sql);
323 ResultSet rs = ps.executeQuery();
324 rs.next();
325 recordCount = rs.getInt(1);
326 rs.close();
327 ps.close();
328 } catch (SQLException throwables) {
329 throwables.printStackTrace();
330 }
331 return recordCount;
332 }
333
334 /**
335 * 获取总页数
336 * @param tableName 表名
337 * @param pageSize 每一页记录的数据量
338 * @param condition 条件
339 * @return 总页数
340 */
341 public int getPageCount(String tableName, int pageSize, String condition) {
342 recordCount = getRecordCount(tableName, condition);
343 if (recordCount % pageSize == 0) {
344 pageCount = recordCount / pageSize;
345 } else {
346 pageCount = recordCount / pageSize + 1;
347 }
348 return pageCount;
349 }
350
351 public int getPageCount(String tableName, String where) {
352 return getPageCount(tableName, pageSize, where);
353 }
354
355 /**
356 * 查看某一页的数据
357 * @param tableName 表名
358 * @param fieldName 字段名（1个或多个）
359 * @param currentPage 选择要查看的页数（当前页）
360 * @param pageSize 每页记录的数据量
361 * @param condition 条件
362 * @return list集合
363 */
364 public List<Map<String, Object>> page(String tableName, String fieldName, int currentPage, int pageSize, String condition) {
365 pageCount = getPageCount(tableName, pageSize, condition);
366 List<Map<String, Object>> list = new LinkedList<>();
367 if (currentPage <= pageCount) {
368 int cp = pageSize * (currentPage - 1);
369 String sql = String.format("select %s from %s limit %d,%d %s", fieldName, tableName, cp, pageSize, condition);
370 System.out.println(sql);
371 try {
372 PreparedStatement ps = conn.prepareStatement(sql);
373 ResultSet rs = ps.executeQuery();
374 ResultSetMetaData rsm = rs.getMetaData();
375 Map<String, Object> m;
376 while (rs.next()) {
377 m = new LinkedHashMap<>();
378 for (int i = 1; i <= rsm.getColumnCount(); i++) {
379 m.put(rsm.getColumnLabel(i), rs.getObject(rsm.getColumnLabel(i)));
380 }
381 list.add(m);
382 }
383 rs.close();
384 ps.close();
385 } catch (SQLException throwables) {
386 throwables.printStackTrace();
387 }
388 } else {
389 Map<String, Object> m = new HashMap<>();
390 m.put("error", "页码输入有误！");
391 list.add(m);
392 }
393 return list;
394 }
395
396 /**
397 * 查看某一页的数据
398 * @param tableName 表名
399 * @param fieldName 字段名（1个或多个）
400 * @param currentPage 选择要查看的页数（当前页）
401 * @return list集合
402 */
403 public List<Map<String, Object>> page(String tableName, String fieldName, int currentPage){
404 return page(tableName, fieldName, currentPage, pageSize, "");
405 }
406
407 /**
408 * 导出表数据到txt文件,需要判断secure_file_priv(show variables like "secure_file_priv";)的状态，
409 * 若为null，则是对mysqld的导入、导出做限制，修改my.ini文件添加或修改secure_file_priv="";
410 * @param tableName 表名
411 * @param fieldName 字段名（1个或多个）
412 * @param file 文件路径
413 */
414 public void exportData(String tableName, String fieldName, String file) {
415 String sql = String.format("select %s from %s into outfile '%s'", fieldName,tableName,file);
416 try {
417 PreparedStatement ps = conn.prepareStatement(sql);
418 ResultSet rs = ps.executeQuery();
419 ps.close();
420 rs.close();
421 } catch (SQLException throwables) {
422 throwables.printStackTrace();
423 }
424 }
425
426 /**
427 * 导入表数据，数据库必须有此表的表结构
428 * @param file 文件路径
429 * @param tableName 表名
430 */
431 public void importData(String file, String tableName) {
432 String sql = String.format("load data infile '%s' into table %s",file,tableName);
433 try {
434 PreparedStatement ps = conn.prepareStatement(sql);
435 ResultSet rs = ps.executeQuery();
436 ps.close();
437 rs.close();
438 } catch (SQLException throwables) {
439 throwables.printStackTrace();
440 }
441 }
442
443 public void close() {
444 try {
445 conn.close();
446 } catch (SQLException throwables) {
447 throwables.printStackTrace();
448 }
449 }
450 }
dbUtils.java
db.properties
1 db.driver = com.mysql.sj.jdbc.Driver 2 db.url = jdbc:mysql://localhost:3306/db?serverTimezone=PRC&useUnicode=true&characterEncoding=utf8 3 db.user = root 4 db.password = 5 db.pageSize = 5db.properties