spring boot mybatis配置主从数据库（多数据源）

香草物语博客 2021-05-28

2449

MyBatis
配置多数据源基本步骤：

1.预制两个测试的数据库master
和cluster
2.添加mybatis
及druid
依赖3.配置文件配置两个数据源（配置数据源时，必须要有一个主数据源）4.测试代码

预制数据库表

预制两个数据库，主库为master
，并创建表test
，从库为cluster
，并创建表user
。

主库

从库创建表语句：

CREATE TABLE `test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4

预制数据

INSERT INTO cluster.user (id, username) VALUES (1, '李四');

从库

从库创建表语句

CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4

预制语句

INSERT INTO cluster.user (id, username) VALUES (1, '李四');

添加项目依赖

创建spring boot
项目，并添加如下依赖

<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>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.4</version>
</dependency>

添加配置数据库连接

在application.properties
中添加两个数据库的配置文件

#主库配置
master.datasource.url=jdbc:mysql://139.198.172.114:3306/master?useSSL=false&characterEncoding=utf8
master.datasource.username=master
master.datasource.password=master
master.datasource.driver=com.mysql.jdbc.Driver
#从库配置
cluster.datasource.url=jdbc:mysql://139.198.172.114:3306/cluster?useSSL=false&characterEncoding=utf8
cluster.datasource.username=cluster
cluster.datasource.password=cluster
cluster.datasource.driver=com.mysql.jdbc.Driver

添加数据源

主库

添加主库数据源MasterDataSourceConfig.java

package net.xiangcaowuyu.mybatsmultidatasource.config.ds;

import com.alibaba.druid.pool.DruidDataSource; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.SqlSessionTemplate; import org.mybatis.spring.annotation.MapperScan; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.beans.factory.annotation.Value; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**

•主库配置••@author laughing @2021.5.27•/ @Configuration @MapperScan(basePackages = MasterDataSourceConfig.PACKAGE,sqlSessionFactoryRef = "masterSqlSessionFactory") public class MasterDataSourceConfig { final static String PACKAGE = "net.xiangcaowuyu.mybatsmultidatasource.dao.master"; private final static String mapperLocation = "classpath:mapper/**/.xml"; @Value("${master.datasource.url}") private String url; @Value("${master.datasource.username}") private String username; @Value("${master.datasource.password}") private String password; @Value("${master.datasource.driver}") private String driver; @Bean(name = "masterDataSource") @Primary public DataSource masterDataSource() {

DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setDriverClassName(driver);
return dataSource;

} @Bean(name = "masterDataSourceTransactionManager") @Primary public DataSourceTransactionManager masterDataSourceTransactionManager() {

return new DataSourceTransactionManager(masterDataSource());

} @Bean(name = "masterSqlSessionFactory") @Primary public SqlSessionFactory masterSqlSessionFactory(@Qualifier(value = "masterDataSource") DataSource dataSource) throws Exception {

SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);

  sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MasterDataSourceConfig.mapperLocation));

return sqlSessionFactoryBean.getObject();

} }

## 从库
添加从库数据源`ClusterDataSourceConfig.java`
```java
package net.xiangcaowuyu.mybatsmultidatasource.config.ds;


import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;


import javax.sql.DataSource;


/**
 * 从库配置
 *
 * @author laughing @2021.5.27
 */
@Configuration
@MapperScan(basePackages = ClusterDataSourceConfig.PACKAGE,sqlSessionFactoryRef = "clusterSqlSessionFactory")
public class ClusterDataSourceConfig {


    final static String PACKAGE = "net.xiangcaowuyu.mybatsmultidatasource.dao.cluster";
    private final static String mapperLocation = "classpath*:mapper/**/*.xml";


    @Value("${cluster.datasource.url}")
    private String url;


    @Value("${cluster.datasource.username}")
    private String username;


    @Value("${cluster.datasource.password}")
    private String password;


    @Value("${cluster.datasource.driver}")
    private String driver;


    @Bean(name = "clusterDataSource")
    @Primary
    public DataSource clusterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setDriverClassName(driver);
        return dataSource;
    }


    @Bean(name = "clusterDataSourceTransactionManager")
    @Primary
    public DataSourceTransactionManager clusterDataSourceTransactionManager() {
        return new DataSourceTransactionManager(clusterDataSource());
    }




    @Bean(name = "clusterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier(value = "clusterDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(ClusterDataSourceConfig.mapperLocation));
        return sqlSessionFactoryBean.getObject();
    }


}

主库、从库的数据源通过@MapperScan
注解注入不同数据库mapper
所在的包。

创建DAO

主库

主库的包位于net.xiangcaowuyu.mybatsmultidatasource.dao.master
与@MapperScan
配置的位置要保持一致。主库的包里面创建一个selectAll()
方法，用于查询所有数据，及查询主库Test
表的所有数据。

从库

从库的包位于net.xiangcaowuyu.mybatsmultidatasource.dao.cluster
与@MapperScan
配置的位置要保持一致。从库的包里面创建一个selectAll()
方法，用于查询所有数据，及查询主库User
表的所有数据。

创建Service

代码就不罗列了，也是有一个selectAll()
方法。

测试

创建controller
分别查询主库及从库的数据，如下

@RestController
public class UserController {


    @Resource
    private ITestService testService;


    @Resource
    private IUserService userService;


    @GetMapping("/master")
    public List<Test> getMasterAll(){
        return testService.selectAll();
    }


    @GetMapping("/cluster")
    public List<User> getClusterAll(){
        return userService.selectAll();
    }


}

访问主库

访问从库

可以看到，系统正确的访问了主库及从库的数据。

代码下载

可以通过https://gitee.com/lisen0629/lisen_org/tree/master/mybats-multi-datasource下载代码。

数据库

文章转载自香草物语博客，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。