利用JDBC根据表结构生成ModelClass

---

有时为了加快开发效率，特别是项目初始阶段，数据库设计完成之后，希望直接读取表结构以生成数据库实体。本例中为生成hibernate实体:

//根据数据库生成实体模型的工具类
public class ModelClassBuilder {

    // mysql链接相关
    private final String driverClassName = "com.mysql.jdbc.Driver";
    private final String url = "jdbc:mysql:localhost/test?useUnicode=true&autoReconnect=true";
    private final String username = "test";
    private final String password = "test";

    private final String tableName;
    private final String tableClassName;

    public ModelClassBuilder(String tableName, String tableClassName) {
        this.tableName = tableName;
        this.tableClassName = tableClassName;
    }

    private Connection getDbConnection() {
        try {
            Class.forName(driverClassName);
            Class.forName(driverClassName).newInstance();
            StringBuilder connectUrlSb = new StringBuilder(url);
            connectUrlSb.append("&user=").append(username);
            connectUrlSb.append("&password=").append(password);
            Connection con = DriverManager.getConnection(connectUrlSb.toString());
            return con;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    private ResultSet readColumsFromJdbc(Connection connection) {
        try {
            String queryColumsSql = "select * from information_schema.columns where table_name = '" + tableName + "'";
            Statement stmt = connection.createStatement();
            return stmt.executeQuery(queryColumsSql);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    private List<ColnumEntity> getColunFromRs(ResultSet rs) throws Exception {
        ResultSetMetaData metaData = rs.getMetaData();
        int columns = metaData.getColumnCount();
        for (int index = 1; index <= columns; index++) {
            // System.out.print(metaData.getColumnName(index));
            // System.out.print("\t\t");
        }
        System.out.println();
        List<ColnumEntity> results = new ArrayList<ColnumEntity>();
        while (rs.next()) {
            // for (int i = 1; i <= columns; i++){
            // System.out.print(resultSet.getString(i));
            // System.out.print("\t\t");
            // }
            // System.out.println();
            String columnKey = rs.getString("COLUMN_KEY");// 包含pri则为主键
            String columnName = rs.getString("COLUMN_NAME");
            boolean isNullAble = rs.getBoolean("IS_NULLABLE");
            String dataType = rs.getString("DATA_TYPE");
            String maxLength = rs.getString("CHARACTER_MAXIMUM_LENGTH");
            String comment = rs.getString("COLUMN_COMMENT");
            results.add(new ColnumEntity(columnKey, columnName, isNullAble, dataType, maxLength, comment));
        }
        return results;
    }

    //注解加在field上
    public void genModelClass() {
        try {
            Connection con = getDbConnection();
            ResultSet resultSet = readColumsFromJdbc(con);
            List<ColnumEntity> colnumEntities = getColunFromRs(resultSet);
            StringBuilder sb = new StringBuilder();

            sb.append("import java.io.Serializable;");
            sb.append("import java.util.Date;");
            sb.append("import javax.persistence.Column;");
            sb.append("import javax.persistence.Entity;");
            sb.append("import javax.persistence.Id;");
            sb.append("import javax.persistence.Table;");

            sb.append("import javax.validation.constraints.NotNull;");
            sb.append("import org.hibernate.validator.constraints.NotBlank;");
            sb.append("import cn.com.maitao.common.model.BaseObject;");

            sb.append("@Entity").append("\n");
            sb.append(String.format("@Table(name = \"%s\")", tableName));
            sb.append("@SuppressWarnings(\"serial\")");
            sb.append("public class ").append(tableClassName).append(" extends BaseObject implements Serializable").append("{")
                    .append("\n");

            //field
            for (ColnumEntity colnumEntity : colnumEntities) {
                String columnName = colnumEntity.columnName;
                String fieldName = genFieldName(columnName);
                String dataType = getDataType(colnumEntity);
                sb.append("\t//").append(colnumEntity.comment).append("\n");
                // field
                if (colnumEntity.columnKey.equalsIgnoreCase("pri")) {
                    sb.append("@Id").append("\n");
                }
                if (!colnumEntity.isNullAble) {
                    if (dataType.equals("String")) {
                        sb.append(String.format("@NotBlank(message = \"%s不能为空\")", fieldName)).append("\n");
                    } else {
                        sb.append(String.format("@NotNull(message = \"%s不能为空\")", fieldName)).append("\n");
                    }
                }
                sb.append(String.format("@Column(name = \"%s\")", columnName));
                sb.append(String.format(field_Pattern, dataType, fieldName)).append("\n\n");
            }

            for (ColnumEntity colnumEntity : colnumEntities) {
                String columnName = colnumEntity.columnName;
                String fieldName = genFieldName(columnName);
                String dataType = getDataType(colnumEntity);
                //sb.append("\t//").append(colnumEntity.comment).append("\n");

                // set
                sb.append(String.format(method_Set_Pattern, firstToUpperCase(fieldName), dataType, fieldName, fieldName, fieldName))
                        .append("\n");
                // get
                sb.append(String.format(method_Get_Pattern, dataType, firstToUpperCase(fieldName), fieldName)).append("\n");
            }

            sb.append("}");
            System.out.println(sb.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private String field_Pattern = "private %s %s;";

    private String method_Get_Pattern = "public %s get%s(){return this.%s;} ";

    private String method_Set_Pattern = "public void set%s(%s %s){this.%s = %s;} ";

    private String getDataType(ColnumEntity colnumEntity) {
        String type = colnumEntity.dataType.toLowerCase();

        if (type.equalsIgnoreCase("datetime") || type.equalsIgnoreCase("date") || type.equalsIgnoreCase("time")
                || type.equalsIgnoreCase("timestamp")) {
            return "Date";
        }

        if (type.equalsIgnoreCase("bigint")) {
            return "Long";
        } else if (type.contains("int")) {
            return "Integer";
        }

        if (type.equalsIgnoreCase("float")) {
            return "Float";
        }

        if (type.equalsIgnoreCase("double") || type.equalsIgnoreCase("decimal")) {
            return "Double";
        }

        return "String";

    }

    class ColnumEntity {
        String columnKey;
        String columnName;
        boolean isNullAble;
        String dataType;
        String maxLength;
        String comment;

        public ColnumEntity(String columnKey, String columnName, boolean isNullAble, String dataType, String maxLength, String comment) {
            super();
            this.columnKey = columnKey;
            this.columnName = columnName;
            this.isNullAble = isNullAble;
            this.dataType = dataType;
            this.maxLength = maxLength;
            this.comment = comment;
        }
    }

    private static final Pattern pattern = Pattern.compile("_(\\w)");

    public static String genFieldName(String columnName) {
        Matcher matcher = pattern.matcher(columnName);
        while (matcher.find()) {
            columnName = columnName.replaceFirst(matcher.group(0), matcher.group(1).toUpperCase());
        }
        columnName.replaceAll("_", "");
        return columnName;
    }

    // 将首字母改成大写
    private String firstToUpperCase(String str) {
        if (StringUtils.isBlank(str)) {
            return "";
        }
        if (str.length() == 1) {
            return str.toUpperCase();
        }
        StringBuilder sb = new StringBuilder();
        sb.append(str.substring(0, 1).toUpperCase());
        sb.append(str.substring(1, str.length()));
        return sb.toString();
    }

    public static void main(String[] args) {
        ModelClassBuilder builder = new ModelClassBuilder("notice_wx_template", "NoticeWxTemplateModel");
        builder.genModelClass();
    }

}