【Java】jdbc数据库操作简单工具包

　　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 = 5

db.properties