java自定义注解根据实体类生成sql

功能描述

通过识别在实体类中自定义注解，来生成sql语句，目前支持mysql、sqlserver、oracle的建表语句和单条插入语句生成。

自定义注解类

自定义@Table注解

/**
 * 自定义注解@Table，定义表名等基础信息
 * @author wangzg
 * @date 2021/6/18 9:47
 */
@Documented
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE, ElementType.FIELD})
public @interface Table {
    String tableName() default ""; //默认表名为空
    String primaryKey() default "id"; //默认主键为id
}

自定义@Cloumn注解

/**
 * 自定义注解@Column，定义字段信息
 * @author wangzg
 * @date 2021/6/18 9:56
 */
@Documented
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE, ElementType.FIELD})
public @interface Column {
    String name() default ""; //字段名
    String mysqlType() default ""; //mysql中的字段类型
    String oracleType() default ""; //oracle中的字段类型
    String mssqlType() default ""; //sqlserver中的字段类型
    int length() default 0; //字段长度
    boolean nullable() default true; //字段默认可为空
    String params() default ""; //其他参数设置
    String comment() default ""; //字段说明
}

定义实体类并添加注解

参考写法如下

@Table(tableName = "webpage_info", primaryKey = "sid")
public class WebpageInfo {
    @Column(mysqlType = "VARCHAR", oracleType = "VARCHAR", mssqlType = "VARCHAR", length = 100, comment = "主键")
    private String sid;
    @Column(mysqlType = "VARCHAR", oracleType = "VARCHAR", mssqlType = "VARCHAR", length = 100, comment = "专题ID")
    private String subject_id;

    //其他参数及方法等已省略
}

工具类

目前工具类比较简单，采集字符串拼接实现

/**
 * TODO: 待添加对类型长度的判断
 * sql生成工具类
 * @author wangzg
 * @date 2021/6/18 11:28
 */
public class GenerateSql {

    public static final String DB_TYPE_MYSQL = "mysql";
    public static final String DB_TYPE_ORACLE = "oracle";
    public static final String DB_TYPE_MSSQL = "mssql";

