1、实现
1.1 实体
package com.xxx;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ListDemo implements Serializable {
private static final long serialVersionUID = -8904669583037473651L;
@MappingAnnotation(primaryKey = true, description = "主键", nullable = false, maxLength = 36, type = "varchar")
private String pk_id;
@MappingAnnotation(description = "年龄", nullable = false, maxLength = 11, type = "Integer")
private int age;
@MappingAnnotation(description = "类型", nullable = false, maxLength = 11, type = "Integer")
private Integer type;
@MappingAnnotation(description = "xxx", nullable = false, maxLength = 36, type = "varchar")
private String file_id_test;
}
1.2 注解
package com.xxx;
import java.lang.annotation.*;
/**
* 实体映射表字段校验注解
* @author copy
*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface MappingAnnotation {
//是否为主键
boolean primaryKey() default false;
//是否可以为空
boolean nullable() default false;
//最大长度
int maxLength() default 0;
//最小长度
int minLength() default 0;
//若type为decimal,小数点后保留几位
int decimal() default 0;
//选择类型 0选择,1输入,2获取
int selType() default 0;
//类型
String type() default "";
//参数或者字段描述,这样能够显示友好的异常信息
String description() default "";
}
1.3 工具类
package com.xxx;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.ReflectionUtils;
import org.sqlite.util.StringUtils;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
/**
* @author copy
*/
@Slf4j
public class DynamicSQLGenerateUtil {
/**
* 生成插入sql
*
* @param list
* @return
*/
public static <T> String generateInsertSQL(String tableName, Class<T> clazz, List<T> list) {
String filesList = null;
String valueList = null;
try {
filesList = createFieldList(clazz);
valueList = getBeanValueList(clazz, list);
} catch (Exception e) {
log.error(String.format("生成插入sql时发生异常,异常信息:%s", e.getMessage()), e.fillInStackTrace());
return null;
}
return "REPLACE INTO " + tableName + filesList + " VALUES " + valueList + ";";
}
/**
* 生成建表语句-DDl
*
* @param tableName 表名
* @param clazz 表关联的实体的类型
* @return
*/
public static synchronized <T> String createTableSQL(String tableName, Class<T> clazz) {
MappingAnnotation face;
try {
//通过反射,获取传入类名的字段信息
StringBuilder sb = new StringBuilder();
String reg = " ";
sb.append("CREATE TABLE IF NOT EXISTS ").append(tableName).append(" (");
//获取该类型声明的成员
Field[] fields = clazz.getDeclaredFields();
//遍历属性
for (Field field : fields) {
//对于private私有化的成员变量,通过setAccessible来修改器访问权限
field.setAccessible(true);
face = field.getAnnotation(MappingAnnotation.class);
if (face == null) {
continue;
}
//通过获得的字段,获取字段类型,和字段名,来定义创建表的字段名和字段类型
if (face.primaryKey()) {
sb.append(reg).append("`").append(field.getName()).append("`").append(" ").append(face.type()).append("(").append(face.maxLength()).append(")").append(" not null primary key");
} else {
if (face.type().equals("text") || face.type().equals("longtext")) {
sb.append(reg).append("`").append(field.getName()).append("`").append(" ").append(face.type());
} else if (face.type().equals("decimal") || face.type().equals("double")) {
sb.append(reg).append("`").append(field.getName()).append("`").append(" ").append(face.type()).append("(").append(face.maxLength()).append(",").append(face.decimal()).append(")");
} else {
sb.append(reg).append("`").append(field.getName()).append("`").append(" ").append(face.type()).append("(").append(face.maxLength()).append(")");
}
}
reg = ",";
}
sb.append(") ;");
return sb.toString();
} catch (Exception e) {
log.error(e.getMessage(), e.fillInStackTrace());
}
return null;
}
/**
* 生成字段
*
* @param tClass
* @param <T>
* @return
*/
public static synchronized <T> String createFieldList(Class<T> tClass) {
StringBuilder declaration = new StringBuilder();
Field[] fields = Arrays.asList(tClass.getDeclaredFields())
.stream()
.filter(field -> field.getAnnotation(MappingAnnotation.class) != null)
.toArray(Field[]::new);
declaration.append("(");
String reg = "";
for (Field field : fields) {
field.setAccessible(true);
declaration.append(reg).append("`").append(field.getName()).append("`");
reg = ",";
}
declaration.append(")");
return declaration.toString();
}
/**
* 数据映射为SQL插入/更新语句
*
* @param list
* @param <T>
* @return
*/
public static synchronized <T> String getBeanValueList(List<T> list) {
if (ListUtils.isNullOrEmpty(list)) {
return "";
}
List<String> dataSqlArray = new ArrayList<>();
String dataValue = "null";
Field[] fields = Arrays.asList(list.get(0).getClass().getDeclaredFields())
.stream()
.filter(field -> field.getAnnotation(MappingAnnotation.class) != null)
.toArray(Field[]::new);
list.forEach(t -> {
StringBuilder oneData = new StringBuilder();
oneData.append(" (");
List jsonList;
int i = 1;
for (Field field : fields) {
try {
field.setAccessible(true);
if (field.get(t) == null) {
oneData.append(dataValue);
} else if (String.class.isAssignableFrom(field.getType())) {
//传入的对象中字段是字符串时,插入数据库需要转义单引号
if (isJson(field.get(t))) {
jsonList = JSONObject.parseArray(field.get(t).toString());
String value = JSON.toJSON(jsonList).toString();
if (!StringUtil.isEmpty(value)) {
value = value.replaceAll("'", "’");
}
oneData.append("\'").append(value).append("\'");
} else {
oneData.append("\'").append(field.get(t)).append("\'");
}
// 传入的对象中字段是日期类型时,先将日期转换为 yyyy-MM-dd 的形式(我这里,传入对象的日期对象格式为:Wed Jul 11 14:34:07 CST 2018),在将其转换成字符串,注意转义单引号
} else {
oneData.append(field.get(t));
}
} catch (IllegalAccessException e) {
log.error(e.getMessage(), e.fillInStackTrace());
}
if (i == fields.length) {
oneData.append(")");
} else {
oneData.append(",");
i++;
}
}
dataSqlArray.add(oneData.toString());
});
String dataSql = StringUtils.join(dataSqlArray, ",");
//主键存在则更新,否则插入,使用replace
return dataSql;
}
/**
* 数据映射为SQL插入/更新语句
*
* @param clazz
* @param objList
* @param <T>
* @return
*/
public static synchronized <T> String getBeanValueList(Class<T> clazz, List<T> objList) {
StringBuilder sb = new StringBuilder();
try {
Field[] tableFields = Arrays.stream(clazz.getDeclaredFields())
.filter(field -> field.getAnnotation(MappingAnnotation.class) != null)
.toArray(Field[]::new);
for (Object obj : objList) {
sb.append("(");
for (int i = 0; i < tableFields.length; i++) {
PropertyDescriptor pd = new PropertyDescriptor(tableFields[i].getName(), clazz);
Method getMethod = pd.getReadMethod();//获得get方法
Object fieldValue = ReflectionUtils.invokeMethod(getMethod, obj);
if (fieldValue == null) {
sb.append("" + null + ",");
continue;
}
if (fieldValue instanceof String) {
//判断字符串类型是否含有'
if (((String) fieldValue).contains("'")) {
fieldValue = ((String) fieldValue).replaceAll("'", "\\\\'");
}
}
sb.append("'" + fieldValue.toString() + "',");
}
sb.deleteCharAt(sb.toString().lastIndexOf(","));
sb.append(")");
sb.append(",");
}
sb.deleteCharAt(sb.toString().lastIndexOf(","));
return sb.toString();
} catch (Exception e) {
log.error(e.getMessage(), e.fillInStackTrace());
return null;//TODO 异常清空如何处理?
}
}
private static boolean isJson(Object obj) {
try {
if (StringUtil.isEmpty(obj.toString().trim())) {
return false;
}
JSONArray jsonArray = JSONObject.parseArray(obj.toString());
return jsonArray != null;
} catch (Exception e) {
return false;
}
}
}
2、使用示例
package com.xxx;
import java.util.ArrayList;
import java.util.List;
public class DbDataSyncTest {
public static void main(String[] args) {
String ddl = DynamicSQLGenerateUtil.createTableSQL("t_table_name_test", ListDemo.class);
System.out.println(ddl);
String insertSql = DynamicSQLGenerateUtil.generateInsertSQL("t_table_name_test", ListDemo.class, initializeList());
System.out.println(insertSql);
}
public static List<ListDemo> initializeList() {
List<ListDemo> list = new ArrayList<>();
list.add(new ListDemo("1", 234, 0, "fjjjkk24"));
list.add(new ListDemo("2", 2, 1, "xvcnvc24"));
list.add(new ListDemo("3", 67, null, "df4564"));
list.add(new ListDemo("4", 2, 0, "fy43yre"));
list.add(new ListDemo("5", 2, 0, "ds"));
list.add(new ListDemo("6", 2, 0, "sdg"));
list.add(new ListDemo("7", 2, 0, "dfjfdjd"));
list.add(new ListDemo("11", 111, 11, "xvcnvc24"));
list.add(new ListDemo("12", 222, 22, "seyer612122122"));
list.add(new ListDemo("13", 333, 33, null));
list.add(new ListDemo("14", 444, 44, "seyer614144144"));
list.add(new ListDemo("15", 555, 55, "seyer615155155"));
list.add(new ListDemo("16", 666, 66, "seyerdfh616166166"));
list.add(new ListDemo("17", 777, 77, "seyer617177177"));
list.add(new ListDemo("21", 111, 11, "seyer621211211"));
list.add(new ListDemo("22", 222, 22, "seyer622222222"));
list.add(new ListDemo("23", 333, 33, "seyer6d23233233"));
list.add(new ListDemo("24", 444, 44, "seyer624244244"));
list.add(new ListDemo("25", 555, 55, "seyer625255255"));
list.add(new ListDemo("26", 666, 66, "seyer62fhj6266266"));
list.add(new ListDemo("27", 777, 77, "seyer627277277"));
list.add(new ListDemo("31", 111, 11, "seyer63df1311311"));
list.add(new ListDemo("32", 222, 22, "seyer632322322"));
list.add(new ListDemo("33", 333, 33, "seyer633333333"));
list.add(new ListDemo("34", 444, 44, "seyer634344344"));
list.add(new ListDemo("35", 555, 55, "seyer635355355"));
list.add(new ListDemo("36", 666, 66, "seyer636366366"));
list.add(new ListDemo("37", 777, 77, "seyer637377377"));
list.add(new ListDemo("41", 111, 11, "seyer641411411"));
list.add(new ListDemo("42", 222, 22, "seyer642422422"));
list.add(new ListDemo("43", 333, 33, "seyer643433433"));
list.add(new ListDemo("44", 444, 44, "seyer644444444"));
list.add(new ListDemo("45", 555, 55, "seyer645455455"));
list.add(new ListDemo("46", 666, 66, "seyer646466466"));
list.add(new ListDemo("47", 777, 77, "seyer647477477"));
return list;
}
}
输出
CREATE TABLE IF NOT EXISTS t_table_name_test ( `pk_id` varchar(36) not null primary key,`age` Integer(11),`type` Integer(11),`file_id_test` varchar(36)) ;
REPLACE INTO t_table_name_test(`pk_id`,`age`,`type`,`file_id_test`) VALUES
('1','234','0','fjjjkk24'),
('2','2','1','xvcnvc24'),
('3','67',null,'df4564'),
('4','2','0','fy43yre'),
('13','333','33',null),
('14','444','44','seyer614144144'),
('47','777','77','seyer647477477');