Hibernate动态建表

作者: Michael 日期: 2012 年 2 月 14 日发表评论 (0) 查看评论

背景：由于项目特殊的应用场景，需要实现一个动态创建表的功能。

基本思路：
查了一些资料同时结合到项目里用到了hibernate，就想到利用hibernate的SchemaExport 来实现动态建表

设计两个javabean：FormTable(表的基本属性)、ColumnAttribute（列的基本属性），实现一对多的关系
Freemarker 可以根据定义好的模板生成 hibernate配置文件

提供完整的源代码下载见附件：dynamic_db_table

lib文件比较多，就不提供了下载了，提供一个lib文件的截图如下

下面是本人测试的主要代码的片段:

package com.michael;

import java.util.ArrayList;
import java.util.List;

import com.michael.vo.ColumnAttribute;
import com.michael.vo.FormTable;

/**
 * @author Michael
 *
 */
public class TestMain {
    /**
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        TestMain test = new TestMain();
        FormTable fromTable = test.initData();

        TableGenerator tg = new TableGenerator(fromTable);
        tg.generatorTable();

    }

    /**
     * 初始化数据
     * @return
     */
    private FormTable initData() {
        FormTable form = new FormTable();
        form.setName("testTable");
        form.setTableName("TB_GEN");

        List<ColumnAttribute> list = new ArrayList<ColumnAttribute>();
        ColumnAttribute attr = new ColumnAttribute();
        attr.setName("collectKey");
        attr.setColumnType("string");
        attr.setColumnName("COLLECTKEY");
        attr.setLength(100);
        list.add(attr);
        ColumnAttribute attr1 = new ColumnAttribute();
        attr1.setName("mibVal");
        attr1.setColumnType("string");
        attr1.setColumnName("MIBVAL");
        attr1.setLength(100);
        list.add(attr1);
        ColumnAttribute attr2 = new ColumnAttribute();
        attr2.setName("dsname");
        attr2.setColumnType("string");
        attr2.setColumnName("DSNAME");
        attr2.setLength(100);
        list.add(attr2);
        ColumnAttribute attr3 = new ColumnAttribute();
        attr3.setName("timestamp");
        attr3.setColumnType("long");
        attr3.setColumnName("TIMESTAMP");
        list.add(attr3);
        form.setFormAttributeList(list);
        return form;
    }

}

package com.michael;

import java.util.ArrayList;

import java.util.List;

import com.michael.vo.ColumnAttribute;

import com.michael.vo.FormTable;

/**

* @author Michael

public class TestMain {

/**

* @param args

* @throws Exception

public static void main(String[] args) throws Exception {

TestMain test = new TestMain();

FormTable fromTable = test.initData();

TableGenerator tg = new TableGenerator(fromTable);

tg.generatorTable();

}

/**

* 初始化数据

* @return

private FormTable initData() {

FormTable form = new FormTable();

form.setName("testTable");

form.setTableName("TB_GEN");

List<ColumnAttribute> list = new ArrayList<ColumnAttribute>();

ColumnAttribute attr = new ColumnAttribute();

attr.setName("collectKey");

attr.setColumnType("string");

attr.setColumnName("COLLECTKEY");

attr.setLength(100);

list.add(attr);

ColumnAttribute attr1 = new ColumnAttribute();

attr1.setName("mibVal");

attr1.setColumnType("string");

attr1.setColumnName("MIBVAL");

attr1.setLength(100);

list.add(attr1);

ColumnAttribute attr2 = new ColumnAttribute();

attr2.setName("dsname");

attr2.setColumnType("string");

attr2.setColumnName("DSNAME");

attr2.setLength(100);

list.add(attr2);

ColumnAttribute attr3 = new ColumnAttribute();

attr3.setName("timestamp");

attr3.setColumnType("long");

attr3.setColumnName("TIMESTAMP");

list.add(attr3);

form.setFormAttributeList(list);

return form;

}

TableGenerator.java:

package com.michael;

import java.io.StringWriter;
import java.io.Writer;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;

import org.hibernate.cfg.Configuration;
import org.hibernate.tool.hbm2ddl.SchemaExport;

import com.michael.vo.FormTable;

import freemarker.template.Template;

/**
 * @author Michael
 *
 */
public class TableGenerator {

    /**
     * tableVo
     */
    private FormTable tableVo;

