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

用户2781897

发布于 2020-11-02 11:27:11

4400

发布于 2020-11-02 11:27:11

文章被收录于专栏：服务端思维服务端思维

【前言】

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

【实战多数据源】

一、Pom中引入相应的Jar包

<!-- mysql 基础服务-->
<dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <version>5.1.39</version>
</dependency>

<!-- mybatis -->
<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);
    }
}

三、效果图