Mybatis multi-data source configuration and use

Mybatis multi-data source configuration and use

The last blog introduces the JdbcTemplate configuration of the use of multiple data sources posture, in our actual project development, the use of Mybatis to operate the database may be very much, this article simply introduces mybatis, the use of multiple data sources posture

You can specify the implementation mode of different data sources in different packages by differentiating packet paths and matching configuration files

I. Environment preparation

1. Database related

Take mysql as an example to illustrate, because multiple data sources are needed, the simplest case is multiple logical libraries on a physical library, this paper is based on the local mysql operation

Create database test and story; create table money; create database test and story;

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT ' ' COMMENT 'Username',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT 'money',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time'.PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Copy the code

2. Project environment

This project is developed by SpringBoot 2.2.1.RELEASE + Maven 3.5.3 + IDEA

Here is the core POM.xml (source code available at the end of this article)

<dependencies>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>1.3.2</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
</dependencies>
Copy the code

Configuration file information application.yml

# Database configuration
spring:
  datasource:
    story:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: JDBC: mysql: / / 127.0.0.1:3306 / story? useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
      username: root
      password:
    test:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: JDBC: mysql: / / 127.0.0.1:3306 / test? useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
      username: root
      password:


# log correlation
logging:
  level:
    root: info
    org:
      springframework:
        jdbc:
          core: debug
Copy the code

Please note that the database configuration above, which we described earlier, is as follows, they are not the same level, and the above configuration requires additional load parsing ourselves

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: JDBC: mysql: / / 127.0.0.1:3306 / story? useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password:
Copy the code

II. Specify the package path

This implementation is similar to the multi-source configuration of JdbcTemplate in the previous article. Mapper files of different data sources are split into different packages, and then specified respectively when configuring mybatis data source and resource file loading

1. Project structure

Two databases, Story + test, are used in this project. Mapper. XML and corresponding entity-related classes of different databases are released respectively, as shown in the figure below

2. Implementation

The Entity, Mapper, Repository, and XML files in the figure above are basically the same because the table structures in the two libraries are exactly the same. The code below shows only one of them

Database entity class StoryMoneyEntity

@Data
public class StoryMoneyEntity {
    private Integer id;

    private String name;

    private Long money;

    private Integer isDeleted;

    private Timestamp createAt;

    private Timestamp updateAt;
}
Copy the code

XML corresponds to the Mapper interface StoryMoneyMapper

@Mapper
public interface StoryMoneyMapper {
    List<StoryMoneyEntity> findByIds(List<Integer> ids);
}
Copy the code

XML file corresponding to mapper


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.git.hui.boot.multi.datasource.story.mapper.StoryMoneyMapper">

    <resultMap id="BaseResultMap" type="com.git.hui.boot.multi.datasource.story.entity.StoryMoneyEntity">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="money" property="money" jdbcType="INTEGER"/>
        <result column="is_deleted" property="isDeleted" jdbcType="TINYINT"/>
        <result column="create_at" property="createAt" jdbcType="TIMESTAMP"/>
        <result column="update_at" property="updateAt" jdbcType="TIMESTAMP"/>
    </resultMap>
    <sql id="money_po">
      id, `name`, money, is_deleted, create_at, update_at
    </sql>

    <select id="findByIds" parameterType="list" resultMap="BaseResultMap">
        select
        <include refid="money_po"/>
        from money where id in
        <foreach item="id" collection="list" separator="," open="(" close=")" index="">
            #{id}
        </foreach>
    </select>
</mapper>
Copy the code

Database operations encapsulate class StoryMoneyRepository

@Repository
public class StoryMoneyRepository {
    @Autowired
    private StoryMoneyMapper storyMoneyMapper;

    public void query(a) {
        List<StoryMoneyEntity> list = storyMoneyMapper.findByIds(Arrays.asList(1.1000)); System.out.println(list); }}Copy the code

Next, we’ll focus on the data source and the related configuration of Mybatis StoryDatasourceConfig

// Note that MapperScan binds the data source to the corresponding package path
@Configuration
@MapperScan(basePackages = "com.git.hui.boot.multi.datasource.story.mapper", sqlSessionFactoryRef = "storySqlSessionFactory")
public class StoryDatasourceConfig {

