思享工具箱

spring boot:用dynamic-datasource-spring-boot-starter配置druid多数据源(spring boot 2.3.3)

摘要：
一，dynamic-datasource-spring-boot-starter的用途?--dynamicdatasourcebegin--˃com.baomidoudynamic-datasource-spring-boot-starter3.0.0com.alibabadruid-spring-boot-starter1.1.23org.springframework.bootspring-boot-starter-log4j2com.lmaxdisruptor3.4.2org.mybatis.spring.bootmybatis-spring-boot-starter2.1.3mysqlmysql-connector-javaruntime˂!

一，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://www.cnblogs.com/architectforest

对应的源码可以访问这里获取：https://github.com/liuhongdi/

说明：作者:刘宏缔 邮箱: 371125307@qq.com

二，演示项目的相关信息

1,项目的地址:

https://github.com/liuhongdi/multidruiddynamic

2,项目的功能说明:

访问两个数据库，分别打印出两个库中商品和订单的信息

3,项目的结构：如图:

spring boot:用dynamic-datasource-spring-boot-starter配置druid多数据源(spring boot 2.3.3)第1张

三，配置文件说明

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

#errorserver.error.include-stacktrace=always
#errorlogging.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=5spring.datasource.dynamic.datasource.orderdb.druid.max-active=20spring.datasource.dynamic.datasource.orderdb.druid.min-idle=5spring.datasource.dynamic.datasource.orderdb.druid.max-wait=60000spring.datasource.dynamic.datasource.orderdb.druid.min-evictable-idle-time-millis=300000spring.datasource.dynamic.datasource.orderdb.druid.max-evictable-idle-time-millis=300000spring.datasource.dynamic.datasource.orderdb.druid.time-between-eviction-runs-millis=60000spring.datasource.dynamic.datasource.orderdb.druid.validation-query=select 1spring.datasource.dynamic.datasource.orderdb.druid.validation-query-timeout=-1spring.datasource.dynamic.datasource.orderdb.druid.test-on-borrow=falsespring.datasource.dynamic.datasource.orderdb.druid.test-on-return=falsespring.datasource.dynamic.datasource.orderdb.druid.test-while-idle=truespring.datasource.dynamic.datasource.orderdb.druid.pool-prepared-statements=truespring.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=5spring.datasource.dynamic.datasource.goodsdb.druid.max-active=20spring.datasource.dynamic.datasource.goodsdb.druid.min-idle=5spring.datasource.dynamic.datasource.goodsdb.druid.max-wait=60000spring.datasource.dynamic.datasource.goodsdb.druid.min-evictable-idle-time-millis=300000spring.datasource.dynamic.datasource.goodsdb.druid.max-evictable-idle-time-millis=300000spring.datasource.dynamic.datasource.goodsdb.druid.time-between-eviction-runs-millis=60000spring.datasource.dynamic.datasource.goodsdb.druid.validation-query=select 1spring.datasource.dynamic.datasource.goodsdb.druid.validation-query-timeout=-1spring.datasource.dynamic.datasource.goodsdb.druid.test-on-borrow=falsespring.datasource.dynamic.datasource.goodsdb.druid.test-on-return=falsespring.datasource.dynamic.datasource.goodsdb.druid.test-while-idle=truespring.datasource.dynamic.datasource.goodsdb.druid.pool-prepared-statements=truespring.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 = 20spring.datasource.druid.useGlobalDataSourceStat = truespring.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=truespring.datasource.druid.filter.stat.log-slow-sql=truespring.datasource.druid.filter.stat.slow-sql-millis=10000spring.datasource.druid.filter.stat.merge-sql=true#druid uri monitor
spring.datasource.druid.web-stat-filter.enabled=truespring.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 interfaceGoodsMapper {
    Goods selectOneGoods(Long goodsId);
}

2,OrderMapper.java

@Repository
@Mapper
public interfaceOrderMapper {
    Order selectOneOrder(Long orderId);
}

3,HomeController.java

@Controller
@RequestMapping("/home")
public classHomeController {

    @Resource
    privateGoodsMapper goodsMapper;

    @Resource
    privateOrderMapper orderMapper;

    //商品详情 参数:商品id
    @GetMapping("/goodsinfo")
    @ResponseBody
    @DS("goodsdb")
    public Goods goodsInfo(@RequestParam(value="goodsid",required = true,defaultValue = "0") Long goodsId) {
        Goods goods =goodsMapper.selectOneGoods(goodsId);
        returngoods;
    }

    //订单详情 参数：订单id
    @GetMapping("/orderinfo")
    @ResponseBody
    @DS("orderdb")
    public Order orderInfo(@RequestParam(value="orderid",required = true,defaultValue = "0") Long orderId) {
        Order order =orderMapper.selectOneOrder(orderId);
        returnorder;
    }
}

