1 Main dependency version
(1) SpringBoot 2.7.8
(2) Mybatis 2.2.2
(3)Pagehelper 1.3.0
(4)MySQL 8.0.26
(5)Oracle 11.2.0.3
2 Overview
(1) Two data sources are used here, namely MySQL and Oracle, and two databases are configured;
(2) Configure Mybatis and Pagehelper respectively for the above data sources (to implement paging);
(3) The data source, Mybatis, and paging are all custom configured, and their automatic configuration is turned off in the startup class.
3 SpringBoot integrates Mybatis to realize dynamic switching of multiple data sources
3.1 Integration and Configuration
3.1.1 Related dependencies pom.xml
<!-- mybatis-spring-boot-starter -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.2</version>
</dependency>
<!-- pagehelper-spring-boot-starter -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.3.0</version>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
<scope>runtime</scope>
</dependency>
<!-- oracle -->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.3</version>
</dependency>
<!--hutool -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.22</version>
</dependency>
<!-- yml file custom configuration, input prompts -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
3.1.2 application.yml configuration
Two data sources, db1 and db2, and paging are configured in the yml configuration. db1 corresponds to MySQL and db2 corresponds to Oracle. If paging is not required, the configuration under spring.pagehelper can be omitted.
spring:
datasource:
db1:
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/studb?characterEncoding=UTF-8 & amp;useUnicode=true
username: root
password: root
db2:
driver-class-name: oracle.jdbc.driver.OracleDriver
jdbc-url: jdbc:oracle:thin:@//localhost:1521/XE
username: root
password: root
#Paging configuration
pagehelper:
db1:
dialect: mysql
offsetAsPageNum: true
rowBoundsWithCount: true
reasonable: true
supportMethodsArguments: true
params: count=countSql;pageNum=pageNum;pageSize=pageSize;
db2:
dialect: oracle
offsetAsPageNum: true
rowBoundsWithCount: true
reasonable: true
supportMethodsArguments: true
params: count=countSql;pageNum=pageNum;pageSize=pageSize;
3.1.3 Set the paging configuration in yml to the corresponding Bean
The @ConfigurationProperties annotation is used here to automatically fill the data with the specified prefix in the yml configuration file into the Bean.
(1) Paging attribute Bean of db1 data source
@Data
@Component
@ConfigurationProperties(prefix = "spring.pagehelper.db1")
public class PrimaryMybatisProperties {
private String dialect;
private String offsetAsPageNum;
private String rowBoundsWithCount;
private String reasonable;
private String supportMethodsArguments;
private String params;
}
(2) Paging attribute Bean of db2 data source
@Data
@Component
@ConfigurationProperties(prefix = "spring.pagehelper.db2")
public class SecondMybatisProperties {
private String dialect;
private String offsetAsPageNum;
private String rowBoundsWithCount;
private String reasonable;
private String supportMethodsArguments;
private String params;
}
3.1.4 Data source configuration
(1) Data source configuration of db1 PrimaryDataSourceConfig.java
Note: In the case of multiple data sources, the @Primary annotation is used here to specify that db1 is used by default.
@Configuration
@MapperScan(basePackages = "com.wen.mapper1", sqlSessionFactoryRef = "sqlSessionFactory1")
public class PrimaryDataSourceConfig {
@Resource
private PrimaryMybatisProperties primaryMybatisProperties;
@Bean(name = "dataSource1")
@Primary
@ConfigurationProperties(prefix = "spring.datasource.db1")
public DataSource dataSource1(){
return DataSourceBuilder.create().build();
}
@Bean(name = "sqlSessionFactory1")
@Primary
public SqlSessionFactory sqlSessionFactory1(@Qualifier("dataSource1") DataSource dataSource1) throws Exception{
SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
sessionFactoryBean.setDataSource(dataSource1);
String locationPattern = "classpath*:mapper1/**/*.xml";
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
sessionFactoryBean.setMapperLocations(resolver.getResources(locationPattern));
// mybatis
org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
configuration.setMapUnderscoreToCamelCase(true);
configuration.setLogImpl(StdOutImpl.class);
sessionFactoryBean.setConfiguration(configuration);
// paging
Interceptor interceptor = new PageInterceptor();
Properties properties = new Properties();
properties.setProperty("helperDialect", primaryMybatisProperties.getDialect());
properties.setProperty("offsetAsPageNum", primaryMybatisProperties.getOffsetAsPageNum());
properties.setProperty("rowBoundsWithCount", primaryMybatisProperties.getRowBoundsWithCount());
properties.setProperty("reasonable", primaryMybatisProperties.getReasonable());
properties.setProperty("supportMethodsArguments",primaryMybatisProperties.getSupportMethodsArguments());
properties.setProperty("params",primaryMybatisProperties.getParams());
interceptor.setProperties(properties);
sessionFactoryBean.setPlugins(interceptor);
return sessionFactoryBean.getObject();
}
@Bean(name = "sqlSessionTemplate1")
@Primary
public SqlSessionTemplate sqlSessionTemplate1(@Qualifier("sqlSessionFactory1") SqlSessionFactory sqlSessionFactory1) {
return new SqlSessionTemplate(sqlSessionFactory1);
}
@Bean(name = "dataSourceTransactionManager1")
@Primary
public DataSourceTransactionManager dataSourceTransactionManager1(@Qualifier("dataSource1") DataSource dataSource1){
return new DataSourceTransactionManager(dataSource1);
}
}
(2) db2 data source configuration SecondDataSourceConfig.java
@Configuration
@MapperScan(basePackages = "com.wen.mapper2",sqlSessionFactoryRef = "sqlSessionFactory2")
public class SecondDataSourceConfig {
@Resource
private SecondMybatisProperties secondMybatisProperties;
@Bean(name = "dataSource2")
@Primary
@ConfigurationProperties(prefix = "spring.datasource.db2")
public DataSource dataSource2(){
return DataSourceBuilder.create().build();
}
@Bean(name = "sqlSessionFactory2")
@Primary
public SqlSessionFactory sqlSessionFactory1(@Qualifier("dataSource2") DataSource dataSource2) throws Exception{
SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
sessionFactoryBean.setDataSource(dataSource2);
String locationPattern = "classpath*:mapper2/**/*.xml";
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
sessionFactoryBean.setMapperLocations(resolver.getResources(locationPattern));
// mybatis underline to camel case
org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
configuration.setMapUnderscoreToCamelCase(true);
configuration.setLogImpl(StdOutImpl.class);
sessionFactoryBean.setConfiguration(configuration);
//Paging configuration
Interceptor interceptor = new PageInterceptor();
Properties properties = new Properties();
properties.setProperty("helperDialect", secondMybatisProperties.getDialect());
properties.setProperty("offsetAsPageNum", secondMybatisProperties.getOffsetAsPageNum());
properties.setProperty("rowBoundsWithCount", secondMybatisProperties.getRowBoundsWithCount());
properties.setProperty("reasonable", secondMybatisProperties.getReasonable());
properties.setProperty("supportMethodsArguments",secondMybatisProperties.getSupportMethodsArguments());
properties.setProperty("params",secondMybatisProperties.getParams());
interceptor.setProperties(properties);
sessionFactoryBean.setPlugins(interceptor);
return sessionFactoryBean.getObject();
}
@Bean(name = "sqlSessionTemplate2")
@Primary
public SqlSessionTemplate sqlSessionTemplate1(@Qualifier("sqlSessionFactory2") SqlSessionFactory sqlSessionFactory2) {
return new SqlSessionTemplate(sqlSessionFactory2);
}
@Bean(name = "dataSourceTransactionManager2")
@Primary
public DataSourceTransactionManager dataSourceTransactionManager1(@Qualifier("dataSource2") DataSource dataSource2){
return new DataSourceTransactionManager(dataSource2);
}
}
Note: If the @Transactional transaction annotation is used in the project, the transaction management of the data source must be configured separately, which has been configured in the above code.
3.1.5 Startup class exclusion related automatic configuration
The startup class excludes the automatic configuration of data source (DataSourceAutoConfiguration.class), Mybatis (MybatisAutoConfiguration.class), and paging (PageHelperAutoConfiguration.class).
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class, MybatisAutoConfiguration.class, PageHelperAutoConfiguration.class})
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
3.2 Sample code
3.2.1 po
src/main/java/com/wen/po
@Data
public class Student {
privateInteger id;
private String name;
private Integer age;
private String gender;
private String address;
private Date birth;
}
@Data
public class User {
private String id;
private String userName;
}
3.2.2 controller layer
src/main/java/com/wen/controller
@RestController
@RequestMapping("/student")
public class StudentController {
@Resource
private StudentServiceI studentService;
@GetMapping
public PageInfo<Student> queryByPage(int pageNum, int pageSize, Student student){
return studentService.queryByPage(pageNum,pageSize,student);
}
}
@RestController
@RequestMapping("/user")
public class UserController {
@Resource
private UserServiceI userService;
@GetMapping
public PageInfo<User> queryByPage(int pageNum, int pageSize, User user){
return userService.queryByPage(pageNum,pageSize,user);
}
}
3.2.3 service layer
src/main/java/com/wen/service
public interface StudentServiceI {
PageInfo<Student> queryByPage(int pageNum, int pageSize, Student student);
}
@Service
public class StudentServiceImpl implements StudentServiceI {
@Resource
private StudentMapper studentMapper;
@Override
public PageInfo<Student> queryByPage(int pageNum, int pageSize, Student student) {
if (pageNum <= 0) {
pageNum = 1;
}
if (pageSize <= 0) {
pageSize = 10;
}
if(ObjectUtil.isEmpty(student)){
student = new Student();
}
PageMethod.startPage(pageNum,pageSize);
List<Student> students = studentMapper.selectByPage(student);
PageInfo<Student> pageInfo = new PageInfo<>(students);
return pageInfo;
}
}
public interface UserServiceI {
PageInfo<User> queryByPage(int pageNum, int pageSize, User user);
}
@Service
public class UserServiceImpl implements UserServiceI {
@Resource
private UserMapper userMapper;
@Override
public PageInfo<User> queryByPage(int pageNum, int pageSize, User user) {
if (pageNum <= 0) {
pageNum = 1;
}
if (pageSize <= 0) {
pageSize = 10;
}
if(ObjectUtil.isEmpty(user)){
user = new User();
}
PageMethod.startPage(pageNum,pageSize);
List<User> users = userMapper.selectByPage(user);
PageInfo<User> pageInfo = new PageInfo<>(users);
return pageInfo;
}
}
3.2.4 mapper layer
Pay attention to the mappers corresponding to different data sources. The attribute basePackages = “com.wen.mapper2” corresponding to @MapperScan in the data source configuration class specifies the mapper corresponding to the data source.
(1) Mapper layer corresponding to db1 data source
src/main/java/com/wen/mapper1/StudentMapper.java
@Mapper
public interface StudentMapper {
List<Student> selectByPage(@Param("student") Student student);
}
src/main/resources/mapper1/StudentMapper.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.wen.mapper1.StudentMapper">
<sql id="student">
id,name,age,gender,address,birth
</sql>
<select id="selectByPage" resultType="com.wen.po.Student">
select
<include refid="student"/>
from student
<where>
<if test="student.id!=null">
and id = #{student.id}
</if>
<if test="student.name!=null and student.name!=''">
<bind name="stuName" value="'%' + student.name + '%'"/>
and name like #{stuName}
</if>
</where>
</select>
</mapper>
(2) Mapper layer corresponding to db2 data source
src/main/java/com/wen/mapper2/UserMapper.java
@Mapper
public interface UserMapper {
List<User> selectByPage(@Param("user") User user);
}
src/main/resources/mapper2/UserMapper.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.wen.mapper2.UserMapper">
<sql id="user">
id,user_name
</sql>
<select id="selectByPage" resultType="com.wen.po.User">
select
<include refid="user"/>
from sys_user
<where>
<if test="user.id!=null">
and id = #{student.id}
</if>
<if test="user.userName!=null and user.userName!=''">
<bind name="userName" value="'%' + user.userName + '%'"/>
and user_name like #{userName}
</if>
</where>
</select>
</mapper>
3.3 Test
Use Postman to test as follows:
(1) Use db1 data source
(2) Use db2 data source
The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. Java Skill TreeHomepageOverview 137733 people are learning the system