SpringBoot多数据源+Mybatis配置实现

最新推荐文章于 2024-01-22 14:20:04 发布

wlccomeon

最新推荐文章于 2024-01-22 14:20:04 发布

阅读量1.2w

点赞数 13

分类专栏： SpringBoot

本文链接：https://blog.csdn.net/wlccomeon/article/details/104728489

版权

SpringBoot 专栏收录该内容

0 篇文章 1 订阅

订阅专栏

文章目录

目录结构

由于在springboot项目当中需要同时操作不止一个数据库，需要用到多个数据源。在本例当中以first和second来区分两个数据源。同时为了书写方便，在Controller当中直接调用的Mapper接口。最终结果整合和测试，通过了获取不同数据源中的数据。示例代码的目录结构如下：
在这里插入图片描述

sql脚本

分别创建两个数据库，每个数据库创建一张表，然后插入几条数据，如下：

CREATE DATABASE `test1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;  ;
USE `test1`;
CREATE TABLE `student`(
  `id` VARCHAR(12) PRIMARY KEY,
  `name` VARCHAR(36) NOT NULL ,
  `age` INTEGER  DEFAULT 0
);

-- 插入数据
INSERT INTO test1.student (id, name, age) VALUES ('1', '张梦为', 1);
INSERT INTO test1.student (id, name, age) VALUES ('2', '上官婉儿', 2);
INSERT INTO test1.student (id, name, age) VALUES ('3', '唐因', 2);

CREATE DATABASE `test2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `test2`;
CREATE TABLE `lesson`(
  `id` VARCHAR(12) PRIMARY KEY,
  `name` VARCHAR(36) NOT NULL ,
  `credit` FLOAT  DEFAULT 0,
  `teacher` VARCHAR(36) DEFAULT 0
);
-- 插入数据
INSERT INTO test2.lesson (id, name, credit, teacher) VALUES ('1', '大学物理', 5, '张思瑞');
INSERT INTO test2.lesson (id, name, credit, teacher) VALUES ('2', '高等数学', 5, '李佛');

pom引用jar包

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-devtools</artifactId>
    <scope>runtime</scope>
    <optional>true</optional>
</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>

application.yml配置数据源

spring:
  datasource:
    first:
      url: jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
      username: root
      password: 123456
      driverClassName: com.mysql.jdbc.Driver
    second:
      url: jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
      username: root
      password: 123456
      driverClassName: com.mysql.jdbc.Driver
server:
  port: 2020

注意，这个里面相当于自定义了一套数据源的配置，不需要使用yml的spring datasource提示。同时，需要将springboot自动配置数据源的功能给去掉：

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})

这个时候，在yml文件当中就不能像单个数据源的时那样配置mapper的位置了：

mybatis:
  mapper-locations: classpath:mapper/*.xml

上面的配置已经没用了，需要在为DataSource创建SessionFactory的时候直接指定mapper地址，否则会出现“Invalid bound statement (not found)”的问题。

数据源配置

第1个数据源

@Configuration
@MapperScan(basePackages = "com.lc.multidb.dao.first",sqlSessionTemplateRef ="firstSqlSessionTemplate")
public class FirstDataSourceConfig {
	@Value("${spring.datasource.first.url}")
	private String url;
	@Value("${spring.datasource.first.username}")
	private String username;
	@Value("${spring.datasource.first.password}")
	private String password;
	@Value("${spring.datasource.first.driverClassName}")
	private String driverClassName;
	/**本数据源扫描的mapper路径*/
	static final String MAPPER_LOCATION = "classpath:mapper/first/*.xml";


	/**创建数据源*/
	@Bean(name = "firstDS")
	@Primary
	public DataSource getFirstDataSource() {
		DataSource build =  DataSourceBuilder.create()
				.driverClassName(driverClassName)
				.url(url)
				.username(username)
				.password(password)
				.build();
		return build;
	}


	/**创建SessionFactory*/
	@Bean(name = "firstSqlSessionFactory")
	@Primary
	public SqlSessionFactory firstSqlSessionFactory(@Qualifier("firstDS") DataSource dataSource) throws Exception {
		SqlSessionFactoryBean  bean = new SqlSessionFactoryBean();
		bean.setDataSource(dataSource);
		//设置mapper配置文件
		bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
		return bean.getObject();
	}

	/**创建事务管理器*/
	@Bean("firstTransactionManger")
	@Primary
	public DataSourceTransactionManager firstTransactionManger(@Qualifier("firstDS") DataSource dataSource){
		return new DataSourceTransactionManager(dataSource);
	}

	/**创建SqlSessionTemplate*/
	@Bean(name = "firstSqlSessionTemplate")
	@Primary
	public SqlSessionTemplate firstSqlSessionTemplate(@Qualifier("firstSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
		return new SqlSessionTemplate(sqlSessionFactory);
	}
}

