Shulou(Shulou.com)05/31 Report--

This article shows you how to configure Mybatis multiple data sources in Spring boot. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Environmental preparation

Experimental environment:

JDK 1.8

SpringBoot 2.4.1

Maven 3.6.3

MySQL 5.7

Because I only have a MySQL database locally, I will demonstrate it by starting a local MySQL and creating two databases in MySQL, each with a table in it.

Data preparation

The local MySQL port remains unchanged by default, and the port number is 3306.

Create the database demo1,demo2. Create the table book in the demo1 database.

-- create table create table Book (id int auto_increment primary key, author varchar (64) not null comment 'author Information', name varchar (64) not null comment 'Book name', price decimal not null comment 'Price', createTime datetime null comment 'time on shelves', description varchar (128) null comment 'Book description') -- insert data INSERT INTO demo1.Book (id, author, name, price, createTime, description) VALUES (1, 'Jin Yong', 'The Smiling、Proud Wanderer', 13, '2020-12-19 15-26 name 51-51,' martial arts novel'); INSERT INTO demo1.Book (id, author, name, price, createTime, description) VALUES (2, 'Luo Guanzhong', 'Romance of the three Kingdoms', 14, '2020-12-19 15 15-28-36 name,' historical novel')

Create the table user in the demo2 database.

-- create table create table User (id int auto_increment primary key, name varchar (32) null comment 'user name', birthday date null comment 'date of birth') comment 'user Information Table';-- insert data INSERT INTO demo2.User (id, name, birthday) VALUES (1, 'Jin Yong', '1924-03-10') INSERT INTO demo2.User (id, name, birthday) VALUES (2, 'Luo Guanzhong', '1330-01-10')

When the data is ready, two new pieces of data have been added to the table.

Project preparation

Here you initialize a SpringBoot project that adds web, lombok, mybatis, and mysql dependencies directly from the Spring official.

Visit direct download: https://start.spring.io/starter.zip?type=maven-project&language=java&bootVersion=2.4.1.RELEASE&baseDir=demo&groupId=com&artifactId=wdbyte&name=demo&description=Demo%20project%20for%20Spring%20Boot&packageName=com.wdbyte.demo&packaging=jar&javaVersion=1.8&dependencies=mybatis,lombok,web,mysql

If you already have a SpringBoot project on hand, since you want to transform it into multiple data sources, you should already have a data source. If the new data source database is the same as the current one, you can directly use your project for transformation testing.

Multiple data sources

The multi-data source development of SpringBoot is very simple. If the databases of multiple data sources are the same, such as MySQL, then the dependency does not need to be changed, only the multi-data source configuration is needed.

If your new database data source is different from the current database, remember to introduce driver dependencies for the new database, such as MySQL and PGSQL.

Mysql mysql-connector-java runtime org.postgresql postgresql 42.2.7

Connection configuration

Since there are multiple data sources, because the database user name and password may be different, it is necessary to configure multiple data source information, which can be configured directly in properties/yml. Note that the distinction is based on the property name of the configuration, and because the data source should have a default data source, it is best to distinguish in name (here primary is used as the primary data source identity).

# # main data source # # spring.datasource.primary.jdbc-url=jdbc:mysql://127.0.0.1:3306/demo1?characterEncoding=utf-8&serverTimezone=GMT%2B8 spring.datasource.primary.driver-class-name=com.mysql.jdbc.Driver spring.datasource. Primary.username=root spring.datasource.primary.password= # # second data source # # spring.datasource.datasource2.jdbc-url=jdbc:mysql://127.0.0.1:3306/demo2?characterEncoding=utf-8&serverTimezone=GMT%2B8 spring.datasource.datasource2.driver-class-name=com .mysql.jdbc.Driver spring.datasource.datasource2.username=root spring.datasource.datasource2.password= # mybatis mybatis.mapper-locations=classpath:mapper/*.xml mybatis.type-aliases-package=com.wdbyte.domain

