Spring boot is combined with mybatis and druid to realize multiple data sources, which can be switched automatically

Time:2021-8-2

Original address: https://blog.lanweihong.com/p…

Github: spring-boot-mybatis-multiple-data-source

Add dependent packages

editpom.xmlFile, add related dependencies:

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.8</version>
        <relativePath/>
    </parent>
    
<properties>
        <mysql.driver.version>8.0.16</mysql.driver.version>
        <mybatis.spring.boot.version>1.3.2</mybatis.spring.boot.version>
        <druid.version>1.1.10</druid.version>
        <tk.mybatis.version>2.1.5</tk.mybatis.version>
        <lombok.version>1.16.18</lombok.version>
    </properties>

    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis.spring.boot.version}</version>
        </dependency>

        <!-- mysql driver -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.driver.version}</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid.version}</version>
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-autoconfigure</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>${tk.mybatis.version}</version>
        </dependency>

    </dependencies>

to configureapplication.ymlfile

In profileapplication.ymlConfigure data sources in:

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    username: root
    password: Aa123456.
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      initial-size: 5
      max-active: 50
      max-wait: 60000
      min-idle: 5
      #Configure the book data source, which can be customized
      book:
        # type: com.alibaba.druid.pool.DruidDataSource
        # driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db01?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=Asia/Shanghai
        # username: root
        # password: 1
      #Configure user data source
      user:
        url: jdbc:mysql://localhost:3306/db02?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=Asia/Shanghai
        #Reconfigure the password. If it is not configured, the password configured above will be used by default
        username: root
        password: 1

Manually configure data sources

Master data source configuration

Create configuration classBookDataSourceConfig, the configuration class needs toDataSourceDataSourceTransactionManagerSqlSessionFactorySqlSessionTemplateFour data items are configured. When there are multiple data sources in the system, there must be one data source as the main data source, which can be used@PrimaryAnnotation modification.

BookDataSourceConfigConfiguration class:

@Configuration
//Automatically switch data sources by configuring 'basepackages' and' sqlsessiontemplateref 'in the ` @ mapperscan' annotation
@MapperScan(basePackages = "com.lanweihong.dao.book",
        sqlSessionFactoryRef = "bookSqlSessionFactory", sqlSessionTemplateRef = "bookSqlSessionTemplate")
public class BookDataSourceConfig {

    public static final String MAPPER_LOCATION = "classpath:mapper/book/*.xml";

    /**
     *Master data source
     *Note: @ primary if there are multiple beans of the same kind, the bean is preferred. When there are multiple data sources, a master data source must be configured and marked with this annotation
     * @return
     */
    @Primary
    @Bean("bookDataSource")
    @ConfigurationProperties("spring.datasource.druid.book")
    public DataSource bookDataSource(){
        return DruidDataSourceBuilder.create().build();
    }

    @Primary
    @Bean("bookTransactionManager")
    public DataSourceTransactionManager bookTransactionManager() {
        return new DataSourceTransactionManager(bookDataSource());
    }

    @Primary
    @Bean("bookSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("bookDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        return sqlSessionFactoryBean.getObject();
    }

    @Primary
    @Bean("bookSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("bookDataSource") DataSource dataSource) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory(dataSource));
    }
}

Mybatis is configured through the scan package and the correspondingsqlSessionTemplateTo automatically switch data sources, that is, through@MapperScanConfiguration in annotationbasePackagesandsqlSessionTemplateRef

@MapperScan(basePackages = "com.lanweihong.dao.book",
        sqlSessionFactoryRef = "bookSqlSessionFactory", sqlSessionTemplateRef = "bookSqlSessionTemplate")

Configure second data source

Write configuration classUserDataSourceConfig

@Configuration
@MapperScan(basePackages = "com.lanweihong.dao.user", sqlSessionTemplateRef = "userSqlSessionTemplate")
public class UserDataSourceConfig {

    public static final String MAPPER_LOCATION = "classpath:mapper/user/*.xml";

    /**
     *User data source
     * @return
     */
    @Bean("userDataSource")
    @ConfigurationProperties("spring.datasource.druid.user")
    public DataSource userDataSource(){
        return DruidDataSourceBuilder.create().build();
    }

