springboot+mybatis多数据源并处理传入的sql语句

最新推荐文章于 2023-08-25 17:16:30 发布

lost程序猿

最新推荐文章于 2023-08-25 17:16:30 发布

阅读量1.7k

点赞数

配置文件

server:
  port: 8080

spring:
  aop:
    proxy-target-class: true
    auto: true
  datasource:
    druid:
      # 数据库 1
      db1:
        url: jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true
        username: root
        password: jiazhangjia217
        driver-class-name: com.mysql.jdbc.Driver
        initialSize: 5
        minIdle: 5
        maxActive: 20
      # 数据库 2
      db2:
        url: jdbc:mysql://localhost:3306/db2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true
        username: root
        password: jiazhangjia217
        driver-class-name: com.mysql.jdbc.Driver
        initialSize: 5
        minIdle: 5
        maxActive: 20
#全局日志级别
logging:
  level:
    root: info

mybatis-plus:
  #mybatis日志打印
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

mybatis配置类，源切换配置类不做太多赘述，底部有源码链接，可下载查看

package com.warm.config.mybatis;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.MybatisConfiguration;
import com.baomidou.mybatisplus.entity.GlobalConfiguration;
import com.baomidou.mybatisplus.mapper.LogicSqlInjector;
import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.plugins.PerformanceInterceptor;
import com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean;
import com.warm.common.DBTypeEnum;
import com.warm.common.DynamicDataSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * @Author DGD
 * @date 2018/2/6.
 */
@Configuration
@MapperScan({"com.warm.system.mapper"})
public class MybatisPlusConfig {

    /**
     * mybatis-plus分页插件<br>
     * 文档：http://mp.baomidou.com<br>
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        paginationInterceptor.setLocalPage(true);// 开启 PageHelper 的支持
        return paginationInterceptor;
    }

    /**
     * mybatis-plus SQL执行效率插件【生产环境可以关闭】
     */
    @Bean
    public PerformanceInterceptor performanceInterceptor() {
        return new PerformanceInterceptor();
    }

    @Bean(name = "db1")
    @ConfigurationProperties(prefix = "spring.datasource.druid.db1" )
    public DataSource db1 () {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "db2")
    @ConfigurationProperties(prefix = "spring.datasource.druid.db2" )
    public DataSource db2 () {
        return DruidDataSourceBuilder.create().build();
    }
    /**
     * 动态数据源配置
     * @return
     */
    @Bean
    @Primary
    public DataSource multipleDataSource (@Qualifier("db1") DataSource db1,
                                          @Qualifier("db2") DataSource db2 ) {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        Map< Object, Object > targetDataSources = new HashMap<>();
        targetDataSources.put(DBTypeEnum.db1.getValue(), db1 );
        targetDataSources.put(DBTypeEnum.db2.getValue(), db2);
        dynamicDataSource.setTargetDataSources(targetDataSources);
        dynamicDataSource.setDefaultTargetDataSource(db1);
        return dynamicDataSource;
    }