Note that the data source connection url in the configuration uses jdbc-url at the end.

Because of the use of the Mybatis framework, so the configuration information of the Mybatis framework is also necessary, specify to scan the mapper xml configuration file under the directory mapper.

Mybatis configuration

How to write Mybatis Mapper or how to use tools to generate Mybatis Mapper is not the focus of this article, if you do not know, you can refer to the official Mybatis documentation or my previous article.

Link 1: access the database using Mybatis (automatic generation plug-in)

Link 2: use Mybatis to integrate pagehelper paging plug-ins and mapper plug-ins

Now I have written the corresponding Mybatis configuration according to the two tables in the above two libraries, the Book table and the User table.

Create BookMapper.xml and UserMapper.xml and put them in the path mapper directory of the configuration file. Create UserMapper and BookMapper interface operation classes in different directories. Note that the Mapper interface should be opened in different directories according to the data source. Later, scan different directories with different data source configurations, so that different Mapper can use different data source configurations.

The Service layer remains unchanged, where both BookMapper and UserMapper have a selectAll () method for query testing.

Multiple data source configuration

As you should see above, the only difference so far from Mybatis single data source writing is that the Mapper interface is separated by different directories, so this difference must be reflected in the data source configuration.

Master data source

To start configuring the information of two data sources, first configure the main data source, and configure the scanned MapperScan directory to be com.wdbyte.mapper.primary

/ * * Primary data source configuration * * @ author niujinpeng * @ website: https://www.wdbyte.com * @ date 2020-12-19 * / @ Configuration @ MapperScan (basePackages = {"com.wdbyte.mapper.primary"}) SqlSessionFactoryRef = "sqlSessionFactory" public class PrimaryDataSourceConfig {@ Bean (name = "dataSource") @ ConfigurationProperties (prefix = "spring.datasource.primary") @ Primary public DataSource dataSource () {return DataSourceBuilder.create () .build () } @ Bean (name = "sqlSessionFactory") @ Primary public SqlSessionFactory sqlSessionFactory (@ Qualifier ("dataSource") DataSource dataSource) throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean (); bean.setDataSource (dataSource); bean.setMapperLocations (new PathMatchingResourcePatternResolver (). GetResources ("classpath:mapper/*.xml")); return bean.getObject () @ Bean (name = "transactionManager") @ Primary public DataSourceTransactionManager transactionManager (@ Qualifier ("dataSource") DataSource dataSource) {return new DataSourceTransactionManager (dataSource);} @ Bean (name = "sqlSessionTemplate") @ Primary public SqlSessionTemplate sqlSessionTemplate (@ Qualifier ("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) {return new SqlSessionTemplate (sqlSessionFactory);}}

Unlike single data sources, here

DataSource sqlSessionFactory transactionManager sqlSessionTemplate

Are configured separately, simple bean creation, the following are some of the notes used.

ConfigurationProperties (prefix = "spring.datasource.primary"): use the configuration that starts with spring.datasource.primary.

Primary: declare that this is a master data source (the default data source) and is necessary for multiple data source configuration.

Qualifier: explicitly select the incoming Bean.

The second data source

The only difference between the second data source and the primary data source is the MapperScan scan path and the Bean name created, and there is no annotation for the @ Primary primary data source.

/ * * second data source configuration * * @ author niujinpeng * @ website: https://www.wdbyte.com * @ date 2020-12-19 * / @ Configuration @ MapperScan (basePackages = {"com.wdbyte.mapper.datasource2"} SqlSessionFactoryRef = "sqlSessionFactory2") public class SecondDataSourceConfig {@ Bean (name = "dataSource2") @ ConfigurationProperties (prefix = "spring.datasource.datasource2") public DataSource dataSource () {return DataSourceBuilder.create () .build () } @ Bean (name = "sqlSessionFactory2") public SqlSessionFactory sqlSessionFactory (@ Qualifier ("dataSource2") DataSource dataSource) throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean (); bean.setDataSource (dataSource); bean.setMapperLocations (new PathMatchingResourcePatternResolver (). GetResources ("classpath:mapper/*.xml")); return bean.getObject () @ Bean (name = "transactionManager2") public DataSourceTransactionManager transactionManager (@ Qualifier ("dataSource2") DataSource dataSource) {return new DataSourceTransactionManager (dataSource);} @ Bean (name = "sqlSessionTemplate2") public SqlSessionTemplate sqlSessionTemplate (@ Qualifier ("sqlSessionFactory2") SqlSessionFactory sqlSessionFactory) {return new SqlSessionTemplate (sqlSessionFactory);}}