    /**
     * 生成相应数据库的建表语句
     * @author wangzg
     * @date 2021/6/18 11:27
     * @param dbType 数据库类型，mysql,oracle,mssql
     * @param clazz 实体类
     * @return java.lang.String
     */
    public static String generateCreateTableSql(String dbType, Class<?> clazz) {

        //判断是否有 @Table 注解
        boolean hasTableAnno = clazz.isAnnotationPresent(Table.class);
        String tableName = "";
        String primaryKey = "";
        if (hasTableAnno) {
            //获取类上的@Table注解
            Table tableAnno = clazz.getAnnotation(Table.class);
            //获取表名
            tableName = tableAnno.tableName();
            check(tableName, "表名为空");
            //获取主键名
            primaryKey = tableAnno.primaryKey();
            check(primaryKey, "主键名为空");
        } else {
            throw new RuntimeException("实体类"+clazz.getName()+"缺少@Table注解");
        }

        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE IF NOT EXISTS ").append(tableName).append(" ( \n");
        Field[] fields = clazz.getDeclaredFields();
        String columnName = "";
        String columnType = "";
        int columnLength = 0;
        String typeInfo = "";
        for (Field field : fields) {
            //初始化列明为属性名
            columnName = field.getName();
            boolean hasColumnAnno = field.isAnnotationPresent(Column.class);
            if (hasColumnAnno) {
                Column columnAnno = field.getAnnotation(Column.class);
                if (!"".equals(columnAnno.name())) {
                    //注解中显式声明了name的值，否则列名为类的属性名
                    columnName = columnAnno.name();
                }
                switch (dbType) {
                    case DB_TYPE_MYSQL:
                        columnType = columnAnno.mysqlType();
                        break;
                    case DB_TYPE_MSSQL:
                        columnType = columnAnno.mssqlType();
                        if ("NVARCHAR".equals(columnType)) {
                            columnType = columnType+"(MAX)";
                        }
                        break;
                    case DB_TYPE_ORACLE:
                        columnType = columnAnno.oracleType();
                        break;
                    default:
                        check("", "当前不支持创建类型为"+dbType+"的表");
                        break;
                }
                check(columnType, columnName+"的类型为空");
                columnLength = columnAnno.length();
                if (columnLength == 0) {
                    typeInfo = columnType;
                }else {
                    typeInfo = columnType+"("+columnLength+")";
                }
                if (primaryKey.equals(columnName)) {
                    sb.append(columnName + " " + typeInfo + " PRIMARY KEY");
                } else {
                    sb.append(columnName + " " + typeInfo);
                }
                sb.append(",\n");
            } else {
                continue;
            }
        }
        sb.append(");");
        String sql = sb.toString().replace(",\n)", "\n)");
        if (DB_TYPE_ORACLE.equals(dbType)) {
            sql = sql.replace("CREATE TABLE IF NOT EXISTS", "CREATE TABLE ");
        }else if (DB_TYPE_MYSQL.equals(dbType)) {
            sql = sql.replace(");", ")COLLATE=utf8_general_ci ENGINE=InnoDB;");
        }
        return sql;
    }

    /**
     * 生成insert sql
     * @author wangzg
     * @date 2021/6/18 14:54
     * @param dbType 数据库类型
     * @param object 数据对象
     * @param clazz 数据对象的类
     * @return java.lang.String
     */
    public static String generateInsertSql(String dbType, Object object, Class<?> clazz) throws NoSuchFieldException, IllegalAccessException {
        Table tableAnno = clazz.getAnnotation(Table.class);
        //获得表名
        String tableName = tableAnno.tableName();
        String sql = "";
        StringBuilder sb = new StringBuilder();
        sb.append("insert into " + tableName + "(");

        //oracle insert sql 处理clob类型的字段
        StringBuilder sb2 = new StringBuilder();
        sb2.append("declare ");

        Field[] fields = object.getClass().getDeclaredFields();
        for (Field field : fields) {
            sb.append(field.getName() + ",");
        }
        sb.append(") values(");
        for (Field field : fields) {
            Column columnAnno = field.getAnnotation(Column.class);
            String annoType = columnAnno.oracleType();
            field.setAccessible(true);
            String fieldName = field.getName();
            Object value = field.get(object);
            String fieldType = field.getType().getName();
            if ("CLOB".equals(annoType) && DB_TYPE_ORACLE.equals(dbType)) {
                sb2.append(fieldName+" clob := '"+value+"'; ");
                sb.append(fieldName+",");
                continue;
            }
            if ("java.lang.String".equals(fieldType)) {
                sb.append("'"+value+"',");
            }else if ("java.lang.Long".equals(fieldType) || "java.lang.Integer".equals(fieldType)) {
                sb.append(value+",");
            }
        }
        sb.append(")");
        sql = sb.toString();
        sql = sql.replace(",)", ")");

        if (DB_TYPE_ORACLE.equals(dbType)) {
            sb2.append("begin ");
            String oracleDec = sb2.toString().replace("; begin ", " begin ");
            sql = oracleDec + sql;
        }

        return sql;
    }

    /**
     * 检查对象是否符合要求，不符合则抛异常
     * @author wangzg
     * @date 2021/6/18 11:26
     * @param obj 检查对象
     * @param msg 异常信息
     */
    public static void check(Object obj, String msg) {
        if ("".equals(obj) || obj == null) {
            throw new RuntimeException(msg);
        }
    }

    //测试
    public static void main(String[] args) throws NoSuchFieldException, IllegalAccessException {
//        String createTableSql = generateCreateTableSql(DB_TYPE_MYSQL, WebpageInfo.class);
//        System.out.println(createTableSql);
//        WebpageInfo webpageInfo = new WebpageInfo();
//        webpageInfo.setSid("243523465");
//        String s = generateInsertSql(DB_TYPE_MYSQL, webpageInfo, WebpageInfo.class);
//        System.out.println(s);
    }
}