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