Java实现MySQL批量新增

注意：Oracle与MySQL不同，该方法在Oracle无效，并且会报ORA-00933:SQL命令未正确结束
首先创建两个自定义注解

@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TableName {
    String value() default "";
}

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Column {
    String value() default "";
}

然后，在Bean上面的使用

@TableName("USER")
public class User{
    @Column("IID")
    private String iid;
    @Column("NAME")
    private String name;
    // 省略get/set
}

实现：

/**
 * <p>
 * 生成批量新增SQL语句
 * </p>
 *
 * @author lisonglin
 * @version 1.0
 * @since 2022/2/28 17:59
 */
public class SqlTool<T> {
    public static void main(String[] args) {
        // PreparedStatement statement = null;
        List<User> userList = new ArrayList<>();
        User user = new User();
        user.setIid("123");
        user.setName("456");
        zjdspNhTzList.add(zjdspNhTz);
        SqlTool<User> sqlTool = new SqlTool<>();
        String sqlInsert = sqlTool.genSqlInsert(user, userList.size());
        System.out.println(sqlInsert);
        // 执行就可以了
        // statement.executeUpdate(sqlInsert, sqlTool.genColumn(list));
    }
    /**
     * 根据Table对象生成批量新增SQL
     * 生成语句格式：INSERT INTO TABLENAME(FIELD,...) VALUES(?,...),(?,...)
     *
     * @param table Bean对象，映射表名
     * @return 批量新增SQL语句
     */
    public String genSqlInsert(T table, int size) {
        StringBuilder sbField = new StringBuilder();
        StringBuilder sbValue = new StringBuilder();
        Class<?> aClass = table.getClass();
        TableName tableNameDeclared = aClass.getDeclaredAnnotation(TableName.class);
        String tableName = tableNameDeclared.value();
        sbField.append("INSERT INTO ").append(tableName.toUpperCase()).append(" (");
        for (Field field : aClass.getDeclaredFields()) {
            if (field.isAnnotationPresent(Column.class)) {
                Column column = field.getAnnotation(Column.class);
                sbField.append(column.value()).append(",");
            }
        }
        // 根据字段
        String field = sbField.substring(0, sbField.length() - 1);
        String[] columns;
        if (StringUtil.isNotEmpty(field)) {
            columns = field.split(",");
            sbValue.append("(");
            for (int i = 0; i < columns.length; i++) {
                // 拼接SQL语句，生成VALUES('value1','value2') 部分
                if (i == columns.length - 1) {
                    sbValue.append("?");
                } else {
                    sbValue.append("?,");
                }
            }
        }
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < size; i++) {
            sb.append(sbValue);
            if (i == size - 1) {
                sb.append(")");
            } else {
                sb.append("),");
            }
        }
        return field + ") VALUES " + sb;
    }
    /**
     * 批量新增的SQL，匹配新增语句的参数
     *
     * @param jsonData 批量新增对象
     * @return 参数
     */
    public Object[] genColumn(List<T> jsonData) {
        List<Object> param = new ArrayList<>(10);
        Map<String, Object> fieldAndValueMap = new LinkedHashMap<>(10);
        // 循环遍历list对象
        jsonData.forEach(obj -> {
            Class<?> clazz = obj.getClass();
            try {
                for (Field field : clazz.getDeclaredFields()) {
                    field.setAccessible(true);
                    // 获取一个字段的值
                    Object pojoValue = field.get(obj);
                    if (field.isAnnotationPresent(Column.class)) {
                        Column column = field.getAnnotation(Column.class);
                        // 反射获取Column注解的值
                        String columnValue = column.value();
                        fieldAndValueMap.put(columnValue, pojoValue);
                    }
                    field.setAccessible(false);
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            for (Map.Entry<String, Object> entry : fieldAndValueMap.entrySet()) {
                Object value = entry.getValue();
                param.add(value);
            }
        });
        return param.toArray();
    }
}