注解加反射动态创建表

场景：一个实体，几十个字段，Java实体类建一遍，数据库建一遍，痛苦面具

 

所需代码：

package me.xiaomaju.base;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @Description 注解 表名
 * @Author @xmj
 */
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface TableNameAnnotation {

    String value(); //表名

}

package me.xiaomaju.base;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @Description 注解 列名
 * @Author @xmj
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface TableColumnAnnotation {

    String value();//列名

    MySQLColumnType type() default MySQLColumnType.VARCHAR; //类型,默认varchar

    int length() default 20;//长度,默认20

}

package me.xiaomaju.base;

/**
 * @Description 列类型枚举
 * @Author @xmj
 */
public enum MySQLColumnType {
    CHAR,
    VARCHAR,
    INT,
    DOUBLE,
    FLOAT
}

package me.xiaomaju.base;

import java.lang.reflect.Field;

/**
 * @Description 动态SQL工具类
 * @Author @xmj
 */
public class DynamicSQLUtils {

    private static Class<?> clazz = null;

    public static void main(String[] args) {

        String str = "me.xiaomaju.entity.bd.FreqDefDO";

        System.out.println("建表sql----------------------------");
        String creatTableSql = creatTableSql(str);
        System.out.println(creatTableSql);

//        System.out.println("查询sql----------------------------");
//        String selectSql = getSelectSQL(str);
//        System.out.println(selectSql);

//        System.out.println("mybatis添加sql----------------------------");
//        String addSql = getAddSql(str);
//        System.out.println(addSql);

//        System.out.println("mybatis修改sql----------------------------");
//        String updateSQL = getUpdateSQL(str);
//        System.out.println(updateSQL);

    }

