Springboot+Mybatis+通用Mapper多數(shù)據(jù)源實(shí)現(xiàn)數(shù)據(jù)同步

Architect_home 2019-04-19

展開全文

有個(gè)需求需要查詢遠(yuǎn)程數(shù)據(jù)庫，然后將遠(yuǎn)程數(shù)據(jù)庫中的數(shù)據(jù)抓取到本地，遠(yuǎn)程數(shù)據(jù)庫中的數(shù)據(jù)是每天都增加的，所以就需要寫個(gè)程序自動(dòng)實(shí)現(xiàn)實(shí)時(shí)抓取。

這里我用到的框架是Springboot2.0+Mybatis+Mapper，涉及的數(shù)據(jù)庫有SqlServer，Oracle，Mysql

添加依賴

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-quartz</artifactId>
        </dependency>
        <dependency>
            <groupId>org.quartz-scheduler</groupId>
            <artifactId>quartz</artifactId>
            <version>2.3.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
        </dependency>
        <!--<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>2.6</version>
        </dependency>
         <!-- sqlserver -->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.4</version>
        </dependency>
        <!-- oracle -->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc14</artifactId>
            <version>10.2.0.4.0</version>
        </dependency>
        <!-- 通用Maper -->
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>1.2.4</version>
        </dependency>
        <!--druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.28</version>
        </dependency>
        <!-- log4j -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.25</version>
        </dependency>

如果沒有l(wèi)og包，可能在配置文件中的DruidDataSource會(huì)出現(xiàn)錯(cuò)誤提示，但實(shí)際上不會(huì)影響到程序。

application.yml項(xiàng)目配置

也可以用application.properties格式配置，但是感覺yml格式看起來比較直觀.

##端口
server:
  port:8081
logging:
  level:
    cn.javabb.bootdemo.mapper: info
