Dbutil封装类实现对MySQL数据库的增删改查及分页

db.properties配置文件
代码如下

db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/db?serverTimezone=PRC&useSSL=false&useUnicode=true&characterEncoding=utf8
db.username=root
db.password=

package com;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

public class Newdbutil {
    private String driver;
    private String url;
    private String username;
    private String password;
    private String dbname;
    private Connection conn;
    private int recordcount; //总记录条数
    private int currpage = 1;//当前页
    private int pagesize = 10;//每页记录条数
    private int pagecount = 1;//总页数


    /**
     * 初始化方法，完成配置文件的读取（driver，URL，username，password）
     */
    public void init() {
        Properties prop = new Properties();
        InputStream is =this.getclass().getClassLoader().getResourceAsStream("db.properties");
        try {
            prop.load(is);
            this.driver = prop.getProperty("db.driver");
            this.url = prop.getProperty("db.url");
            this.username = prop.getProperty("db.username");
            this.password = prop.getProperty("db.password");
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 无参构造方法链接数据库中的test库
     */
    public Newdbutil() {
        this.init();
        try {
            Class.forName(this.driver);
            try {
                this.conn = DriverManager.getConnection(this.url, this.username, this.password);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * <p>功能：实现数据插入操作</p>
     *
     * @param sql  数据库预处理语句，实现插入数据功能
     * @param objs 根据预处理语句填写对应内容，有多少个问号就填几个
     * @return
     */
    public int save(String sql, Object... objs) {
        int result = 0;
        try {
            PreparedStatement pst = this.conn.prepareStatement(sql);
            for (int i = 0; i < objs.length; i++) {
                pst.setObject(i + 1, objs[i]);
            }
            result = pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * <p>功能：实现数据插入操作</p>
     *
     * @param sql    数据库预处理语句，实现插入数据功能
     * @param values 根据预处理语句填写对应内容，有多少个问号数组中就填几个值
     * @return
     */
    public int add(String sql, Object[] values) {
        int result = 0;
        try {
            PreparedStatement pst = this.conn.prepareStatement(sql);
            int indes = 1;
            for (Object obj : values) {
                pst.setObject(indes++, obj);
            }
            result = pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * @param tablename 表名
     * @param values    map集合，key为表的列名，values为添加的值。
     * @return
     */
    public int insert(String tablename, Map<String, Object> values) {
        int result = 0;
        StringBuilder f = new StringBuilder(values.size());
        StringBuilder v = new StringBuilder(values.size());
        Set<String> kset = values.keySet();
        for (String key : kset) {
            f.append(key + ",");
            v.append("?,");
        }
        String sql = String.format("insert into %s(%s) values(%s)", tablename, f.substring(0, f.length() - 1), v.substring(0, v.length() - 1));
        try {
            PreparedStatement pst = this.conn.prepareStatement(sql);
            int index = 1;
            for (String key : kset) {
                pst.setObject(index++, values.get(key));
            }
            result = pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

    /**
     * 功能：返回一个表的主键名
     *
     * @param tablename
     * @return
     */
    public String getPk(String tablename) {
        String pk = null;
        DatabaseMetaData dbmd;
        try {
            dbmd = this.conn.getMetaData();
            ResultSet rs = dbmd.getPrimaryKeys(this.dbname, null, tablename);
            if (rs.next()) {
                pk = rs.getString(4);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pk;
    }

    /**
     * 功能：根据主键删除一条记录
     *
     * @param tablename  表名
     * @param primarykey 要删除记录的主键名
     * @return
     */
    public int delete(String tablename, Object primarykey) {
        int result = 0;
        String sql = String.format("delete from %s where %s = ?", tablename, getPk(tablename));
        try {
            PreparedStatement pst = this.conn.prepareStatement(sql);
            pst.setObject(1, primarykey);
            result = pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * @param tablename
     * @param where     表示删除条件一个条件一个问号
     * @param objs      根据预处理语句填写对应内容，有多少个问号就填几个
     * @return
     */
    public int delete(String tablename, String where, Object... objs) {
        int result = 0;
        String sql = String.format("delete from %s %s", tablename, where);
        try {
            PreparedStatement pst = this.conn.prepareStatement(sql);
            for (int i = 0; i < objs.length; i++) {
                pst.setObject(i + 1, objs[i]);
            }
            result = pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * @param sql  update tablename set name = ? ... where id = ?,
     * @param objs "andy",...,100 根据update语句填写共有几个值
     * @return
     */
    public int update(String sql, Object... objs) {
        int result = 0;
        try {
            PreparedStatement pst = this.conn.prepareStatement(sql);
            for (int i = 0; i < objs.length; i++) {
                pst.setObject(i + 1, objs[i]);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * @param tablename 表名
     * @param m         map集合存放要修改的列名及值
     * @param where     修改的条件
     * @return
     */
    public int update(String tablename, Map<String, Object> m, String where) {
        int result = 0;
        StringBuilder s = new StringBuilder();
        for (String k : m.keySet()) {
            s.append(k + "=?,");
        }
        String sql = String.format("update %s set %s %s", tablename, s.toString().substring(0, s.length() - 1), where);
        try {
            PreparedStatement pst = this.conn.prepareStatement(sql);
            int i = 0;
            for (Object o : m.values()) {
                pst.setObject(++i, o);
            }
            result = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 功能实现修改数据
     *
     * @param tablename 表名
     * @param map       map集合存放要修改的列名及值，和表的主键名和主键值
     * @return
     */
    public int update(String tablename, Map<String, Object> map) {
        int result = 0;
        String pk = getPk(tablename);
        if (map.containsKey(pk)) {
            StringBuilder f = new StringBuilder(map.size());
            Object pkname = map.get(pk);
            map.remove(pk);
            Set<String> kset = map.keySet();
            for (String key : kset) {
                f.append(key + "=?,");
            }
            String sql = String.format("update %s set %s where %s=?", tablename, f.substring(0, f.length() - 1), pk);
            try {
                PreparedStatement pst = this.conn.prepareStatement(sql);
                int index = 1;
                for (String key : kset) {
                    pst.setObject(index++, map.get(key));
                }
                pst.setObject(index, pkname);
                result = pst.executeUpdate();
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return result;
    }


    /**
     * 功能：实现表的查询工作 只能查询一条结果
     *
     * @param sql    sql查询语句// select score name from stu where id = ?,1
     * @param params 条件
     * @return
     */
    public Map<String, Object> queryone(String sql, Object... params) {
        Map<String, Object> map = null;
        try {
            PreparedStatement pst = this.conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i + 1, params[i]);
            }
            ResultSet rs = pst.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            String[] keys = new String[rsmd.getColumnCount()];
            for (int c = 1; c <= rsmd.getColumnCount(); c++) {
                keys[c - 1] = rsmd.getColumnLabel(c);
            }
            map = new HashMap<String, Object>();
            rs.next();
            for (int n = 0; n < keys.length; n++) {
                map.put(keys[n], rs.getObject(keys[n]));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }

    /**
     * 功能：实现表的查询工作 能查询多条结果,返回List<Map<String,Object>>集合。一条结果就是一个map。
     *
     * @param sql  sql查询语句// select score name from stu where id = ?,1
     * @param objs 条件
     * @return
     */
    public List<Map<String, Object>> query(String sql, Object... objs) {
        List<Map<String, Object>> list = null;
        try {
            PreparedStatement pst = this.conn.prepareStatement(sql);
            for (int i = 0; i < objs.length; i++) {
                pst.setObject(i + 1, objs[i]);
            }
            ResultSet rs = pst.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            String[] keys = new String[rsmd.getColumnCount()];
            for (int m = 1; m <= rsmd.getColumnCount(); m++) {
                keys[m - 1] = rsmd.getColumnLabel(m);
            }
            list = new ArrayList<Map<String, Object>>();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int n = 0; n < keys.length; n++) {
                    map.put(keys[n], rs.getObject(keys[n]));
                }
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**   实现分页查询与多条查询类似
     * @param sql  select * from stu where id>?,1
     * @param objs  条件
     * @return
     */
    public List<Map<String, Object>> page(String sql, Object... objs) {
        List<Map<String, Object>> list = null;
        String ccc = "select count(*)" + sql.substring(sql.indexOf("from"));
        try {
            PreparedStatement pst = this.conn.prepareStatement(ccc);
            int index = 1;
            for (Object o : objs) {
                pst.setObject(index++, o);
            }
            ResultSet rs = pst.executeQuery();
            rs.next();
            this.recordcount = rs.getInt(1);//总记录条数
            this.pagecount = this.recordcount % this.pagesize == 0 ? this.recordcount / this.pagesize : this.recordcount / this.pagesize + 1;
            if (this.currpage < 1) this.currpage = 1;
            if (this.currpage > getPagecount()) this.currpage = this.pagecount;

            String psql = sql + " limit ?,?";
            pst = this.conn.prepareStatement(psql);
            index = 1;
            for (Object o : objs) {
                pst.setObject(index++, o);
            }
            pst.setInt(index++, this.currpage * this.pagesize - this.pagesize);
            pst.setInt(index, this.pagesize);
            rs = pst.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();
            list = new ArrayList<Map<String, Object>>();
            while (rs.next()) {
                Map<String, Object> m = new HashMap<String, Object>();
                int cc = rsmd.getColumnCount();
                for (int i = 1; i <= cc; i++) {
                    String name = rsmd.getColumnLabel(i);
                    m.put(name, rs.getObject(name));
                }
                list.add(m);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }


        return list;
    }

    /**
     *   实现分页查询功能
     * @param currpage 当前页（查询的第几页）
     * @param pagesize 每页的记录条数
     * @param tablename 表名
     * @param fields 查询的对象（列名）
     * @param where 查询的条件
     * @return
     */
    public List<Map<String, Object>> page(int currpage, int pagesize, String tablename, String fields, String where) {
        this.currpage = currpage;
        this.pagesize = pagesize;
        String sql = String.format("select %s from %s %s", fields, tablename, where);
        return page(sql);
    }

    public int getRecordcount() {
        return recordcount;
    }

    public void setRecordcount(int recordcount) {
        this.recordcount = recordcount;
    }

    public int getCurrpage() {
        return currpage;
    }

    public void setCurrpage(int currpage) {
        this.currpage = currpage;
    }

    public int getPagesize() {
        return pagesize;
    }

    public void setPagesize(int pagesize) {
        this.pagesize = pagesize;
    }

    public int getPagecount() {
        return pagecount;
    }

    public void setPagecount(int pagecount) {
        this.pagecount = pagecount;
    }


    public String getDriver() {
        return driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getDbname() {
        return dbname;
    }

    public void setDbname(String dbname) {
        this.dbname = dbname;
    }

    public Connection getConn() {
        return conn;
    }

    public void setConn(Connection conn) {
        this.conn = conn;
    }
}