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