“This is the first day of my participation in the Gwen Challenge in November. See details of the event: The last Gwen Challenge in 2021”.

  • 💬 If the article is helpful to you, welcome to follow, like, bookmark (click three links) and subscribe.

🚐 1. Preface


  • Hello everyone, I am xiao Cheng, unconsciously the last article has been more than 20 days ago! In fact this time also have not been idle, one is before combing knowledge and resources, to build technical circles, another is in thinking about their own an output direction, community development very quickly, hot all sorts of all kinds of articles have a list of reflection, insisted on the article in essence is not much, quality is more important, So the direction of the future blog will be more inclined to actual combat and experience, and strive to share more valuable blog!

  • If the article is helpful to you, you can help to subscribe to the three key and column oh! Technical circle after this period of planning, has been preliminary shape! Interested, like-minded partners can check the left navigation bar technology circle introduction, looking forward to your joining!

  • This article focuses on SpringBoot collection MyBatis and MyBatis-Plus integration of multi-data sources knowledge!


🚅 2. Column recommendation


Conscience recommendation: the following related technical column is still free to share oh, we can help a little subscription oh!

JAVA advanced knowledge

Algorithm Diary Practice


🚔 3. Knowledge needed to integrate multiple data sources


1. When will multiple data sources be used

The emergence and application of a technology is bound to solve some existing problems. Common scenarios of multi-data sources are as follows:

(1), docking with a third party, some partners will not be to some of your demand and give you develop a function, they can provide you with a read-only access to the data source account, what do you need to get the data logic to handle by yourself, this time can’t avoid the need for multiple data sources integration.

(2) Business data has reached an order of magnitude, and the use of single database storage has reached a bottleneck, which requires operations such as database and table for data management. When operating data, it is inevitable to involve multiple data sources.

2. What are the ways of multi-data source integration

Referring to many materials on the Internet, it is found that there are no more than the following integration methods:

(1) Subcontracting is used to configure different MapperScan and Mapper files for different data sources

(2), the use of APO slice, to achieve dynamic data source switching (if you are not familiar with Aop, welcome to see my previous article, this knowledge is ripe oh! What is aspect oriented programming? )

(3) Use database proxy middleware, such as Mycat, etc

3. Differences between different methods

(1) Subcontracting can integrate JTA(JAVA Transactional API) to implement distributed transactions, but the implementation of the whole process is relatively complex.

(2) The drawback of AOP dynamic configuration of data sources is that global distributed transactions cannot be realized, so if only the docking of third-party data sources does not involve the need to ensure distributed transactions, it can be used as an option.

(3), using a database agent middleware is now more popular a kind of way, many companies are using this way, the developers do not need to concern too much has nothing to do with the business, to deal with them all to database agent middleware, a large number of generic data aggregation, transaction and data source switch are handled by the middleware, The performance and processing capability of middleware directly determine the read and write performance of applications, such as Mycat and TDDL. Now Ali has developed a 100% self-developed distributed database OceanBase, which supports distribution from the bottom layer and has very strong performance. If you are interested, you can go to know about it!

4. The actual combat selection method of this paper

Given the meet demand of the integration of multiple source scenario is need docking third-party data, temporarily not involve the problem of distributed transactions, so this paper practical integration of multiple data sources used way is to implement a simple sub-contracting 】 【 more data source integration, and the pit of a distributed transaction, as for the other way back slowly fill again (o (╥ man ╥) o)!


🚢 4. SpringBoot+MyBatis integration of multiple data sources


🔴 4.1 illustrates


This case involves a lot of codes, so only part of the article is posted. All the case codes have been uploaded to Gitee and can be directly accessed by those who need them: [SpringBoot and MyBatis integrate multi-data sources]. The project structure is as follows:




🟠 4.2 Dependency packages are involved


  • Spring-boot-starter -web – Web-related support
  • Mybatis -spring-boot-starter — springboot integrates mybatis dependencies
  • Mysql-connector-java — Mysql data driver
  • Lombok – Automatic generation of common method dependency packages for entity classes
  • Hutool -all — Common methods to encapsulate dependencies


<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2. 0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.013.</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.4. 5</version>
        </dependency>
