博客

在看视频学习过程中，看到有老师自己封装的数据库工具类，感觉用起来很方便；自己也跟着写了一个，以后再使用JDBC时可直接使用。

import java.sql.*;

/**
 * JDBC的工具类
 */

public class DBUtil {

    private static Connection conn = null;
    private static PreparedStatement pstmt = null;
    private static ResultSet rs = null;
    private DBUtil(){
    }
    
    /**
     * 创建一个连接
     */
    public static Connection getConnection() {
        String url = "jdbc:mysql:// localhost:3306/test?serverTimezone=GMT%2B8";
        try {
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //建立连接
            conn = DriverManager.getConnection(url, "root", "123456");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 关闭数据库资源
     */
    public static void closeAll() {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 执行insert、update、delelte 三个DML操作
     * 传入参数sql语句，以及sql语句中占位符对应的数据
     */
    public  static int executeUpdate(String sql, Object[] prams){
        conn = getConnection();
        int n = 0;
        try {
            pstmt = conn.prepareStatement(sql);
            for (int i=0;i<prams.length;i++){
                pstmt.setObject(i+1,prams[i]);
            }
            n=pstmt.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return n;
    }

    /**
     * 执行查询所返回的Resultset对象
     */
    public static ResultSet executeQuery(String sql, Object[] prams){
        conn = getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            for (int i=0;i<prams.length;i++){
                pstmt.setObject(i+1,prams[i]);
            }
            rs = pstmt.executeQuery();
        }catch (Exception e){
            e.printStackTrace();
        }
        return rs;
    }
}

增加数据操作：

public int addUser(String uname, String pwd) {
        String sql = "insert into t_user values(default,?,?)";
        Object[] prams = {uname, pwd};
        int ok = DBUtil.executeUpdate(sql, prams);
        DBUtil.closeAll();
        return ok;
    }

删除数据操作:

String sql = "delete from t_user where uid=?";
Object[] prams = {4};
int ok = DBUtil.executeUpdate(sql, prams);
DBUtil.closeAll();

更新数据操作：

String sql = "update t_user set pwd=? where uname=?";
String uname = "lisi";
String pwd = "789";
Object[] prams = {pwd, uname};
int ok = DBUtil.executeUpdate(sql, prams);
DBUtil.closeAll();

查询数据操作：

public User login(String uname, String pwd) {
        String sql = "select * from t_user where uname=? and pwd=?";
        Object[] prams = {uname, pwd};

        ResultSet rs = DBUtil.executeQuery(sql, prams);
        User user = null;
        try {
            while (rs.next()) {
                int uid = rs.getInt("uid");
                String name = rs.getString("uname");
                String pwd2 = rs.getString("pwd");
                user = new User(uid, name, pwd2);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        DBUtil.closeAll();
        return user;
    }