    @Bean("userTransactionManager")
    public DataSourceTransactionManager userTransactionManager() {
        return new DataSourceTransactionManager(userDataSource());
    }

    @Bean("userSqlSessionFactory")
    public SqlSessionFactory userSqlSessionFactory(@Qualifier("userDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean("userSqlSessionTemplate")
    public SqlSessionTemplate userSqlSessionTemplate(@Qualifier("userDataSource") DataSource dataSource) throws Exception {
        return new SqlSessionTemplate(userSqlSessionFactory(dataSource));
    }

}

So far, the configuration of different data sources has been completed. Next, we use.

use

Write and use normallyDAOandService, spring will automatically switch the corresponding data source according to the mapping of the data source configuration. It does not need to be specified in the service, but can be used directly.

followingDAO/Mapper/Service/ControllerThe code is an example code. Only part of the code is listed. For the complete code, please see: https://github.com/lanweihong…

IBookDao

public interface IBookDao extends BaseMapper<BookDO> {

    BookDO getByBookName(@Param("bookName") String bookName);
}

BookMapper.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.lanweihong.dao.book.IBookDao">

    <resultMap id="BaseResultMap" type="com.lanweihong.entity.BookDO">
        <id column="id" jdbcType="BIGINT" property="id" />
        <result column="version" jdbcType="INTEGER" property="version" />
        <result column="book_name" jdbcType="VARCHAR" property="bookName" />
        <result column="status" jdbcType="TINYINT" property="status" />
        <result column="add_time" jdbcType="TIMESTAMP" property="addTime" />
    </resultMap>

    <sql id="BaseColumnList">
        id, version, book_name, status, add_time
    </sql>

    <select id="getByBookName" resultMap="BaseResultMap">
        SELECT <include refid="BaseColumnList" /> FROM book WHERE book_name = #{bookName}
    </select>
    
</mapper>

Service

@Service("bookService")
public class BookServiceImpl implements IBookService {

    private final IBookDao bookDao;

    @Autowired
    public BookServiceImpl(IBookDao bookDao) {
        this.bookDao = bookDao;
    }

    @Override
    public List<BookDO> listAll() {
        return bookDao.selectAll();
    }

    @Override
    public BookDO getByBookName(String bookName) {
        return bookDao.getByBookName(bookName);
    }
}

Controller

@RestController
@RequestMapping("/api/v1/")
public class MainController {

    private final IUserService userService;
    private final IBookService bookService;

    @Autowired
    public MainController(IUserService userService, IBookService bookService) {
        this.userService = userService;
        this.bookService = bookService;
    }

    @GetMapping("/books")
    public Map<String, Object> queryBooks(@RequestParam(value = "name", required = false) String bookName) {
        List<BookDO> books = new ArrayList<>();
        if (StringUtil.isEmpty(bookName)) {
            books = this.bookService.listAll();
        } else {
            BookDO book = bookService.getByBookName(bookName);
            books.add(book);
        }
        Map<String, Object> result = new HashMap<>(1);
        result.put("data", books);
        return result;
    }

    @GetMapping("/users")
    public Map<String, Object> queryUsers(@RequestParam(value = "name", required = false) String userName) {
        List<UserDO> users = new ArrayList<>();
        if (StringUtil.isEmpty(userName)) {
            users = this.userService.listAll();
        } else {
            UserDO user = userService.getByUserName(userName);
            users.add(user);
        }

        Map<String, Object> result = new HashMap<>(1);
        result.put("data", users);
        return result;
    }
}

test

Launch the application and access it in the browserhttp://127.0.0.1:8015/api/v1/usersandhttp://127.0.0.1:8015/api/v1/booksTesting;

Spring boot is combined with mybatis and druid to realize multiple data sources, which can be switched automatically

Spring boot is combined with mybatis and druid to realize multiple data sources, which can be switched automatically

Spring boot is combined with mybatis and druid to realize multiple data sources, which can be switched automatically

Project structure

The project structure is as follows:

Spring boot is combined with mybatis and druid to realize multiple data sources, which can be switched automatically

The code has been uploaded to GitHub: spring boot mybatis multiple data source

Reference documents

  1. https://blog.csdn.net/acquain…