20220909 JDBC+Mysql通用工具类

JDBC+Mysql通用工具类

• DBUtil

/**
 * 数据库访问通用类
 *
 * @author Administrator
 *
 */
public final class DBUtil {

    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/testdatabase?serverTimezone=Asia/Shanghai";
    private static final String USER = "root";
    private static final String PASSWORD = "root";

    private DBUtil() {
        // TODO Auto-generated constructor stub
    }

    /**
     * 获取连接对象Connection
     *
     * @return java.sql.Connection
     */
    public static Connection getConn() {
        Connection conn = null;
        try {
            Class.forName(DRIVER);
            // 2创建connection
            // 得到连接对象
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return conn;
    }

    /**
     * 通用的增加，删除，修改的代码
     *
     * @param sql    要执行的sql语句
     * @param params 占位符的值，可以用数组来传递或者直接赋值
     * @return int 受影响的行
     */
    public static int exUpdate(String sql, Object... params) {

        Connection conn = null;
        PreparedStatement pstmt = null;
        int result = 0;
        try {
            conn = getConn();

            // 3创建一个PreparedStatement =>增加 删除 修改 完全一致，除了sql语句不同
            pstmt = conn.prepareStatement(sql);
            // 调用设置参数的方法
            setPstmt(pstmt, params);
            // 5 执行sql
            result = pstmt.executeUpdate();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            // 释放资源
            closeAll(pstmt, conn, null);
        }

        return result;

    }

    /**
     * 设置参数,补齐占位符
     *
     * @param pstmt  PreparedStatement
     * @param params 占位符的数据值列表(数组)
     */
    private static void setPstmt(PreparedStatement pstmt, Object... params) {
        // 补齐占位符
        if (params != null) {
            for (int i = 0; i < params.length; i++) {
                try {
                    pstmt.setObject(i + 1, params[i]);
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }

        System.out.println(pstmt);
    }

    /**
     * 释放资源你的方法
     *
     * @param pstmt PreparedStatement 对象
     * @param conn  Connection 连接对象
     * @param rs    ResultSet 结果集对象
     */
    public static void closeAll(PreparedStatement pstmt, Connection conn, ResultSet rs) {
        // 6 释放资源
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     *
     * @param sql
     * @param cls
     * @param params
     * @return
     */
    public static List exQuery(String sql, Class cls, Object... params) {

        List list = new ArrayList();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet result = null;
        try {
            conn = getConn();

            // 3创建一个PreparedStatement =>增加 删除 修改 完全一致，除了sql语句不同
            pstmt = conn.prepareStatement(sql);
            // 调用设置参数的方法
            setPstmt(pstmt, params);
            // 5 执行sql
            result = pstmt.executeQuery();

            while (result.next()) {
                list.add(convert(result, cls));
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            // 释放资源
            closeAll(pstmt, conn, result);
        }

        return list;
    }

    /**
     * 查询的方法
     *
     * @param rs  结果集
     * @param cls Class对象
     * @return 对象
     */
    public static Object convert(ResultSet rs, Class cls) {
        Object obj = null;
        try {
            obj = cls.newInstance();
            ResultSetMetaData rsmd = rs.getMetaData();

            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                String name = rsmd.getColumnLabel(i);
                Object value = rs.getObject(i);

                BeanUtils.setProperty(obj, name, value);
            }

        } catch (SQLException | InstantiationException | IllegalAccessException | InvocationTargetException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return obj;

    }
    /**
     * 记录数查询
     *
     * @param countSQL    查询sql语句 count()
     * @param cls    Object
     * @param params
     * @return long记录数有几条
     */
    public static long getTotalCount(String countSQL, Class cls, Object... params) {
         select count(*) from
        // (select * from news where (newstitle like ? or newscontent like ?) order by
         pubdate desc) as temp

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        long result = 0;
        try {
            // 获取连接对象
            conn = getConn();
            // 创建pstmt对象
            pstmt = conn.prepareStatement(countSQL);
            // parmas当成数组来处理
            setPstmt(pstmt, params);
            // 执行查询的方法
            rs = pstmt.executeQuery();
            // 判断Class -->查询单个值
            if (cls.getName().equals("java.lang.Object")) {
                // 单个值
                if (rs.next()) {
                    result = rs.getLong(1);
                }
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            // 释放资源
            closeAll(pstmt, conn, rs);
        }
        return result;

    }

    /**
     * @func 分页查询
     * @param sql
     * @param cls
     * @param pageNo
     * @param pageSize
     * @param params
     * @return
     */
    public static PageData exQueryByPage(String sql, Class cls, int pageNo, int pageSize, Object... params) {
        // sql ?
        // select * from news where (newstitle like '%测%' or newscontent like '%测%')
        // order by pubdate desc

        // 1 查询记录数
        // select count(*) from
        // (select * from news where (newstitle like '%测%' or newscontent like '%测%')
        // order by pubdate desc) as temp

        String countSQL = "select count(1) from (" + sql + ") as temp";
        long result = getTotalCount(countSQL, Object.class, params);
        // 2 limit
        // select * from news where (newstitle like '%测%' or newscontent like '%测%')
        // order by pubdate desc
        // limit 0,5
        int start = (pageNo - 1) * pageSize;
        sql = sql + " limit " + start + "," + pageSize;

        // list就是存放查询到到结果集
        List list = new ArrayList();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            // 获取连接对象
            conn = getConn();
            // 创建pstmt对象
            pstmt = conn.prepareStatement(sql);
            setPstmt(pstmt, params);
            // 执行查询的方法
            rs = pstmt.executeQuery();
            // 查询多个列 -> 遍历
            while (rs.next()) {
                // 得到一行数据就调用convert方法将列的数据填充到一个对象中，然后返回
                // Object obj ->Type News
                Object obj = convert(rs, cls);
                // 将对象添加到list集合中
                list.add(obj);
            }

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }  finally {
            // 释放资源
            closeAll(pstmt, conn, rs);
        }
        // 构造一个PageData对象
        PageData pd = new PageData(pageNo, pageSize, result, list);
        // 返回结果
        return pd;
    }


}

• PageData

/**
 *
 * @param <T>
 */
public class PageData<T> {
    // 当前页
    private int pageNo;
    // 每页记录数
    private int pageSize;
    // 总记录数
    private long total;
    // 总页数
    private int totalPage;
    // 当前页数据集
    private List<T> data;

    public int getPageNo() {
        return pageNo;
    }

    public void setPageNo(int pageNo) {
        this.pageNo = pageNo;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public long getTotal() {
        return total;
    }

    public void setTotal(long total) {
        this.total = total;
    }

    public int getTotalPage() {
        // 自己来计算一下 10条记录 -》2 11-》3 15-》3
        totalPage = (int) (total / pageSize);
        if (total % pageSize != 0) {
            totalPage++;
        }
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }

    public List<T> getData() {
        return data;
    }

    public void setData(List<T> data) {
        this.data = data;
    }

    public PageData(int pageNo, int pageSize, long total, List<T> data) {
        super();
        this.pageNo = pageNo;
        this.pageSize = pageSize;
        this.total = total;
        this.data = data;
    }

    public PageData(int pageNo, int pageSize, long total, int totalPage, List<T> data) {
        super();
        this.pageNo = pageNo;
        this.pageSize = pageSize;
        this.total = total;
        this.totalPage = totalPage;
        this.data = data;
    }

    public PageData() {
        // TODO Auto-generated constructor stub
    }

    @Override
    public String toString() {
        return "PageData [pageNo=" + pageNo + ", pageSize=" + pageSize + ", total=" + total + ", totalPage=" + getTotalPage()
                + ", data=" + data + "]";
    }

}