spring boot:使mybatis访问多个druid数据源(spring boot 2.3.2)

一，为什么要使用多个数据源?

1，什么情况下需要使用多个数据源?

当我们需要访问不同的数据库时，则需要配置配置多个数据源，

例如：电商的业务数据库(包括用户/商品/订单等)

和统计数据库(按月日年的订单数量/金额等的统计)通常是分开到不同的数据库

所以我们需要在应用中创建多个数据库连接池

2,通常的作法：

例如我们有两个数据库orderdb,goodsdb

两个数据库的mapper.xml文件需要放到不同的目录下，

通过给不同的目录配置不同的数据源，

从而实现处理不同的业务功能

说明：刘宏缔的架构森林是一个专注架构的博客，地址：https://www.cnblogs.com/architectforest

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

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

二，演示项目的相关信息

1,项目地址:

https://github.com/liuhongdi/multidruid

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>

        <!--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

#   数据源goodsdb基本配置

spring.datasource.druid.goodsdb.username = root

spring.datasource.druid.goodsdb.password = lhddemo

spring.datasource.druid.goodsdb.driver-class-name = com.mysql.cj.jdbc.Driver

spring.datasource.druid.goodsdb.url = jdbc:mysql://127.0.0.1:3306/store?serverTimezone=UTC

spring.datasource.druid.goodsdb.type = com.alibaba.druid.pool.DruidDataSource

spring.datasource.druid.goodsdb.initialSize = 5

spring.datasource.druid.goodsdb.minIdle = 5

spring.datasource.druid.goodsdb.maxActive = 20

spring.datasource.druid.goodsdb.maxWait = 60000

spring.datasource.druid.goodsdb.timeBetweenEvictionRunsMillis = 60000

spring.datasource.druid.goodsdb.minEvictableIdleTimeMillis = 300000

spring.datasource.druid.goodsdb.validationQuery = SELECT 1 FROM DUAL

spring.datasource.druid.goodsdb.testWhileIdle = true

spring.datasource.druid.goodsdb.testOnBorrow = false

spring.datasource.druid.goodsdb.testOnReturn = false

spring.datasource.druid.goodsdb.poolPreparedStatements = true

#   数据源orderdb基本配置

spring.datasource.druid.orderdb.username = root

spring.datasource.druid.orderdb.password = lhddemo

spring.datasource.druid.orderdb.driver-class-name = com.mysql.cj.jdbc.Driver

spring.datasource.druid.orderdb.url = jdbc:mysql://127.0.0.1:3306/orderdb?serverTimezone=UTC

spring.datasource.druid.orderdb.type = com.alibaba.druid.pool.DruidDataSource

spring.datasource.druid.orderdb.initialSize = 5

spring.datasource.druid.orderdb.minIdle = 5

spring.datasource.druid.orderdb.maxActive = 20

spring.datasource.druid.orderdb.maxWait = 60000

spring.datasource.druid.orderdb.timeBetweenEvictionRunsMillis = 60000

spring.datasource.druid.orderdb.minEvictableIdleTimeMillis = 300000

spring.datasource.druid.orderdb.validationQuery = SELECT 1 FROM DUAL

spring.datasource.druid.orderdb.testWhileIdle = true

spring.datasource.druid.orderdb.testOnBorrow = false

spring.datasource.druid.orderdb.testOnReturn = false

spring.datasource.druid.orderdb.poolPreparedStatements = 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

#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,GoodsdbSourceConfig.java

@Configuration

@MapperScan(basePackages = "com.multidruid.demo.mapper.goodsdb", sqlSessionTemplateRef = "goodsdbSqlSessionTemplate")

public class GoodsdbSourceConfig {

    @Bean

    @Primary

    @ConfigurationProperties("spring.datasource.druid.goodsdb")

    public DataSource goodsdbDataSource() {

        return DruidDataSourceBuilder.create().build();

    }

    @Bean

    @Primary

