根据实体类生成sql语句的工具类

最近用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 "";
}