SpringBoot+Mybatis配置多数据源连接多个数据库

最新推荐文章于 2024-08-09 18:07:53 发布

ic_xcc

最新推荐文章于 2024-08-09 18:07:53 发布

阅读量3k

点赞数 1

分类专栏： Mybatis SpringBoot 文章标签：数据库 mybatis java

本文链接：https://blog.csdn.net/ic_xcc/article/details/109156425

版权

SpringBoot 同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

Mybatis

2 篇文章 0 订阅

订阅专栏

因项目需要，项目中需要连接多个数据源获取不同的信息，由此记录一下，项目采用SpringBoot+Mybatis，并配合Mybatis框架编写xml文件来执行SQL，末位附带一个接口展示案例

下面开始上代码：

在pom.xml文件中需要添加一些依赖在pom.xml文件中需要添加一些依赖

<!-- Spring Boot Mybatis 依赖 -->
<dependency>
  <groupId>org.mybatis.spring.boot</groupId>
  <artifactId>mybatis-spring-boot-starter</artifactId>
  <version>1.2.0</version>
</dependency>
 
<!-- MySQL 连接驱动依赖 -->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.39</version>
</dependency>
 
<!-- Druid 数据连接池依赖 -->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
  <version>1.0.18</version>
</dependency>

在application.yaml配置两个数据源信息

server:
  port: 9090
  tomcat:
    uri-encoding: UTF-8

# master 数据源配置
master:
  datasource:
    url: jdbc:mysql://10.1.92.134:3306/cashcheckordb?autoReconnect=true&characterEncoding=utf8&useSSL=false
    driver-class-name: com.mysql.jdbc.Driver
    username: name
    password: password
    initial-size: 1
    min-idle: 1
    max-active: 20
    test-on-borrow: true
    max-wait: 60000
    time-between-eviction-runs-millis: 60000
    min-evictable-idle-time-millis: 300000
    validation-query: SELECT 1 FROM DUAL
    test-While-Idle: true
    test-on-return: false
    pool-prepared-statements: false
    max-pool-prepared-statement-per-connection-size: 20
    filters: stat,wall,log4j,config
# second 数据源配置
second:
  datasource:
    url: jdbc:mysql://10.1.92.134:3306/unibatchdb?autoReconnect=true&characterEncoding=utf8&useSSL=false
    username: name
    password: password
    driver-class-name: com.mysql.jdbc.Driver
    max-idle: 10
    max-wait: 10000
    min-idle: 5
    initial-size: 5

数据源配置注意事项

多数据源配置的时候注意，必须要有一个主数据源，即 MasterDataSourceConfig 配置

@Primary 标志这个 Bean 如果在多个同类 Bean 候选时，该 Bean 优先被考虑。「多数据源配置的时候注意，必须要有一个主数据源，用 @Primary 标志该 Bean

@MapperScan 扫描 Mapper 接口并容器管理，包路径精确到 master，为了和下面 cluster 数据源做到精确区分

@Value 获取全局配置文件 application.properties 的 kv 配置,并自动装配sqlSessionFactoryRef 表示定义了 key ，表示一个唯一 SqlSessionFactory 实例

MasterDataSourceConfig代码：

package com.payment.config.dataSource;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
    // 精确到 master 目录，以便跟其他数据源隔离
    static final String PACKAGE = "com.payment.mapper.master";
    static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml";

    @Value("${master.datasource.url}")
    private String url;

    @Value("${master.datasource.username}")
    private String user;

    @Value("${master.datasource.password}")
    private String password;

    @Value("${master.datasource.driver-class-name}")
    private String driverClass;

    @Bean(name = "masterDataSource")
    @Primary
    public DataSource masterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(masterDataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MasterDataSourceConfig.MAPPER_LOCATION));
        return bean.getObject();
    }

    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager(@Qualifier("masterDataSource") DataSource masterDataSource) {
        return new DataSourceTransactionManager(masterDataSource);
    }

    @Bean(name = "masterSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate masterSqlSessionTemplate(@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

SecondDataSourceConfig代码：

package com.payment.config.dataSource;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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 javax.sql.DataSource;

@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = SecondDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "secondSqlSessionFactory")
public class SecondDataSourceConfig {
    // 精确到 second 目录，以便跟其他数据源隔离
    static final String PACKAGE = "com.payment.mapper.second";
    static final String MAPPER_LOCATION = "classpath:mapper/second/*.xml";