    @Bean("sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(multipleDataSource(db1(),db2()));

        MybatisConfiguration configuration = new MybatisConfiguration();
        //configuration.setDefaultScriptingLanguage(MybatisXMLLanguageDriver.class);
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setCacheEnabled(false);
        sqlSessionFactory.setConfiguration(configuration);
        sqlSessionFactory.setPlugins(new Interceptor[]{ //PerformanceInterceptor(),OptimisticLockerInterceptor()
                paginationInterceptor()
        });
        sqlSessionFactory.setGlobalConfig(globalConfiguration());
        //设置xml文件路径
        sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:Mapper/*.xml"));
        return sqlSessionFactory.getObject();
    }

    @Bean
    public GlobalConfiguration globalConfiguration() {
        GlobalConfiguration conf = new GlobalConfiguration(new LogicSqlInjector());
        conf.setLogicDeleteValue("1");
        conf.setLogicNotDeleteValue("0");
        conf.setIdType(0);
        conf.setMetaObjectHandler(new MyMetaObjectHandler());
        conf.setDbColumnUnderline(true);
        conf.setRefresh(true);
        return conf;
    }



}

此处注意，xml文件路径一定要配置成功，mapper.java和mapper.xml一定要相同名字，否则后期会报如下错误

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.warm.system.mapper.UserMapper.listAllDesc
   at org.apache.ibatis.binding.MapperMethod$SqlCommand.<init>(MapperMethod.java:225)
   at org.apache.ibatis.binding.MapperMethod.<init>(MapperMethod.java:48)
   at org.apache.ibatis.binding.MapperProxy.cachedMapperMethod(MapperProxy.java:65)
   at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58)
   at com.sun.proxy.$Proxy82.listAllDesc(Unknown Source)
   at com.warm.system.service.impl.UserServiceImpl.listAllDesc(UserServiceImpl.java:44)
   at com.warm.system.service.impl.UserServiceImpl$$FastClassBySpringCGLIB$$95c70d98.invoke(<generated>)
   at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
   at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738)

下方是swagger配置类，接口查看工具

package com.warm.config.mybatis;

import com.google.common.base.Predicates;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.service.Contact;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

@Configuration
@EnableSwagger2
public class Swagger2Config {

    /**
     * swagger插件
     * http://192.168.1.6:8080/swagger-ui.html 访问
     * @return
     */
    @Bean
    public Docket adminApiConfig() {
        return new Docket(DocumentationType.SWAGGER_2)
                .groupName("测试Api")//组名
                .apiInfo(adminApiInfo())// 调用apiInfo方法,创建一个ApiInfo实例,里面是展示在文档页面信息内容
                .select()//开启选择
                .paths(Predicates.not(PathSelectors.regex("/admin/.*")))//排除admin包下的所有接口方法
                .paths(Predicates.not(PathSelectors.regex("/error.*")))//排除error下的多所有接口方法
                .build();
    }
    private ApiInfo adminApiInfo() {
        return new ApiInfoBuilder()
                .title("测试")//大标题
                .description("此文档描述了测试用到的所有服务接口")//详细描述
                .version("1.0")//版本
                .contact(new Contact("sayiamfun", "http://192.168.1.6:8080", "liwenjieemail@163.com"))//作者
                .build();
    }
}

controller层

package com.warm.system.controller;


import com.warm.system.entity.Order;
import com.warm.system.entity.User;
import com.warm.system.service.db1.UserService;
import com.warm.system.service.db2.OrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.math.BigDecimal;
import java.util.List;

/**
 * <p>
 * 前端控制器
 * </p>
 *
 * @author dgd123
 * @since 2018-02-10
 */
@RestController
public class IndexController {
    @Autowired
    private UserService userService;
    @Autowired
    private OrderService orderService;

    @GetMapping("/user")
    public Object getUserList() {
        return userService.getUserList();
    }

    @GetMapping("/order")
    public Object getOrderList() {
        return orderService.getOrderList();
    }

    @GetMapping("/price")
    public Object getPrice() {
        return orderService.getOrderPriceByUserId(1);
    }

    @GetMapping("/price2")
    public Object getPrice2() {
        return userService.getOrderPriceByUserId(1);
    }

    @GetMapping("userDesc")
    public Object listAllDesc() {
        return userService.listAllDesc();
    }
}

serviceImpl层

package com.warm.system.service.impl;

import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import com.warm.common.DBTypeEnum;
import com.warm.common.DataSourceSwitch;
import com.warm.system.entity.User;
import com.warm.system.mapper.OrderMapper;
import com.warm.system.mapper.UserMapper;
import com.warm.system.service.db1.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.List;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author dgd123
 * @since 2018-02-10
 */
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
    @Autowired
    private OrderMapper orderMapper;
    @Autowired
    private UserMapper userMapper;
    @Override
    public List<User> getUserList() {
        return selectList(null);
    }

    @DataSourceSwitch(DBTypeEnum.db2)
    @Override
    public BigDecimal getOrderPriceByUserId(Integer userId) {
        return orderMapper.getPriceByUserId(userId);
    }

    @Override
    public Object listAllDesc() {
        String sql = "select * from user order by id desc";
        return userMapper.listAllDesc(sql);
    }
}

注意：此处sql为测试语句，可根据你的实际需要动态拼接而成传入

dao层

package com.warm.system.mapper;

import com.baomidou.mybatisplus.mapper.Wrapper;
import com.warm.system.entity.User;
import com.baomidou.mybatisplus.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author dgd123
 * @since 2018-02-10
 */
@Mapper
public interface UserMapper extends BaseMapper<User> {

    List<User> listAllDesc(@Param("sql") String sql);
}

注意：此处加@Param注解可在xml文件中使用自定义的变量名字，当不加注解切传入参数数量大于2时，xml文件中使用参数需使用param1，param2...以此类推

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.warm.system.mapper.UserMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.warm.system.entity.User">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="age" property="age" />
        <result column="create_time" property="createTime" />
        <result column="modify_time" property="modifyTime" />
    </resultMap>

    <select id="listAllDesc" resultMap="BaseResultMap" parameterType="java.lang.String">
        ${sql}
    </select>

</mapper>

注意：此处只能使用${sql}的形式，用#{sql}会造成给执行的sql语句加入单引号，造成SQL语句报错

程序结构