第2个数据源：

@Configuration
@MapperScan(basePackages = "com.lc.multidb.dao.second",sqlSessionTemplateRef ="secondSqlSessionTemplate")
public class SecondDataSourceConfig  {
	@Value("${spring.datasource.second.url}")
	private String url;
	@Value("${spring.datasource.second.username}")
	private String username;
	@Value("${spring.datasource.second.password}")
	private String password;
	@Value("${spring.datasource.second.driverClassName}")
	private String driverClassName;
	/**本数据源扫描的mapper路径*/
	static final String MAPPER_LOCATION = "classpath:mapper/second/*.xml";

	/**创建数据源*/
	@Bean(name = "secondDS")
	public DataSource getSecondDataSource() {
		DataSource build =  DataSourceBuilder.create()
				.driverClassName(driverClassName)
				.url(url)
				.username(username)
				.password(password)
				.build();
		return build;
	}


	/**创建SessionFactory*/
	@Bean(name = "secondSqlSessionFactory")
	public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDS") DataSource dataSource) throws Exception {
		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
		bean.setDataSource(dataSource);
		//设置mapper配置文件
		bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
		return bean.getObject();
	}

	/**创建事务管理器*/
	@Bean("secondTransactionManger")
	public DataSourceTransactionManager secondTransactionManger(@Qualifier("secondDS") DataSource dataSource){
		return new DataSourceTransactionManager(dataSource);
	}

	/**创建SqlSessionTemplate*/
	@Bean(name = "secondSqlSessionTemplate")
	public SqlSessionTemplate secondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
		return new SqlSessionTemplate(sqlSessionFactory);
	}
}

Mapper接口

public interface StudentMapper {
	List<Student> queryStudents();
}

public interface LessonMapper {
	List<Lesson> findById(Integer id);
}

Mapper配置文件

<?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.lc.multidb.dao.first.StudentMapper">
    <resultMap id="studentMap" type="com.lc.multidb.po.Student">
        <result column="id" property="id" />
        <result column="name" property="id" />
        <result column="age" property="age" />
    </resultMap>
    <select id="queryStudents" resultMap="studentMap">
        SELECT * FROM student
    </select>
</mapper>

<?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.lc.multidb.dao.second.LessonMapper">
    <resultMap id="lessonMap" type="com.lc.multidb.po.Lesson">
        <result column="id" property="id" />
        <result column="name" property="name" />
        <result column="credit" property="credit" />
        <result column="teacher" property="teacher" />
    </resultMap>
    <select id="findById" parameterType="int" resultMap="lessonMap">
        SELECT * FROM lesson WHERE id=#{id}
    </select>
</mapper>

PO

@Data
@Accessors(chain = true)
public class Student implements Serializable {
	private String id;
	private  String name;
	private String age;
}

@Data
@Accessors(chain = true)
public class Lesson implements Serializable {
	private String id;
	private String name;
	private String teacher;
	private  float credit;
}

测试

此处省略了Service层：

@RestController
@RequestMapping("/multidb")
public class MultiDbController {

	@Autowired
	private LessonMapper lessonMapper;
	@Autowired
	private StudentMapper studentMapper;

	@GetMapping("/getLesson")
	public List<Lesson> getLessons(){
		return lessonMapper.findById(1);
	}

	@GetMapping("/getStudents")
	public List<Student> getStudents(){
		return studentMapper.queryStudents();
	}
}

浏览器当中直接访问Controller地址，即可拿到不同数据源当中的数据。

wlccomeon

关注

13
点赞
踩
64

收藏

觉得还不错? 一键收藏
13
评论
SpringBoot多数据源+Mybatis配置实现

文章目录目录结构sql脚本pom引用jar包application.yml配置数据源数据源配置Mapper接口Mapper配置文件PO测试目录结构由于在springboot项目当中需要同时操作不止一个数据库，需要用到多个数据源。在本例当中以first和second来区分两个数据源。同时为了书写方便，在Controller当中直接调用的Mapper接口。最终结果整合和测试，通过了获取不同数据源中...
复制链接

扫一扫

专栏目录