说明:用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 classGoods {
    //商品id
Long goodsId;
    publicLong getGoodsId() {
        return this.goodsId;
    }
    public voidsetGoodsId(Long goodsId) {
        this.goodsId =goodsId;
    }

    //商品名称
    privateString goodsName;
    publicString getGoodsName() {
        return this.goodsName;
    }
    public voidsetGoodsName(String goodsName) {
        this.goodsName =goodsName;
    }

    //商品标题
    privateString subject;
    publicString getSubject() {
        return this.subject;
    }
    public voidsetSubject(String subject) {
        this.subject =subject;
    }

    //商品价格
    privateBigDecimal price;
    publicBigDecimal getPrice() {
        return this.price;
    }
    public voidsetPrice(BigDecimal price) {
        this.price =price;
    }

    //库存
    intstock;
    public intgetStock() {
        return this.stock;
    }
    public void setStock(intstock) {
        this.stock =stock;
    }

    publicString toString(){
        return " Goods:goodsId=" + goodsId +" goodsName=" + goodsName+" subject=" + subject+" price=" + price+" stock=" +stock;
    }
}

7,Order.java

//订单模型
public classOrder {
    //订单id
Long orderId;
    publicLong getOrderId() {
        return this.orderId;
    }
    public voidsetOrderId(Long orderId) {
        this.orderId =orderId;
    }

    //订单编号
    privateString orderSn;
    publicString getOrderSn() {
        return this.orderSn;
    }
    public voidsetOrderSn(String orderSn) {
        this.orderSn =orderSn;
    }

    //下单时间
    privateString orderTime;
    publicString getOrderTime() {
        return this.orderTime;
    }
    public voidsetOrderTime(String orderTime) {
        this.orderTime =orderTime;
    }

    //订单状态
    intorderStatus;
    public intgetOrderStatus() {
        return this.orderStatus;
    }
    public void setOrderStatus(intorderStatus) {
        this.orderStatus =orderStatus;
    }

    //订单状态
    intuserId;
    public intgetUserId() {
        return this.userId;
    }
    public void setUserId(intuserId) {
        this.userId =userId;
    }

    //订单价格
    privateBigDecimal price;
    publicBigDecimal getPrice() {
        return this.price;
    }
    public voidsetPrice(BigDecimal price) {
        this.price =price;
    }


    publicString toString(){
        return " Order:orderId=" + orderId +" orderSn=" + orderSn+" orderTime=" + orderTime+" orderStatus:"+orderStatus+" userId:"+userId+" price=" +price;
    }
}

8,DemoApplication.java

@SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class)
public classDemoApplication {

    public static voidmain(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:用dynamic-datasource-spring-boot-starter配置druid多数据源(spring boot 2.3.3)第2张

可以看到已连接了两个数据源

六，查看spring boot的版本:

.   ____          _            __ _ _
 /\ / ___'_ __ _ _(_)_ __  __ _    
( ( )\___ | '_ | '_| | '_ / _` |    
 \/  ___)| |_)| | | | | || (_| |) ) ) )
  '|____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/:: Spring Boot ::        (v2.3.3.RELEASE)

标签：#druid | 浏览：502 | 发布日期：

免责声明：文章转载自《spring boot:用dynamic-datasource-spring-boot-starter配置druid多数据源(spring boot 2.3.3)》仅用于学习参考。如对内容有疑问，请及时联系本站处理。

上篇用C#中的键值对遍历数组或字符串元素的次数OpenCV4系列之图像梯度下篇

宿迁高防，2C2G15M，22元/月；香港BGP，2C5G5M，25元/月 雨云优惠码：MjYwNzM=

相关文章

Mysql连接池 [ druid ] 的坑

Mysql无效链接异常：com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure 背景分析 1、异常详情：异常的意思是，前一次成功的使用连接是699,944毫秒以前，也就是大概11分钟之前（空闲时间超过10分钟） 2019-06-29 14:16:02...

基本项目框架搭建 sqlserver druid配置

  1.  我的连接池采用的是阿里云的druid的连接池，工具是IDEA 框架是springboot+maven    以下是我的项目框架结构    2. pom  中配置 <?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM...

Druid 架构

本篇译自 Druid项目白皮书部分内容（ https://github.com/apache/incubator-druid/tree/master/publications/whitepaper/druid.pdf），如果有兴趣可看细看原pdf【初次翻译多多包涵】 一个 Druid 集群包含多种特定功能的节点， 我们相信这种设计能够分散业务并且简化整个系...

springboot postgresql druid连接池和jpa，jdbctemplate执行sql查询

1.maven依赖配置(pom.xml) 1 <dependency> 2 <groupId>org.springframework.boot</groupId> 3 <artifactId>spring-boot-starter</...

Druid连接池的简单使用

感谢原文作者：chenhongyong原文链接：https://www.cnblogs.com/chy18883701161/p/12594889.html更多请查阅阿里官方API文档：https://github.com/alibaba/druid/wiki 目录 Druid简介 druid的优点 Druid的使用 方式一：纯代码方式 方式二：配置...

在Spring+maven项目中配置数据库连接池Druid

目录 1.Druid的简介 2.Druid组成 3.Druid功能 4.在maven项目中配置Druid 4.1pom.xml中添加依赖 4.2web.xml添加过滤器和servlet 4.3配置数据库连接池spring datasource 4.4spring 配置 4.5访问监控界面 1.Druid的简介Druid是一个数据库连接池。Druid是目前...

最新文章

随机推荐

思享工具箱导航