数据库反向生成实体类 (转)

数据库反向生成实体类是dbdesigner的一个功能，现在很多开发工具也带有这样的功能。实现原理也很简单：先通过SQL语句获取所有数据库表，再通过SQL语句获取表的结构，最后根据表的字段信息生成相应的hibernate实体类。
本文的初步实现代码只考虑mysql 5.1版本，其它版本数据库暂不考虑。

获取所有表的SQl：
show tables

获取表字段信息的SQL有两种：
(1) SELECT COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT FROM information_schema.columns WHERE table_name = 表名
(2) describe 表名

主要的实现代码如下
DBDao主要提供获取数据库表和表字段信息的方法

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.dbdesigner.ui.component.ConnectDbDialog;
import org.dbdesigner.ui.util.NodeProperty;

public class DBDao {
    private NodeProperty conConfig;

    public DBDao(NodeProperty con) {
        this.conConfig = con;
    }

    private Connection getConnect() throws InstantiationException,
            IllegalAccessException, ClassNotFoundException, SQLException {
        String strDriver = conConfig.getProperty(ConnectDbDialog.PRO_DRIVER);
        String strUrl = "jdbc:mysql://"
                + conConfig.getProperty(ConnectDbDialog.PRO_SERVER) + ":"
                + conConfig.getProperty(ConnectDbDialog.PRO_PORT) + "/"
                + conConfig.getProperty(ConnectDbDialog.PRO_DATABASE);
        String strUser = conConfig.getProperty(ConnectDbDialog.PRO_USER);
        String strPass = conConfig.getProperty(ConnectDbDialog.PRO_PSW);
        Class.forName(strDriver).newInstance();
        return DriverManager.getConnection(strUrl, strUser, strPass);
    }

    
        /** *//**
     * 获取所有数据表
     */
    public ArrayList getAllTable() {
        ArrayList result = null;
        Connection con = null;
        try {
            con = getConnect();
            QueryRunner qr = new QueryRunner();
            ResultSetHandler rsh = new ArrayListHandler();
            String strsql = "show tables";   //show tables为mysql获取所有表的方法
            result = qr.query(con, strsql, rsh);
//            for (int i = 0; i < result.size(); i++) {
//                System.out.println(((Object[]) result.get(i))[0]);
//            }
        } catch (Exception ex) {
            ex.printStackTrace(System.out);
        } finally {
            try {
                DbUtils.close(con);
            } catch (Exception ex) {
            }
        }
        return result;
    }

        /** *//**
     * 获取指定表的字段信息
     * @param t 表名
     */
    public NodeProperty getTableDescribe(String t) {
        Connection con = null;
        NodeProperty table = NodeProperty.getInstance(NodeProperty.NODE_TABLE);
        table.addProperty(NodeProperty.PROPERTY_NAME, t);
        try {
            con = getConnect();
            QueryRunner qr = new QueryRunner();
            String strsql = "describe " + t;
            List results = qr.query(con, strsql, new MapListHandler());
            NodeProperty columns = NodeProperty
                    .getInstance(NodeProperty.NODE_COLUMNS);
            table.addChild(columns);
            for (int i = 0; i < results.size(); i++) {
                NodeProperty column = NodeProperty
                        .getInstance(NodeProperty.NODE_COLUMN);
                Map map = (Map) results.get(i);
                String name = map.get("COLUMN_NAME").toString();
                String key = map.get("COLUMN_KEY").toString();
                String isKey = key.equals("PRI") ? "true" : "false";
                String nullable = map.get("IS_NULLABLE").toString();
                boolean isNullable = nullable.equals("YES") ? true : false;
                String type = map.get("COLUMN_TYPE").toString();
                String unsigned = "false";
                int index = type.indexOf("unsigned");
                if(index > 0){
                    unsigned = "true";
                    type = type.substring(0, index - 1);
                }
                column.addProperty(NodeProperty.PROPERTY_NAME, name);
                column.addProperty(NodeProperty.PROPERTY_COMMENT, name);
                column.addProperty(NodeProperty.PROPERTY_PRIMARYKEY, isKey);
                column.addProperty(NodeProperty.PROPERTY_UNSIGNED, unsigned);
                column.addProperty(NodeProperty.PROPERTY_DATATYPE, type);
                columns.addChild(column);
            }
        } catch (Exception ex) {
            ex.printStackTrace(System.out);
        } finally {
            try {
                DbUtils.close(con);
            } catch (Exception ex) {
            }
        }
        return table;
    }
}

GenerateEntity主要是根据NodeProperty的内容，生成实体类。

