mybatis连接多源数据库

梁霖编程工具库 2023-04-13

180

背景

在工作中，偶尔会遇到需要配置多源数据库的场景，通常的建议是一个微服务配置一个数据库的源，这样这个默认的数据源就是主数据源。但如果有时候需要在一个微服务中连接多个数据源，可以做如下的配置实现。
这里我们以两个数据源为例介绍如何进行连接

‍pom文件中引入不同数据源依赖

对于第三方的数据源，数据库的设计厂商会提供连接数据库的驱动jar包，只需要我们手动将厂商提供的jar包通过mvn install:install-file命令将其打包到本地仓库中即可。

 <!--手动将厂商提供的jar打包进本地仓库后引入-->
        <dependency>
            <groupId>NQjc.jar</groupId>
            <artifactId>netsuite</artifactId>
            <version>1.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

application.yml文件配置



server:
  port: 7000


spring:
  application:
    name: financial-bi-service
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      # 初始连接数
      initialSize: 5
      # 最小连接池数量
      minIdle: 10
      # 最大连接池数量
      maxActive: 20
      # 配置获取连接等待超时的时间
      maxWait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      timeBetweenEvictionRunsMillis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      minEvictableIdleTimeMillis: 300000
      # 配置一个连接在池中最大生存的时间，单位是毫秒
      maxEvictableIdleTimeMillis: 900000
      #ns数据库
      ns:
        #  厂商提供jar包中的连接数据库的驱动类
        driver-class-name: com.netsuite.jdbc.openaccess.OpenAccessDriver
        # 厂商提供的数据库地址url
        jdbc-url: jdbc:ns://5810519.connect.api.netsuite.com:1708;ServerDataSource=NetSuite.com;encrypted=1;NegotiateSSLClose=false
        username: usernamexxxyyy
        password: passwordxxyy
      #微服务数据库
      master:
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: password123
        jdbc-url: jdbc:mysql://172.10.10.119:3306/dbname?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8


# 控制台输出sql、下划线转驼峰
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true

在config包下完成两个数据源配置类的创建并交由spring进行管理【核心】

注意：在配置的过程中必须有一个配置类叫MasterDataSourceConfig，因为在spring启动的时候会自动加载这个Master的配置类

/**
 * 配置mysql的数据源
 */
@Configuration
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
    // mysql数据源的mapper接口的路径
    static final String PACKAGE = "com.company.dao.master";
    // mysql数据源的mapper.xml文件路径
    static final String MAPPER_LOCATION = "classpath:mapping/master/*.xml";


    //从配置文件的指定标签中读取数据源信息并创建DataSource
    @Bean(name = "masterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid.master")
    @Primary //声明是主数据源
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }


    //数据源事务设置
    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(masterDataSource());
    }


    //根据DataSource和mapper映射文件路径创建SqlSessionFactory
    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MasterDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }


}

配置第二个数据源【其他数据源的名字可以取别的名称】

/**
 * 配置ns的数据源
 */
@Configuration
@MapperScan(basePackages = NsDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "nsSqlSessionFactory")
public class NsDataSourceConfig {
   // ns数据源的mapper接口的路径
    static final String PACKAGE = "com.company.dao.ns";
    // ns数据源的mapper.xml文件的类路径
    static final String MAPPER_LOCATION = "classpath:mapping/ns/*.xml";




    //从配置文件读取数据源信息并创建DataSource
    @Bean(name = "nsDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid.ns")
    public DataSource nsDataSource() {
        return DataSourceBuilder.create().build();
    }


    //数据源事务设置
    @Bean(name = "nsTransactionManager")
    public DataSourceTransactionManager nsTransactionManager() {
        return new DataSourceTransactionManager(nsDataSource());
    }


