所谓的多数据源,就是一个JavaEE项目中采用了不同数据库实例中的多个库,或者同一个数据源实例中多个不同的库。
一般来说,采用MyCat
等分布式数据库中间件
是比较好的解决方案,这样可以把数据库读写分离、分库分表、备份等操作交给中间件去做
,Java代码只需专注于业务即可。不过,这并不意味这无法使用Java代码解决类似的问题,在Spring Framework中就可以配置多数据源,SpringBoot继承其衣钵,只不过配置方式有所变化。
一、JdbcTemplate多数据源
JdbcTemplate多数据源的配置比较简单的,因为一个JdbcTemplate对应一个DataSource,因此只需要手动提供多个DataSource,在手动配置JdbcTemplate即可。
- 创建数据库
略 - 创建项目
创建SpringBootWeb项目,添加如下依赖:
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.6.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
</dependencies>
注意这里添加的数据库连接池依赖是druid-spring-boot-starter。druid-spring-boot-starter可以帮助开发者在SpringBoot项目中轻松集成Druid数据库连接池和监控。
3.配置数据库连接
在application.properties中配置数据库连接信息,代码如下:
spring.datasource.type = com.alibaba.druid.pool.DruidDataSource
spring.datasource.one.url=jdbc:mysql://localhost/book?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
spring.datasource.one.username=cms
spring.datasource.one.password=1
spring.datasource.two.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.two.url=jdbc:mysql://localhost/t_book?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
spring.datasource.two.username=cms
spring.datasource.two.password=1
- 配置数据源
创建DataSourceConfig配置数据源,根据application.properties中的配置生成两个数据源:
package com.more.config;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.mysql.cj.jdbc.MysqlDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
@Configuration
public class DataSourceConfig {
@Bean
@ConfigurationProperties("spring.datasource.one")
DataSource dsOne() {
return DruidDataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.two")
DataSource dsTwo() {
return DruidDataSourceBuilder.create().build();
}
}
代码解释:
- DataSourceConfig中提供了两个数据源:dsOne和dsTwo,默认方法名即实例名。
- @ConfigurationProperties注解表示使用不同前缀的配置文件来创建不同的DataSource实例。
- 配置JdbcTemplate
只要引入了spring-jdbc依赖,那么开发者没有提供JdbcTemplate实例时,SpringBoot默认会提供一个JdbcTemplate实例。现在配置多数据源时,由开发者自己提供JdbcTemplate实例,代码如下:
@Configuration
public class JdbcTemplateConfig {
@Bean
JdbcTemplate jdbcTemplateOne(@Qualifier("dsOne")DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
@Bean
JdbcTemplate jdbcTemplateTwo(@Qualifier("dsTwo")DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
代码解释:
- JdbcTemplateConfig中提供两个JdbcTemplate实例,每个JdbcTemplate实例都需要提供DataSource,由于Spring容器中有两个DataSource实例,因此需要通过方法名查找。@Qualifier注解表示查找不同名称的DataSource实例注入进来。
- 创建BookController
创建实体类Book和BookController
- Book
package com.more.vo;
import java.io.Serializable;
public class Book implements Serializable {
private Integer bkId;
private String bkname;
private String bktype;
private Integer bkNum;
private String bkCounty;
private String bkAuthor;
private String bkCbs;
public Integer getBkId() {
return bkId;
}
public void setBkId(Integer bkId) {
this.bkId = bkId;
}
public String getBkname() {
return bkname;
}
public void setBkname(String bkname) {
this.bkname = bkname;
}
public String getBktype() {
return bktype;
}
public void setBktype(String bktype) {
this.bktype = bktype;
}
public Integer getBkNum() {
return bkNum;
}
public void setBkNum(Integer bkNum) {
this.bkNum = bkNum;
}
public String getBkCounty() {
return bkCounty;
}
public void setBkCounty(String bkCounty) {
this.bkCounty = bkCounty;
}
public String getBkAuthor() {
return bkAuthor;
}
public void setBkAuthor(String bkAuthor) {
this.bkAuthor = bkAuthor;
}
public String getBkCbs() {
return bkCbs;
}
public void setBkCbs(String bkCbs) {
this.bkCbs = bkCbs;
}
@Override
public String toString() {
return "Book{" +
"bkId=" + bkId +
", bkname='" + bkname + '\'' +
", bktype='" + bktype + '\'' +
", bkNum=" + bkNum +
", bkCounty='" + bkCounty + '\'' +
", bkAuthor='" + bkAuthor + '\'' +
", bkCbs='" + bkCbs + '\'' +
'}';
}
}
- TBook
package com.more.vo;
import javax.persistence.Column;
import java.io.Serializable;
public class TBook implements Serializable {
private Integer id;
private String name;
private String author;
private Float price;
private String description;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public Float getPrice() {
return price;
}
public void setPrice(Float price) {
this.price = price;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
@Override
public String toString() {
return "TBook{" +
"id=" + id +
", name='" + name + '\'' +
", author='" + author + '\'' +
", price=" + price +
", description='" + description + '\'' +
'}';
}
}
- BookController
package com.more.controller;
import com.more.vo.Book;
import com.more.vo.TBook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.List;
@RestController
public class BookController {
@Resource(name = "jdbcTemplateOne")
JdbcTemplate jdbcTemplate;
@Autowired
@Qualifier("jdbcTemplateTwo")
@GetMapping("/test1")
public void test1() {
List<Book> books = jdbcTemplate.query("select * from book", new BeanPropertyRowMapper<>(Book.class));
System.out.println("books1:" + books);
List<TBook> tbooks = jdbcTemplate.query("select * from t_book", new BeanPropertyRowMapper<>(TBook.class));
System.out.println("books1:" + books);
System.out.println("tbooks1:" + tbooks);
}
}
在Controlle中注入JdbcTemplate有两种不同的方式:一种是使用@Resource注解,并指明name属性,即按name进行装配,此时会根据实例名查找相应的实例注入:另一种是使用@Autowired注解结合@Qualifier注解,效果等同于使用@Resource注解。
二、MyBatis多数据源
- 引入依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.6.3</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
</resource>
</resources>
</build>
<groupId>com.morejdbctemplate</groupId>
<artifactId>morejdbctemplate</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.6.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
</dependencies>
</project>
application.propertis 同上
DataSourceConfig同上
Book、TBook实体类同上
2.创建MyBatis配置
配置MyBatis,主要提供SqlSessionFactory实例和SqlSessionTemplate实例
package com.more.config;
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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
@Configuration
@MapperScan(value="com.more.dao",sqlSessionFactoryRef = "sqlSessionFactoryBean1")
public class MyBatisConfigOne {
@Autowired
@Qualifier("dsOne")
DataSource dsOne;
@Bean
SqlSessionFactory sqlSessionFactoryBean1() throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(dsOne);
return factoryBean.getObject();
}
@Bean
SqlSessionTemplate sqlSessionTemplate1() throws Exception{
return new SqlSessionTemplate(sqlSessionFactoryBean1());
}
}
- @MapperScan注解中指定dao接口所在的位置,同时指定SqlSessionFactory的实例名,则该位置下的dao将使用SqlSessionFactory实例。提供SqlSessionFactory实例,直接创建出来,同时将DataSource的实例设置给SqlSessionFactory,这里创建SqlSessionFactory实例也就是@MapperScan注解中sqlSessionFactoryRef参数指定的实例。
- 提供一个SqlSessionTemplate实例。这是一个线程安全类,主要用来管理MyBatis中的SqlSession操作。
package com.more.config;
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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
@Configuration
@MapperScan(value="com.more.dao",sqlSessionFactoryRef = "sqlSessionFactoryBean2")
public class MyBatisConfigTwo {
@Autowired
@Qualifier("dsTwo")
DataSource dsTwo;
@Bean
SqlSessionFactory sqlSessionFactoryBean2() throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(dsTwo);
return factoryBean.getObject();
}
@Bean
SqlSessionTemplate sqlSessionTemplate2() throws Exception{
return new SqlSessionTemplate(sqlSessionFactoryBean2());
}
}
- 创建Mapper
- bookMppaer.xml
<mapper namespace="com.more.dao.BookDao">
<select id="getAllBooks" resultType="com.more.vo.Book">
select * from book
</select>
</mapper>
- tbookMapper.xml
<mapper namespace="com.more.dao.tbookDao">
<select id="getAllTBooks" resultType="com.more.vo.TBook">
select * from t_book
</select>
</mapper>
- 创建Controller
@RestController
public class BookControllerMybatis {
@Autowired
BookDao bookDao;
@Autowired
tbookDao tbookDao;
@GetMapping("/test2")
public void test1() {
List<Book> books = bookDao.getAllBooks();
System.out.println("books1:" + books);
List<TBook> tbooks = tbookDao.getAllTBooks();
System.out.println("books1:" + books);
System.out.println("tbooks1:" + tbooks);
}
}