JDBC database operation encapsulation class

DB.properties database configuration file

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

JdbcUtils.java JDBC database operation encapsulation class

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 {<!-- -->
        // read file

        try {<!-- -->
            //Create Properties collection
            Properties pro = new Properties();
            //Load file
            pro.load(new FileReader("src/resources/DB.properties"));
            // Get attributes
            driver = pro.getProperty("driver");
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            //Register driver
            Class.forName(driver);
        } catch (IOException e) {<!-- -->
            throw new RuntimeException(e);
        } catch (ClassNotFoundException e) {<!-- -->
            throw new RuntimeException(e);
        }
    }

    // Get database connection
    public static Connection getConnection() throws Exception {<!-- -->
        return DriverManager.getConnection(url, user, password);
    }

    //Execute select statement
    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 result set is converted to List data
    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;
    }

    //Execute insert, delete, update statements
    public static boolean execute(String sql) {<!-- -->
        try {<!-- -->
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            System.out.println("Log: Execute statement" + sql);
            if (conn != null & amp; & amp; ps != null & amp; & amp; 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("Log: Execute statement" + sql1 + ";" + sql2);
            if (conn != null & amp; & amp; ps1 != null & amp; & amp; ps2 != null & amp; & amp; (sql1 != null | sql1 != "") & amp; & amp; (sql2 != null | sql2 != "") & amp; & amp; ps1.executeUpdate() != 0 & amp; & amp; 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();
            }
        }
    }
}

Test class

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));
        }

    }
}