spring boot:用dynamic-datasource-spring-boot-starter配置druid多数据源(spring boot 2.3.3)
一，dynamic-datasource-spring-boot-starter的用途?
1,dynamic-datasource-spring-boot-starter 是一个基于springboot的快速集成多数据源的启动器
它由苞米豆团队出品
2,官方站及文档:
官方站
https://mybatis.plus/
官方代码站:
https://gitee.com/baomidou/dynamic-datasource-spring-boot-starter
官方文档站:
https://mybatis.plus/guide/dynamic-datasource.html
说明：刘宏缔的架构森林是一个专注架构的博客，
网站：https://blog.imgtouch.com
本文: https://blog.imgtouch.com/index.php/2023/05/24/springboot-yong-dynamicdatasourcespringbootstarter-pei-zhi-druid-duo-shu-ju-yuan-springboot233/
对应的源码可以访问这里获取： https://github.com/liuhongdi/
说明：作者:刘宏缔 邮箱: 371125307@qq.com
二，演示项目的相关信息
1,项目的地址:
https://github.com/liuhongdi/multidruiddynamic
2,项目的功能说明:
访问两个数据库，分别打印出两个库中商品和订单的信息
3,项目的结构：如图:
三，配置文件说明
1,pom.xml
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> <exclusions> <exclusion> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-logging</artifactId> </exclusion> </exclusions> </dependency> <!--dynamic datasource begin--> <dependency> <groupId>com.baomidou</groupId> <artifactId>dynamic-datasource-spring-boot-starter</artifactId> <version>3.0.0</version> </dependency> <!--dynamic datasource end--> <!--druid begin--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.23</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-log4j2</artifactId> </dependency> <dependency> <groupId>com.lmax</groupId> <artifactId>disruptor</artifactId> <version>3.4.2</version> </dependency> <!--druid end--> <!--mybatis begin--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency> <!--mybatis end--> <!--mysql begin--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!--mysql end-->
说明：因为给druid使用了log4j2日志，为避免冲突，
在spring-boot-starter-web中排除了spring-boot-starter-logging
2,application.properties
#error server.error.include-stacktrace=always #error logging.level.org.springframework.web=trace #name spring.application.name = dynamic # orderdb设置为主数据源 spring.datasource.dynamic.primary = orderdb # orderdb数据源配置 spring.datasource.dynamic.datasource.orderdb.url = jdbc:mysql://127.0.0.1:3306/orderdb?useSSL=false&useUnicode=true&characterEncoding=UTF-8 spring.datasource.dynamic.datasource.orderdb.driver-class-name = com.mysql.cj.jdbc.Driver spring.datasource.dynamic.datasource.orderdb.username = root spring.datasource.dynamic.datasource.orderdb.password = lhddemo spring.datasource.dynamic.datasource.orderdb.type= com.alibaba.druid.pool.DruidDataSource spring.datasource.dynamic.datasource.orderdb.druid.initial-size=5 spring.datasource.dynamic.datasource.orderdb.druid.max-active=20 spring.datasource.dynamic.datasource.orderdb.druid.min-idle=5 spring.datasource.dynamic.datasource.orderdb.druid.max-wait=60000 spring.datasource.dynamic.datasource.orderdb.druid.min-evictable-idle-time-millis=300000 spring.datasource.dynamic.datasource.orderdb.druid.max-evictable-idle-time-millis=300000 spring.datasource.dynamic.datasource.orderdb.druid.time-between-eviction-runs-millis=60000 spring.datasource.dynamic.datasource.orderdb.druid.validation-query=select 1 spring.datasource.dynamic.datasource.orderdb.druid.validation-query-timeout=-1 spring.datasource.dynamic.datasource.orderdb.druid.test-on-borrow=false spring.datasource.dynamic.datasource.orderdb.druid.test-on-return=false spring.datasource.dynamic.datasource.orderdb.druid.test-while-idle=true spring.datasource.dynamic.datasource.orderdb.druid.pool-prepared-statements=true spring.datasource.dynamic.datasource.orderdb.druid.filters=stat,wall,log4j2 spring.datasource.dynamic.datasource.orderdb.druid.share-prepared-statements=true # goodsdb数据源配置 spring.datasource.dynamic.datasource.goodsdb.url = jdbc:mysql://127.0.0.1:3306/store?useSSL=false&useUnicode=true&characterEncoding=UTF-8 spring.datasource.dynamic.datasource.goodsdb.driver-class-name = com.mysql.cj.jdbc.Driver spring.datasource.dynamic.datasource.goodsdb.username = root spring.datasource.dynamic.datasource.goodsdb.password = lhddemo spring.datasource.dynamic.datasource.goodsdb.type= com.alibaba.druid.pool.DruidDataSource spring.datasource.dynamic.datasource.goodsdb.druid.initial-size=5 spring.datasource.dynamic.datasource.goodsdb.druid.max-active=20 spring.datasource.dynamic.datasource.goodsdb.druid.min-idle=5 spring.datasource.dynamic.datasource.goodsdb.druid.max-wait=60000 spring.datasource.dynamic.datasource.goodsdb.druid.min-evictable-idle-time-millis=300000 spring.datasource.dynamic.datasource.goodsdb.druid.max-evictable-idle-time-millis=300000 spring.datasource.dynamic.datasource.goodsdb.druid.time-between-eviction-runs-millis=60000 spring.datasource.dynamic.datasource.goodsdb.druid.validation-query=select 1 spring.datasource.dynamic.datasource.goodsdb.druid.validation-query-timeout=-1 spring.datasource.dynamic.datasource.goodsdb.druid.test-on-borrow=false spring.datasource.dynamic.datasource.goodsdb.druid.test-on-return=false spring.datasource.dynamic.datasource.goodsdb.druid.test-while-idle=true spring.datasource.dynamic.datasource.goodsdb.druid.pool-prepared-statements=true spring.datasource.dynamic.datasource.goodsdb.druid.filters=stat,wall,log4j2 spring.datasource.dynamic.datasource.goodsdb.druid.share-prepared-statements=true # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙 #spring.datasource.druid.filters = stat,wall,log4j2 spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize = 20 spring.datasource.druid.useGlobalDataSourceStat = true spring.datasource.druid.connectionProperties = druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500 #druid sql firewall monitor spring.datasource.druid.filter.wall.enabled=true #druid sql monitor spring.datasource.druid.filter.stat.enabled=true spring.datasource.druid.filter.stat.log-slow-sql=true spring.datasource.druid.filter.stat.slow-sql-millis=10000 spring.datasource.druid.filter.stat.merge-sql=true #druid uri monitor spring.datasource.druid.web-stat-filter.enabled=true spring.datasource.druid.web-stat-filter.url-pattern=/* spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/* #druid session monitor spring.datasource.druid.web-stat-filter.session-stat-enable=true spring.datasource.druid.web-stat-filter.profile-enable=true #druid spring monitor spring.datasource.druid.aop-patterns=com.druid.* #monintor,druid login user config spring.datasource.druid.stat-view-servlet.enabled=true spring.datasource.druid.stat-view-servlet.login-username=root spring.datasource.druid.stat-view-servlet.login-password=root # IP白名单 (没有配置或者为空，则允许所有访问) spring.datasource.druid.stat-view-servlet.allow = 127.0.0.1,192.168.163.1 # IP黑名单 (存在共同时，deny优先于allow) spring.datasource.druid.stat-view-servlet.deny = 192.168.10.1 #mybatis mybatis.mapper-locations=classpath:/mapper/*Mapper.xml mybatis.type-aliases-package=com.example.demo.mapper mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl #log logging.config = classpath:log4j2.xml
3,log4j2.xml
<?xml version="1.0" encoding="UTF-8"?> <configuration status="OFF"> <appenders> <Console name="Console" target="SYSTEM_OUT"> <!--只接受程序中DEBUG级别的日志进行处理--> <ThresholdFilter level="DEBUG" onMatch="ACCEPT" onMismatch="DENY"/> <PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] [%file:%line] %-5level %logger{35} - %msg %n"/> </Console> <!--处理INFO级别的日志，并把该日志放到logs/info.log文件中--> <RollingFile name="RollingFileInfo" fileName="./logs/info.log" filePattern="logs/$${date:yyyy-MM}/info-%d{yyyy-MM-dd}-%i.log.gz"> <Filters> <ThresholdFilter level="INFO"/> <ThresholdFilter level="WARN" onMatch="DENY" onMismatch="NEUTRAL"/> </Filters> <PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] [%file:%line] %-5level %logger{35} - %msg %n"/> <Policies> <SizeBasedTriggeringPolicy size="500 MB"/> <TimeBasedTriggeringPolicy/> </Policies> </RollingFile> <!--处理WARN级别的日志，并把该日志放到logs/warn.log文件中--> <RollingFile name="RollingFileWarn" fileName="./logs/warn.log" filePattern="logs/$${date:yyyy-MM}/warn-%d{yyyy-MM-dd}-%i.log.gz"> <Filters> <ThresholdFilter level="WARN"/> <ThresholdFilter level="ERROR" onMatch="DENY" onMismatch="NEUTRAL"/> </Filters> <PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] [%file:%line] %-5level %logger{35} - %msg %n"/> <Policies> <SizeBasedTriggeringPolicy size="500 MB"/> <TimeBasedTriggeringPolicy/> </Policies> </RollingFile> <!--处理error级别的日志，并把该日志放到logs/error.log文件中--> <RollingFile name="RollingFileError" fileName="./logs/error.log" filePattern="logs/$${date:yyyy-MM}/error-%d{yyyy-MM-dd}-%i.log.gz"> <ThresholdFilter level="ERROR"/> <PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] [%file:%line] %-5level %logger{35} - %msg %n"/> <Policies> <SizeBasedTriggeringPolicy size="500 MB"/> <TimeBasedTriggeringPolicy/> </Policies> </RollingFile> <!--druid的日志记录追加器--> <RollingFile name="druidSqlRollingFile" fileName="./logs/druid-sql.log" filePattern="logs/$${date:yyyy-MM}/api-%d{yyyy-MM-dd}-%i.log.gz"> <PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] [%file:%line] %-5level %logger{35} - %msg %n"/> <Policies> <SizeBasedTriggeringPolicy size="500 MB"/> <TimeBasedTriggeringPolicy/> </Policies> </RollingFile> </appenders> <loggers> <AsyncRoot level="info"> <appender-ref ref="Console"/> <appender-ref ref="RollingFileInfo"/> <appender-ref ref="RollingFileWarn"/> <appender-ref ref="RollingFileError"/> </AsyncRoot> <!--记录druid-sql的记录--> <AsyncLogger name="druid.sql.Statement" level="debug" additivity="false"> <appender-ref ref="druidSqlRollingFile"/> </AsyncLogger> </loggers> </configuration>
4,数据库的相关业务表:
goods表
CREATE TABLE `goods` ( `goodsId` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id', `goodsName` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT 'name', `subject` varchar(200) NOT NULL DEFAULT '' COMMENT '标题', `price` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '价格', `stock` int(11) NOT NULL DEFAULT '0' COMMENT 'stock', PRIMARY KEY (`goodsId`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='商品表'
goods表中的数据:
INSERT INTO `goods` (`goodsId`, `goodsName`, `subject`, `price`, `stock`) VALUES (3, '100分电动牙刷', '好用到让你爱上刷牙', '59.00', 96);
order表:
CREATE TABLE `orderinfo` ( `orderId` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id', `orderSn` varchar(100) NOT NULL DEFAULT '' COMMENT '编号', `orderTime` timestamp NOT NULL DEFAULT '1971-01-01 00:00:01' COMMENT '下单时间', `orderStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态:0,未支付，1，已支付，2，已发货，3，已退货,4,已过期', `userId` int(12) NOT NULL DEFAULT '0' COMMENT '用户id', `price` decimal(10,0) NOT NULL DEFAULT '0' COMMENT '价格', `addressId` int(12) NOT NULL DEFAULT '0' COMMENT '地址', PRIMARY KEY (`orderId`), UNIQUE KEY `orderSn` (`orderSn`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单表'
order表中的数据:
INSERT INTO `orderinfo` (`orderId`, `orderSn`, `orderTime`, `orderStatus`, `userId`, `price`, `addressId`) VALUES (77, '20200814171411660', '2020-08-14 09:14:12', 0, 8, '100', 0);
四，java代码说明
1,GoodsMapper.java
@Repository @Mapper public interface GoodsMapper { Goods selectOneGoods(Long goodsId); }
2,OrderMapper.java
@Repository @Mapper public interface OrderMapper { Order selectOneOrder(Long orderId); }
3,HomeController.java
@Controller @RequestMapping("/home") public class HomeController { @Resource private GoodsMapper goodsMapper; @Resource private OrderMapper orderMapper; //商品详情 参数:商品id @GetMapping("/goodsinfo") @ResponseBody @DS("goodsdb") public Goods goodsInfo(@RequestParam(value="goodsid",required = true,defaultValue = "0") Long goodsId) { Goods goods = goodsMapper.selectOneGoods(goodsId); return goods; } //订单详情 参数：订单id @GetMapping("/orderinfo") @ResponseBody @DS("orderdb") public Order orderInfo(@RequestParam(value="orderid",required = true,defaultValue = "0") Long orderId) { Order order = orderMapper.selectOneOrder(orderId); return order; } }
说明:用DS注解指明要使用的数据，
dynamic-datasource官方建议把注解添加到service的方法上
4,GoodsMapper.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.multidruiddynamic.demo.mapper.goodsdb.GoodsMapper"> <select id="selectOneGoods" parameterType="long" resultType="com.multidruiddynamic.demo.pojo.Goods"> select * from goods where goodsId=#{goodsId} </select> </mapper>
5,OrderMapper.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.multidruiddynamic.demo.mapper.orderdb.OrderMapper"> <select id="selectOneOrder" parameterType="long" resultType="com.multidruiddynamic.demo.pojo.Order"> select * from orderinfo where orderId=#{orderId} </select> </mapper>
6,Goods.java
public class Goods { //商品id Long goodsId; public Long getGoodsId() { return this.goodsId; } public void setGoodsId(Long goodsId) { this.goodsId = goodsId; } //商品名称 private String goodsName; public String getGoodsName() { return this.goodsName; } public void setGoodsName(String goodsName) { this.goodsName = goodsName; } //商品标题 private String subject; public String getSubject() { return this.subject; } public void setSubject(String subject) { this.subject = subject; } //商品价格 private BigDecimal price; public BigDecimal getPrice() { return this.price; } public void setPrice(BigDecimal price) { this.price = price; } //库存 int stock; public int getStock() { return this.stock; } public void setStock(int stock) { this.stock = stock; } public String toString(){ return " Goods:goodsId=" + goodsId +" goodsName=" + goodsName+" subject=" + subject+" price=" + price+" stock=" + stock; } }
7,Order.java
//订单模型 public class Order { //订单id Long orderId; public Long getOrderId() { return this.orderId; } public void setOrderId(Long orderId) { this.orderId = orderId; } //订单编号 private String orderSn; public String getOrderSn() { return this.orderSn; } public void setOrderSn(String orderSn) { this.orderSn = orderSn; } //下单时间 private String orderTime; public String getOrderTime() { return this.orderTime; } public void setOrderTime(String orderTime) { this.orderTime = orderTime; } //订单状态 int orderStatus; public int getOrderStatus() { return this.orderStatus; } public void setOrderStatus(int orderStatus) { this.orderStatus = orderStatus; } //订单状态 int userId; public int getUserId() { return this.userId; } public void setUserId(int userId) { this.userId = userId; } //订单价格 private BigDecimal price; public BigDecimal getPrice() { return this.price; } public void setPrice(BigDecimal price) { this.price = price; } public String toString(){ return " Order:orderId=" + orderId +" orderSn=" + orderSn+" orderTime=" + orderTime+" orderStatus:"+orderStatus+" userId:"+userId+" price=" + price; } }
8,DemoApplication.java
@SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class) public class DemoApplication { public static void main(String[] args) { SpringApplication.run(DemoApplication.class, args); } }
说明：在启动类上需要排除：DruidDataSourceAutoConfigure，
否则启动时会提示找不到数据源的url
五，测试效果
1,查询商品信息，访问：
http://127.0.0.1:8080/home/goodsinfo?goodsid=3
返回:
{"goodsId":3,"goodsName":"100分电动牙刷","subject":"好用到让你爱上刷牙","price":59.00,"stock":96}
2,查询订单信息，访问:
http://127.0.0.1:8080/home/orderinfo?orderid=77
返回：
{"orderId":77,"orderSn":"20200814171411660","orderTime":"2020-08-14 17:14:12","orderStatus":0,"userId":8,"price":100}
3,查看druid管理页面中的数据源:
可以看到已连接了两个数据源
六，查看spring boot的版本:
. ____ _ __ _ _ /\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \ ( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \ \\/ ___)| |_)| | | | | || (_| | ) ) ) ) ' |____| .__|_| |_|_| |_\__, | / / / / =========|_|==============|___/=/_/_/_/ :: Spring Boot :: (v2.3.3.RELEASE)