    @Value("${second.datasource.url}")
    private String url;

    @Value("${second.datasource.username}")
    private String user;

    @Value("${second.datasource.password}")
    private String password;

    @Value("${second.datasource.driver-class-name}")
    private String driverClass;

    @Bean(name = "secondDataSource")
    public DataSource clusterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    @Bean(name = "secondSqlSessionFactory")
    public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDataSource") DataSource secondDataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(secondDataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(SecondDataSourceConfig.MAPPER_LOCATION));
        return bean.getObject();
    }

    @Bean(name = "secondTransactionManager")
    public DataSourceTransactionManager secondTransactionManager(@Qualifier("secondDataSource") DataSource secondDataSource) {
        return new DataSourceTransactionManager(secondDataSource);
    }

    @Bean(name = "secondSqlSessionTemplate")
    public SqlSessionTemplate secondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

项目目录结构如下：

举例一个接口实现：

在TransactionMapper.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.payment.mapper.second.TransactionMapper">
    <select id="uploadedFiles" parameterType="java.util.Map" resultType="java.lang.Integer" >
        <bind name="timeSection" value="'%' + _parameter.timeSection + '%'" />
        select count(distinct filename) uploadedFiles   from t_chk_file_mer_tolal
        where merno=#{memberNo} and filename like #{timeSection};
    </select>
</mapper>

在TransactionMapper中代码：

package com.payment.mapper.second;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;

@Repository
public interface TransactionMapper {
    /**
     * 上传文件数
     * @param userSign
     * @return
     */
    Integer uploadedFiles(Map<String,Object> userSign);
}

在TransactionServiceImpl中代码：

package com.payment.service.impl;

import com.payment.mapper.second.TransactionMapper;
import com.payment.service.TransactionService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class TransactionServiceImpl implements TransactionService {

    @Autowired
    private TransactionMapper transactionMapper;

    /**
     * 文件信息显示
     * @param userSign
     * @return
     */
    @Override
    public Map<String,Object> tradeShow(Map<String, Object> userSign){
        Map<String,Object> information = new HashMap();
        information.put("uploadedFiles",transactionMapper.uploadedFiles(userSign));
        return information;
    }

}

在TransactionService中代码展示：

package com.payment.service;

import java.util.Map;

public interface TransactionService {

    /**
     * 展示数据
     * @param userSign
     * @return
     */
    Map<String,Object> tradeShow(Map<String, Object> userSign);
}

在TransactionController中代码展示：

package com.payment.controller;

import com.payment.common.R;
import com.payment.common.enums.ExceptionEnum;
import com.payment.service.TransactionService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

@RestController
public class TransactionController {

    @Autowired
    private TransactionService transactionService;

    /**
     * 文件信息显示
     * @param map
     * @return
     */
    @PostMapping("/tradeShow")
    public R tradeShow(@RequestBody Map<String, String> map) {
        String memberNo = map.get("merno");
        String timeSection = map.get("timeSection");
        Map<String, Object> userSign = new HashMap<>(16);
        try {
            if (memberNo != null && !memberNo.equals("")) {
                userSign.put("memberNo", memberNo);
            }
            if (timeSection != null && !timeSection.equals("")) {
                userSign.put("timeSection", timeSection);
            } else {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
                String time = sdf.format(new Date());
                userSign.put("timeSection", time);
            }
            return R.ok().data(transactionService.tradeShow(userSign));
        } catch (Exception e) {
            return R.error(ExceptionEnum.PARAM_ERROR.getCode(), ExceptionEnum.PARAM_ERROR.getMsg());
        }
    }
}

启动类PayMonitorApplication中代码展示：

package com.payment;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.payment.mapper")//将项目中对应的mapper类的路径加进来就可以了
public class PayMonitorApplication {
    public static void main(String[] args) {
        SpringApplication.run(PayMonitorApplication.class, args);
    }

}