SpringBoot实战（三）:整合Mybatis配置多数据源

Myth丶恋晨 2022-03-27 07:16 299阅读 0赞

### 【前言】 ###

最近接到一个新需求，经过分析后做了相应的设计；其中需要在一个项目中操做不同的数据源；于是进行了相关验证；在此记录一下验证过程。

### 【实战多数据源】 ###

**         一、Pom中引入相应的Jar包**

<dependency>
           <groupId>mysql</groupId>
           <artifactId>mysql-connector-java</artifactId>
           <version>5.1.39</version>
    </dependency>
    
    
    <dependency>
           <groupId>org.mybatis.spring.boot</groupId>
           <artifactId>mybatis-spring-boot-starter</artifactId>
           <version>1.3.2</version>
    </dependency>

**         二、主要代码展示**

1、application.properties增加数据库连接的相关配置

#****************************用户数据库***************************
    user.datasource.url=jdbc:mysql://127.0.0.1:3306/zh_user?useUnicode=true&zeroDateTimeBehavior=convertToNull&characterEncoding=UTF-8
    user.datasource.username=root
    user.datasource.password=root
    user.datasource.connectionTimeout=30000
    user.datasource.idleTimeout=600000
    user.datasource.maxLifetime=1800000
    user.datasource.maximumPoolSize=10
    user.datasource.minimumIdle=5
    #****************************订单数据库***************************
    order.datasource.url=jdbc:mysql://127.0.0.1:3306/zh_order?useUnicode=true&zeroDateTimeBehavior=convertToNull&characterEncoding=UTF-8
    order.datasource.username=root
    order.datasource.password=root
    order.datasource.connectionTimeout=30000
    order.datasource.idleTimeout=600000
    order.datasource.maxLifetime=1800000
    order.datasource.maximumPoolSize=10
    order.datasource.minimumIdle=5

2、启动类中排除DataSourceAutoConfiguration和MybatisAutoConfiguration

package com.zhanghan.zhboot;
    
    import org.mybatis.spring.boot.autoconfigure.MybatisAutoConfiguration;
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
    
    @SpringBootApplication(exclude = {
            DataSourceAutoConfiguration.class,
            MybatisAutoConfiguration.class})
    public class ZhBootApplication {
    
        public static void main(String[] args) {
            SpringApplication.run(ZhBootApplication.class, args);
        }
    
    }

3、数据库属性读取类（以UserDataSourceProperties为例）

package com.zhanghan.zhboot.properties;
    
    import lombok.Data;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.stereotype.Component;
    
    
    @Component
    @Data
    @ConfigurationProperties(prefix = "user.datasource")
    public class UserDataSourceProperties {
    
        private String url;
    
        private String username;
    
        private String password;
    
        private Integer connectionTimeout;
    
        private Integer idleTimeout;
    
        private Integer maxLifetime;
    
        private Integer maximumPoolSize;
    
        private Integer minimumIdle;
    
    
    
    }

4、数据源配置器（以UserDataSourceConfig为例）---注：数据库连接池用的是springboot2.0后默认的hikariCP（性能高）

package com.zhanghan.zhboot.config;
    
    import com.zaxxer.hikari.HikariConfig;
    import com.zaxxer.hikari.HikariDataSource;
    import com.zhanghan.zhboot.properties.UserDataSourceProperties;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    
    import javax.sql.DataSource;
    
    @Configuration
    // 扫描 Mapper 接口并容器管理
    @MapperScan(basePackages = UserDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "userSqlSessionFactory")
    public class UserDataSourceConfig {
    
        static final String PACKAGE = "com.zhanghan.zhboot.mybatis.mapper.user";
    
        @Autowired
        private UserDataSourceProperties userDataSourceProperties;
    
    
        @Bean(name = "userDataSource")
        public DataSource userDataSource() {
            HikariConfig config = new HikariConfig();
            config.setJdbcUrl(userDataSourceProperties.getUrl());
            config.setUsername(userDataSourceProperties.getUsername());
            config.setPassword(userDataSourceProperties.getPassword());
            config.setConnectionTestQuery("select 1");
            config.setConnectionTimeout(userDataSourceProperties.getConnectionTimeout());
            config.setIdleTimeout(userDataSourceProperties.getIdleTimeout());
            config.setMaxLifetime(userDataSourceProperties.getMaxLifetime());
            config.setMaximumPoolSize(userDataSourceProperties.getMaximumPoolSize());
            config.setMinimumIdle(userDataSourceProperties.getMinimumIdle());
            return new HikariDataSource(config);
        }
    
        @Bean(name = "userTransactionManager")
        public DataSourceTransactionManager userTransactionManager() {
            return new DataSourceTransactionManager(userDataSource());
        }
    
        @Bean(name = "userSqlSessionFactory")
        public SqlSessionFactory userSqlSessionFactory(
                @Qualifier("userDataSource") DataSource userDataSource)
                throws Exception {
            final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(userDataSource);
            return sessionFactory.getObject();
        }
    }

