JDBC 实现批量插入-任意表名

相关代码

声明常量

static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/your_database_name";
 
static final String USER = "数据库账号";
static final String PASS = "数据库密码";

主方法

public void batchInsert(List records, String tableName,Class className) throws SQLException{
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DB_URL, USER, PASS);
            // 创建预处理语句对象
            StringBuilder columnNames = new StringBuilder();
            StringBuilder placeholders = new StringBuilder();
 
            Field[] fields = className.getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
                String fieldName = fields[i].getName();
                if (fieldName.equals("id")) {
                    continue;
                }
                if (isCamelCase(fieldName)) {
                    fieldName = toUnderscoreCase(fieldName);
                }
                columnNames.append(fieldName);
                placeholders.append("?");
                if (i < fields.length - 1) {
                    columnNames.append(", ");
                    placeholders.append(", ");
                }
            }
            String sql = "INSERT INTO " + tableName + " (" + columnNames.toString() + ") VALUES (" + placeholders.toString() + ")";
            preparedStatement = connection.prepareStatement(sql);
 
            // 添加批量数据
            for (Object entity : records) {
                setFieldValues(entity, preparedStatement);
                preparedStatement.addBatch();
            }
 
            // 执行批量插入
            int[] result = preparedStatement.executeBatch();
 
            // 输出结果
            for (int i = 0; i < result.length; i++) {
                log.error("插入结果：" + result[i]);
            }
            log.error("插入结果数量{}", result.length);
 
        } catch (Exception e) {
            log.error("插入结果：", e);
        } finally {
            // 关闭资源
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
    }

设置属性值

 private static void setFieldValues(Object obj, PreparedStatement preparedStatement) throws IllegalAccessException, SQLException {
        Field[] fields = obj.getClass().getDeclaredFields();
 
        fields = Arrays.stream(fields)
                .filter(field -> !field.getName().equals("id")).toArray(Field[]::new);
 
        for (int i = 0; i < fields.length; i++) {
 
            fields[i].setAccessible(true);
 
            preparedStatement.setObject(i + 1, fields[i].get(obj));
        }
    }

是否为小驼峰

 private static boolean isCamelCase(String str) {
         if(str != null && !str.isEmpty()){
             String regex = "^[a-z]+([A-Z][a-z0-9]*)*$";
             return str.matches(regex);
         }
        return false;
    }

小驼峰转为下划线

 private static String toUnderscoreCase(String str) {
        StringBuilder result = new StringBuilder();
        for (int i = 0; i < str.length(); i++) {
            char c = str.charAt(i);
            if (Character.isUpperCase(c)) {
                if (i > 0) {
                    result.append('_');
                }
                result.append(Character.toLowerCase(c));
            } else {
                result.append(c);
            }
        }
        return result.toString();
    }

实体

@Data
public class ProductEntity implements Serializable {
    /**
     * 主键id
     */
    private BigInteger id;
    /**
     * 用户id
     */
    private String userId;
   /**
     * 商品名称
     */
    private String productName;
 
}

调用方法

public void copyData(UserDataModel model) throws SQLException {
	
        List products = prodctMapper.selectAllInfos(model.getFromUserId());
        products.forEach(record -> record.setUserId(Long.valueOf(model.getToUserId())));
        batchInsert(products, "product_record",ProductEntity.class);
}

说明

如果实体属性与数据库字段一致，不需要做驼峰转下划线这一操作，否则就需要进行转化。

补充

如果数据库内容有表情的时候，确保你的Java程序使用正确的字符集连接MySQL数据库。在JDBC连接字符串中添加&useUnicode=true&characterEncoding=UTF-8参数，例如： 

String url = "jdbc:mysql://localhost:3306/your_database_name?useUnicode=true&characterEncoding=UTF-8";