Spring boot 使用druid配置多个数据源
Spring boot 使用druid连接多个数据库
spring注解 @primary
ruoyi（spring-boot）项目 druid 连接池配置多个数据源
MybatisPlus 配置多个数据源
1. 创建数据库及表
📅 1. mysql：
CREATE TABLE `tb_user` (
`user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`user_name` varchar(20) NOT NULL COMMENT '用户名',
`password` varchar(20) NOT NULL COMMENT '密码',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`address` varchar(100) DEFAULT NULL COMMENT '地址',
`version` int(11) DEFAULT '1',
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO `tb_user` VALUES (1, 'hanbing', '123', '寒冰', 22, NULL, 1);
INSERT INTO `tb_user` VALUES (2, 'gailun', '753', '盖伦', 30, '德玛西亚', 5);
INSERT INTO `tb_user` VALUES (3, 'timo', '123', '提莫', 20, NULL, 1);
INSERT INTO `tb_user` VALUES (4, 'ruiwen', '123', '瑞文', 22, NULL, 1);
INSERT INTO `tb_user` VALUES (5, 'liulang', '123', '流浪', 50, NULL, 1);
📅 2. kingbase8：
CREATE TABLE "tmradmin"."tb_user" (
"user_id" numeric(20,0) NOT NULL,
"user_name" character varying(20 char) NOT NULL,
"password" character varying(20 char) NOT NULL,
"name" character varying(30 char) NULL DEFAULT NULL::varchar,
"age" numeric(20,0) NULL DEFAULT NULL::numeric,
"address" character varying(100 char) NULL DEFAULT NULL::varchar,
"version" numeric(20,0) NULL DEFAULT '1'::numeric,
CONSTRAINT "tb_user_pkey" PRIMARY KEY (user_id)
);
INSERT INTO "tmradmin"."tb_user" VALUES (1,'nuoke','123','诺克',33,NULL,1),
INSERT INTO "tmradmin"."tb_user" VALUES (2,'anying','123','暗影',22,NULL,1),
INSERT INTO "tmradmin"."tb_user" VALUES (3,'wuya','123','乌鸦',22,NULL,1);
2. 引入依赖
<!-- mysql驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- kingbase8驱动 -->
<dependency>
<groupId>kingbase8</groupId>
<artifactId>kingbase8</artifactId>
<version>0.0.1</version>
<scope>system</scope>
<systemPath>${basedir}/src/main/resources/lib/kingbase8-8.6.0.jar</systemPath>
</dependency>
<!-- 动态数据源依赖 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.5.0</version>
</dependency>
3. 配置多数据源
spring:
datasource:
dynamic:
# 设置默认的数据源或者数据源组,默认值即为 mysql
primary: mysql
# 严格匹配数据源，默认false。 true：未匹配到指定数据源时抛异常；false：使用默认数据源。
strict: false
datasource:
mysql:
url: jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
kingbase8:
url: jdbc:kingbase8://192.168.2.190:54321/ZNDB
username: tmradmin
password: dbyongyou
driver-class-name: com.kingbase8.Driver
primary: mysql：指定默认数据源。
4. 创建接口 UserService
public interface UserService extends IService<User> {
public List<User> listByMysql();
public List<User> listByKingbase8();
}
5. 创建实现类 UserServiceImpl
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
@Autowired
public UserMapper userMapper;
@Override
@DS("mysql") //指定所操作的数据源
public List<User> listByMysql() {
List<User> list = userMapper.selectList(null);
return list;
}
@Override
@DS("kingbase8") //指定所操作的数据源
public List<User> listByKingbase8() {
List<User> list = userMapper.selectList(null);
return list;
}
}
@DS("mysql") 、@DS("kingbase8") 指定所操作的数据源。
@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DS {
/**
* groupName or specific database name or spring SPEL name.
*
* @return the database you want to switch
*/
String value();
}
注意：@DS可以修饰类、方法，修饰类时，类方法全部使用指定数据源。@DS标注在接口无效。
6. 测试
@Test
public void testDynamicDataSource() {
//原生方法默认使用：mysql
List<User> list = userService.list();
System.out.println(list);
//指定使用：mysql
list = userService.listByMysql();
System.out.println(list);
//指定使用：kingbase8
list = userService.listByKingbase8();
System.out.println(list);
}
打印结果：
2023-12-12 11:56:47.771 INFO 16512 --- [ main] org.example.MPTest : Started MPTest in 2.99 seconds (JVM running for 3.839)
2023-12-12 11:56:47.772 INFO 16512 --- [ main] o.s.b.a.ApplicationAvailabilityBean : Application availability state LivenessState changed to CORRECT
2023-12-12 11:56:47.774 INFO 16512 --- [ main] o.s.b.a.ApplicationAvailabilityBean : Application availability state ReadinessState changed to ACCEPTING_TRAFFIC
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@684e8c9d] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@2016442966 wrapping com.mysql.cj.jdbc.ConnectionImpl@40d52be7] will not be managed by Spring
==> Preparing: SELECT user_id,user_name,password,name,age,address AS addressStr,version FROM tb_user
==> Parameters:
<== Columns: user_id, user_name, password, name, age, addressStr, version
<== Row: 1, hanbing, 123, 寒冰, 22, null, 1
<== Row: 2, gailun, 753, 盖伦, 30, 德玛西亚, 5
<== Row: 3, timo, 123, 提莫, 20, null, 1
<== Row: 4, ruiwen, 123, 瑞文, 22, null, 1
<== Row: 5, liulang, 123, 流浪, 50, null, 1
<== Total: 5
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@684e8c9d]
[User(userId=1, userName=hanbing, password=123, name=寒冰, age=22, addressStr=null, version=1), User(userId=2, userName=gailun, password=753, name=盖伦, age=30, addressStr=德玛西亚, version=5), User(userId=3, userName=timo, password=123, name=提莫, age=20, addressStr=null, version=1), User(userId=4, userName=ruiwen, password=123, name=瑞文, age=22, addressStr=null, version=1), User(userId=5, userName=liulang, password=123, name=流浪, age=50, addressStr=null, version=1)]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4d705112] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1493657028 wrapping com.mysql.cj.jdbc.ConnectionImpl@40d52be7] will not be managed by Spring
==> Preparing: SELECT user_id,user_name,password,name,age,address AS addressStr,version FROM tb_user
==> Parameters:
<== Columns: user_id, user_name, password, name, age, addressStr, version
<== Row: 1, hanbing, 123, 寒冰, 22, null, 1
<== Row: 2, gailun, 753, 盖伦, 30, 德玛西亚, 5
<== Row: 3, timo, 123, 提莫, 20, null, 1
<== Row: 4, ruiwen, 123, 瑞文, 22, null, 1
<== Row: 5, liulang, 123, 流浪, 50, null, 1
<== Total: 5
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4d705112]
[User(userId=1, userName=hanbing, password=123, name=寒冰, age=22, addressStr=null, version=1), User(userId=2, userName=gailun, password=753, name=盖伦, age=30, addressStr=德玛西亚, version=5), User(userId=3, userName=timo, password=123, name=提莫, age=20, addressStr=null, version=1), User(userId=4, userName=ruiwen, password=123, name=瑞文, age=22, addressStr=null, version=1), User(userId=5, userName=liulang, password=123, name=流浪, age=50, addressStr=null, version=1)]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2d5a1588] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1326541986 wrapping com.kingbase8.jdbc.KbConnection@125d47c4] will not be managed by Spring
==> Preparing: SELECT user_id,user_name,password,name,age,address AS addressStr,version FROM tb_user
==> Parameters:
<== Columns: user_id, user_name, password, name, age, addressStr, version
<== Row: 1, nuoke, 123, 诺克, 33, null, 1
<== Row: 2, anying, 123, 暗影, 22, null, 1
<== Row: 3, wuya, 123, 乌鸦, 22, null, 1
<== Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2d5a1588]
[User(userId=1, userName=nuoke, password=123, name=诺克, age=33, addressStr=null, version=1), User(userId=2, userName=anying, password=123, name=暗影, age=22, addressStr=null, version=1), User(userId=3, userName=wuya, password=123, name=乌鸦, age=22, addressStr=null, version=1)]
💦结果可见：没有标注@DS的方法list()，使用默认数据源mysql，打印 “寒冰 盖伦 提莫 瑞文 流浪”。
标注@DS("kingbase8")的方法listByKingbase8()，使用数据源kingbase8，打印 “诺克 暗影 乌鸦”。
📌 应用场景：
多数据源的场景不单单应用于访问不同类型的数据库，还可以应用读写分离的数据库，将写操作方法加上主库数据源，读操作方法加上从库数据源，自动切换，就能实现读写分离。
📌 引申：
@DS修饰类时，类方法全部使用指定数据源，测试下
🎈 修改 UserServiceImpl，标注@DS，其他不变。
@Service
@DS("kingbase8")
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
@Autowired
public UserMapper userMapper;
@Override
public List<User> listByMysql() {
List<User> list = userMapper.selectList(null);
return list;
}
@Override
public List<User> listByKingbase8() {
List<User> list = userMapper.selectList(null);
return list;
}
}
打印结果：
2023-12-12 14:31:57.614 INFO 7696 --- [ main] org.example.MPTest : Started MPTest in 3.079 seconds (JVM running for 3.93)
2023-12-12 14:31:57.615 INFO 7696 --- [ main] o.s.b.a.ApplicationAvailabilityBean : Application availability state LivenessState changed to CORRECT
2023-12-12 14:31:57.617 INFO 7696 --- [ main] o.s.b.a.ApplicationAvailabilityBean : Application availability state ReadinessState changed to ACCEPTING_TRAFFIC
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6fa2448b] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@2084634607 wrapping com.kingbase8.jdbc.KbConnection@286855ea] will not be managed by Spring
==> Preparing: SELECT user_id,user_name,password,name,age,address AS addressStr,version FROM tb_user
==> Parameters:
<== Columns: user_id, user_name, password, name, age, addressStr, version
<== Row: 1, nuoke, 123, 诺克, 33, null, 1
<== Row: 2, anying, 123, 暗影, 22, null, 1
<== Row: 3, wuya, 123, 乌鸦, 22, null, 1
<== Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6fa2448b]
[User(userId=1, userName=nuoke, password=123, name=诺克, age=33, addressStr=null, version=1), User(userId=2, userName=anying, password=123, name=暗影, age=22, addressStr=null, version=1), User(userId=3, userName=wuya, password=123, name=乌鸦, age=22, addressStr=null, version=1)]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@704067c6] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@2135267024 wrapping com.kingbase8.jdbc.KbConnection@286855ea] will not be managed by Spring
==> Preparing: SELECT user_id,user_name,password,name,age,address AS addressStr,version FROM tb_user
==> Parameters:
<== Columns: user_id, user_name, password, name, age, addressStr, version
<== Row: 1, nuoke, 123, 诺克, 33, null, 1
<== Row: 2, anying, 123, 暗影, 22, null, 1
<== Row: 3, wuya, 123, 乌鸦, 22, null, 1
<== Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@704067c6]
[User(userId=1, userName=nuoke, password=123, name=诺克, age=33, addressStr=null, version=1), User(userId=2, userName=anying, password=123, name=暗影, age=22, addressStr=null, version=1), User(userId=3, userName=wuya, password=123, name=乌鸦, age=22, addressStr=null, version=1)]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@588545ac] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@721975085 wrapping com.kingbase8.jdbc.KbConnection@286855ea] will not be managed by Spring
==> Preparing: SELECT user_id,user_name,password,name,age,address AS addressStr,version FROM tb_user
==> Parameters:
<== Columns: user_id, user_name, password, name, age, addressStr, version
<== Row: 1, nuoke, 123, 诺克, 33, null, 1
<== Row: 2, anying, 123, 暗影, 22, null, 1
<== Row: 3, wuya, 123, 乌鸦, 22, null, 1
<== Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@588545ac]
[User(userId=1, userName=nuoke, password=123, name=诺克, age=33, addressStr=null, version=1), User(userId=2, userName=anying, password=123, name=暗影, age=22, addressStr=null, version=1), User(userId=3, userName=wuya, password=123, name=乌鸦, age=22, addressStr=null, version=1)]
💦 结果可见：不管是没有标注@DS的原生方法list()，还是类内方法，均使用数据源kingbase8，打印 “诺克 暗影 乌鸦”。
1070
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
