可能是史上最强的 jdbc DBManager

秒速五厘米 2022-06-07 07:18 243阅读 0赞

package com.lb.util;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.ResultSetMetaData;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.HashMap;
    import java.util.LinkedList;
    import java.util.List;
    import java.util.Map;
    
    public class DBManagerV1 { 
    
        private String userName;
        private String password;
        private String url;
        private String driver;
    
        public DBManagerV1(String userName, String password, String url, String driver) {
            this.userName = userName;
            this.password = password;
            this.url = url;
            this.driver = driver;
        }
    
        public Connection getConnection() {
            Connection conn = null;
    
            try {
                Class.forName(driver);
                conn = DriverManager.getConnection(url, userName, password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    
            return conn;
        }
    
        public void close(ResultSet rs, Statement statement, Connection conn) {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    
        public 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();
                }
            }
        }
    
        /** * 执行一条sql ( 没有使用预编译, 有sql 注入风险) * @param sql */
        public void execute(String sql) {
            Connection conn = null;
            Statement statement = null;
            conn = getConnection();
            try {
                statement = conn.createStatement();
                statement.execute(sql);
            } catch (SQLException e) {
                System.out.println(sql);
                System.out.println("出错了");
                e.printStackTrace();
                if (conn != null) {
                    // 事务回滚
                    try {
                        conn.rollback();
                    } catch (SQLException e1) {
                        e1.printStackTrace();
                    }
                }
            } finally {
                close(null, statement, conn);
            }
        }
    
        /** * 批量执行多条 sqls, 一条失败全部回滚 ( 没有使用预编译, 有sql 注入风险) */
        public void executesBantch(List<String> sqls) {
            Connection conn = null;
            Statement statement = null;
            conn = getConnection();
            // 禁用自动提交
            try {
                statement = conn.createStatement();
                conn.setAutoCommit(false);
    
                for (String sql : sqls) {
                    statement.addBatch(sql);
                }
    
                statement.executeBatch();
    
                // 提交事务
                conn.commit();
                conn.setAutoCommit(true);
            } catch (SQLException e) {
                for (String sql : sqls) {
                    System.out.println(sql);
                }
                System.out.println("出错了");
    
                e.printStackTrace();
                if (conn != null) {
                    // 事务回滚
                    try {
                        conn.rollback();
                    } catch (SQLException e1) {
                        e1.printStackTrace();
                    }
                }
            } finally {
                close(null, statement, conn);
            }
        }
    
        /** * @param querySql 查询语句 * @return Map<String, Object> </br> * * key : 列名</br> * value : 列值 * */
        public List<Map<String, Object>> query(String querySql) {
            Connection conn = null;
            PreparedStatement ps = null;
            conn = getConnection();
            try {
                ps = conn.prepareStatement(querySql);
                ResultSet rs = ps.executeQuery();
                List<Map<String, Object>> objMapList = new LinkedList<>();
                while (rs.next()) {
                    Map<String, Object> objMap = new HashMap<String, Object>();
                    ResultSetMetaData rsm  = rs.getMetaData(); //获得列集
                    int colCount = rsm.getColumnCount();   //获得列的个数
                    for (int i = 0; i < colCount; i++) {
                       String colName = rsm.getColumnName( i + 1 );
                       Object colValue = rs.getObject(colName);
                       objMap.put(colName, colValue);
                    }
                    objMapList.add(objMap);
                }
    
                return objMapList;
            } catch (SQLException e) {
                e.printStackTrace();
            }
    
            return null;
        }
    
        /** * 预编译的批量执行 （推荐） * * @param paramsList 参数列表 * @param sqlTemplate sql 模板 * @throws RuntimeException */
        public void bantchExecute(List<Object[]> paramsList, String sqlTemplate) throws RuntimeException {
            Connection conn = null;
            PreparedStatement ps = null;
    
            try {
                conn = getConnection();
                conn.setAutoCommit(false);
                ps = conn.prepareStatement(sqlTemplate);
    
                for (Object[] params : paramsList) {
                    for (int i = 0; i < params.length; i++) {
                        ps.setObject(i + 1, params[i]);
                    }
                    ps.addBatch();
                }
                ps.executeBatch();
    
                // 提交事务
                conn.commit();
            } catch (SQLException e) {
                e.printStackTrace();
                if (conn != null) {
                    // 事务回滚
                    try {
                        conn.rollback();
                        throw new RuntimeException();
                    } catch (SQLException e1) {
                        e1.printStackTrace();
                    }
                }
            } finally {
                close(null, ps, conn);
            }
        }
    
        /** * 推荐使用， 安全高效 * @param sqlMaps</br> * List<Object[]> paramList * String sqlTemplate * @throws RuntimeException */
        public void bantchExecute(LinkedHashMap<List<Object[]>, String> sqlMaps) throws RuntimeException {
            Connection conn = null;
            PreparedStatement ps = null;
    
            try {
                conn = getConnection();
                for (Entry<List<Object[]>, String> sqlMap : sqlMaps.entrySet()) {
                    conn.setAutoCommit(false);
                    List<Object[]> paramList = sqlMap.getKey();
                    String sqlTemplate = sqlMap.getValue();
                    ps = conn.prepareStatement(sqlTemplate);
    
                    for (Object[] params : paramList) {
                        for (int i = 0; i < params.length; i++) {
                            ps.setObject(i + 1, params[i]);
                        }
                        ps.addBatch();
                    }
    
                    ps.executeBatch();
                    conn.commit();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                if (conn != null) {
                    // 事务回滚
                    try {
                        conn.rollback();
                        throw new RuntimeException();
                    } catch (SQLException e1) {
                        e1.printStackTrace();
                    }
                }
            } finally {
                close(null, ps, conn);
            }
        }
    }