项目有一个表由于业务猛增,需要分表,分表之后就必然用到动态查询表。
项目框架为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);
}