jdbc工具类

jdbc 工具类，具体见下面代码，直接可以用。

/**
 * @version 1.0
 * @descpription: jdbc工具类
 * @date 2024/4/6
 */
public class JDBCUtils {

    private static final String URL = "jdbc:mysql://127.0.0.1:3306/mybatis";
    private static final String USER = "root";
    private static final String PASSWORD = "root";
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    /**
     * 批量插入的最大数量
     */
    private static final int BATCH_SIZE = 2;
    
    private static final String  INSERT_SQL = "INSERT INTO user (name, age) VALUES (?, ?)";
    private static final String  UPDATE_SQL = "UPDATE user SET name=?, age=? WHERE id=?";
    private static final String DELETE_SQL = "DELETE FROM user WHERE id=?";
    public static final String SELECT_SQL = "SELECT * FROM user WHERE id=?";

    /**
     *  获取数据库连接
     * @return
     * @throws Exception
     */
    public static Connection getConnection() throws Exception {
        //注册驱动
        Class.forName(DRIVER);
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    /**
     *  关闭数据库连接
     * @param connection
     */
    public static void closeConnection(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 关闭closeResultSet
     * @param resultSet
     */
    public static void closeResultSet(ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     *   关闭PreparedStatement
     * @param preparedStatement
     */
    public static void closeStatement(PreparedStatement preparedStatement){
        try {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 新增
     * @param paramMap
     */
    public static void insert(Map<String,Object> paramMap){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(INSERT_SQL);
            preparedStatement.setString(1, (String) paramMap.get("name"));
            preparedStatement.setInt(2, paramMap.get("age") == null ? 0 : (int) paramMap.get("age"));
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.closeConnection(connection);
            JDBCUtils.closeStatement(preparedStatement);
        }
    }

    /**
     *  批量新增
     * @param paramMapList
     * @param size
     */
    public static void batchInsert(List<Map<String,Object>> paramMapList,int size){
        // 批量插入
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        long start = System.currentTimeMillis();
        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(INSERT_SQL);
            //设置手动提交，关闭自动提交
            connection.setAutoCommit(false);
            for (int i = 1; i <= size; i++) {
                Map<String, Object> paramMap = paramMapList.get(i-1);
                preparedStatement.setString(1, (String) paramMap.get("name"));
                preparedStatement.setInt(2, paramMap.get("age") == null ? 0 : (int) paramMap.get("age"));

                // 添加到批处理中
                preparedStatement.addBatch();

                if (i % BATCH_SIZE == 0) {
                    // 执行批处理
                    preparedStatement.executeBatch();
                    // 提交事务
                    connection.commit();
                    // 清空批处理
                    preparedStatement.clearBatch();
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeConnection(connection);
            JDBCUtils.closeStatement(preparedStatement);
            long end = System.currentTimeMillis() - start;
            System.out.println(end);
        }
    }

    /**
     *  删除
     * @param paramMap
     */
    public static void delete(Map<String,Object> paramMap){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(DELETE_SQL);
            preparedStatement.setInt(1, (int) paramMap.get("id"));
            preparedStatement.executeUpdate();
        }catch (Exception e){
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.closeConnection(connection);
            JDBCUtils.closeStatement(preparedStatement);
        }
    }

    /**
     *  更新
     * @param paramMap
     */
    public static void update(Map<String,Object> paramMap){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(UPDATE_SQL);
            preparedStatement.setString(1, (String) paramMap.get("name"));
            preparedStatement.setInt(2, paramMap.get("age") == null ? 0 : (int) paramMap.get("age"));
            preparedStatement.setInt(3, (int) paramMap.get("id"));
            preparedStatement.executeUpdate();
        }catch (Exception e){
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.closeConnection(connection);
            JDBCUtils.closeStatement(preparedStatement);
        }
    }

    /**
     * 根据id查询
     * @param paramMap
     */
    public static void selectById(Map<String,Object> paramMap) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(SELECT_SQL);
            preparedStatement.setInt(1, (int) paramMap.get("id"));
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                System.out.println(id + " " + name + " " + age);
            }
        }catch (Exception e){
            throw new RuntimeException(e);
        } finally {
            // 关闭资源
            JDBCUtils.closeResultSet(resultSet);
            JDBCUtils.closeConnection(connection);
            JDBCUtils.closeStatement(preparedStatement);
        }
    }

    /**
     *  查询所有
     */
    public static void selectAll(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement("SELECT * FROM user ");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                System.out.println(id + " " + name + " " + age);
            }
        }catch (Exception e){
            throw new RuntimeException(e);
        } finally {
            // 关闭资源
            JDBCUtils.closeResultSet(resultSet);
            JDBCUtils.closeConnection(connection);
            JDBCUtils.closeStatement(preparedStatement);
        }
    }
}

/**
 * @descpription:
 * @date 2024/4/2
 */
public class JdbcTest {

    public static void main(String[] args) throws ClassNotFoundException{
//        insertTest();
//        updateTest();
//        deleteTest();
        batchInsertTest();
    }

    public static void batchInsertTest(){
        List<Map<String,Object>> paramMapList = new ArrayList<>();
        Map<String,Object> paramMap = new HashMap<>();
        paramMap.put("name","姜科");
        paramMap.put("age",18);
        Map<String,Object> paramMap2 = new HashMap<>();
        paramMap2.put("name","老朱");
        paramMap2.put("age",20);
        paramMapList.add(paramMap);
        paramMapList.add(paramMap2);

        JDBCUtils.batchInsert(paramMapList,2);
        JDBCUtils.selectAll();
    }
    public static void insertTest(){
        Map<String,Object> paramMap = new HashMap<>();
        paramMap.put("name","测试");
        paramMap.put("age",18);
        JDBCUtils.insert(paramMap);
        JDBCUtils.selectAll();
    }

    public static void updateTest(){
        Map<String,Object> paramMap = new HashMap<>();
        paramMap.put("name","zhangtao");
        paramMap.put("age",100);
        paramMap.put("id",1);
        JDBCUtils.update(paramMap);
        JDBCUtils.selectAll();
    }

    public static void deleteTest(){
        Map<String,Object> paramMap = new HashMap<>();
        paramMap.put("id",9999004);
        JDBCUtils.delete(paramMap);
        JDBCUtils.selectAll();
    }
}