每日一博 | 获取 PO 注解信息生成表 sql

开发过程中，常常创建了PO之后，还要去数据库里创建对应的表，相似的过程，却要做两次，有没有感觉很麻烦？使用Hibernate可以生成表，还要去配置，而且无法得到表创建sql，于是 DBGenerator 应运而生了。DBGenerator是一个工具类，能获取PO类的注解信息，自动生成表创建SQL，节省了很多时间，同时也能保证测试环境和生产环境表的一致性。

1. jar包依赖

commons-lang-2.6.jar

hibernate3.jar

2. 源码

package com.cg.db;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;

import org.apache.commons.lang.StringUtils;

import com.sun.xml.internal.bind.v2.model.core.ID;

/** 
 *
 * <p>根据PO生成表创建sql<p>
 * <p>根据read方法上的注解，生成数据库字段信息，没有read方法的Field跳过<p>
 * @since jdk1.6
 * @date 2016-8-15
 */
public class DBGenerator {

	/**
	 * 
	 * 根据类的注解信息生成表sql
	 * @param clazz
	 * @return
	 */
	public String generate(Class clazz){
		if(!clazz.isAnnotationPresent(Table.class)){
			throw new RuntimeException("No Annotation[Table] founded in class["+clazz.getName()+"]");
		}
		Table table = (Table)clazz.getAnnotation(Table.class);
		String tableName = table.name();
		StringBuilder sb = new StringBuilder("-- Create table/n");
		sb.append("create table " + tableName + "/n");
		sb.append("(/n");
		Field[] fieldArr = clazz.getDeclaredFields();
		int maxLength = getMaxLength(clazz, fieldArr);
		String primaryKey = generateFields(clazz, sb, fieldArr, maxLength);
		sb.append(");/n");
		generatePrimaryKey(sb, tableName, primaryKey);
		return sb.toString();
	}
	 
	/** 
	 * 生成sql字段
	 * @param clazz
	 * @param sb
	 * @param fieldArr
	 * @param primaryKey
	 * @param maxLength
	 * @return  
	 */
	  	
	private String generateFields(Class clazz, StringBuilder sb,
			Field[] fieldArr, int maxLength) {
		String primaryKey = "";
		Field field;
		for(int i=0;i<fieldArr.length;i++){
			field = fieldArr[i];
			int offset;
			try {
				PropertyDescriptor pd = new PropertyDescriptor(field.getName(), clazz);
				Method mt = pd.getReadMethod();
				if(null != mt){
					Column col = (Column)mt.getAnnotation(Column.class);
					writeBlankSpace(sb, 2);
					sb.append(col.name());
					offset = maxLength - col.name().length();
					writeBlankSpace(sb, offset);
					writeBlankSpace(sb, 1);
					sb.append(convertJava2Oracle(field, col));
					if(mt.isAnnotationPresent(Id.class)){
						sb.append(" not null");
						primaryKey = col.name();
					}
				}
			} catch (IntrospectionException e) {
				//找不到readMethod,跳过
				//throw new RuntimeException("No " + field.getName() + "'s ReadMethod founded in class["+clazz.getName()+"]");
				continue;
			}
			if(i != (fieldArr.length -1)){
				sb.append(",");
			}
			sb.append("/n");
		}
		return primaryKey;
	}
	 
	/** 
	 * 生成主键sql
	 * @param tableName
	 * @param sb
	 * @param primaryKey  
	 */
	  	
	private void generatePrimaryKey(StringBuilder sb, String tableName, String primaryKey) {
		if(StringUtils.isNotBlank(primaryKey)){
			sb.append("alter table " + tableName + "/n");
			sb.append("  add primary key (" + primaryKey + ")/n");
			sb.append("  using index;");
		}
	}
	 
	/** 
	 * 获得Field最大长度
	 * @param clazz
	 * @param fieldArr  
	 */
	  	
	private int getMaxLength(Class clazz, Field[] fieldArr) {
		Field field;
		int maxLength = 0;
		for(int i=0;i<fieldArr.length;i++){
			field = fieldArr[i];
			PropertyDescriptor pd;
			try {
				pd = new PropertyDescriptor(field.getName(), clazz);
				Method mt = pd.getReadMethod();
				if(null != mt){
					if(!mt.isAnnotationPresent(Column.class)){
						throw new RuntimeException("No Annotation[Column] founded in Method["+mt.getName()+"]");
					}
					Column col = (Column)mt.getAnnotation(Column.class);
					int temLength = col.name().length();
					if(temLength > maxLength){
						maxLength = temLength;
					}
				}
			} catch (IntrospectionException e) {
				//找不到readMethod,跳过
				//throw new RuntimeException("No " + field.getName() + "'s ReadMethod founded in class["+clazz.getName()+"]");
				continue;
			}
		}
		return maxLength;
	}
	
