两个数据源下表的同步实现——Mybatis流式查询百万数据及数据的分批插入

两个数据源下表的同步实现——Mybatis流式查询百万数据及数据的分批插入

@DS("slave")
public interface TcurrentsMapper {

//	@Options(resultSetType = ResultSetType.FORWARD_ONLY) // 结果集的游标只能向下滚动,默认属性
//    @ResultType(Tcurrents.class)
//    @Select("select \r\n" + 
//    		"		l_date lDate,\r\n" + 
//    		"		en_fare4 enFare4,\r\n" + 
//    		"		l_busin_amount lBusinAmount,\r\n" + 
//    		"		en_entrust_price enEntrustPrice,\r\n" + 
//    		"		vc_seat_id vcSeatId,\r\n" + 
//    		"		c_stock_type cStockType\r\n" + 
//    		"		from tcurrents\r\n")
    void queryListByStream(ResultHandler<Tcurrents> handler);

}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yhfund.tcms.basic.o32sync.tcurrents.dao.TcurrentsMapper">
	<select id="queryListByStream" resultType="com.yhfund.tcms.basic.o32sync.tcurrents.po.Tcurrents" >
		select 
		l_date lDate,
		en_fare4 enFare4,
		l_busin_amount lBusinAmount,
		en_entrust_price enEntrustPrice,
		vc_seat_id vcSeatId,
		c_stock_type cStockType
		from trade.tcurrents
		where vc_seat_id is not null
	</select>
</mapper>

@Service
public class SyncTableDataServiceImpl implements SyncTableDataService {

	private static final Logger logger = LoggerFactory.getLogger(SyncTableDataServiceImpl.class);

	@Autowired
	private TcurrentsMapper tcurrentsMapper;

	public void insertTcurrents() {
		long start = System.currentTimeMillis();
        List<Tcurrents> tcurrentsList = new LinkedList<>();
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        try {
        	tcurrentsMapper.queryListByStream(resultContext -> {
            	tcurrentsList.add(resultContext.getResultObject());
            	if (tcurrentsList.size() % Constant.BATCH_SIZE == 0) {
            		syncTableDataMapper.insertTcurrents(tcurrentsList);
            		sqlSession.flushStatements();
            		tcurrentsList.clear();
            	}
            });
        	if (tcurrentsList.size() % Constant.BATCH_SIZE != 0) {
        		syncTableDataMapper.insertTcurrents(tcurrentsList);
				sqlSession.flushStatements();
			}
        	logger.info("批量tcurrents表数据耗时 :: {} ", System.currentTimeMillis() - start);
        } catch (Exception e) {
        	logger.error("SyncTableDataServiceImpl.insertTcurrents.批量插入数据异常:{}", e.getMessage(), e);
			sqlSession.rollback();
        } finally {
        	// 关闭事务
        	sqlSession.close();
        }
	}
}

@DS("master")
public interface SyncTableDataMapper {

	void insertTcurrents(List<Tcurrents> list);

}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yhfund.tcms.basic.task.dao.SyncTableDataMapper">
	
	<delete id="deleteAllStockholder">
		delete from tstockholder
	</delete>
	
	<insert id="insertStockholder" parameterType="list">
		insert into
		tstockholder(c_bind_status,c_market_no,vc_fund_ids)
		<foreach collection ="list" item="item" index= "index" separator ="union all">
            select #{item.cBindStatus,jdbcType=CHAR}, #{item.cMarketNo,jdbcType=CHAR},#{item.vcFundIds,jdbcType=VARCHAR} from dual
        </foreach>
	</insert>
	
	<delete id="deleteAllTcurrents">
		delete from tcurrents
	</delete>
	
	<insert id="insertTcurrents" parameterType="list">
        insert all
        <foreach collection ="list" item="item" index= "index">
        	into tcurrents(l_date,en_fare4,l_busin_amount,en_entrust_price,vc_seat_id,c_stock_type) values
            (#{item.lDate,jdbcType=DECIMAL},
             #{item.enFare4,jdbcType=DECIMAL},
             #{item.lBusinAmount,jdbcType=DECIMAL}, 
            #{item.enEntrustPrice,jdbcType=DECIMAL},
            #{item.vcSeatId,jdbcType=VARCHAR},
            #{item.cStockType,jdbcType=VARCHAR}
            )
        </foreach>
        select 1 from dual
	</insert>
</mapper>