用mybatis实现批量插入在使用sharingJdbc分库分表情况下遇到的问题

最新推荐文章于 2024-05-12 10:53:02 发布

竹下星空

最新推荐文章于 2024-05-12 10:53:02 发布

阅读量3.4k

点赞数 1

分类专栏： mybatis及分库分表

本文链接：https://blog.csdn.net/shidebin/article/details/104953753

版权

mybatis及分库分表专栏收录该内容

2 篇文章 0 订阅

订阅专栏

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");

这就可以了

竹下星空

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
用mybatis实现批量插入在使用sharingJdbc分库分表情况下遇到的问题

service层代码：@Override public void testBatchInsert() { List<VideoAuditProcess> datas = new ArrayList<>(); for (int i = 1; i < 5; i++) { Long videoId = Long.parseLong(i + ""); ...
复制链接

扫一扫

专栏目录