注意:Oracle与MySQL不同,该方法在Oracle无效,并且会报ORA-00933:SQL命令未正确结束
这个SQL命令在Oracle高版本是能够运行的,但是在Java引入的jar包,版本过低,无法运行。
首先创建两个自定义注解
@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();
}
}