Extjs在线进销存-动态建表

 

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

      通常的处理方式没注册一个新的帐套，那么系统自动创建一套表结构，那么带来的问题就是如何处理动态表的曾，删，改，查问题呢，而且我还不想去每次都拼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();  
        }  
    }  
  
}