前言:
本文使用application或yml+配置类的方式,进行多数据源配置,与传统的xml方式相比,原理一致,但该方式更简便,上手难度低,避免的繁琐的xml配置,企业级开发一般使用该方式
以MySQL和Oracle两个数据库为例
1、准备工作
1.1、项目中使用的基本清单
- springboot 2.3.7.RELEASE
- MySQL 5.7.38
- Oracle 19
- mybatis-plus
1.2、两种数据库分别新建两个测试表
建表如下:
1.2.1、MySQL建表及初始化数据语句
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `user` VALUES (1, '张三', 18);
INSERT INTO `user` VALUES (2, '李四', 19);
INSERT INTO `user` VALUES (3, '王五', 20);
INSERT INTO `user` VALUES (4, '赵六', 19);
INSERT INTO `user` VALUES (5, '老七', 17);
INSERT INTO `user` VALUES (6, '芈八子', 18);
INSERT INTO `user` VALUES (7, '久久', 99);
1.2.2、Oracle建表及初始化数据语句
--Oracle测试表
CREATE TABLE city (
id number(10) not null,
name varchar2(40),
parent varchar2(40)
);
insert into city(id,name,parent) values(1,'广州','广东');
insert into city(id,name,parent) values(1,'深圳','广东');
insert into city(id,name,parent) values(1,'厦门','福建');
insert into city(id,name,parent) values(1,'长沙','湖南');
commit;
1.3、数据库基本信息如下
数据库类型 | 数据库名称 | 账号 | 密码 |
MySQL | demo | root | root |
Oracle | demo2 | root | 123456 |
2、搭建springboot项目
这里我就略过了,我使用的版本为2.3.7.RELEASE
这里附上项目所需的maven依赖,如下(注意Druid与mybatis的版本):
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.0</version>
</dependency>
<!-- mybatis版本必须与druid版本兼容,否则无法创建DataSource -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.21</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.72</version>
</dependency>
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>2.8.5</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.1</version>
</dependency>
<!-- MySQL驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.48</version>
<scope>runtime</scope>
</dependency>
<!-- Oracle驱动 -->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.4.0</version>
<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>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
3、配置开始
3.1、配置两个数据源的链接信息
在resource目录下,新建application.yml文件,并配置两个数据源的链接信息:
如下:
spring:
datasource:
#使用Druid数据源连接池
type: com.alibaba.druid.pool.DruidDataSource
#数据源01的连接信息
db1:
#存在多个 url时,必须使用jdbc-url来定义,否则连接不到数据库
jdbc-url: jdbc:mysql://127.0.0.1:3306/demo
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
#数据源02的连接信息
db2:
jdbc-url: jdbc:oracle:thin:@127.0.0.1:1521:salescdh
username: root
password: 123456
driver-class-name: oracle.jdbc.OracleDriver
3.2、生成配置类
在与controller同层中,新建一个包,存放两个数据源对应的配置类
如下:
内容如下:
3.2.1、DataSource01配置类:
package com.shuizhu.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 javax.sql.DataSource;
/**
* 数据源01的配置类
*
* @author 睡竹
* @date 2022/9/19
*/
@Configuration
@MapperScan(basePackages = "com.shuizhu.dao.db1", sqlSessionFactoryRef = "db1SqlSessionFactory")
public class DataSource01 {
@Primary
@Bean(name = "db1DataSource")
@ConfigurationProperties("spring.datasource.db1")
public DataSource db1DataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "db1SqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
//bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResource("classpath:mapper/db1/Demo.xml"));
PathMatchingResourcePatternResolver resource = new PathMatchingResourcePatternResolver();
bean.setMapperLocations(resource.getResources("classpath:mapper/db1/*.xml"));
return bean.getObject();
}
}
3.2.2、DataSource02配置类:
package com.shuizhu.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 javax.sql.DataSource;
/**
* 数据源02的配置类
*
* @author 睡竹
* @date 2022/9/19
*/
@Configuration
@MapperScan(basePackages = "com.shuizhu.dao.db2", sqlSessionFactoryRef = "db2SqlSessionFactory")
public class DataSource02 {
@Primary
@Bean(name = "db2DataSource")
@ConfigurationProperties("spring.datasource.db2")
public DataSource db2DataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "db2SqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/db2/*.xml"));
return bean.getObject();
}
}
3.3、生成xml映射文件
如图所示:
db1文件夹表示数据源1的映射xml文件存放地;
db2文件夹表示数据源2的映射xml文件存放地。
分别在文件夹下新建xml映射文件:
手写测试的SQL代码:
Demo.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.shuizhu.dao.db1.DemoDao">
<select id="getUsers" resultType="com.shuizhu.domain.User">
select id,name,age from user
</select>
</mapper>
Demo2.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.shuizhu.dao.db2.DemoOracleDao">
<select id="getCitys" resultType="com.shuizhu.domain.City">
select id,name,parent from city
</select>
</mapper>
3.4、生成DAO接口层代码
如图所示:
测试代码如下:
DemoDao代码:
package com.shuizhu.dao.db1;
import com.shuizhu.domain.User;
import org.springframework.stereotype.Repository;
import java.util.List;
/**
* MySQL数据源的dao
* @author 睡竹
* @date 2022/9/15
*/
@Repository
public interface DemoDao {
//@Select("select id,name,age from user")
List<User> getUsers();
}
DemoOracleDao代码:
package com.shuizhu.dao.db2;
import com.shuizhu.domain.City;
import org.springframework.stereotype.Repository;
import java.util.List;
/**
* Oracle数据源的dao
* @author 睡竹
* @date 2022/9/15
*/
@Repository
public interface DemoOracleDao {
List<City> getCitys();
}
3.5、在启动类上,添加注解属性
如图所示:
4、测试
4.1、Controller层:
package com.shuizhu.controller;
import com.shuizhu.domain.City;
import com.shuizhu.domain.User;
import com.shuizhu.service.IDemoService;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.List;
/**
* @author 睡竹
* @date 2022/9/15
*/
@RestController
public class DemoController {
@Resource
private IDemoService service;
/**
* 测试MySQL数据源是否生效
* @return
*/
@RequestMapping("testMySQL")
public Object testMySQL(){
List<User> users = service.getUsers();
return users;
}
/**
* 测试Oracle数据源是否生效
* @return
*/
@RequestMapping("testOracle")
public Object testOracle(){
List<City> users = service.getCitys();
return users;
}
}
4.2、Service接口层:
package com.shuizhu.service;
import com.shuizhu.domain.City;
import com.shuizhu.domain.User;
import java.util.List;
/**
* @author 睡竹
* @date 2022/9/15
*/
public interface IDemoService {
List<User> getUsers();
List<City> getCitys();
}
4.3、Service接口实现层:
package com.shuizhu.service.impl;
import com.shuizhu.dao.mysql.DemoDao;
import com.shuizhu.dao.oracle.DemoOracleDao;
import com.shuizhu.domain.City;
import com.shuizhu.domain.User;
import com.shuizhu.service.IDemoService;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* 偷懒了,直接写入到同一个service
* @author 睡竹
* @date 2022/9/15
*/
@Service
@RequiredArgsConstructor
public class DemoService implements IDemoService {
//MySQL的Dao构造器注入
final DemoDao dao1;
//Oracle的Dao构造器注入
final DemoOracleDao dao2;
@Override
public List<User> getUsers() {
List<User> users = dao1.getUsers();
return users;
}
@Override
public List<City> getCitys() {
List<City> citys = dao2.getCitys();
return citys;
}
}
4.4、Domain实体类层:
User实体类:
package com.shuizhu.domain;
import lombok.Data;
import java.io.Serializable;
/**
* MySQL库user表实体类
* @author 睡竹
* @date 2022/9/15
*/
@Data
public class User implements Serializable {
private static final long serialVersionUID = 1L;
private Integer id;
private String name;
private Integer age;
}
City实体类:
package com.shuizhu.domain;
import lombok.Data;
import java.io.Serializable;
/**
* Oracle库city表实体类
* @author 睡竹
* @date 2022/9/16
*/
@Data
public class City implements Serializable {
private static final long serialVersionUID = 1L;
private Integer id;
private String name;
private String parent;
}
至此,测试流程及代码都已实现
附上整个springboot项目完整目录结构如:
5、使用api工具测试接口
使用api工具对接口进行测试,检验数据源是否生效
5.1、测试MySQL数据的接口
请求路径:http://127.0.0.1:9080/testMySQL
5.2、 测试Oracle数据的接口
请求路径:http://127.0.0.1:9080/testOracle
测试成功,多数据源配置生效
案例代码:shuizhu-multiple-ds: 多数据源配置-通过SqlSessionFactory指定的数据源来操作指定目录的XML文件的方式https://gitee.com/ct668/shuizhu-multiple-ds