    public SqlSessionFactory goodsdbSqlSessionFactory(@Qualifier("goodsdbDataSource") DataSource dataSource) throws Exception {

        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

        bean.setDataSource(dataSource);

        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/goodsdb/*.xml"));

        return bean.getObject();

    }

    @Bean

    @Primary

    public DataSourceTransactionManager goodsdbTransactionManager(@Qualifier("goodsdbDataSource") DataSource dataSource) {

        return new DataSourceTransactionManager(dataSource);

    }

    @Bean

    @Primary

    public SqlSessionTemplate goodsdbSqlSessionTemplate(@Qualifier("goodsdbSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {

        return new SqlSessionTemplate(sqlSessionFactory);

    }

}

说明：用basePackages指定mapper程序所在目录,

bean.setMapperLocations指定mapper的xml文件所在目录

2,OrderdbSourceConfig.java

@Configuration

@MapperScan(basePackages = "com.multidruid.demo.mapper.orderdb", sqlSessionTemplateRef = "orderdbSqlSessionTemplate")

public class OrderdbSourceConfig {

    @Bean

    @ConfigurationProperties(prefix = "spring.datasource.druid.orderdb")

    public DataSource orderdbDataSource() {

        return DruidDataSourceBuilder.create().build();

    }

    @Bean

    public SqlSessionFactory orderdbSqlSessionFactory(@Qualifier("orderdbDataSource") DataSource dataSource) throws Exception {

        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

        bean.setDataSource(dataSource);

        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/orderdb/*.xml"));

        return bean.getObject();

    }

    @Bean

    public DataSourceTransactionManager orderdbTransactionManager(@Qualifier("orderdbDataSource") DataSource dataSource) {

        return new DataSourceTransactionManager(dataSource);

    }

    @Bean

    public SqlSessionTemplate orderdbSqlSessionTemplate(@Qualifier("orderdbSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {

        return new SqlSessionTemplate(sqlSessionFactory);

    }

}

说明：用basePackages指定mapper程序所在目录,

bean.setMapperLocations指定mapper的xml文件所在目录

主要是把两个数据源对应的mapper接口程序和mapper的xml文件隔离开

3,GoodsMapper.java

@Repository

@Mapper

public interface GoodsMapper {

    Goods selectOneGoods(Long goodsId);

}

4,OrderMapper.java

@Repository

@Mapper

public interface OrderMapper {

    Order selectOneOrder(Long orderId);

}

5,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.multidruid.demo.mapper.goodsdb.GoodsMapper">

    <select id="selectOneGoods" parameterType="long" resultType="com.multidruid.demo.pojo.Goods">

        select * from goods where goodsId=#{goodsId}

    </select>

</mapper>

6,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.multidruid.demo.mapper.orderdb.OrderMapper">

    <select id="selectOneOrder" parameterType="long" resultType="com.multidruid.demo.pojo.Order">

        select * from orderinfo where orderId=#{orderId}

    </select>

</mapper>

7,HomeController.java

@Controller

@RequestMapping("/home")

public class HomeController {

    @Resource

    private GoodsMapper goodsMapper;

    @Resource

    private OrderMapper orderMapper;

    //商品详情 参数:商品id

    @GetMapping("/goodsinfo")

    @ResponseBody

    public Goods goodsInfo(@RequestParam(value="goodsid",required = true,defaultValue = "0") Long goodsId) {

        Goods goods = goodsMapper.selectOneGoods(goodsId);

        return goods;

    }

    //订单详情 参数:订单id

    @GetMapping("/orderinfo")

    @ResponseBody

    public Order orderInfo(@RequestParam(value="orderid",required = true,defaultValue = "0") Long orderId) {

        Order order = orderMapper.selectOneOrder(orderId);

        return order;

    }

}

8，Goods/Order 两个pojo类很简单，不列出了，大家可以访问github查看

五，测试效果:

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.2.RELEASE)

巴特西