import java.util.List;
import org.dbdesigner.db.MySqlDataType;
import org.dbdesigner.ui.component.DBGraphComponent;
import org.dbdesigner.ui.util.NodeProperty;
import org.dbdesigner.utils.FileUtil;
import org.dbdesigner.utils.StringUtil;

public class GenerateEntity {
    private String filePath = null;
    private String packageName = null;
    public GenerateEntity(String filePath, String packageName, DBGraphComponent graphComponent){
        this.filePath = filePath;
        this.packageName = packageName;
    }
    
    //根据DBDao.getTableDescribe(String t)方法返回的NodeProperty内容，生成相应的实体类
    public void NodeToEntity(NodeProperty node){
        StringBuilder sr = new StringBuilder();
        StringBuilder declareSr = new StringBuilder();
        StringBuilder methodSr = new StringBuilder();
        String tableName = node.getProperty(NodeProperty.PROPERTY_NAME);
        String uTableName = StringUtil.upperFirst(tableName);
        if(StringUtil.isEmpty(packageName) == false){
            sr.append("package ").append(packageName).append("\n");
        }
        //添加引用包
        sr.append("import java.io.Serializable;\n");
        sr.append("import javax.persistence.*;\n");
        NodeProperty columns = node.getChildByType(NodeProperty.NODE_COLUMNS);
        if(columns != null){
            List<NodeProperty> list = columns.getChild();
            if(list.size() > 0){
                for(NodeProperty column:list){
                    String dataType =column.getProperty(NodeProperty.PROPERTY_DATATYPE);
                    if(dataType != null){
                         String type = dataType.toLowerCase();
                         if(type.equals("date") || type.equals("datetime")){
                             sr.append("import java.util.Date;\n");
                         }
                    }
                    //声明
                    generateDeclare(declareSr, column, dataType);
                    //方法
                    generateMethod(methodSr, column, dataType);
                }
            }
        }
        
        /**//*输出类名*/
        sr.append("\n");
        sr.append("@Entity\n");
        sr.append("@Table(name =\"").append(tableName)
        .append("\")\n");
        sr.append("public class ").append(uTableName).append(
                " implements Serializable {\n");
        sr.append("\tprivate static final long serialVersionUID = 1L;\n");
        sr.append("\n");
        
        /**//*输出声明*/
        sr.append(declareSr.toString());
        /**//*输出方法*/
        sr.append(methodSr.toString());
        
        sr.append("}\n");
        sr.append("\n");
        try{
            FileUtil.save(filePath + FileUtil.sp + uTableName + ".java", sr.toString().getBytes());
        }
        catch(Exception e){
            System.out.println("生成实体类出错!");
            e.printStackTrace();
        }
    }
    
    //生成字段声明内容
    public void generateDeclare(StringBuilder deSr, NodeProperty column, String dataType){
        int start = dataType.indexOf("(");
        int end = dataType.indexOf(")");
        String type = dataType;
        String lenght = "";
        if(start > 0 && end > 0){
            type = dataType.substring(0, start);
            lenght = dataType.substring(start + 1, dataType.length() - 1);
        }
        String primaryKey = column.getProperty(NodeProperty.PROPERTY_PRIMARYKEY);
        if(primaryKey != null && primaryKey.equals("true")){
            deSr.append("\t@Id\n");
        }
        deSr.append("\t@Column(name =\"").append(column.getProperty(NodeProperty.PROPERTY_NAME)).append("\"");
        if (type.equals("varchar") || type.equals("char")) {
            deSr.append(", length=").append(lenght);
        } 

        deSr.append(")\n");
        if (type.equals("date") || type.equals("datetime")) {
            deSr.append("\t@Temporal(TemporalType.TIMESTAMP)\n");
        }
        
        deSr.append("\tprivate ").append(MySqlDataType.get(type)).append(" ").append(column.getProperty(NodeProperty.PROPERTY_NAME)).append(";\n");
        deSr.append("\n");
    }
    
    //生成字段的setter、getter方法
    public void generateMethod(StringBuilder deSr, NodeProperty column, String dataType){
        String name = column.getProperty(NodeProperty.PROPERTY_NAME);
        String upperName = StringUtil.upperFirst(name);
        int start = dataType.indexOf("(");
        if(start > 0){
            dataType = dataType.substring(0, start);
        }
        String type = MySqlDataType.get(dataType) != null ? MySqlDataType.get(dataType) : dataType;
        deSr.append("\tpublic ").append(type).append(" ").append(
                "boolean".equals(dataType) ? "is" : "get").append(upperName)
                .append("() {\n");
        deSr.append("\t\treturn ").append(name).append(";\n");
        deSr.append("\t}\n");
        deSr.append("\n");
        deSr.append("\tpublic void set").append(upperName).append("(").append(type)
                .append(" ").append(name).append(") {\n");
        deSr.append("\t\tthis.").append(name).append(" = ").append(name)
                .append(";\n");
        deSr.append("\t}\n");
        deSr.append("\n");
    }
}

