Extjs在线进销存-动态建表

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

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

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

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

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

 

 

<bean id="sessionFactory"

		class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="namingStrategy">
			<bean  class="com.webjxc.core.orm.hibernate.MyNamingStrategy" />
		</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);
	@Override
	public 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>();
	// 当前帐套ID
	private 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());
	}
	
}

 

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

 

 

@Entity
public 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 class="com.webjxc.estore.entity.needCreate.Csale" />
   	<mapping class="com.webjxc.estore.entity.needCreate.Csaledetail" />
   	<mapping class="com.webjxc.estore.entity.needCreate.Dallot" />
   	<mapping class="com.webjxc.estore.entity.needCreate.Detailbillflow" />
   	<mapping class="com.webjxc.estore.entity.needCreate.Goods" />
 
    </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();
		}
	}

}