项目有一个表由于业务猛增,需要分表,分表之后就必然用到动态查询表。
项目框架为spring+springMVC+hibernate,通过重写hibernate拦截器EmptyInterceptor可以实现动态表映射。因为spring-orm还用的是3.1.0,它的LocalSessionFactoryBean不支持entityInterceptor属性,因此需要升级spring版本到4.x,这次按照网上的方法升级到了4.2.7。
1 spring依赖
<properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <jackson.version>2.5.1</jackson.version> <spring.version>4.2.7.RELEASE</spring.version> <spring-security.version>3.0.5.RELEASE</spring-security.version> <hibernate.version>4.3.8.Final</hibernate.version> </properties> <dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>${spring.version}</version> <exclusions> <exclusion> <groupId>commons-logging</groupId> <artifactId>commons-logging</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-beans</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context-support</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aop</artifactId> <version>${spring.version}</version> <exclusions> <exclusion> <groupId>commons-logging</groupId> <artifactId>commons-logging</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-orm</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-oxm</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-web</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>${spring.version}</version> </dependency> <!-- jackson json --> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-core</artifactId> <version>${jackson.version}</version> </dependency> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-databind</artifactId> <version>${jackson.version}</version> </dependency> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-annotations</artifactId> <version>${jackson.version}</version> </dependency> <dependency> <groupId>com.fasterxml.jackson.module</groupId> <artifactId>jackson-module-jaxb-annotations</artifactId> <version>${jackson.version}</version> </dependency> </dependencies>
项目里还用到了spring-security,本来想一起升级,但是改动太大,最后还是沿用了之前的版本,等以后有时间再研究一下。
注意:这里需要把jackson也升级。
2 修改springmvc.xml,使用MappingJackson2HttpMessageConverter代替MappingJacksonHttpMessageConverter,MappingJackson2JsonView代替MappingJacksonJsonView。
3 修改spring.xml,在LocalSessionFactoryBean中加入entityInterceptor属性。
<!-- 配置sessionFactory --> <bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean"> <property name="dataSource" ref="dataSource"/> <property name="namingStrategy"> <bean class="org.hibernate.cfg.ImprovedNamingStrategy"/> </property> <property name="entityInterceptor"> <bean class="com.eversec.base.base.BaseInterceptor"/> </property> <property name="packagesToScan" value="com.eversec"/><!-- 如果多个,用“,”分隔 --> <property name="hibernateProperties"> <props> <!-- 链接释放策略 on_close | after_transaction | after_statement | auto --> <prop key="hibernate.connection.release_mode">after_transaction</prop> <prop key="current_session_context_class"> org.springframework.orm.hibernate4.SpringSessionContext </prop> <prop key="hibernate.dialect">${hibernate.dialect}</prop> <prop key="hibernate.show_sql">${hibernate.show_sql}</prop> <prop key="hibernate.generate_statistics">${hibernate.generate_statistics}</prop> <prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop> <prop key="hibernate.jdbc.batch_size">${hibernate.jdbc.batch_size}</prop> <prop key="hibernate.connection.release_mode">after_transaction</prop> <prop key="transaction.auto_close_session">false</prop> <prop key="connection.autocommit">false</prop> </props> </property> </bean>
4 下面就是重写的hibernate拦截器
package com.eversec.base.base; import org.hibernate.EmptyInterceptor; /** * 重写Hibernate拦截器 * 实现动态替换表名和添加时忽略重复记录 * * @author gb * @version 2015-01-13 */ public class BaseInterceptor extends EmptyInterceptor { private static final long serialVersionUID = 1L; /** * 动态替换表名 */ public static final String REPLACE_TABLE_NAME = "replace_table_name"; /** * insert时添加ignore */ public static final String ADD_IGNORE = "add_ignore"; /** * insert时添加ignore并动态替换表名 */ public static final String ADD_IGNORE_AND_REPLACE_TABLE_NAME = "add_ignore_replace_table_name"; // 使用ThreadLocal存放日期,保证每个线程取到自己需要动态替换表命的日期 private static ThreadLocal<String[]> thread = new ThreadLocal<String[]>(); /** * 存放SQL替换内容 * * @param contents [0]关键词,[1]被替换内容,[2]替换内容<br> * 1)关键词replace_table_name,替换表名<br> * 2)关键词add_ignore,insert into时添加ignore<br> * 3)关键词add_ignore_replace_table_name,insert into时添加ignore并替换表名<br> */ public static void replace(String... contents) { thread.set(contents); } /** * SQL替换内容 * <p> * [0]关键词,[1]被替换内容,[2]替换内容<br> * 1)关键词replace_table_name,替换表名<br> * 2)关键词add_ignore,insert into时添加ignore<br> * 3)关键词add_ignore_replace_table_name,insert into时添加ignore并替换表名<br> * 4)替换内容为空时,不处理SQL并返回 * </p> */ public String onPrepareStatement(String sql) { String[] contents = thread.get(); if (contents == null || contents.length < 1) { return sql; } if (contents[0].equals(REPLACE_TABLE_NAME)) { sql = replaceTableName(sql, contents[1], contents[2]); } else if (contents[0].equals(ADD_IGNORE)) { sql = addIgnore(sql); } else if (contents[0].equals(ADD_IGNORE_AND_REPLACE_TABLE_NAME)) { sql = addIgnoreAndReplaceTableName(sql, contents[1], contents[2]); } thread.remove(); //System.out.println(sql); return sql; } // 动态替换表名 private String replaceTableName(String sql, String oldContent, String replaceContent) { sql = sql.replaceAll(oldContent, replaceContent); return sql; } // insert忽略重复记录 private String addIgnore(String sql) { sql = sql.replaceAll("insert into", "insert ignore into"); return sql; } // insert忽略重复记录同时替换表名 private String addIgnoreAndReplaceTableName(String sql, String oldContent, String replaceContent) { sql = sql.replaceAll("insert into", "insert ignore into"); return replaceTableName(sql, oldContent, replaceContent); } }
5 最后,怎么使用呢?不用关注微信号,马上告诉你答案。
使用起来相当的简单,只要在service或者dao层执行库操作前调用 BaseInterceptor.replace(BaseInterceptor.REPLACE_TABLE_NAME,"black_list_phone", "black_list_phone_" + groupId);即可。其中black_list_phone是模板表,groupId是我的逻辑分组,如果是1,就从black_list_phone直接映射到black_list_phone_1。
private void replaceTable(Integer groupId) { BaseInterceptor.replace(BaseInterceptor.REPLACE_TABLE_NAME, "black_list_phone", "black_list_phone_" + groupId); } public void save(BlackListPhone entity, Integer groupId) { replaceTable(groupId); super.save(entity); }
相关推荐
spring-3.1.0中文版api帮助文档
spring3.1.0 全部jar包 spring3.1.0 全部jar包 spring3.1.0 全部jar包 •Spring Framework 3.1.0.RELEASE is the current production release (requires Java 1.5+)
struts2.2.3 + spring3.1.0 + mybatis3.1.0集成简单demo。具体请看:http://blog.csdn.net/afgasdg/article/details/7392367
Spring Security 3.1.0 官方文档中文.chm
spring3.1.0 必须的jar包文件。
spring-3.1.0中文版api帮助文档.pdf
Spring v3.1.0.RELEASE源码
spring-framework-3.1.0.M1spring-framework-3.1.0.M1
Spring v3.1.0.RC1源码
spring-framework-3.1.0源码
spring-framework-3.1.0.RELEASE
Spring Boot 3.1.0,mybatis-plus 3.5.3.1安装包
spring-framework-3.1.0.M2, jar格式, 内附spring包结构的说明
Spring Framework 3.1.0 M1 API DOC chm版,完美的的搜索功能,支持全文检索。2011年3月制作。如果打不开,请选中它,点右键 看属性,选择解除锁定。别再抱怨打不开了。
spring3.1.0.RELEASE项目源码,分享给需要的人。