mysql客户端反向映射entity_数据库反向生成实体类

数据库反向生成实体类是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主要提供获取数据库表和表字段信息的方法

importjava.sql.Connection;

importjava.sql.DriverManager;

importjava.sql.SQLException;

importjava.util.ArrayList;

importjava.util.List;

importjava.util.Map;

importorg.apache.commons.dbutils.DbUtils;

importorg.apache.commons.dbutils.QueryRunner;

importorg.apache.commons.dbutils.ResultSetHandler;

importorg.apache.commons.dbutils.handlers.ArrayListHandler;

importorg.apache.commons.dbutils.handlers.MapListHandler;

importorg.dbdesigner.ui.component.ConnectDbDialog;

importorg.dbdesigner.ui.util.NodeProperty;

publicclassDBDao{

privateNodeProperty conConfig;

publicDBDao(NodeProperty con){

this.conConfig=con;

    }

privateConnection getConnect()throwsInstantiationException,

            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();

returnDriverManager.getConnection(strUrl, strUser, strPass);

    }

    

/** *//**     * 获取所有数据表

*/

publicArrayList getAllTable(){

        ArrayList result=null;

        Connection con=null;

try{

            con=getConnect();

            QueryRunner qr=newQueryRunner();

            ResultSetHandler rsh=newArrayListHandler();

            String strsql="show tables";//show tables为mysql获取所有表的方法result=qr.query(con, strsql, rsh);

//for (int i = 0; i 

//System.out.println(((Object[]) result.get(i))[0]);

//}

}catch(Exception ex){

            ex.printStackTrace(System.out);

        }finally{

try{

                DbUtils.close(con);

            }catch(Exception ex){

            }        }returnresult;

    }

/** *//**     * 获取指定表的字段信息

     *@paramt 表名

*/

publicNodeProperty getTableDescribe(String t){

        Connection con=null;

        NodeProperty table=NodeProperty.getInstance(NodeProperty.NODE_TABLE);

        table.addProperty(NodeProperty.PROPERTY_NAME, t);

try{

            con=getConnect();

            QueryRunner qr=newQueryRunner();

            String strsql="describe"+t;

            List results=qr.query(con, strsql,newMapListHandler());

            NodeProperty columns=NodeProperty

                    .getInstance(NodeProperty.NODE_COLUMNS);

            table.addChild(columns);

for(inti=0; 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();

booleanisNullable=nullable.equals("YES")?true:false;

                String type=map.get("COLUMN_TYPE").toString();

                String unsigned="false";

intindex=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){

            }        }returntable;

    }}

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

importjava.util.List;

importorg.dbdesigner.db.MySqlDataType;

importorg.dbdesigner.ui.component.DBGraphComponent;

importorg.dbdesigner.ui.util.NodeProperty;

importorg.dbdesigner.utils.FileUtil;

importorg.dbdesigner.utils.StringUtil;

publicclassGenerateEntity{

privateString filePath=null;

privateString packageName=null;

publicGenerateEntity(String filePath, String packageName, DBGraphComponent graphComponent){

this.filePath=filePath;

this.packageName=packageName;

    }    

//根据DBDao.getTableDescribe(String t)方法返回的NodeProperty内容，生成相应的实体类

publicvoidNodeToEntity(NodeProperty node){

        StringBuilder sr=newStringBuilder();

        StringBuilder declareSr=newStringBuilder();

        StringBuilder methodSr=newStringBuilder();

        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){

            Listlist=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();

        }    }    

//生成字段声明内容