spring:
  remote-datasource:
    url: jdbc:sqlserver://66.66.66.66:1433;DatabaseName=dbName
    username: sa
    password: 123
    #使用Druid的數(shù)據(jù)源
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    filters: stat
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: select 'x'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20
  local-datasource:
    url: jdbc:sqlserver://77.77.77.77:1433;DatabaseName=dbName
    username: sa
    password: 1234
    #使用Druid的數(shù)據(jù)源
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    filters: stat
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: select 'x'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20
  oracle-datasource:
    url: jdbc:oracle:thin:@88.88.88.88:1521/sid
    username: tiger
    password: tiger
    #使用Druid的數(shù)據(jù)源
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: oracle.jdbc.driver.OracleDriver
    filters: stat
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: select 'x'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20
#Mybatis
mybatis:
  configuration:
    map-underscore-to-camel-case: true
  mapper-locations: classpath:mapper/*.xml
  typeAliasesPackage: cn.javabb.**.entity
#Mapper
mapper:
  mappers:
  - cn.javabb.bootdemo.base.BaseMapper
  not-empty: false
  identity: sqlserver
  before: true

其中remote-datasource,oracle-datasource和local-datasource為兩個(gè)不同的數(shù)據(jù)源，mybatis配置和通用mapper配置一樣的,如果不需要用到通用mapper可以去掉配置。

application啟動(dòng)類：

@EnableAsync
@EnableScheduling
@ServletComponentScan
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class,DataSourceTransactionManagerAutoConfiguration.class, MybatisAutoConfiguration.class})
public class BootDemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(BootDemoApplication.class, args);
    }
}

注解說明：

@EnableAsync：開啟異步任務(wù)支持，如果不需要可以去掉

@EnableScheduling：開啟定時(shí)任務(wù)支持

@ServletComponentScan：用來掃描mapper

@SpringBootApplication:springboot注解支持

注意：如果項(xiàng)目依賴中有，mybatis-spring-boot-starter，會(huì)默認(rèn)自動(dòng)根據(jù)配置文件配置mybatis,如果剛開始項(xiàng)目沒有配置DataSource，啟動(dòng)項(xiàng)目就會(huì)報(bào)錯(cuò)，在這里去掉DataSource的默認(rèn)自動(dòng)配置類不加載，使項(xiàng)目在使用的時(shí)候選擇去DataSource。

數(shù)據(jù)源配置

有幾個(gè)數(shù)據(jù)源配置幾個(gè)DataSource

配置第一個(gè)數(shù)據(jù)源：local-datasource

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import tk.mybatis.spring.annotation.MapperScan;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "cn.javabb.bootdemo.mapper.local",sqlSessionFactoryRef = "localSqlSessionFactory")
public class LocalDataSourceConfig {
    @Bean(name = "localDataSource")
    @ConfigurationProperties("spring.local-datasource")
    public DataSource localDataSource(){
        return DataSourceBuilder.create().build();
    }
    @Bean(name = "localSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("localDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath*:mapper/local/*.xml"));
        return sessionFactoryBean.getObject();
    }
    @Bean(name = "localTransactionManager")
    public DataSourceTransactionManager localTransactionManager(){
        return new DataSourceTransactionManager(localDataSource());
    }
}

spring.local-datasource 為application.yml中配置的數(shù)據(jù)源名稱

classpath*:mapper/local/*.xml 指定local數(shù)據(jù)源的mybatis的xml文件位置

原本一個(gè)數(shù)據(jù)源的時(shí)候，可以在啟動(dòng)程序的時(shí)候加載DataSource，但是這里是多數(shù)據(jù)源，需要為每個(gè)數(shù)據(jù)源配置DataSource，并且需要添加MapperScan注解，（因?yàn)檫@里是使用的通用Mapper，所以引入的class應(yīng)該是mapper的，import tk.mybatis.spring.annotation.MapperScan;，如果沒用就引入org的MapperScan）

basePackages這里的包路徑是需要用到這個(gè)數(shù)據(jù)源的路徑，這個(gè)包下所有的接口文件都會(huì)使用這個(gè)local數(shù)據(jù)源

配置第二個(gè)數(shù)據(jù)源：remote-datasource

@Configuration
@MapperScan(basePackages = "cn.javabb.bootdemo.mapper.remote",sqlSessionFactoryRef = "remoteSqlSessionFactory")
public class RemoteDataSourceConfig {
    @Bean(name = "remoteDataSource")
    @Primary
    @ConfigurationProperties("spring.remote-datasource")
    public DataSource remoteDataSource(){
        return DataSourceBuilder.create().build();
    }
    @Bean(name = "remoteSqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("remoteDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath*:mapper/remote/*.xml"));
        return sessionFactoryBean.getObject();
    }
    @Bean(name = "remoteTransactionManager")
    @Primary
    public DataSourceTransactionManager remoteTransactionManager(){
        return new DataSourceTransactionManager(remoteDataSource());
    }
}

@Primary指定默認(rèn)數(shù)據(jù)源。

配置第三個(gè)數(shù)據(jù)源：oracle-datasource

@Configuration
@MapperScan(basePackages = "cn.javabb.bootdemo.mapper.oracle",sqlSessionFactoryRef = "oracleSqlSessionFactory")
public class OracleDataSourceConfig {
    @Bean(name = "oracleDataSource")
    @ConfigurationProperties("spring.oracle-datasource")
    public DataSource oracleDataSource(){
        return DataSourceBuilder.create().build();
    }
    @Bean(name = "oracleSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("oracleDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath*:mapper/oracle/*.xml"));
        return sessionFactoryBean.getObject();
    }
    @Bean(name = "oracleTransactionManager")
    public DataSourceTransactionManager oracleTransactionManager(){
        return new DataSourceTransactionManager(oracleDataSource());
    }
}

接下來所有的接口都應(yīng)該按照不同的數(shù)據(jù)源放置，對(duì)應(yīng)的xml也需要按照不同的數(shù)據(jù)源分開。

按照我項(xiàng)目的實(shí)際例子：

Mapper（使用local數(shù)據(jù)源，下面所有的方法都會(huì)在local數(shù)據(jù)源中查詢）

import cn.javabb.bootdemo.entity.*;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Component;
/**
 * @Author QINB imqinbao@163.com
 * @CreateDate 2019/1/18/018 13:08
 * @Since V1.0
 */