Note: because the scanned Mapper path has been configured in each of the two data sources, you need to delete it if you have previously used Mapper scanning annotations in the SpringBoot startup class.

Access test

Write two simple query Controller and then test the access.

/ / BookController @ RestController public class BookController {@ Autowired private BookService bookService; @ GetMapping (value = "/ books") public Response selectAll () throws Exception {List books = bookService.selectAll (); return ResponseUtill.success (books);}} / / UserController @ RestController public class UserController {@ Autowired private UserService userService ResponseBody @ GetMapping (value = "/ users") public Response selectAll () {List userList = userService.selectAll (); return ResponseUtill.success (userList);}}

Access test, I directly CURL request here.

➜~ curl localhost:8080/books {"code": "0000", "message": "success", "data": [{"id": 1, "author": "Jin Yong", "name": "The Smiling、Proud Wanderer", "price": 13, "createtime": "2020-12-19T07:26:51.000+00:00" "description": "martial arts novel"}, {"id": 2, "author": "Luo Guanzhong", "name": "Romance of the three Kingdoms", "price": 14, "createtime": "2020-12-19T07:28:36.000+00:00" "description": "Historical novel"}} ➜~ curl localhost:8080/users {"code": "0000", "message": "success", "data": [{"id": 1, "name": "Jin Yong", "birthday": "1924-03-09T16:00:00.000+00:00"} {"id": 2, "name": "Luo Guanzhong", "birthday": "1330-01-09T16:00:00.000+00:00"}]} ➜~

At this point, the configuration of most data sources is complete and the test is successful.

Connection pool

In fact, in the transformation of multiple data sources, we usually do not use the default JDBC connection method, and we often need to introduce connection pooling for connection optimization, otherwise you may often encounter error logs such as data source connections being disconnected. In fact, data source switching connection pool data source is also very simple, directly introduce connection pool dependency, and then replace the part of creating dataSource with connection pool data source creation.

Take Ali's Druid as an example, first introduce connection pool data source dependency.

Com.alibaba druid

Add some configurations for Druid.

Spring.datasource.datasource2.initialSize=3 # set spring.datasource.datasource2.minIdle=3 spring.datasource.datasource2.maxActive=20 according to your own situation

Rewrite the creation code part of dataSource Bean.

@ Value ("${spring.datasource.datasource2.jdbc-url}") private String url; @ Value ("${spring.datasource.datasource2.driver-class-name}") private String driverClassName; @ Value ("${spring.datasource.datasource2.username}") private String username; @ Value ("${spring.datasource.datasource2.password}") private String password; @ Value ("${spring.datasource.datasource2.initialSize}") private int initialSize; @ Value ("${spring.datasource.datasource2.minIdle}") private int minIdle @ Value ("${spring.datasource.datasource2.maxActive}") private int maxActive; @ Bean (name = "dataSource2") public DataSource dataSource () {DruidDataSource dataSource = new DruidDataSource (); dataSource.setUrl (url); dataSource.setDriverClassName (driverClassName); dataSource.setUsername (username); dataSource.setPassword (password); dataSource.setInitialSize (initialSize); dataSource.setMinIdle (minIdle); dataSource.setMaxActive (maxActive); return dataSource } the above is how to configure Mybatis multi-data sources in Spring boot. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.