publicvoidgenerateDeclare(StringBuilder deSr, NodeProperty column, String dataType){

intstart=dataType.indexOf("(");

intend=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方法

publicvoidgenerateMethod(StringBuilder deSr, NodeProperty column, String dataType){

        String name=column.getProperty(NodeProperty.PROPERTY_NAME);

        String upperName=StringUtil.upperFirst(name);

intstart=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节点的内容

importjava.io.Serializable;

importjava.util.ArrayList;

importjava.util.Iterator;

importjava.util.List;

importjava.util.Map;

importjava.util.Map.Entry;

importjava.util.Hashtable;

/** *//** * 记录xml内容的java类

*/

publicclassNodePropertyimplementsSerializable{

publicstaticfinalString NODE_TABLE="Table";

publicstaticfinalString NODE_COLUMNS="Columns";

publicstaticfinalString NODE_COLUMN="Column";

    

publicstaticfinalString PROPERTY_NAME="Name";

publicstaticfinalString PROPERTY_ID="Id";

publicstaticfinalString PROPERTY_DESCRIPT="Descript";

publicstaticfinalString PROPERTY_G="G";

publicstaticfinalString PROPERTY_P="P";

publicstaticfinalString PROPERTY_COMMENT="Comment";

publicstaticfinalString PROPERTY_DATATYPE="DataType";

publicstaticfinalString PROPERTY_MANDATORY="Mandatory";

publicstaticfinalString PROPERTY_UNSIGNED="Unsigned";

publicstaticfinalString PROPERTY_PRIMARYKEY="isPrimaryKey";

publicstaticfinalString PROPERTY_LENGTH="Length";

    

privateString type=null;

privateListchild=null;

privateMapproperty=null;

privateString value=null;

privateNodeProperty(){

this.child=newArrayList();

this.property=newHashtable();

    }

publicvoidaddChild(NodeProperty fp){

        child.add(fp);

    }

publicListgetChild(){

returnchild;

    }

publicvoidaddProperty(String key, String value){

        property.put(key, value);

    }

publicString getProperty(String key){

returnproperty.get(key);

    }

publicstaticNodeProperty getInstance(String type){

        NodeProperty xml=newNodeProperty();

        xml.setType(type);

returnxml;

    }

publicString getValue(){

returnvalue;

    }

publicvoidsetValue(String value){

this.value=value;

    }

publicString getType(){

returntype;

    }

publicvoidsetType(String type){

this.type=type;

    }

publicString toXML(){

        StringBuilder sb=newStringBuilder();

        sb.append("

        Iterator>it=property.entrySet().iterator();

while(it.hasNext()){

            sb.append("");

            Entrye=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("/>");

        }returnsb.toString();

    }

/** *//**     * 获取当前节点中指定类型的子节点

     *@paramtype

     *@return*/

publicNodeProperty getChildByType(String type){

        NodeProperty result=null;

if(this.child.size()>0){

for(NodeProperty np : child){

if(np.getType().equals(type)){

                    result=np;

                }            }        }returnresult;

    }

publicvoidremoveChild(){

if(child.size()>0){

intsize=child.size();

for(inti=0; i{

                child.remove(0);

            }        }    }

publicString toString(){

returnthis.getProperty(PROPERTY_NAME)==null?"": getProperty(PROPERTY_NAME);

    }}

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

添加数据库连接

//生成的实体类效果

packagecom.penngo.model;

importjava.io.Serializable;

importjavax.persistence.*;

importjava.math.BigInteger;

@Entity

@Table(name="jbpm4_deployment")

publicclassJbpm4_deploymentimplementsSerializable{

privatestaticfinallongserialVersionUID=1L;

    @Id

    @Column(name="DBID_")

privateBigInteger dbid_;

    @Column(name="NAME_")

privateString name_;

    @Column(name="TIMESTAMP_")

privateBigInteger timestamp_;

    @Column(name="STATE_", length=255)

privateString state_;

publicBigInteger getDbid_(){

returndbid_;

    }

publicvoidsetDbid_(BigInteger dbid_){

this.dbid_=dbid_;

    }

publicString getName_(){

returnname_;

    }

publicvoidsetName_(String name_){

this.name_=name_;

    }

publicBigInteger getTimestamp_(){

returntimestamp_;

    }

publicvoidsetTimestamp_(BigInteger timestamp_){

this.timestamp_=timestamp_;

    }

publicString getState_(){

returnstate_;

    }

publicvoidsetState_(String state_){

this.state_=state_;

    }}