Posted on 2011-04-30 10:57
penngo 閱讀(7258)
評論(4) 編輯 收藏 所屬分類:
Java
數(shù)據(jù)庫反向生成實體類是dbdesigner的一個功能，現(xiàn)在很多開發(fā)工具也帶有這樣的功能。實現(xiàn)原理也很簡單：先通過SQL語句獲取所有數(shù)據(jù)庫表，再通過SQL語句獲取表的結(jié)構(gòu)，最后根據(jù)表的字段信息生成相應(yīng)的hibernate實體類。
本文的初步實現(xiàn)代碼只考慮mysql 5.1版本，其它版本數(shù)據(jù)庫暫不考慮。
獲取所有表的SQl：
show tables
獲取表字段信息的SQL有兩種：
(1) SELECT COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT FROM information_schema.columns WHERE table_name = 表名
(2) describe 表名
主要的實現(xiàn)代碼如下
DBDao主要提供獲取數(shù)據(jù)庫表和表字段信息的方法
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);
}
/** *//**
* 獲取所有數(shù)據(jù)表
*/
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主要是根據(jù)NodeProperty的內(nèi)容，生成實體類。
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;
}
//根據(jù)DBDao.getTableDescribe(String t)方法返回的NodeProperty內(nèi)容，生成相應(yīng)的實體類
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();
}
}
//生成字段聲明內(nèi)容
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節(jié)點的內(nèi)容
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內(nèi)容的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();
}
/** *//**
* 獲取當(dāng)前節(jié)點中指定類型的子節(jié)點
* @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的運(yùn)行效果:
添加數(shù)據(jù)庫連接
//生成的實體類效果
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