Recently in the project development, it is necessary to add a PLSQL database data source for a SpringBoot project using MySQL database, so it is necessary to carry out SpringBoot multi-data source development. The code is simple, and here’s how it works.
Environment to prepare
Experimental environment:
- JDK 1.8
- SpringBoot against 2.4.1
- Maven 3.6.3
- MySQL 5.7
Since I only have the MySQL database locally, for the sake of demonstration, I will start a local MySQL and create two databases in MySQL with one table in each library to demonstrate.
Data preparation
The default port number of the local MySQL database is 3306.
Create 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 'Shelf Time',
description varchar(128) null comment 'Book Description'
);
-- insert data
INSERT INTO demo1.Book (id, author, name, price, createTime, description) VALUES (1.'jin yong'.'Swordsman's Smile'.13.'the 2020-12-19 15:26:51'.'Wuxia Novels');
INSERT INTO demo1.Book (id, author, name, price, createTime, description) VALUES (2.'Luo Guanzhong'.Romance of The Three Kingdoms.14.'the 2020-12-19 15:28:36'.'Historical fiction');
Copy the codeCreate 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');
Copy the codeData is ready. Two new data items are added to the table.
Project preparation
A SpringBoot project with web, Lombok, Mybatis, and mysql dependencies is initialized directly from Spring.
Access to direct download: start. Spring. IO/starter.zip…
If you already have a SpringBoot project, and you want to convert to multiple data sources, you should already have one. If the new data source database matches the current one, you can test the transformation directly with your project.
Multiple data sources
SpringBoot multi-data source development is very simple, if multiple data sources are the same database, such as MySQL, then there is no need to change the dependency, just need to configure the multi-data source.
If your new database data source is different from your current database, be sure to introduce driver dependencies for the new database, such as MySQL and PGSQL.
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.7</version>
</dependency>
Copy the codeConnection configuration
Since there are multiple data sources, the database user name and password may be different, so you need to configure multiple data sources information, directly in the properties/ YML configuration. The distinction is based on the configured attribute name, and because the data source has a default data source, it is best to distinguish it by name (primary is used as the primary data source identifier here).
# # # # # # # # # # # # # # # # # # # # # # # # # # the main data source # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
spring.datasource.primary.jdbc-url=JDBC: mysql: / / 127.0.0.1:3306 / not? 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=
# # # # # # # # # # # # # # # # # # # # # # # # # # the 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
Copy the codeNote that jDBC-URL is used at the end of the data source connection URL in the configuration.
Because the Mybatis framework is used, so the configuration information of Mybatis framework is also necessary, specify the mapper XML configuration file under the scan 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, please refer to Mybatis official documentation or my previous article.
Link 1: Access the database using Mybatis (Automatic generation plug-in)
Link 2: Use Mybatis to integrate pageHelper and Mapper
Below I have written the corresponding Mybatis configuration according to the two tables in the above two libraries, Book and User respectively.
Create bookmapper. XML and usermapper. XML in the mapper directory specified in the configuration file. Create UserMapper and BookMapper interface action classes in different directories. Note here that Mapper interfaces are placed in separate directories by data source. Later, it is better to scan different directories using different data source configurations, so that different Mapper can be implemented using different data source configurations.
There is no change in the Service layer, where both BookMapper and UserMapper have a selectAll() method for query testing.
Multi-data Source Configuration
As you can see, the only difference between Mybatis and Mapper is that the Mapper interface is separated by a different directory, so this difference must be reflected in the data source configuration.
The main data source
Start configuring two data source information, to configure the main data sources, allocation of scanning MapperScan directory for the com. Wdbyte. Mapper. The primary
/** * Primary data source configuration **@author niujinpeng
* @website: https://www.wdbyte.com
* @date2020/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(a) {
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 newSqlSessionTemplate(sqlSessionFactory); }}Copy the codeInstead of a single data source, this is where you put
dataSourcesqlSessionFactorytransactionManagersqlSessionTemplate
Are individually configured, simple bean creation, and the following are some of the annotations used.
@ConfigurationProperties(prefix = "spring.datasource.primary"): use the spring. The datasource. Primary opening configuration.@Primary: Declares that this is a master data source (the default data source) when configuring multiple data sourcesessential.@Qualifier: Explicitly selects the incoming Bean.
Second data source
The only difference between the second data source and the Primary data source is that MapperScan scans the path and creates the Bean name, and there is no annotation for the @primary Primary data source.
/** * Second data source configuration **@author niujinpeng
* @website: https://www.wdbyte.com
* @date2020/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(a) {
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 newSqlSessionTemplate(sqlSessionFactory); }}Copy the codeNote: Since the scanned Mapper path is already configured in both data sources, you need to delete the Mapper scan annotation if you have previously used it in the SpringBoot boot class.
Access to the test
Write two simple query controllers and then perform access tests.
// BookController
@RestController
public class BookController {
@Autowired
private BookService bookService;
@GetMapping(value = "/books")
public Response selectAll(a) throws Exception {
List<Book> books = bookService.selectAll();
returnResponseUtill.success(books); }}// UserController
@RestController
public class UserController {
@Autowired
private UserService userService;
@ResponseBody
@GetMapping(value = "/users")
public Response selectAll(a) {
List<User> userList = userService.selectAll();
returnResponseUtill.success(userList); }}Copy the codeTo access the test, I’m going to CURL the request directly.
➜ ~ curl localhost: 8080 / books {" code ":" 0000 ", "message" : "success", "data" : [{" id ": 1," author ":" jin yong ", "name" : 13, "thrive", "price" : "createtime" : "the 2020-12-19 T07: smote. 000 + 00:00", "description" : "kung fu novel"}, {" id ": 2," author ": "Price ": "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", "the 1924-03-09 T16:00:00) 000 + 00:00"}, {" id ": 2," name ":" guan ", "birthday" : "1330-01-09T16:00:00.000+ 00:00.000 "}]} ➜ ~Copy the codeAt this point, the multi-data source configuration is complete and the test is successful.
The connection pool
In the case of multi-data source transformation, we usually do not use the default JDBC connection method, and often need to introduce connection pooling for connection optimization, otherwise you may often encounter data source connection disconnection and other error logs. It is easy to switch the connection pool dataSource from the dataSource to the connection pool dataSource by importing the connection pool dependency.
Using Druid as an example, we introduce the connection pool data source dependency.
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
</dependency>
Copy the codeAdd some configuration for Druid.
spring.datasource.datasource2.initialSize=3 # Customize your Settings
spring.datasource.datasource2.minIdle=3
spring.datasource.datasource2.maxActive=20
Copy the codeRewrite the dataSource Bean creation code.
@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(a) {
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;
}
Copy the codeTo briefly mention the importance of using connection pooling, see Druid’s documentation for more details.
The code has been uploaded to Github: github.com/niumoo/spri…
The last word
The article is available at github.com/niumoo/java…
Article helpful can point a “like” or “share”, are support! This post is updated weekly. You can follow the unread code account or my blog.