    /**
     * 脚本文件
     */
    private String scriptFileName = "d:/test/table.sql";

    /**
     * 构造函数
     * @param tableVo
     */
    public TableGenerator(FormTable tableVo) {
        this.tableVo = tableVo;
    }

    /**
     * 构造函数
     * @param tableVo
     * @param scriptFileName
     */
    public TableGenerator(FormTable tableVo, String scriptFileName) {
        this.tableVo = tableVo;
        if (null != scriptFileName && !"".equals(scriptFileName)) {
            this.scriptFileName = scriptFileName;
        }
    }

    /**
     *
     */
    public void generatorTable() {
        if (tableVo.getColumnAttrList().isEmpty()) {
            System.out.println(" column attr list size==0 ");
            return;
        }

        Template tl;
        try {
            Map<String, Object> paramMap = new HashMap<String, Object>();
            paramMap.put("entity", tableVo);

            tl = getTemplateConfig("/com/michael/ftl").getTemplate(
                    "template.hb.ftl");
            Writer out = new StringWriter();
            tl.process(paramMap, out);
            String hbxml = out.toString();
            System.out.println(hbxml);
            Configuration hbcfg = this.getHibernateCfg(hbxml);

            // Properties pp = CommonUtil
            // .getPropertiesByResource(Constant.PPFILENAME);
            // DataSource ds = BasicDataSourceFactory.createDataSource(pp);

            createDbTableByCfg(hbcfg);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取freemarker的cfg
     * @param resource
     * @return Configuration
     */
    protected freemarker.template.Configuration getTemplateConfig(
            String resource) {

        freemarker.template.Configuration cfg = new freemarker.template.Configuration();
        cfg.setDefaultEncoding("UTF-8");
        cfg.setClassForTemplateLoading(this.getClass(), resource);
        return cfg;
    }

    /**
     * 处理hibernate的配置文件
     * @param resource
     */
    protected Configuration getHibernateCfg(String hbxml) {
        org.hibernate.cfg.Configuration hbcfg = new org.hibernate.cfg.Configuration();
        hbcfg.configure("/hibernate.cfg.xml");
        Properties extraProp = new Properties();
        extraProp.put("hibernate.hbm2ddl.auto", "update");
        hbcfg.addProperties(extraProp);
        hbcfg.addXML(hbxml);
        return hbcfg;
    }

    /**
     * 根据hibernate cfg配置文件动态建表
     * @param hbcfg
     */
    public void createDbTableByCfg(Configuration hbcfg) {
        SchemaExport schemaExport;
        try {
            schemaExport = new SchemaExport(hbcfg);
            // 设置脚本文件
            schemaExport.setOutputFile(scriptFileName);
            schemaExport.create(true, true);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据配置文件、Connection 来动态建表
     * @param conf
     * @param ds
     */
    public void createDbTableByConn(Configuration conf, DataSource ds) {
        SchemaExport schemaExport;
        try {

            schemaExport = new SchemaExport(conf, ds.getConnection());
            schemaExport.setOutputFile(scriptFileName);
            schemaExport.create(true, true);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

package com.michael;

import java.io.StringWriter;

import java.io.Writer;

import java.util.HashMap;

import java.util.Map;

import java.util.Properties;

import javax.sql.DataSource;

import org.hibernate.cfg.Configuration;

import org.hibernate.tool.hbm2ddl.SchemaExport;

import com.michael.vo.FormTable;

import freemarker.template.Template;

/**

* @author Michael

public class TableGenerator {

/**

* tableVo

private FormTable tableVo;

/**

* 脚本文件

private String scriptFileName = "d:/test/table.sql";

/**

* 构造函数

* @param tableVo

public TableGenerator(FormTable tableVo) {

this.tableVo = tableVo;

}

/**

* 构造函数

* @param tableVo

* @param scriptFileName

public TableGenerator(FormTable tableVo, String scriptFileName) {

this.tableVo = tableVo;

if (null != scriptFileName && !"".equals(scriptFileName)) {

this.scriptFileName = scriptFileName;

}

/**

public void generatorTable() {

if (tableVo.getColumnAttrList().isEmpty()) {

System.out.println(" column attr list size==0 ");

return;

}

Template tl;

try {

Map<String, Object> paramMap = new HashMap<String, Object>();

paramMap.put("entity", tableVo);

tl = getTemplateConfig("/com/michael/ftl").getTemplate(

"template.hb.ftl");

Writer out = new StringWriter();

tl.process(paramMap, out);

String hbxml = out.toString();

System.out.println(hbxml);

Configuration hbcfg = this.getHibernateCfg(hbxml);

// Properties pp = CommonUtil

// .getPropertiesByResource(Constant.PPFILENAME);

// DataSource ds = BasicDataSourceFactory.createDataSource(pp);

createDbTableByCfg(hbcfg);

} catch (Exception e) {

e.printStackTrace();

}

/**

* 获取freemarker的cfg

* @param resource

* @return Configuration

protected freemarker.template.Configuration getTemplateConfig(

String resource) {

freemarker.template.Configuration cfg = new freemarker.template.Configuration();

cfg.setDefaultEncoding("UTF-8");

cfg.setClassForTemplateLoading(this.getClass(), resource);

return cfg;

}

/**

* 处理hibernate的配置文件

* @param resource

protected Configuration getHibernateCfg(String hbxml) {

org.hibernate.cfg.Configuration hbcfg = new org.hibernate.cfg.Configuration();

hbcfg.configure("/hibernate.cfg.xml");

Properties extraProp = new Properties();

extraProp.put("hibernate.hbm2ddl.auto", "update");

hbcfg.addProperties(extraProp);

hbcfg.addXML(hbxml);

return hbcfg;

}

/**

* 根据hibernate cfg配置文件动态建表

* @param hbcfg

public void createDbTableByCfg(Configuration hbcfg) {

SchemaExport schemaExport;

try {

schemaExport = new SchemaExport(hbcfg);

// 设置脚本文件

schemaExport.setOutputFile(scriptFileName);

schemaExport.create(true, true);

} catch (Exception e) {

e.printStackTrace();

}

/**

* 根据配置文件、Connection 来动态建表

* @param conf

* @param ds

public void createDbTableByConn(Configuration conf, DataSource ds) {

SchemaExport schemaExport;

try {

schemaExport = new SchemaExport(conf, ds.getConnection());

schemaExport.setOutputFile(scriptFileName);

schemaExport.create(true, true);

} catch (Exception e) {

e.printStackTrace();

}

Hibernate配置模板template.hb.ftl:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping
  PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
         "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <class
        name="${entity.name}"
        table="${entity.tableName}"
        dynamic-update="false"
        dynamic-insert="false"
        select-before-update="false"
        optimistic-lock="version">
        <id
            name="id"
            column="ID"
            type="long"
            unsaved-value="null">
            <generator class="native" />
        </id>
        <#if entity.columnAttrList?exists>
            <#list entity.columnAttrList as attr>
                <#if attr.name == "id">
                <#elseif attr.columnType=="string">
        <property
            name="${attr.name}"
            type="java.lang.String"
            update="true"
            insert="true"
            access="property"
            column="${attr.columnName}"
            length="${attr.length}"
            not-null="false"
            unique="false"
        />
        <#else>
        <property
            name="${attr.name}"
            type="${attr.columnType}"
            update="true"
            insert="true"
            access="property"
            column="`${attr.columnName}`"
            not-null="false"
            unique="false"
        />

                </#if>
            </#list>
        </#if>
    </class>
</hibernate-mapping>

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping

PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"

"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

<class

name="${entity.name}"

table="${entity.tableName}"

dynamic-update="false"

dynamic-insert="false"

select-before-update="false"

optimistic-lock="version">

<id

name="id"

column="ID"

type="long"

unsaved-value="null">

</id>

<#if entity.columnAttrList?exists>

<#list entity.columnAttrList as attr>

<#if attr.name == "id">

<#elseif attr.columnType=="string">

<property

name="${attr.name}"

type="java.lang.String"

update="true"

insert="true"

access="property"

column="${attr.columnName}"

length="${attr.length}"

not-null="false"

unique="false"

<#else>

<property

name="${attr.name}"

type="${attr.columnType}"

update="true"

insert="true"

access="property"

column="`${attr.columnName}`"

not-null="false"

unique="false"

</#if>

</#list>

</#if>

</class>

</hibernate-mapping>

运行的log信息如下：

2010-12-12 13:57:28 org.hibernate.cfg.Environment <clinit>
信息: Hibernate 3.2.5
2010-12-12 13:57:28 org.hibernate.cfg.Environment <clinit>
信息: hibernate.properties not found
2010-12-12 13:57:28 org.hibernate.cfg.Environment buildBytecodeProvider
信息: Bytecode provider name : cglib
2010-12-12 13:57:28 org.hibernate.cfg.Environment <clinit>
信息: using JDK 1.4 java.sql.Timestamp handling
2010-12-12 13:57:28 org.hibernate.cfg.Configuration configure
信息: configuring from resource: /hibernate.cfg.xml
2010-12-12 13:57:28 org.hibernate.cfg.Configuration getConfigurationInputStream
信息: Configuration resource: /hibernate.cfg.xml
2010-12-12 13:57:29 org.hibernate.cfg.Configuration doConfigure
信息: Configured SessionFactory: null
2010-12-12 13:57:29 org.hibernate.cfg.HbmBinder bindRootPersistentClassCommonValues
信息: Mapping class: testTable -> TB_GEN
2010-12-12 13:57:29 org.hibernate.dialect.Dialect <init>
信息: Using dialect: org.hibernate.dialect.MySQL5Dialect
2010-12-12 13:57:29 org.hibernate.tool.hbm2ddl.SchemaExport execute
信息: Running hbm2ddl schema export
2010-12-12 13:57:29 org.hibernate.tool.hbm2ddl.SchemaExport execute
信息: writing generated schema to file: d:/test/table.sql
2010-12-12 13:57:29 org.hibernate.tool.hbm2ddl.SchemaExport execute
信息: exporting generated schema to database
2010-12-12 13:57:29 org.hibernate.connection.DriverManagerConnectionProvider configure
信息: Using Hibernate built-in connection pool (not for production use!)
2010-12-12 13:57:29 org.hibernate.connection.DriverManagerConnectionProvider configure
信息: Hibernate connection pool size: 20
2010-12-12 13:57:29 org.hibernate.connection.DriverManagerConnectionProvider configure
信息: autocommit mode: false
2010-12-12 13:57:29 org.hibernate.connection.DriverManagerConnectionProvider configure
信息: using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://localhost/jsnmp
2010-12-12 13:57:29 org.hibernate.connection.DriverManagerConnectionProvider configure
信息: connection properties: {user=root, password=****}
drop table if exists TB_GEN
create table TB_GEN (ID bigint not null auto_increment, COLLECTKEY varchar(100), MIBVAL varchar(100), DSNAME varchar(100), `TIMESTAMP` bigint, primary key (ID))
2010-12-12 13:57:30 org.hibernate.tool.hbm2ddl.SchemaExport execute
信息: schema export complete
2010-12-12 13:57:30 org.hibernate.connection.DriverManagerConnectionProvider close
信息: cleaning up connection pool: jdbc:mysql://localhost/jsnmp

2010-12-12 13:57:28 org.hibernate.cfg.Environment <clinit>

信息: Hibernate 3.2.5

2010-12-12 13:57:28 org.hibernate.cfg.Environment <clinit>

信息: hibernate.properties not found

2010-12-12 13:57:28 org.hibernate.cfg.Environment buildBytecodeProvider

信息: Bytecode provider name : cglib

2010-12-12 13:57:28 org.hibernate.cfg.Environment <clinit>

信息: using JDK 1.4 java.sql.Timestamp handling

2010-12-12 13:57:28 org.hibernate.cfg.Configuration configure

信息: configuring from resource: /hibernate.cfg.xml

2010-12-12 13:57:28 org.hibernate.cfg.Configuration getConfigurationInputStream

信息: Configuration resource: /hibernate.cfg.xml

2010-12-12 13:57:29 org.hibernate.cfg.Configuration doConfigure

信息: Configured SessionFactory: null

2010-12-12 13:57:29 org.hibernate.cfg.HbmBinder bindRootPersistentClassCommonValues

信息: Mapping class: testTable -> TB_GEN

2010-12-12 13:57:29 org.hibernate.dialect.Dialect <init>

信息: Using dialect: org.hibernate.dialect.MySQL5Dialect

2010-12-12 13:57:29 org.hibernate.tool.hbm2ddl.SchemaExport execute

信息: Running hbm2ddl schema export

2010-12-12 13:57:29 org.hibernate.tool.hbm2ddl.SchemaExport execute

信息: writing generated schema to file: d:/test/table.sql

2010-12-12 13:57:29 org.hibernate.tool.hbm2ddl.SchemaExport execute

信息: exporting generated schema to database