    //根据DataSource和mapper映射文件路径创建SqlSessionFactory
    @Bean(name = "nsSqlSessionFactory")
    public SqlSessionFactory nsSqlSessionFactory(@Qualifier("nsDataSource") DataSource nsDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(nsDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(NsDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }


}

对不同的数据源分别建立dao/master的mapper接口的文件夹和dao/ns的mapper接口文件夹

@Repository
public interface MasterDetailReportMapper {
    /**
     * 将明细报告批量插入数据库
     * @param reportList
     * @return
     */
    int insertBatch(@Param("reportList") List<MasterDetailReportDO> reportList);


    int insert(@Param("report") MasterDetailReportDO report);


    /**
     * 根据transctionid和lineid查询明细 报表
     * @param transactionId
     * @param lineId
     * @return
     */
    MasterDetailReportDO getMasterDetailReportByTranIdAndLineId(@Param("transactionId") long transactionId,@Param("lineId") int lineId);


    /**
     * 将report插入到历史记录表
     * @param report
     * @return
     */
    int insertHistory(@Param("report") MasterDetailReportDO report);


    /**
     * 根据transctionid和lineid删除明细 报表
     * @param transactionId
     * @param lineId
     * @return
     */
    int deleteMasterDetailReportByTranIdAndLineId(@Param("transactionId") long transactionId,@Param("lineId") int lineId);


}

@Repository
public interface NetsuiteMapper {
    /**
     * 根据输入的date查询ns上的 accountingperiod
     * @param date
     * @return
     */
    NSAccountingPeriodsDO getAccountingPeriodIdByDate(@Param("date") Date date);
}

在类路径下分别建立对应的mapper.xml文件

classpath:mapping/master 文件夹下的xml文件如下：

<?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.company.dao.master.MasterDetailReportMapper">
    <resultMap id="detailReportMap" type="com.shokz.domain.MasterDetailReportDO">
        <id column="id" property="id"/>
        <result column="transaction_id" property="transactionId"/>
        <result column="transaction_type" property="transactionType"/>
        <result column="trandate" property="trandate"/>
        <result column="tranid" property="tranid"/>


        <result column="sunlike_id" property="sunlikeId"/>
        <result column="currency" property="curName"/>
        <result column="subsidy" property="subName"/>
        <result column="transaction_line_id" property="lineId"/>


        <result column="class_name" property="className"/>
        <result column="account_id" property="accountId"/>
        <result column="subject" property="subject"/>
        <result column="subject_type" property="subjectType"/>




        <result column="amount" property="amount"/>
        <result column="foreign_amount" property="foreignAmount"/>
        <result column="entity_id" property="entityId"/>
        <result column="entity_name" property="entityName"/>


        <result column="trans_memo" property="transMemo"/>
        <result column="lines_memo" property="linesMemo"/>
        <result column="xm_name" property="xmName"/>
        <result column="hang_memo" property="hangMemo"/>


        <result column="xgf_name" property="xgfName"/>


        <result column="created_time" property="createdTime"/>
        <result column="updated_time" property="updatedTime"/>
        <result column="status" property="status"/>
        <result column="msg" property="msg"/>


    </resultMap>


    <!--sql片段-->
    <sql id="detailReportField">
        id,
        transaction_id,
        transaction_type,
        trandate,
        tranid,
        sunlike_id,
        currency,subsidy,transaction_line_id,class_name,account_id,subject,subject_type,amount,foreign_amount,entity_id,entity_name,
        trans_memo,lines_memo,xm_name,hang_memo,xgf_name,created_time,updated_time,status,msg
    </sql>


    <insert id="insertBatch" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO ods_expense_detail_report(`transaction_id`,`transaction_type`,`trandate`,`tranid`,`sunlike_id`,`currency`,`subsidy`,`transaction_line_id`,`class_name`,`account_id`,`subject`,`subject_type`,`amount`,`foreign_amount`,`entity_id`,`entity_name`,`trans_memo`,`lines_memo`,`xm_name`,`hang_memo`,`xgf_name`,`created_time`,`updated_time`,`status`,`msg`)
        VALUES
        <foreach collection ="reportList" item="report" separator =",">
            ( #{report.transactionId}, #{report.transactionType},#{report.trandate}, #{report.tranid},#{report.sunlikeId}, #{report.curName},#{report.subName}, #{report.lineId},#{report.className}, #{report.accountId},#{report.subject}, #{report.subjectType},#{report.amount}, #{report.foreignAmount},#{report.entityId}, #{report.entityName},#{report.transMemo}, #{report.linesMemo},#{report.xmName}, #{report.hangMemo},#{report.xgfName}, #{report.createdTime},#{report.updatedTime}, #{report.status},#{report.msg})
        </foreach >
    </insert>




    <insert id="insert" parameterType="com.shokz.domain.MasterDetailReportDO" useGeneratedKeys="true" keyProperty="id" keyColumn="id" >


        INSERT INTO ods_expense_detail_report(`transaction_id`,`transaction_type`,`trandate`,`tranid`,`sunlike_id`,`currency`,`subsidy`,`transaction_line_id`,`class_name`,`account_id`,`subject`,`subject_type`,`amount`,`foreign_amount`,`entity_id`,`entity_name`,`trans_memo`,`lines_memo`,`xm_name`,`hang_memo`,`xgf_name`,`created_time`,`updated_time`,`status`,`msg`)
        VALUES( #{report.transactionId}, #{report.transactionType},#{report.trandate}, #{report.tranid},#{report.sunlikeId}, #{report.curName},#{report.subName}, #{report.lineId},#{report.className}, #{report.accountId},#{report.subject}, #{report.subjectType},#{report.amount}, #{report.foreignAmount},#{report.entityId}, #{report.entityName},#{report.transMemo}, #{report.linesMemo},#{report.xmName}, #{report.hangMemo},#{report.xgfName}, #{report.createdTime},#{report.updatedTime}, #{report.status},#{report.msg});


    </insert>


    <insert id="insertHistory" parameterType="com.shokz.domain.MasterDetailReportDO" useGeneratedKeys="true" keyProperty="id" keyColumn="id" >


        INSERT INTO history_detail_report(`transaction_id`,`transaction_type`,`trandate`,`tranid`,`sunlike_id`,`currency`,`subsidy`,`transaction_line_id`,`class_name`,`account_id`,`subject`,`subject_type`,`amount`,`foreign_amount`,`entity_id`,`entity_name`,`trans_memo`,`lines_memo`,`xm_name`,`hang_memo`,`xgf_name`,`created_time`,`updated_time`,`status`,`msg`)
        VALUES( #{report.transactionId}, #{report.transactionType},#{report.trandate}, #{report.tranid},#{report.sunlikeId}, #{report.curName},#{report.subName}, #{report.lineId},#{report.className}, #{report.accountId},#{report.subject}, #{report.subjectType},#{report.amount}, #{report.foreignAmount},#{report.entityId}, #{report.entityName},#{report.transMemo}, #{report.linesMemo},#{report.xmName}, #{report.hangMemo},#{report.xgfName}, #{report.createdTime},#{report.updatedTime}, #{report.status},#{report.msg});


    </insert>




    <select id="getMasterDetailReportByTranIdAndLineId" resultMap="detailReportMap">
        select <include refid="detailReportField"/> from ods_expense_detail_report where transaction_id = #{transactionId} and transaction_line_id = #{lineId}
    </select>


    <delete id="deleteMasterDetailReportByTranIdAndLineId">
        delete from ods_expense_detail_report where transaction_id = #{transactionId} and transaction_line_id = #{lineId}
    </delete>
</mapper>

classpath:mapping/ns 文件夹下的mapper.xml文件如下

<?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.company.dao.ns.NetsuiteMapper">
    <resultMap id="AccountingPeriods" type="com.shokz.domain.NSAccountingPeriodsDO">
        <result column="period_id" property="accountingPeriodID"/>
        <result column="CLOSED" property="closed"/>
        <result column="CLOSED_ACCOUNTS_PAYABLE" property="closedAccountsPayable"/>
        <result column="CLOSED_ACCOUNTS_RECEIVABLE" property="closedAccountsReceivable"/>


        <result column="CLOSED_ALL" property="closedAll"/>
        <result column="CLOSED_ON" property="closedOn"/>
        <result column="CLOSED_PAYROLL" property="closedPayroll"/>
        <result column="DATE_LAST_MODIFIED" property="dateLastModified"/>


        <result column="ENDING" property="ending"/>
        <result column="FISCAL_CALENDAR_ID" property="fiscalCalendarID"/>
        <result column="FULL_NAME" property="fullName"/>
        <result column="ISINACTIVE" property="isInactive"/>


        <result column="IS_ADJUSTMENT" property="isAdjustment"/>
        <result column="LOCKED_ACCOUNTS_PAYABLE" property="lockedAccountsPayable"/>
        <result column="LOCKED_ACCOUNTS_RECEIVABLE" property="lockedAccountsReceivable"/>
        <result column="LOCKED_ALL" property="lockedAll"/>


        <result column="LOCKED_PAYROLL" property="lockedPayroll"/>
        <result column="NAME" property="name"/>
        <result column="PARENT_ID" property="parentID"/>
        <result column="QUARTER" property="quarter"/>


        <result column="STARTING" property="starting"/>
        <result column="YEAR_0" property="year"/>
        <result column="YEAR_ID" property="yearID"/>


    </resultMap>


    <!--sql片段-->
    <sql id="findById_field">
        ACCOUNTING_PERIOD_ID period_id,CLOSED,CLOSED_ACCOUNTS_PAYABLE,CLOSED_ACCOUNTS_RECEIVABLE,CLOSED_ALL,CLOSED_ON,CLOSED_PAYROLL,DATE_LAST_MODIFIED,ENDING,
        FISCAL_CALENDAR_ID,FULL_NAME,ISINACTIVE,IS_ADJUSTMENT,LOCKED_ACCOUNTS_PAYABLE,LOCKED_ACCOUNTS_RECEIVABLE,LOCKED_ALL,LOCKED_PAYROLL,NAME,
        PARENT_ID,QUARTER,STARTING,YEAR_0,YEAR_ID
    </sql>


    <select id="getAccountingPeriodIdByDate" resultMap="AccountingPeriods">
        select  <include refid="findById_field"/> from ACCOUNTING_PERIODS where STARTING = #{date} and QUARTER='No' and YEAR_0='No'
    </select>
</mapper>

文章转载自梁霖编程工具库，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

mybatis连接多源数据库

评论