Extjs在线进销存-动态建表

? 接上一篇：http://lminqiang.iteye.com/blog/1562341

进销存的单据量加上明细数据量相当庞大，如果要实现多帐套同时使用的话，那么所有的数据都放到一套表结构里面是非常恐怖的，所以每个帐套必须独享一套表结构，同时要考虑到数据量较小的实体可以共享一张表 ，比如用户表，组织结构表，仓库信息表等。

? ? ? 通常的处理方式没注册一个新的帐套，那么系统自动创建一套表结构，那么带来的问题就是如何处理动态表的曾，删，改，查问题呢，而且我还不想去每次都拼sql。

? ? ? ? 那么好，先说动态建表，为了不去拼写sql，那么最简单的方式就是使用Hibernate动态建表机制，根据映射文件，以及帐套（表）的序列号，每次动态的建表，如商品表 estore_2_goods,estore_3_goods, 每次只需要传递一个参数即可。

首先在spring配置文件中配置hibernate的namingStrategy

?

?

<bean id="sessionFactory"

ref="dataSource" /><property name="namingStrategy"><bean  /></property><property name="hibernateProperties"><props><prop key="hibernate.dialect">${hibernate.dialect}</prop><prop key="hibernate.show_sql">${hibernate.show_sql}</prop><prop key="hibernate.format_sql">${hibernate.format_sql}</prop><prop key="hibernate.hbm2ddl.auto">none</prop><prop key="hibernate.query.substitutions">true 1, false 0</prop><prop key="hibernate.cache.use_query_cache">false</prop><prop key="hibernate.cache.use_second_level_cache">false</prop></props></property>

?

public class MyNamingStrategy extends DefaultNamingStrategy{public static final MyNamingStrategy Instance = new MyNamingStrategy();private static final long serialVersionUID = 1L;private static Logger log = Logger.getLogger(MyNamingStrategy.class);@Overridepublic String classToTableName(String className) {String tag = com.webjxc.common.web.EsThreadVariable.getCurrentTableIndex();if(null == tag){tag = EsThreadVariable.CurrentTableIndex;}StringBuffer sb = new StringBuffer("estore_");sb.append(tag).append("_");sb.append(super.classToTableName(className));return sb.toString();}}

?EsThreadVariable.CurrentTableIndex 就是我们要传递进去的线程变量，用来表明是哪个帐套。

?

?

public class EsThreadVariable {public static final String CurrentTableIndex = "_currentTableIndex_";// 当前表名private static ThreadLocal<String> esCurrentTableIndex= new ThreadLocal<String>();// 当前帐套IDprivate static ThreadLocal<String> esAccinfoVariable = new ThreadLocal<String>(); public static String getAccinfo(){return esAccinfoVariable.get();}public static void setAccinfo(String accinfo){esAccinfoVariable.set(accinfo);}public  static void removeAccinfo(){esAccinfoVariable.remove();}public static String getCurrentTableIndex(){return esCurrentTableIndex.get();}public static void setCurrentTableIndex(String s ){esCurrentTableIndex.set(s);}public static void removeCurrentTableIndex(){esCurrentTableIndex.remove();}public static String[] convertTableIndexArray(String[] array){String[] returnArray = new String[array.length];for(int i = 0 ; i < array.length ; i++){String str = array[i];returnArray[i] = str;if(str.indexOf(EsThreadVariable.CurrentTableIndex)!= -1)returnArray[i] = StringUtils.replace(str, EsThreadVariable.CurrentTableIndex, EsThreadVariable.getCurrentTableIndex());}return returnArray;}public static String convertTableStr(String str){return StringUtils.replace(str, EsThreadVariable.CurrentTableIndex, EsThreadVariable.getCurrentTableIndex());}}

?

同时还要注意，在实体声明的时候不要指定表名：如

?

?

@Entitypublic class Goods {private String clientid;   // 供应商private String goodstypeid;  // 类别private String code; // 编码 private String barCode; // 条码private String name;private String shortname;private String specs; // 型号、款号private String unit;  // 商品单位 }

?创建表的过程比较简单，要将我们需要动态创建的表在hibernate.cfg.xml文件中进行标示：如

?

?

<?xml version='1.0' encoding='UTF-8'?><!DOCTYPE hibernate-configuration PUBLIC          "-//Hibernate/Hibernate Configuration DTD 3.0//EN"          "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"><!-- Generated by MyEclipse Hibernate Tools.                   --><hibernate-configuration>    <session-factory>    <property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>    <property name="hibernate.format_sql">true</property>    <mapping />   <mapping />   <mapping />   <mapping />   <mapping />     </session-factory></hibernate-configuration>

?然后在你的Action中调用下面的类 ，完成动态建表。

?

?

public class TableGenerator {private static final long serialVersionUID = 1L;private static Logger log = Logger.getLogger(TableGenerator.class);/** * 根据hibernate.cfg.xml 描述建表 * 加入了 NamingStrategy * @param connection * @throws SQLException  */public void createTable(Connection connection) throws SQLException {log.debug("开始建表....");Configuration conf = new AnnotationConfiguration();MyNamingStrategy strategy = new MyNamingStrategy();conf.setNamingStrategy(strategy);try {SchemaExport dbExport = new SchemaExport(conf.configure(), connection);dbExport.create(false, true);} catch (Exception e) {e.printStackTrace();} finally { connection.close();}}}