Copy the code


🟡 4.3 Project Configuration


Server: port:9090Spring: Application: name: multi-datasource-instance datasource: # master: Jdbc-url: JDBC :mysql: dbC-url: JDBC :mysql://localhost:3306/test1? serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false
      username: root
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver# secondary databaseslaveNote that this is used when integrating multiple data sourcesspringbootDefault database connection poolHikariTo specify that the connection data is usedjdbc-urlRather thanurlattributejdbc-url: jdbc:mysql: / /localhost: 3306 /test2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false
      username: root
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver
Copy the code


🟢 4.4 Write data source configurations for primary and secondary databases


1. Master data sources-related configuration: mainly specify the master data source, scanned Mapper address, transaction manager and other information.

@Configuration
// Specify that the master database scans the corresponding Mapper file to generate proxy objects
@MapperScan(basePackages ="com.diary.it.multi.datasource.mapper" ,sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {

    // Address of mapper. XML
    private static final String MAPPER_LOCATION = "classpath*:mapper/*.xml";


    /** * The Primary annotation must be added to indicate that this data source is the default data source. Other data sources may exist in the project. If the name is not specified when fetching, this data source will be acquired by default
    @Primary
    @Bean(name = "masterDataSource")
    // Read the configuration file with the spring.datasource. Master prefix and map it to the corresponding configuration object
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource dataSource(a) {
        DataSource build = DataSourceBuilder.create().build();
        return build;
    }

    /** * transaction manager, the Primary annotation is used as above */
    @Bean(name = "masterTransactionManager")
    @Primary
    public PlatformTransactionManager dataSourceTransactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /** ** factory; /** * factory

    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MasterDataSourceConfig.MAPPER_LOCATION));
        returnsessionFactoryBean.getObject(); }}Copy the code


2. Secondary data source related configuration: mainly specify data source, scanned Mapper address, transaction manager and other information.


@Configuration
// Specify that Mapper files are scanned from the database to generate proxy objects
@MapperScan(basePackages = "com.diary.it.multi.datasource.mapper2", sqlSessionFactoryRef = "slaveSqlSessionFactory")
public class SlaveDataSourceConfig {
    // Address of mapper. XML
    private static final String MAPPER_LOCATION = "classpath*:mapper2/*.xml";

    /** * data source */
    @Bean(name = "slaveDataSource")
    // Read the configuration file with the spring.datasource. Slave prefix and map it to the corresponding configuration object
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource dataSource(a) {
        DataSource build = DataSourceBuilder.create().build();
        return build;
    }


    /** * transaction manager */
    @Bean(name = "slaveTransactionManager")
    public PlatformTransactionManager dataSourceTransactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /** * factory */

    @Bean(name = "slaveSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(SlaveDataSourceConfig.MAPPER_LOCATION));
        returnsessionFactoryBean.getObject(); }}Copy the code


🔵 4.5 Result


After completing the above steps, just as we usually write business logic in the same way, in the service to write business logic, in the mapper to write SQL statements, etc., let’s see the execution result!




🟣 4.6 Problems encountered during integration


After reading the above tutorial, it is easy to integrate multiple data sources. However, it is all too common to encounter various problems when integrating completely according to the teaching process. The following blogger summarizes the various problems encountered during the integration process. If you also encounter problems during the integration process, you can directly follow the blogger’s solution. .

Error 1: jdbcUrl is required with driverClassName is displayed

The reason: The default database connection pool after SpringBoot2.x is HikariCP(which claims to be the fastest and highest performance ever). The name of the HikariCP connection pool is different from other connection pools. It uses JDBC-URL instead of URL to specify the address to connect to the database. So if we do not specify a database connection pool such as Druid and use springBoot’s default connection pool, we need to change the url to connect to the database in the configuration to jDBC-URL property.

Error 2: Invalid bound Statement (not found) is displayed

The reason:

(1), in defining the data source configuration information is not specified SqlSessionFactoryBean scanning mapper. The location of the XML file is sessionFactoryBean. SetMapperLocations (XXX).

(2) The path corresponding to the namespace attribute in mapper. XML is incorrect, or the id name of the corresponding method and the parameterType attribute are incorrect

The xxxmapper. Java method returns a List, whereas the select element is not properly configured with ResultMap, or only with ResultType

Required a single bean, but 2 were found

The reason: Since we have already scanned the corresponding Mapper. Java using the MapperScan annotation when specifying the primary and secondary data source configuration, the scanned Mapper. Java has generated proxy classes into the Spring container. If you use MapperScan scan in the startup class, the above problem will occur. (Strangely: THIS problem will not be reported on another computer, so the problem will be solved by this solution first.)

Q4. Why was the Primary annotation added to the Primary data source configuration class

Reason: because of the integration with multiple data sources, so the DataSource, PlatformTransactionManager than instance will be injected into the Spring container, such as, the role of Primary note is: When we use an autoconfigured injection Bean such as Autowired, if the Bean has multiple candidates, if one of the candidates has the @primary annotation modifier, that candidate will be selected as the autoconfigured value.

Q5, com.mysql.jdbc.driver and com.mysql.cj. Jdbc.driver

The reason: Driver = com.mysql.cj.jdbc.driver = com.mysql.jdbc.driver = com.mysql.jdbc.driver = com.mysql.jdbc.driver = com.mysql.jdbc.driver = com.mysql.jdbc.driver = com.mysql.jdbc.driver = com.mysql.jdbc.driver = com.mysql.jdbc.driver = com.mysql.jdbc.driver Com.mysql.cj.jdbc.driver is the mysql-connector-Java 6 and later database Driver. Mysql 6.x Driver will be deprecated and will be deprecated at startup if the mysql Driver running mysql6.x continues to use com.mysql.jdbc.driver.


🚲 5. SpringBoot+Mybatis-Plus integration of multiple data sources


However, it is easier to use Mybatis -Plus. Mybatis -Plus officially supports multiple data sources, which can be realized only with a note. It is recommended to use this method when integrating multiple data sources.

🟥 5.1 illustrates


This case involves a lot of code, so only part of the article is posted, all the case code has been uploaded to Gitee, if you need it, you can directly access:




The project structure is as follows:




🟧 5.2 Dependency packages


  • Spring-boot-starter -web – Web-related support
  • Mybatis -plus-boot-starter– Springboot integrates mybatis-plus dependencies
  • Dynamic-datasource -spring-boot-starter — mybatis-plus manages datasource dependencies
  • Mysql-connector-java — Mysql data driver
  • Lombok – Automatic generation of common method dependency packages for entity classes
  • Hutool -all — Common methods to encapsulate dependencies


<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency>  <dependency> <groupId>com.baomidou</groupId> <artifactId>dynamic-datasource-spring-boot-starter</artifactId> <version>3.41.</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId>  <version>3.43.</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.4. 5</version>
        </dependency>
Copy the code


🟨 5.3 Related Configurations


Server: port:9091Spring: Application: name: multi-datasource-instance2 datasource: # Master # set the default data source or data source group, default is master strict:false# Match data source strictly, defaultfalse. trueThrow an exception if the specified data source is not matched,falseMysql: JDBC :mysql://localhost:3306/test1? serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false
          username: root
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver# secondary databaseslave:
          url: jdbc:mysql: / /localhost: 3306 /test2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false
          username: root
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver

Copy the code


🟩 5.4 Usage Mode






🟦 5.5 Result






🚀 6. Write at the end


All the codes in the article have been uploaded to Gitee, if necessary, you can help yourself (later will be uploaded to CSDN for free download), if it is helpful, don’t forget star oh, there will be more practical articles (by the way, the next article is: about Ftp file upload server and download to local actual practice), Gitee project through train as follows:

1. SpringBoot+MyBatis integration of multiple data sources

2, SpringBoot+MyBatis-Plus integration of multiple data sources

Recently, I have been busy sorting out the resources of the technical circle, so there are few articles. Now the resources of the technical circle have been preliminarily sorted out, and the speed of articles will be restored successively later. 【 Technical Circle 】 There are free interview resources, resume template, PPT of year-end report, CSDN VIP download resources and so on, interested people can check the homepage to get