根据实体类生成sql语句的工具类
2020-04-22 本文已影响0人
TinyThing
最近用JdbcTemplate和NamedParameterJdbcTemplate进行sql调用，有很多模板化的代码，因此写一个自动生成sql的工具类便于使用；
代码如下：
import com.google.common.base.Converter;
import lombok.ToString;
import org.apache.commons.lang3.StringUtils;
import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
import java.util.stream.Stream;
import static com.google.common.base.CaseFormat.LOWER_CAMEL;
import static com.google.common.base.CaseFormat.LOWER_UNDERSCORE;
import static java.util.stream.Collectors.joining;
/**
* <p>根据实体类生成基本sql</p>
*
* @date 2020年04月21日 18:54
*/
public class SqlUtils {
// 驼峰转下划线, userName -> user_name
private static final Converter<String, String> CONVERTER = LOWER_CAMEL.converterTo(LOWER_UNDERSCORE);
//缓存
private static final Map<Class<?>, ClassProperty<?>> CLASS_PROPERTY_MAP = new HashMap<>();
//表名称前缀
private static final String TABLE_PREFIX = "tb_";
private static final String TABLE_NAME = "{tableName}";
private static final String TABLE_ID = "{tableId}";
private static final String CLASS_ID = "{classId}";
public static void main(String[] args) {
ClassProperty<User> classProperty = SqlUtils.createClassProperty(User.class);
System.out.println(classProperty);
}
public static <E> String getInsertSql(Class<E> dataClass, String tableName) {
ClassProperty<E> property = getClassProperty(dataClass);
if (StringUtils.isEmpty(tableName)) {
tableName = property.tableName;
}
String sql = property.insertSql;
return sql.replace(TABLE_NAME, tableName);
}
/**
* 获取类属性
*
* @param dataClass 数据类
* @return 类属性
*/
@SuppressWarnings("unchecked")
private static <E> ClassProperty<E> getClassProperty(Class<E> dataClass) {
ClassProperty<?> property = CLASS_PROPERTY_MAP.get(dataClass);
if (property != null) {
return (ClassProperty<E>) property;
}
//double check
synchronized (CLASS_PROPERTY_MAP) {
property = CLASS_PROPERTY_MAP.get(dataClass);
if (property != null) {
return (ClassProperty<E>) property;
}
property = createClassProperty(dataClass);
CLASS_PROPERTY_MAP.put(dataClass, property);
}
return (ClassProperty<E>) property;
}
/**
* 根据数据类型
* 创建类属性
*
* @param dataClass 数据类型
* @return 类属性
*/
private static <E> ClassProperty<E> createClassProperty(Class<E> dataClass) {
ClassProperty<E> property = new ClassProperty<>();
Field[] fields = dataClass.getDeclaredFields();
String[] classFields = new String[fields.length];
String[] tableFields = new String[fields.length];
property.dataClass = dataClass;
property.tableName = getTableName(dataClass);
property.classFields = classFields;
property.tableFields = tableFields;
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
String name = field.getName();
classFields[i] = name;
boolean present = field.isAnnotationPresent(Column.class);
if (present) {
Column annotation = field.getAnnotation(Column.class);
tableFields[i] = annotation.value().isEmpty() ? name : annotation.value();
property.classId = annotation.id() ? field.getName() : null;
property.tableId = annotation.id() ? tableFields[i] : null;
} else {
tableFields[i] = CONVERTER.convert(name);
}
}
property.insertSql = generateInsertSql(classFields, tableFields);
property.selectByIdSql = generateSelectSql(tableFields, property.tableId, property.classId);
property.deleteByIdSql = generateDeleteSql(property.tableId, property.classId);
property.updateByIdSql = generateUpdateSql(classFields, tableFields, property.tableId, property.classId);
return property;
}
/**
* 生成查询语句
* @param tableFields 字段名
* @param tableId 表id
* @param classId 类id
* @return sql
*/
private static String generateSelectSql(String[] tableFields, String tableId, String classId) {
if (StringUtils.isEmpty(tableId) || StringUtils.isEmpty(classId)) {
tableId = TABLE_ID;
classId = CLASS_ID;
}
String tableFieldSql = String.join(",", tableFields);
return "SELECT " + tableFieldSql + " FROM " + TABLE_NAME + " WHERE " + tableId + " = :" + classId;
}
/**
* 生成sql语句
** @param classFields 属性名
* @param tableFields 字段名
* @return 插入sql
*/
public static String generateInsertSql(String[] classFields, String[] tableFields) {
String sql = "INSERT INTO " + TABLE_NAME + " ";
String classFieldsSql = Stream.of(classFields).collect(joining(",:", "(:", ")"));
String tableFieldSql = Stream.of(tableFields).collect(joining(",", "(", ")"));
return sql + tableFieldSql + " VALUES " + classFieldsSql;
}
/**
* 生成删除单个语句
* @param tableId tableId
* @param classId classId
* @return sql
*/
private static String generateDeleteSql(String tableId, String classId) {
if (StringUtils.isEmpty(tableId) || StringUtils.isEmpty(classId)) {
tableId = TABLE_ID;
classId = CLASS_ID;
}
return "DELETE FROM " + TABLE_NAME + " WHERE " + tableId + " = :" + classId;
}
/**
* 生成update语句
* @param classFields 类属性
* @param tableFields 表字段
* @param tableId 表id
* @param classId 类id
* @return sql
*/
private static String generateUpdateSql(String[] classFields, String[] tableFields, String tableId, String classId) {
if (StringUtils.isEmpty(tableId) || StringUtils.isEmpty(classId)) {
tableId = TABLE_ID;
classId = CLASS_ID;
}
StringBuilder sql = new StringBuilder("UPDATE " + TABLE_NAME + " SET ");
for (int i = 0; i < classFields.length; i++) {
String classField = classFields[i];
String tableField = tableFields[i];
sql.append(tableField).append("=:").append(classField).append(",");
}
sql.append("WHERE ").append(tableId).append("=:").append(classId);
return sql.toString();
}
/**
* 根据类获取表名称
*
* @param dataClass 类
* @return 表名
*/
public static String getTableName(Class<?> dataClass) {
boolean present = dataClass.isAnnotationPresent(Table.class);
if (present) {
Table table = dataClass.getAnnotation(Table.class);
return table.value();
}
String className = dataClass.getSimpleName();
return TABLE_PREFIX + CONVERTER.convert(className);
}
/**
* 描述class的属性
* 包括class的内部属性字段数组和对应的表字段数组
*/
@ToString
private static class ClassProperty<E> {
/**
* 表名称
*/
String tableName;
/**
* 数据类
*/
Class<E> dataClass;
/**
* id类属性名称
*/
String classId;
/**
* id字段名称
*/
String tableId;
/**
* 类属性
*/
String[] classFields;
/**
* 表字段
*/
String[] tableFields;
/**
* insert语句
*/
String insertSql;
/**
* update语句
*/
String updateByIdSql;
/**
* 根据id删除
*/
String deleteByIdSql;
/**
* 查询单个
*/
String selectByIdSql;
}
}
自定义的两个注解如下：
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface Column {
/**
* 表字段名称
*/
String value() default "";
/**
* 是否是id
*/
boolean id() default false;
}
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE})
public @interface Table {
String value() default "";
}
