service层代码:
@Override
public void testBatchInsert() {
List<VideoAuditProcess> datas = new ArrayList<>();
for (int i = 1; i < 5; i++) {
Long videoId = Long.parseLong(i + "");
VideoAuditProcess videoAuditProcess = new VideoAuditProcess();
videoAuditProcess.setVideoId(videoId);
videoAuditProcess.setAuditTranscationId(BaseUtils.createVideoAuditTranscationId());
// 机审综合结果(-1:未检测,0:正常,1:初级敏感,2:中级敏感,3:严格敏感)
videoAuditProcess.setmAuditResult(-1);
// 流程状态(0:未开始,1:进行中,2:已完成)
videoAuditProcess.setProcessStatus(0);
// 流程类型(1:机审自动,2:自动抽检)
videoAuditProcess.setProcessType(2);
videoAuditProcess.setCreateTimestamp(new Date().getTime());
datas.add(videoAuditProcess);
}
// CustomerContextHolder.setCustomerType("dataSource_normal");
videoAuditProcessMapperExt.batchInsert(datas);
}
mapper代码:
<insert id="batchInsert" parameterType="java.util.List">
insert into video_audit_process (video_id, audit_transcation_id,
process_type, m_audit_result, process_status,
create_timestamp)
values
<foreach collection="list" item="videoAudit" separator="," >
( #{videoAudit.videoId,jdbcType=BIGINT},
#{videoAudit.auditTranscationId,jdbcType=VARCHAR},
#{videoAudit.processType,jdbcType=INTEGER},
#{videoAudit.mAuditResult,jdbcType=INTEGER},
#{videoAudit.processStatus,jdbcType=INTEGER},
#{videoAudit.createTimestamp,jdbcType=BIGINT})
</foreach>
</insert>
此时运行会报错:
### Cause: io.shardingjdbc.core.exception.ShardingJdbcException: Invoke jdbc method exception
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23) ~[mybatis-3.2.4.jar:3.2.4]
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:150) ~[mybatis-3.2.4.jar:3.2.4]
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:137) ~[mybatis-3.2.4.jar:3.2.4]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_231]
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_231]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_231]
at java.lang.reflect.Method.invoke(Unknown Source) ~[?:1.8.0_231]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358) ~[mybatis-spring-1.2.2.jar:1.2.2]
... 106 more
说明此时sharingJdbc报错了,所以sharingJdbc这个插件不支持批量插入。因为我们代码配置的数据源默认使用的是sharingJdbc数据源。这从我们的配置可以看出:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:sharding="http://shardingjdbc.io/schema/shardingjdbc/sharding"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-4.3.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
http://shardingjdbc.io/schema/shardingjdbc/sharding
http://shardingjdbc.io/schema/shardingjdbc/sharding/sharding.xsd">
<bean id="dataSource_master" class="com.alibaba.druid.pool.DruidDataSource"
init-method="init" destroy-method="close">
<property name="driverClassName" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<property name="maxActive" value="${jdbc.maxActive}" />
<property name="initialSize" value="${jdbc.initialSize}" />
<property name="maxWait" value="${jdbc.maxWait}" />
<property name="minIdle" value="${jdbc.minIdle}" />
<property name="connectionProperties" value="config.decrypt=true;clientEncoding=UTF-8" />
<property name="connectionInitSqls" value="set names utf8mb4;"/>
<property name="filters" value="stat" />
<property name="timeBetweenEvictionRunsMillis" value="600000" />
<property name="minEvictableIdleTimeMillis" value="300000" />
<property name="validationQuery" value="select 1" />
<property name="testWhileIdle" value="true" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<property name="maxOpenPreparedStatements" value="20" />
<property name="removeAbandoned" value="true" />
<property name="removeAbandonedTimeout" value="1800" />
<property name="logAbandoned" value="true" />
</bean>
<bean id="dataSource_normal" class="com.alibaba.druid.pool.DruidDataSource"
init-method="init" destroy-method="close">
<property name="driverClassName" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<property name="maxActive" value="150" />
<property name="initialSize" value="2" />
<property name="maxWait" value="${jdbc.maxWait}" />
<property name="minIdle" value="${jdbc.minIdle}" />
<property name="connectionProperties" value="config.decrypt=true;clientEncoding=UTF-8" />
<property name="connectionInitSqls" value="set names utf8mb4;"/>
<property name="filters" value="stat" />
<property name="timeBetweenEvictionRunsMillis" value="600000" />
<property name="minEvictableIdleTimeMillis" value="300000" />
<property name="validationQuery" value="select 1" />
<property name="testWhileIdle" value="true" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<property name="maxOpenPreparedStatements" value="20" />
<property name="removeAbandoned" value="true" />
<property name="removeAbandonedTimeout" value="1800" />
<property name="logAbandoned" value="true" />
</bean>
<sharding:standard-strategy id="10wUidStrategy"
sharding-column="uid"
precise-algorithm-class="com.weiqu.video.common.util.sharding.StrategyByUidShardingAlgorithm" />
<sharding:standard-strategy id="50wCollectionVideoStrategy"
sharding-column="video_collection_id"
precise-algorithm-class="com.weiqu.video.common.util.sharding.StrategyBy50wUidShardingAlgorithm" />
<sharding:standard-strategy id="50wUidStrategy"
sharding-column="uid"
precise-algorithm-class="com.weiqu.video.common.util.sharding.StrategyBy50wUidShardingAlgorithm" />
<sharding:data-source id="shardDataSource">
<sharding:sharding-rule data-source-names="dataSource_master"
default-data-source-name="dataSource_master">
<sharding:table-rules>
<sharding:table-rule logic-table="wx_user_video"
actual-data-nodes="${dataSource_master.wx_user_video}"
table-strategy-ref="10wUidStrategy"/>
<sharding:table-rule logic-table="wx_user_idol"
actual-data-nodes="${dataSource_master.wx_user_idol}"
table-strategy-ref="10wUidStrategy"/>
<sharding:table-rule logic-table="wx_user_fans"
actual-data-nodes="${dataSource_master.wx_user_fans}"
table-strategy-ref="10wUidStrategy"/>
<sharding:table-rule logic-table="wx_user_favorite"
actual-data-nodes="${dataSource_master.wx_user_favorite}"
table-strategy-ref="10wUidStrategy"/>
<sharding:table-rule logic-table="wx_collection_videos"
actual-data-nodes="${dataSource_master.wx_collection_videos}"
table-strategy-ref="50wCollectionVideoStrategy"/>
<sharding:table-rule logic-table="wx_user_video_collection"
actual-data-nodes="${dataSource_master.wx_user_video_collection}"
table-strategy-ref="50wUidStrategy"/>
<sharding:table-rule logic-table="wx_user_video_favorite"
actual-data-nodes="${dataSource_master.wx_user_video_favorite}"
table-strategy-ref="50wUidStrategy"/>
<sharding:table-rule logic-table="wx_user_favorite_video"
actual-data-nodes="${dataSource_master.wx_user_favorite_video}"
table-strategy-ref="50wUidStrategy"/>
<sharding:table-rule logic-table="wx_user_video_message"
actual-data-nodes="${dataSource_master.wx_user_video_message}"
table-strategy-ref="50wUidStrategy"/>
</sharding:table-rules>
<sharding:binding-table-rules>
<sharding:binding-table-rule logic-tables="wx_user_video,wx_user_idol,wx_user_fans,wx_user_favorite,wx_collection_videos,wx_user_video_collection,wx_user_favorite_video,wx_user_video_favorite,wx_user_video_message" />
</sharding:binding-table-rules>
</sharding:sharding-rule>
<sharding:props>
<prop key="sql.show">false</prop>
</sharding:props>
</sharding:data-source>
<!--动态数据源的配置-->
<bean id="dynamicDataSource" class="com.weiqu.video.common.DataSource.DynamicDataSource">
<property name="defaultTargetDataSource" ref="shardDataSource"></property>
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry key="dataSource_normal" value-ref="dataSource_normal" ></entry>
</map>
</property>
</bean>
<bean id="dataSourceChangeAspect" class="com.weiqu.video.common.DataSource.DataSourceAspect">
<property name="defaultDataSource" value="shardDataSource"></property>
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry key="_normal" value="dataSource_normal" ></entry>
</map>
</property>
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dynamicDataSource" />
<!-- 指定Mybatis的全局配置文件 -->
<property name="configLocation" value="classpath:mybatis-config.xml" />
<!-- 扫描mappers目录以及子目录下的所有xml文件 -->
<property name="mapperLocations" value="classpath:mapper/**/*.xml" />
</bean>
<!-- 指定Mapper接口所在的包 -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.weiqu.video.dao"/>
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
</bean>
<!-- 指定事务-->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dynamicDataSource" />
</bean>
<!-- 配置基于注解的事务 -->
<tx:annotation-driven transaction-manager="transactionManager" />
<!-- 一定要在事务之前切换数据源,不然则会失效-->
<aop:config>
<aop:pointcut id="serviceAop" expression="execution(* com.weiqu.video.service..*.*(..))" />
<aop:aspect ref="dataSourceChangeAspect" order="0">
<aop:before method="doBefore" pointcut-ref="serviceAop"/>
<aop:after-returning method="doAfterReturning" pointcut-ref="serviceAop"/>
</aop:aspect>
</aop:config>
<!--
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="shardDataSource" />
<property name="configLocation" value="classpath:mybatis-config.xml" />
<property name="mapperLocations" value="classpath:mapper/**/*.xml" />
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
<property name="basePackage" value="com.weiqu.video.dao" />
</bean>
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="shardDataSource" />
</bean>
<tx:annotation-driven transaction-manager="transactionManager" />
-->
</beans>
然后使用aop进行数据源的选择:
import java.util.Map;
import org.aspectj.lang.JoinPoint;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* 动态切换数据源切面
*/
public class DataSourceAspect{
private static final Logger logger = LoggerFactory.getLogger(DataSourceAspect.class);
private String defaultDataSource;
private Map<String, Object> targetDataSources;
private String dataSourceBi;
public void doBefore(JoinPoint joinPoint) {
String className = joinPoint.getSignature().getDeclaringType().getSimpleName();
// logger.info("DataSourceAspect.className={}",className);
if ("WechatStatService".equals(className)) {
CustomerContextHolder.setCustomerType(dataSourceBi);
// logger.info("----------------------------------------------------------------className={}--------------------------------------------------------------dataSourceBi={}",className,dataSourceBi);
return;
}
boolean isSetDataSource = false;
String targetMethodName = joinPoint.getSignature().getName();
for(Map.Entry<String, Object> entry : targetDataSources.entrySet()) {
if(targetMethodName.contains(entry.getKey())){
String value = entry.getValue().toString();
CustomerContextHolder.setCustomerType(value);
isSetDataSource = true;
// logger.info("走不分表的数据源--");
break;
}
}
if(!isSetDataSource) {
CustomerContextHolder.setCustomerType(defaultDataSource);
}
}
public void doAfterReturning(JoinPoint joinPoint) {
CustomerContextHolder.clearCustomerType();
}
public Map<String, Object> getTargetDataSources() {
return targetDataSources;
}
public void setTargetDataSources(Map<String, Object> targetDataSources) {
this.targetDataSources = targetDataSources;
}
public String getDefaultDataSource() {
return defaultDataSource;
}
public void setDefaultDataSource(String defaultDataSource) {
this.defaultDataSource = defaultDataSource;
}
public String getDataSourceBi() {
return dataSourceBi;
}
public void setDataSourceBi(String dataSourceBi) {
this.dataSourceBi = dataSourceBi;
}
}
使用CustomerContextHolder保存当前线程使用的数据源:
public class CustomerContextHolder {
//用ThreadLocal来设置当前线程使用哪个dataSource
private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
public static void setCustomerType(String customerType) {
contextHolder.set(customerType);
}
public static String getCustomerType() {
return contextHolder.get();
}
public static void clearCustomerType() {
contextHolder.remove();
}
}
然后使用spring的AbstractRoutingDataSource动态注入数据源:
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* 动态分派数据源
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return CustomerContextHolder.getCustomerType();
}
}
此时动态注入数据源就实现完成,回到最开始的问题:这个问题在于sharingJDBC不支持批量插入,所以应该切换普通数据源:
CustomerContextHolder.setCustomerType("dataSource_normal");
这就可以了