数据库反向生成实体类

本文介绍了一种从现有数据库表结构反向生成Hibernate实体类的方法。通过SQL语句获取数据库表及其字段信息,并利用Java代码将这些信息转换成实体类。此过程包括连接数据库、获取表结构和字段特性,最终生成包含相应属性和方法的实体类。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

数据库反向生成实体类是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的运行效果:

http://www.blogjava.net/pengo/

添加数据库连接

http://www.blogjava.net/pengo/

http://www.blogjava.net/pengo/

http://www.blogjava.net/pengo/

http://www.blogjava.net/pengo/

 //生成的实体类效果

 

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_;
    }
}

备注:转载自http://www.blogjava.net

### 自动生成实体类代码的方法 为了基于现有的数据库表结构自动生成实体类代码,可以采用多种方法和技术来实现这一目标。通常情况下,自动化工具会解析数据库模式并将其映射到编程语言中的类定义。 #### 使用ORM框架生成实体类 许多现代开发环境提供了对象关系映射(ORM)框架的支持,这些框架内置了从数据库生成实体类的功能。例如,在.NET环境中,Entity Framework Core可以通过命令行工具轻松完成这项工作;而在Java生态系统里,则有Hibernate Tools这样的插件可用[^1]。 对于Python开发者而言,SQLAlchemy是一个非常流行的库,它不仅支持手动编写模型文件,还提供了一个名为`sqlacodegen`的第三方工具用于扫描数据库并将结果转换成Python类形式: ```bash pip install sqlacodegen sqlacodegen mysql+pymysql://username:password@localhost/dbname > models.py ``` 这段命令将会连接指定的MySQL服务器,并导出所有表格对应的Pydantic或标准SQLAlchemy ORM模型至`models.py`文件中。 #### 利用手动编写的脚本 如果项目需求较为特殊或者所使用的编程语言缺乏现成解决方案时,也可以考虑自己动手写一段简单的脚本来处理这个问题。下面给出了一种可能的方式——利用JDBC驱动读取元数据信息并输出为Java POJO的形式: ```java import java.sql.*; public class EntityGenerator { public static void main(String[] args) throws SQLException, ClassNotFoundException { Class.forName("com.mysql.cj.jdbc.Driver"); try (Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost/test", "root", "")) { DatabaseMetaData metaData = conn.getMetaData(); ResultSet tables = metaData.getTables(null, null, "%", new String[]{"TABLE"}); while(tables.next()){ System.out.println("@Data\n" + "@AllArgsConstructor\n" + "@NoArgsConstructor\n"+ "public class "+tables.getString("TABLE_NAME")+" {"); ResultSet columns = metaData.getColumns(null,null,tables.getString("TABLE_NAME"),null); while(columns.next()) { String columnName = columns.getString("COLUMN_NAME").toLowerCase(); String columnType = getJavaTypeFromSqlType(columns.getInt("DATA_TYPE")); System.out.printf("\tprivate %s %s;\n",columnType,columnName); } System.out.print("}\n\n"); } } catch(Exception e){ throw new RuntimeException(e.getMessage()); } } private static String getJavaTypeFromSqlType(int typeCode){ switch(typeCode){ case Types.VARCHAR : return "String"; case Types.INTEGER : return "Integer"; default : return "Object"; } } } ``` 此段程序遍历给定数据库下的每一个表及其字段列表,按照一定的模板格式打印出相应的POJO类声明语句。当然实际应用当中还需要进一步完善错误处理机制、增加对外键关联的支持等功能特性。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值