JDBC 实现批量插入

批量插入"/>

JDBC 实现批量插入

相关代码

声明常量

static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://数据库域名/库名";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<ProductEntity> products = prodctMapper.selectAllInfos(model.getFromUserId());products.forEach(record -> record.setUserId(Long.valueOf(model.getToUserId())));batchInsert(products, "product_record",ProductEntity.class);
}

说明

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