|
|
Posted on 2011-04-30 10:57 penngo 阅读(7224) 评论(4) 编辑 收藏 所属分类: Java
数据库反向生成实体类是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_;
}
}
附件:DBDesigner.jar
评论
# re: 数据库反向生成实体类 回复 更多评论
2011-05-02 00:19 by
不错 之前有过类似的想法 但是没动手做~~
# re: 数据库反向生成实体类[未登录] 回复 更多评论
2011-05-23 17:06 by
# re: 数据库反向生成实体类 回复 更多评论
2014-03-04 10:10 by
楼主很赞，不过想请教个问题，那些有外键关系的你有什么想法？
# re: 数据库反向生成实体类 回复 更多评论
2015-10-10 11:28 by
很强大，感谢分享，学习了~!!!
|