NodeProperty记录xml节点的内容

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Hashtable;

/** *//**
 * 记录xml内容的java类
 */
public class NodeProperty implements Serializable {

    public static final String NODE_TABLE = "Table";
    public static final String NODE_COLUMNS = "Columns";
    public static final String NODE_COLUMN = "Column";
    
    public static final String PROPERTY_NAME = "Name";
    public static final String PROPERTY_ID = "Id";
    public static final String PROPERTY_DESCRIPT = "Descript";
    public static final String PROPERTY_G = "G";
    public static final String PROPERTY_P = "P";
    public static final String PROPERTY_COMMENT = "Comment";
    public static final String PROPERTY_DATATYPE = "DataType";
    public static final String PROPERTY_MANDATORY = "Mandatory";
    public static final String PROPERTY_UNSIGNED = "Unsigned";
    public static final String PROPERTY_PRIMARYKEY = "isPrimaryKey";
    public static final String PROPERTY_LENGTH = "Length";
    
    private String type = null;
    private List<NodeProperty> child = null;
    private Map<String, String> property = null;
    private String value = null;

    private NodeProperty() {
        this.child = new ArrayList<NodeProperty>();
        this.property = new Hashtable<String, String>();
    }

    public void addChild(NodeProperty fp) {
        child.add(fp);
    }

    public List<NodeProperty> getChild() {
        return child;
    }

    public void addProperty(String key, String value) {
        property.put(key, value);
    }

    public String getProperty(String key) {
        return property.get(key);
    }

    public static NodeProperty getInstance(String type) {
        NodeProperty xml = new NodeProperty();
        xml.setType(type);
        return xml;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String toXML() {
        StringBuilder sb = new StringBuilder();
        sb.append("<").append(type);
        Iterator<Entry<String, String>> it = property.entrySet().iterator();
        while (it.hasNext()) {
            sb.append(" ");
            Entry<String, String> e = it.next();
            sb.append(e.getKey()).append("=\"").append(e.getValue())
                    .append("\"");
        }
        if (child.size() > 0) {
            sb.append(">\n");
            for (NodeProperty node : child) {
                sb.append(node.toXML());
            }
            sb.append("\n</").append(type).append(">");
        } else {
            sb.append(" />");
        }
        return sb.toString();
    }

    /** *//**
     * 获取当前节点中指定类型的子节点
     * @param type
     * @return
     */
    public NodeProperty getChildByType(String type) {
        NodeProperty result = null;
        if (this.child.size() > 0) {
            for (NodeProperty np : child) {
                if (np.getType().equals(type)) {
                    result = np;
                }
            }
        }
        return result;
    }

    public void removeChild() {
        if (child.size() > 0) {
            int size = child.size();
            for (int i = 0; i < size; i++) {
                child.remove(0);
            }
        }
    }

    public String toString() {
        return this.getProperty(PROPERTY_NAME) == null ? "" : getProperty(PROPERTY_NAME);
    }
}

下面看一下在dbdesigner的运行效果:





添加数据库连接











//生成的实体类效果

package com.penngo.model;

import java.io.Serializable;
import javax.persistence.*;
import java.math.BigInteger;

@Entity
@Table(name ="jbpm4_deployment")
public class Jbpm4_deployment implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name ="DBID_")
    private BigInteger dbid_;

    @Column(name ="NAME_")
    private String name_;

    @Column(name ="TIMESTAMP_")
    private BigInteger timestamp_;

    @Column(name ="STATE_", length=255)
    private String state_;

    public BigInteger getDbid_() {
        return dbid_;
    }

    public void setDbid_(BigInteger dbid_) {
        this.dbid_ = dbid_;
    }

    public String getName_() {
        return name_;
    }

    public void setName_(String name_) {
        this.name_ = name_;
    }

    public BigInteger getTimestamp_() {
        return timestamp_;
    }

    public void setTimestamp_(BigInteger timestamp_) {
        this.timestamp_ = timestamp_;
    }

    public String getState_() {
        return state_;
    }

    public void setState_(String state_) {
        this.state_ = state_;
    }
}