5、相应的mapper文件

package com.zhanghan.zhboot.mybatis.mapper.user;
    
    import com.zhanghan.zhboot.mybatis.entity.UserInfoEntity;
    import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.annotations.Result;
    import org.apache.ibatis.annotations.Results;
    import org.apache.ibatis.annotations.Select;
    import org.apache.ibatis.type.JdbcType;
    
    @Mapper
    public interface UserInfoMapper {
    
        @Select({
                "select",
                "id, no, mobile, name, sex, create_time, update_time, param1, param2, param3, ",
                "param4, param5, param6",
                "from user_info",
                "where mobile = #{mobile,jdbcType=VARCHAR}",
                "limit 1"
        })
        @Results({
                @Result(column="id", property="id", jdbcType= JdbcType.BIGINT, id=true),
                @Result(column="no", property="no", jdbcType=JdbcType.VARCHAR),
                @Result(column="mobile", property="mobile", jdbcType=JdbcType.VARCHAR),
                @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
                @Result(column="sex", property="sex", jdbcType=JdbcType.INTEGER),
                @Result(column="create_time", property="createTime", jdbcType=JdbcType.TIMESTAMP),
                @Result(column="update_time", property="updateTime", jdbcType=JdbcType.TIMESTAMP),
                @Result(column="param1", property="param1", jdbcType=JdbcType.INTEGER),
                @Result(column="param2", property="param2", jdbcType=JdbcType.INTEGER),
                @Result(column="param3", property="param3", jdbcType=JdbcType.INTEGER),
                @Result(column="param4", property="param4", jdbcType=JdbcType.VARCHAR),
                @Result(column="param5", property="param5", jdbcType=JdbcType.VARCHAR),
                @Result(column="param6", property="param6", jdbcType=JdbcType.VARCHAR)
        })
        UserInfoEntity findByMobile(String mobile);
    }

6、相应的Service

package com.zhanghan.zhboot.service.impl;
    
    import com.zhanghan.zhboot.controller.request.OrderRequest;
    import com.zhanghan.zhboot.mybatis.entity.OrderInfoEntity;
    import com.zhanghan.zhboot.mybatis.entity.UserInfoEntity;
    import com.zhanghan.zhboot.mybatis.mapper.order.OrderInfoMapper;
    import com.zhanghan.zhboot.mybatis.mapper.user.UserInfoMapper;
    import com.zhanghan.zhboot.service.OrderService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.util.ObjectUtils;
    
    @Service
    public class OrderServiceImpl implements OrderService {
    
        @Autowired
        private UserInfoMapper userInfoMapper;
        @Autowired
        private OrderInfoMapper orderInfoMapper;
    
        @Override
        public Object findOrder(OrderRequest orderRequest) {
            UserInfoEntity userInfoEntity = userInfoMapper.findByMobile(orderRequest.getMobile());
            if (ObjectUtils.isEmpty(userInfoEntity)) {
                return null;
            }
    
            OrderInfoEntity orderInfoEntity = orderInfoMapper.findByUserNo(userInfoEntity.getNo());
            return orderInfoEntity;
        }
    }

7、相应的Controller

package com.zhanghan.zhboot.controller;
    
    import com.zhanghan.zhboot.controller.request.OrderRequest;
    import com.zhanghan.zhboot.service.OrderService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestBody;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.RestController;
    
    @RestController
    public class OrderController {
    
        @Autowired
        private OrderService orderService;
    
        @RequestMapping(value = "/get/order/borrow", method = RequestMethod.POST)
        public Object getOrderBorrow(@RequestBody OrderRequest orderRequest) {
            return orderService.findOrder(orderRequest);
        }
    }

**         三、效果图**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI4MjkxMjQ_size_16_color_FFFFFF_t_70][]

**        五、项目地址及代码版本**

1、地址：[https://github.com/dangnianchuntian/springboot][https_github.com_dangnianchuntian_springboot]

2、代码版本：1.0.0-Release

### 【总结】 ###

1、实践出真知；

2、技术是为业务服务；

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI4MjkxMjQ_size_16_color_FFFFFF_t_70]: /images/20220327/93623a72422c4b12b0adcfdf11473b24.png
[https_github.com_dangnianchuntian_springboot]: https://github.com/dangnianchuntian/springboot