    // Obtain the database configuration from the configuration file
    @Primary
    @Bean(name = "storyDataSourceProperties")
    @ConfigurationProperties(prefix = "spring.datasource.story")
    public DataSourceProperties storyDataSourceProperties(a) {
        return new DataSourceProperties();
    }

    // DataSource instance creation
    @Primary
    @Bean(name = "storyDataSource")
    public DataSource storyDataSource(@Qualifier("storyDataSourceProperties") DataSourceProperties storyDataSourceProperties) {
        return storyDataSourceProperties.initializeDataSourceBuilder().build();
    }

    // SqlSession factory class for IBatis
    @Primary
    @Bean("storySqlSessionFactory")
    public SqlSessionFactory storySqlSessionFactory(DataSource storyDataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(storyDataSource);
        bean.setMapperLocations(
                // Set the XML location of mybatis
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/story/*.xml"));
        return bean.getObject();
    }

    @Primary
    @Bean("storySqlSessionTemplate")
    public SqlSessionTemplate storySqlSessionTemplate(SqlSessionFactory storySqlSessionFactory) {
        return newSqlSessionTemplate(storySqlSessionFactory); }}Copy the code

The configuration file for the other data source is as follows

@Configuration
@MapperScan(basePackages = "com.git.hui.boot.multi.datasource.test.mapper", sqlSessionFactoryRef = "testSqlSessionFactory")
public class TestDatasourceConfig {

    @Bean(name = "testDataSourceProperties")
    @ConfigurationProperties(prefix = "spring.datasource.test")
    public DataSourceProperties testDataSourceProperties(a) {
        return new DataSourceProperties();
    }

    @Bean(name = "testDataSource")
    public DataSource testDataSource(@Qualifier("testDataSourceProperties") DataSourceProperties storyDataSourceProperties) {
        return storyDataSourceProperties.initializeDataSourceBuilder().build();
    }

    @Bean("testSqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("testDataSource") DataSource testDataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(testDataSource);
        bean.setMapperLocations(
                // Set the XML location of mybatis
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/test/*.xml"));
        return bean.getObject();
    }

    @Bean("testSqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("testSqlSessionFactory") SqlSessionFactory testSqlSessionFactory) {
        return newSqlSessionTemplate(testSqlSessionFactory); }}Copy the code

3. The test

Just to test if this works, call directly from the launch class

@SpringBootApplication
public class Application {

    public Application(StoryMoneyRepository storyMoneyRepository, TestMoneyRepository testMoneyRepository) {
        storyMoneyRepository.query();
        testMoneyRepository.query();
    }

    public static void main(String[] args) { SpringApplication.run(Application.class); }}Copy the code

The output is as follows

4. Summary

DataSource instance, SqlSessionFactory instance, etc

Mybatis configuration process, more than a data source, more than a related configuration, the advantage is easy to understand, the disadvantage is not flexible, if MY Mapper class is put in the wrong position, there may be a problem

So is there another way, if I want to put all the Mappers in one package path, is that supported?

The next blog post, will introduce a way based on AbstractRoutingDataSource + annotation to implement multiple source of support

II. The other

0. Project

Related blog

【DB series 】 Multi-data source configuration and use of JdbcTemplate
【DB series 】Mybatis-Plus code automatic generation
【DB series 】MybatisPlus Integration
【DB series 】Mybatis+ annotations integration
【DB series 】Mybatis+ XML integration

The source code

Project: github.com/liuyueyi/sp…
Source: github.com/liuyueyi/sp…

1. An ashy Blog

As far as the letter is not as good, the above content is purely one’s opinion, due to the limited personal ability, it is inevitable that there are omissions and mistakes, if you find bugs or have better suggestions, welcome criticism and correction, don’t hesitate to appreciate

Below a gray personal blog, record all the study and work of the blog, welcome everyone to go to stroll

A grey Blog Personal Blog blog.hhui.top
A Grey Blog-Spring feature Blog Spring.hhui.top
Wechat official account: One Grey Blog

Mybatis multi-data source configuration and use

I. Environment preparation

1. Database related

2. Project environment

II. Specify the package path

1. Project structure

2. Implementation

3. The test

4. Summary

II. The other

0. Project

1. An ashy Blog

Related Posts

Understand Java generics in depth

Initial understanding of Flink State

Python real case, CV2 module, Python tok Yin character video