@Component
@Mapper
public interface StLocalMapper {
    int insertPptnOne(StPptn pptn);
    int insertRiverOne(StRiver river);
    int insertRsvrOne(StRsvr rsvr);
}

StLocalMapper

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "http:///dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.javabb.bootdemo.mapper.local.StLocalMapper">
    <insert id="insertBatch" parameterType="java.util.List">
        insert into ST_PPTN_R_copy1(stcd,tm,drp,intv,pdr,dyp,wth,tmdown)
        values
        <foreach collection="list" item="item" index="index" separator=",">
        (#{item.stcd},#{item.tm},#{item.drp},#{item.intv},#{item.pdr},#{item.dyp},#{item.wth},#{item.tmdown})
        </foreach>
    </insert>
    <insert id="insertPptnOne" parameterType="cn.javabb.bootdemo.entity.StPptn">
    insert into ST_pptn_R(stcd,tm,drp,intv,pdr,dyp,wth,tmdown)
    values (#{stcd},#{tm},#{drp},#{intv},#{pdr},#{dyp},#{wth},#{tmdown})
    </insert>
    <insert id="insertRiverOne" parameterType="cn.javabb.bootdemo.entity.StRiver">
    insert into ST_river_R(STCD,TM,Z,Q,XSA,XSAVV,XSMXV,FLWCHRCD,WPTN,MSQMT,MSAMT,MSVMT,TMDOWN)
    values (#{STCD},#{TM},#{Z},#{Q},#{XSA},#{XSAVV},#{XSMXV},#{FLWCHRCD},#{WPTN},#{MSQMT},#{MSAMT},#{MSVMT},#{TMDOWN})
    </insert>
    <insert id="insertRsvrOne" parameterType="cn.javabb.bootdemo.entity.StRsvr">
    insert into ST_rsvr_R(STCD,TM,RZ,INQ,W,BLRZ,OTQ,RWCHRCD,RWPTN,INQDR,MSQMT,TMDOWN)
    values (#{STCD},#{TM},#{RZ},#{INQ},#{W},#{BLRZ},#{OTQ},#{RWCHRCD},#{RWPTN},#{INQDR},#{MSQMT},#{TMDOWN})
    </insert>
</mapper>

編寫一個(gè)service調(diào)用方法就行了

@Slf4j
@Service
public class StDataSynService {
    @Autowired
    StLocalMapper stLocalMapper;
    @Async
    public void pptnSyn(){
        String nowDate = DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss");
        String yesterDay = DateFormatUtils.format(DateUtils.addDays(new Date(),-day),"yyyy-MM-dd HH:mm:ss");
        log.info("[{}]掃描Pptn同步數(shù)據(jù)....",nowDate);
        //需要同步的數(shù)據(jù),往前推一個(gè)小時(shí)
        List<StPptn> remoteList = stRemoteMapper.listPptnRemote(nowDate,yesterDay);
        int total = 0;
        if(null!=remoteList && remoteList.size()>0){
            total = remoteList.size();
            log.info("獲取Pptn數(shù)據(jù)：{}條。",total);
            Long startTM = System.currentTimeMillis();
            //執(zhí)行同步
            int n=0;
            for(StPptn pptn:remoteList){
                n += stLocalMapper.insertPptnOne(pptn);
            }
            if(n>0){
                if(n == total){
                    log.info("Pptn同步成功.用時(shí)：{}秒,需要同步記錄:{},成功:{}",(System.currentTimeMillis()-startTM)/1000,total,n);
                }else{
                    log.info("Pptn同步成功.用時(shí)：{}秒,需要同步記錄:{},成功:{},失敗:{}",(System.currentTimeMillis()-startTM)/1000,total,n,(total-n));
                }
            }
        }else{
            log.info("Pptn無數(shù)據(jù)同步");
        }
    }
}

@Async:表示這個(gè)方法是異步執(zhí)行，可以去掉，自己去調(diào)用service這個(gè)方法就行了。

上面的例子中我并沒有用到通用Mapper，其實(shí)調(diào)用通用Mapper的方法會(huì)更簡單一點(diǎn)

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自： Architect_home > 《待分類》

舉報(bào)/認(rèn)領(lǐng)