JDBC 数据库操作封装类

DB.properties 数据库配置文件

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql:///orcHr
user=root
password=root

JdbcUtils.java JDBC 数据库操作封装类

package cn.demo01.utils;

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

/**
 * @Author Jiafang Wen
 * @Date 2023-10-28 12:04
 * @Version 1.0
 */

public class JdbcUtils<T> {
    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    public static Connection conn = null;
    public static PreparedStatement ps = null;
    public static PreparedStatement ps1 = null;
    public static PreparedStatement ps2 = null;
    public static ResultSet rs = null;

    static {
        // 读取文件

        try {
            // 创建Properties集合
            Properties pro = new Properties();
            // 加载文件
            pro.load(new FileReader("src/resources/DB.properties"));
            // 获取属性
            driver = pro.getProperty("driver");
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            // 注册驱动
            Class.forName(driver);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    // 获取数据库连接
    public static Connection getConnection() throws Exception {
        return DriverManager.getConnection(url, user, password);
    }

    // 执行select 语句
    public static List getRsList(String sql) {
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            List rsList = convertList(rs);
            return rsList;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            close(rs, ps, conn);
        }
    }

    // Resultset 结果集转为List数据
    private static List convertList(ResultSet rs) throws SQLException {
        List list = new ArrayList();
        ResultSetMetaData md = rs.getMetaData();
        int columnCount = md.getColumnCount();
        while (rs.next()) {
            Map rowData = new HashMap();
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnName(i), rs.getObject(i));
            }
            list.add(rowData);
        }
        return list;
    }

    // 执行insert、delete、update语句
    public static boolean execute(String sql) {
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            System.out.println("日志：执行语句" + sql);
            if (conn != null && ps != null && ps.executeUpdate() != 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            close(rs, ps, conn);
        }
    }

    public static boolean execute(String sql1, String sql2) {
        try {
            conn = getConnection();
            conn.setAutoCommit(false);
            ps1 = conn.prepareStatement(sql1);
            ps2 = conn.prepareStatement(sql2);
            System.out.println("日志：执行语句" + sql1 + ";" + sql2);
            if (conn != null && ps1 != null && ps2 != null && (sql1 != null | sql1 != "") && (sql2 != null | sql2 != "") && ps1.executeUpdate() != 0 && ps2.executeUpdate() != 0) {
                conn.commit();
                return true;
            } else {
                conn.rollback();
                return false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException ex) {
                e.printStackTrace();
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException ex) {
                e.printStackTrace();
            }
            return false;
        } finally {
            close(rs, ps1, conn);
            close(rs, ps2, conn);
        }

    }
  
    public static void close(ResultSet rs, PreparedStatement ps, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

测试类

public class TestEmp {
    public static void main(String[] args) {
        String sql = "select * from emp ";
        // String sql="update emp set sal=1230 where id=5";
        // String sql = "insert emp (empno,name,job,mgr,hiredate,sal,comm,deptno)
        // values(17935,'Jiafang Wen','ANALYST',7566,'2021-06-08',13000,2000,30)";
        // String sql ="delete from emp where id=14";
        String sql1 = "Update emp set sal =sal-500 where empNo=17499";
        String sql2 = "Update emp set sal =sal+500 where empNo=17521";
        // boolean execute1 = JdbcUtils.execute(sql);
        boolean execute2 = JdbcUtils.execute(sql1,sql2);
        System.out.println(execute2);

        List empList = JdbcUtils.getRsList(sql);
        for (int i = 0; i < empList.size(); i++) {
            System.out.println(empList.get(i));
        }

    }
}