	/**
	 * 
	 * 将java类型转换为orable类型
	 * @param field
	 * @param col
	 * @return
	 */
	private String convertJava2Oracle(Field field, Column col){
		//TODO 此处可根据自己需要做类型转换
		String type = "VARCHAR2";
		if(field.getType().equals(String.class)){
			type = "VARCHAR2(" + col.length() + ")";
		}else if(field.getType().equals(java.util.Date.class) || field.getType().equals(java.sql.Date.class)){
			type = "DATE";
		}else if(field.getType().equals(Integer.class)){
			type = "NUMBER";
		}
		return type;
	}
	
	/**
	 * 
	 * 输出空格
	 * @param sb
	 * @param n
	 */
	private void writeBlankSpace(StringBuilder sb, int n){
		for(int i=0;i<n;i++){
			sb.append(" ");
		}
	}
}

3. 事例

1.TBankCode类，使用了注解@Table、@Id、@Column标识了表名、主键、列信息， DBGenerator能读取类的注解信息，生成对应sql。（ps：由于本人习惯将 Column加到get方法上，在开发过程中限定DBGenerator 只读取get方法上的注解，此外对应数据库（oracle）的类型只做了几个常用的转换，可根据自己需要调整。）

package com.cg.db;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

import org.hibernate.annotations.GenericGenerator;

@Table(name = "T_BANK_CODE")
public class TBankCode implements java.io.Serializable{

	/**
	 * 
	 */
	private static final long serialVersionUID = -9047055962822128897L;
	/**
	 * 主键id
	 */
	private String id;
	/***省名 */
	private String province;
	/*** 省代码*/
	private String provinceNo;
	/*** 地区名 */
	private String area;
	/*** 地区代码*/
	private String areaNo;
	/*** 银行行名称*/
	private String bankName;
	/*** 银行号*/
	private String bankNo;
	/*** 分行名称 */
	private String branchBankName;
	/*** 分行联行号*/
	private String branchBankCode;
	
	
	@Id
	@Column(name = "ID", unique = true, nullable = false, length = 40)
	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	@Column(name = "PROVINCE", length = 100)
	public String getProvince() {
		return province;
	}

	public void setProvince(String province) {
		this.province = province;
	}

	@Column(name = "AREA", length = 100)
	public String getArea() {
		return area;
	}

	public void setArea(String area) {
		this.area = area;
	}
	
	@Column(name = "AREA_NO", length = 10)
	public String getAreaNo() {
		return areaNo;
	}

	public void setAreaNo(String areaNo) {
		this.areaNo = areaNo;
	}

	@Column(name = "PROVINCE_NO", length = 10)
	public String getProvinceNo() {
		return provinceNo;
	}

	public void setProvinceNo(String provinceNo) {
		this.provinceNo = provinceNo;
	}

	@Column(name = "BANK_NAME", length = 100)
	public String getBankName() {
		return bankName;
	}

	public void setBankName(String bankName) {
		this.bankName = bankName;
	}

	@Column(name = "BANK_NO", length = 30)
	public String getBankNo() {
		return bankNo;
	}

	public void setBankNo(String bankNo) {
		this.bankNo = bankNo;
	}

	@Column(name = "BRANCH_BANK_NAME", length = 100)
	public String getBranchBankName() {
		return branchBankName;
	}

	public void setBranchBankName(String branchBankName) {
		this.branchBankName = branchBankName;
	}

	@Column(name = "BRANCH_BANK_CODE", length = 30)
	public String getBranchBankCode() {
		return branchBankCode;
	}

	public void setBranchBankCode(String branchBankCode) {
		this.branchBankCode = branchBankCode;
	}
}

2.实例化 DBGenerator 调用generate方法，传递参数TBankCode.class

package com.cg.db;

public class TestRun {

	public static void main(String[] args) {
		String a = new DBGenerator().generate(TBankCode.class);
		System.out.println(a);
	}
}

3.运行结果

-- Create table
create table T_BANK_CODE
(
  ID               VARCHAR2(40) not null,
  PROVINCE         VARCHAR2(100),
  PROVINCE_NO      VARCHAR2(10),
  AREA             VARCHAR2(100),
  AREA_NO          VARCHAR2(10),
  BANK_NAME        VARCHAR2(100),
  BANK_NO          VARCHAR2(30),
  BRANCH_BANK_NAME VARCHAR2(100),
  BRANCH_BANK_CODE VARCHAR2(30)
);
alter table T_BANK_CODE
  add primary key (ID)
  using index;