    /**
     * 获取mybatis添加SQL
     * @param str
     * @return
     */
    public static String getAddSql(String str){
        try {
            clazz = Class.forName(str);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        TableNameAnnotation tn = clazz.getAnnotation(TableNameAnnotation.class);
        String tableName = tn.value();

        StringBuffer insertStr = new StringBuffer();
        insertStr.append(" insert into "+tableName +"(");

        StringBuffer valuesStr = new StringBuffer();
        valuesStr.append(" values(");

        Field[] fields = clazz.getDeclaredFields();
        TableColumnAnnotation field = null;
        for (Field f : fields) {
            field = f.getAnnotation(TableColumnAnnotation.class);
            if (field == null)
                continue;
            insertStr.append(field.value() + ",");
            valuesStr.append("#{"+field.value() +"},");
        }
        insertStr.deleteCharAt(insertStr.length() - 1);
        insertStr.append(")");
        valuesStr.deleteCharAt(valuesStr.length() - 1);
        valuesStr.append(")");
        return insertStr.toString()+valuesStr.toString();
    }

    /**
     * 获取mybatis修改SQL
     * @param str
     * @return
     */
    public static String getUpdateSQL(String str) {
        StringBuffer sql = new StringBuffer();
        try {
            clazz = Class.forName(str);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        TableNameAnnotation tn = clazz.getAnnotation(TableNameAnnotation.class);
        String tableName = tn.value();
        sql.append(" update "+tableName+" set ");
        Field[] fields = clazz.getDeclaredFields();
        TableColumnAnnotation field = null;
        for (Field f : fields) {
            field = f.getAnnotation(TableColumnAnnotation.class);
            if (field == null)
                continue;
            sql.append(field.value()+"=#{"+field.value() +"},");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" where id=#{id} " );
        return sql.toString();
    }

    /**
     * 获取建表SQL
     * @param str
     * @return
     */
    public static String creatTableSql(String str) {
        StringBuffer sql = new StringBuffer();
        try {
            clazz = Class.forName(str);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        TableNameAnnotation tn = clazz.getAnnotation(TableNameAnnotation.class);
        String tableName = tn.value();
        sql.append(" DROP TABLE IF EXISTS " + tableName + " ; ");
        sql.append("CREATE TABLE " + tableName + " ( ");
        Field[] fields = clazz.getDeclaredFields();
        TableColumnAnnotation field = null;
        for (Field f : fields) {
            field = f.getAnnotation(TableColumnAnnotation.class);
            if (field == null)
                continue;
            sql.append(field.value() + " " + field.type() + "(" + field.length() + "),");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(")");
        return sql.toString();
    }

    /**
     * 获取查询SQL
     * @param str
     * @return
     */
    public static String getSelectSQL(String str) {
        StringBuffer sql = new StringBuffer();
        try {
            clazz = Class.forName(str);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        TableNameAnnotation tn = clazz.getAnnotation(TableNameAnnotation.class);
        String tableName = tn.value();
        sql.append(" select ");
        Field[] fields = clazz.getDeclaredFields();
        TableColumnAnnotation field = null;
        for (Field f : fields) {
            field = f.getAnnotation(TableColumnAnnotation.class);
            if (field == null)
                continue;
            sql.append(field.value() + " ,");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" from " + tableName);
        return sql.toString();
    }

}

实体类中使用注解：

 

 运行 DynamicSQLUtils 中的 main 方法，将控制台的打印语句放到数据库工具中执行

 

 个人认为可以改进的点：

1、拼接建表语句的时候把列的注释也拼进去（列注解最少要传2个参数）

2、写个JDBC执行SQL（个人还是喜欢用数据库工具）

3、获取指定包下面的所有实体的全类名，批量建表（目前表不是很多，没必要）

 

package me.xiaomaju.base;

import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;

/**
 * @Description 动态建表SQL工具类 (迭代版2.0)
 * @Author @xmj
 */
public class DynamicSQLUtils2 {

    // 已解析的类和字段信息的缓存
    private static Map<Class<?>, ClassInfo> classInfoCache = new HashMap<Class<?>, ClassInfo>();

    public static void main(String[] args) {

        String fullName = "me.xiaomaju.entity.CiOrderDO";
        Class clazz = getClass(fullName);

        System.out.println("建表sql----------------------------");
        String creatTableSql = creatTableSql(clazz);
        String creatTableSql2 = creatTableSql(fullName);
        System.out.println(creatTableSql);
        System.out.println(creatTableSql2);

//        System.out.println("查询sql----------------------------");
//        String selectSql = getSelectSQL(fullName);
//        System.out.println(selectSql);
//
//        System.out.println("添加sql----------------------------");
//        String addSql = getAddSql(fullName);
//        System.out.println(addSql);
//
//        System.out.println("修改sql----------------------------");
//        String updateSQL = getUpdateSQL(fullName);
//        System.out.println(updateSQL);

    }

    /**
     * 获取指定类的Class对象
     *
     * @param fullName
     * @return
     */
    public static Class getClass(String fullName){
        Class<?> clazz = null;
        try {
            clazz = Class.forName(fullName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return clazz;
    }

    /**
     * 获取Class对象的信息
     *
     * @param clazz
     * @return
     */
    public static ClassInfo getClassInfo(Class<?> clazz){
        // 检查缓存中是否存在已解析的类信息
        if (classInfoCache.containsKey(clazz)) {
            ClassInfo classInfo = classInfoCache.get(clazz);
            return classInfo;
        }
        // 反射解析类信息
        ClassInfo classInfo = parseClassInfo(clazz);
        // 存入缓存
        classInfoCache.put(clazz, classInfo);
        return classInfo;
    }

    /**
     * 解析类信息
     *
     * @param clazz
     * @return
     */
    private static ClassInfo parseClassInfo(Class<?> clazz) {
        String tableName = null;//表名
        Field[] fields = null;//列数组
        TableNameAnnotation tn = clazz.getAnnotation(TableNameAnnotation.class);
        tableName= tn.value();
        fields = clazz.getDeclaredFields();
        return new ClassInfo(tableName, fields);
    }

    /**
     * 获取添加SQL的字符串
     *
     * @param fullName
     * @return
     */
    public static String getAddSql(String fullName){
        Class<?> clazz = getClass(fullName);
        return getAddSql(clazz);
    }

    /**
     * 获取添加SQL的字符串
     *
     * @param clazz
     * @return
     */
    public static String getAddSql(Class<?> clazz){
        ClassInfo classInfo = getClassInfo(clazz);
        String tableName = classInfo.getTableName();
        Field[] fields = classInfo.getFields();

        StringBuffer insertStr = new StringBuffer();
        insertStr.append(" insert into "+tableName +"(");

        StringBuffer valuesStr = new StringBuffer();
        valuesStr.append(" values(");

        TableColumnAnnotation field;
        for (Field f : fields) {
            field = f.getAnnotation(TableColumnAnnotation.class);
            if (field == null)
                continue;
            insertStr.append(field.value() + ",");
            valuesStr.append("#{"+field.value() +"},");
        }
        insertStr.deleteCharAt(insertStr.length() - 1);
        insertStr.append(")");
        valuesStr.deleteCharAt(valuesStr.length() - 1);
        valuesStr.append(")");
        return insertStr.toString()+valuesStr.toString();
    }

    /**
     * 获取修改SQL的字符串
     *
     * @param fullName
     * @return
     */
    public static String getUpdateSQL(String fullName) {
        Class<?> clazz = getClass(fullName);
        return getUpdateSQL(clazz);
    }
    /**
     * 获取修改SQL的字符串
     *
     * @param clazz
     * @return
     */
    public static String getUpdateSQL(Class<?> clazz) {
        ClassInfo classInfo = getClassInfo(clazz);
        String tableName = classInfo.getTableName();
        Field[] fields = classInfo.getFields();

        StringBuffer sql = new StringBuffer();
        sql.append(" update "+tableName+" set ");
        TableColumnAnnotation field = null;
        for (Field f : fields) {
            field = f.getAnnotation(TableColumnAnnotation.class);
            if (field == null)
                continue;
            sql.append(field.value()+"=#{"+field.value() +"},");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" where id=#{id} " );
        return sql.toString();
    }

    /**
     * 获取建表SQL的字符串
     *
     * @param fullName
     * @return
     */
    public static String creatTableSql(String fullName) {
        Class<?> clazz = getClass(fullName);
        return creatTableSql(clazz);
    }

    /**
     * 获取建表SQL的字符串
     *
     * @param clazz
     * @return
     */
    public static String creatTableSql(Class<?> clazz) {
        ClassInfo classInfo = getClassInfo(clazz);
        String tableName = classInfo.getTableName();
        Field[] fields = classInfo.getFields();

        StringBuffer sql = new StringBuffer();
        sql.append(" DROP TABLE IF EXISTS " + tableName + " ;");
        sql.append(" CREATE TABLE " + tableName + " ( ");
        TableColumnAnnotation field = null;
        for (Field f : fields) {
            field = f.getAnnotation(TableColumnAnnotation.class);
            if (field == null)
                continue;
            sql.append(field.value() + " " + field.type() + "(" + field.length() + "),");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(")");
        return sql.toString();
    }

    /**
     * 获取查询SQL的字符串
     *
     * @param fullName
     * @return
     */
    public static String getSelectSQL(String fullName) {
        Class<?> clazz = getClass(fullName);
        return getSelectSQL(clazz);
    }

    /**
     * 获取查询SQL的字符串
     *
     * @param clazz
     * @return
     */
    public static String getSelectSQL(Class<?> clazz) {
        ClassInfo classInfo = getClassInfo(clazz);
        String tableName = classInfo.getTableName();
        Field[] fields = classInfo.getFields();

        StringBuffer sql = new StringBuffer();
        sql.append(" select ");
        TableColumnAnnotation field = null;
        for (Field f : fields) {
            field = f.getAnnotation(TableColumnAnnotation.class);
            if (field == null)
                continue;
            sql.append(field.value() + " ,");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" from " + tableName);
        return sql.toString();
    }

}

package me.xiaomaju.base;

import java.lang.reflect.Field;

public class ClassInfo {

    private String tableName; //表名
    private Field[] fields; //列

    public ClassInfo(String tableName, Field[] fields) {
        this.tableName = tableName;
        this.fields = fields;

    }

    public String getTableName() {
        return tableName;
    }

    public void setTableName(String tableName) {
        this.tableName = tableName;
    }

    public Field[] getFields() {
        return fields;
    }

    public void setFields(Field[] fields) {
        this.fields = fields;
    }
}

 

TRANSLATE with x

English

Arabic	Hebrew	Polish
Bulgarian	Hindi	Portuguese
Catalan	Hmong Daw	Romanian
Chinese Simplified	Hungarian	Russian
Chinese Traditional	Indonesian	Slovak
Czech	Italian	Slovenian
Danish	Japanese	Spanish
Dutch	Klingon	Swedish
English	Korean	Thai
Estonian	Latvian	Turkish
Finnish	Lithuanian	Ukrainian
French	Malay	Urdu
German	Maltese	Vietnamese
Greek	Norwegian	Welsh
Haitian Creole	Persian

 

TRANSLATE with

COPY THE URL BELOW

Back

EMBED THE SNIPPET BELOW IN YOUR SITE

Enable collaborative features and customize widget: Bing Webmaster Portal

Back