SpringBoot集成多个DataSource数据源及分页等问题处理原理分析

JingAi_jia917

已于 2024-04-25 10:51:23 修改

阅读量1.2k

点赞数 19

文章标签： spring boot java mybatis Datasource MySql multiDataSource Mybatis-plus

于 2024-03-22 16:43:51 首次发布

本文链接：https://blog.csdn.net/JingAi_jia917/article/details/136935631

版权

背景

在项目中，需要连接多个数据库。以下以连接两个MySql数据库为例，其他类型数据库也是相同的处理方式。

准备

准备两个数据库，分别为test1和test2，两个数据库中都只有一个表。

# test1数据库中的tb_member表
CREATE TABLE `tb_member`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `age` int(0) NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

# test2数据库的tb_user表
CREATE TABLE `tb_user`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `age` int(0) NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

代码实现

1、针对每个DataSource，创建一个配置类

1.1 test1数据源的配置类Test1DataSourceConfig.java

package com.jingai.multidatasource.config;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
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 org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.jingai.multidatasource.test1.dao", sqlSessionTemplateRef = "test1SqlSessionTemplate")
public class Test1DataSourceConfig {

    @Bean("test1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.test1")
    @Primary
    public DataSource test1DataSource() {
        // return DataSourceBuilder.create().build();
        // spring.datasource.test1开头的配置项中没有jdbc-url，所以此处不能直接使用DataSourceBuilder创建DataSource
        return DruidDataSourceBuilder.create().build();
    }

    @Bean("test1SqlSessionFactory")
    @Primary
    public SqlSessionFactory test1SqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        // 如果不设置MybatisConfiguration()，默认使用原生Mybatis
        bean.setConfiguration(new MybatisConfiguration());
        bean.setDataSource(dataSource);
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        bean.setPlugins(new Interceptor[]{mybatisPlusInterceptor});
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/test1/*.xml"));
        return bean.getObject();
    }

    @Bean("test1TransactionManager")
    @Primary
    public DataSourceTransactionManager test1TransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean("test1SqlSessionTemplate")
    @Primary
    public SqlSessionTemplate test1SqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

注意：

1）DataSourceBuilder创建的DataSource为HikariDataSource，该DataSource是通过jdbc-url配置项创建Connection。在HikariConfig.validate()中进行校验，如果没有配置jdbc-url，报throw new IllegalArgumentException("jdbcUrl is required with driverClassName.")。

2）如果在创建SqlSessionFactory的bean中没有setConfiguration(new MybatisConfiguration())，则创建的SqlSession为原生的Mybatis，不支持Mybatis-plus的功能及分页。具体原理，在后文分析。

1.2 test2数据源的配置类Test2DataSourceConfig.java

package com.jingai.multidatasource.config;

import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.jingai.multidatasource.test2.dao", sqlSessionTemplateRef = "test2SqlSessionTemplate")
public class Test2DataSourceConfig {

    @Bean("test2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.test2")
    @Primary
    public DataSource test2DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean("test2SqlSessionFactory")
    @Primary
    public SqlSessionFactory test2SqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setConfiguration(new MybatisConfiguration());
        bean.setDataSource(dataSource);
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        bean.setPlugins(new Interceptor[]{mybatisPlusInterceptor});
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/test2/*.xml"));
        return bean.getObject();
    }

    @Bean("test2TransactionManager")
    @Primary
    public DataSourceTransactionManager test2TransactionManager(@Qualifier("test2DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean("test2SqlSessionTemplate")
    @Primary
    public SqlSessionTemplate test2SqlSessionTemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

Test2DataSourceConfig.java和Test1DataSourceConfig.java整体逻辑都一样。
1）指定一个@MapperScan的包路径，即对应的Mapper类的包名。对应的包名下的Mapper都是使用该DataSource连接访问；
2）分别为不同的数据库创建对应的DataSource、SqlSessionFactory、SqlSessionTemplate和DataSourceTransactionManager。在DataSource中使用@ConfigurationProperties(prefix = "spring.datasource.test2")标签，指定对应数据库连接的配置项前缀；
3）在SqlSessionFactory中指定对应的mapper的xml文件路径；

2、添加Entity类

package com.jingai.multidatasource.entity;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.ToString;

import java.util.Date;

@Data
@ToString
@TableName("tb_member")
public class MemberEntity {

    private int id;

    private String name;

    private int age;

    private Date createTime;
}

package com.jingai.multidatasource.entity;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.ToString;

import java.util.Date;

@Data
@ToString
@TableName("tb_user")
public class UserEntity {

    private int id;

    private String name;

    private int age;

    private Date createTime;
}

3、添加Mapper类

3.1 在Test1DataSourceConfig.java中指定test1数据库的Mapper包名为com.jingai.multidatasource.test1.dao

package com.jingai.multidatasource.test1.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jingai.multidatasource.entity.MemberEntity;

public interface MemberMapper extends BaseMapper<MemberEntity> {
}

3.2 在Test2DataSourceConfig.java中指定test2数据库的Mapper包名为com.jingai.multidatasource.test2.dao

package com.jingai.multidatasource.test2.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jingai.multidatasource.entity.UserEntity;

public interface UserMapper extends BaseMapper<UserEntity> {
}

4、添加Service类

package com.jingai.multidatasource.service;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
import com.jingai.multidatasource.entity.UserEntity;

public interface UserService extends IService< UserEntity> {

    IPage<UserEntity> listByPage(Integer pageIndex, Integer pageSize);

}

package com.jingai.multidatasource.service;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
import com.jingai.multidatasource.entity.MemberEntity;

public interface MemberService extends IService<MemberEntity> {

    IPage<MemberEntity> listByPage(Integer pageIndex, Integer pageSize);
}

添加对应实现类

package com.jingai.multidatasource.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.jingai.multidatasource.entity.MemberEntity;
import com.jingai.multidatasource.service.MemberService;
import com.jingai.multidatasource.test1.dao.MemberMapper;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;

@Service
public class MemberServiceImpl extends ServiceImpl<MemberMapper, MemberEntity> implements MemberService {

    @Resource
    private MemberMapper memberMapper;

    public IPage<MemberEntity> listByPage(Integer pageIndex, Integer pageSize) {
        LambdaQueryWrapper<MemberEntity> queryWrapper = new LambdaQueryWrapper<MemberEntity>();
        queryWrapper.orderByDesc(MemberEntity::getId);
        Page<MemberEntity> page = new Page<>(pageIndex, pageSize);
        return memberMapper.selectPage(page, queryWrapper);
    }

}

package com.jingai.multidatasource.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.jingai.multidatasource.entity.UserEntity;
import com.jingai.multidatasource.service.UserService;
import com.jingai.multidatasource.test2.dao.UserMapper;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, UserEntity> implements UserService {

    @Resource
    private UserMapper userMapper;

    public IPage<UserEntity> listByPage(Integer pageIndex, Integer pageSize) {
        LambdaQueryWrapper<UserEntity> queryWrapper = new LambdaQueryWrapper<UserEntity>();
        queryWrapper.orderByDesc(UserEntity::getId);
        Page<UserEntity> page = new Page<>(pageIndex, pageSize);
        return userMapper.selectPage(page, queryWrapper);
    }

}

5、添加Controller

package com.jingai.multidatasource.controller;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.jingai.multidatasource.entity.MemberEntity;
import com.jingai.multidatasource.entity.UserEntity;
import com.jingai.multidatasource.service.MemberService;
import com.jingai.multidatasource.service.UserService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;

@RestController
@RequestMapping("member")
public class MemberController {

    @Resource
    private MemberService memberService;

    @Resource
    private UserService userService;

    @GetMapping("finduser")
    public IPage<UserEntity> findUser() {
        IPage<UserEntity> page = userService.listByPage(1, 20);
        return page;
    }

    @GetMapping("findmember")
    public IPage<MemberEntity> findMember() {
        IPage<MemberEntity> page = memberService.listByPage(1, 20);
        return page;
    }

}

启动项目后，即可正常的访问两个数据库中的数据。

在添加数据源配置类中提到，在创建SqlSessionFactory的bean时，如果不调用setConfiguration(new MybatisConfiguration())，访问如上的接口时，系统会报org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.jingai.multidatasource.test2.dao.UserMapper.selectPage。我们从源码来一探究竟。

问题源码分析

1 org.apache.ibatis.binding.BindingException: Invalid bound statement (not found)分析

上面异常的完整信息为

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.jingai.multidatasource.test2.dao.UserMapper.selectPage
	at org.apache.ibatis.binding.MapperMethod$SqlCommand.<init>(MapperMethod.java:235) ~[mybatis-3.5.6.jar:3.5.6]

通过异常信息，可以发现是在MapperMethod.java:235行报错的。在这一行对应的代码为（只截取关键代码）

public static class SqlCommand {

    private final String name;
    private final SqlCommandType type;

    public SqlCommand(Configuration configuration, Class<?> mapperInterface, Method method) {
      final String methodName = method.getName();
      final Class<?> declaringClass = method.getDeclaringClass();
      // 从异常信息可知，获取对应Mapper的方法没有对应的sql声明
      MappedStatement ms = resolveMappedStatement(mapperInterface, methodName, declaringClass,
          configuration);
      if (ms == null) {
        if (method.getAnnotation(Flush.class) != null) {
          name = null;
          type = SqlCommandType.FLUSH;
        } else {
          // 抛出异常的位置，说明ms为null，且方法中没有添加@Flush注解。
          // selectPage()方法并没有添加该注解，所以只要ms为null就会抛出该异常
          throw new BindingException("Invalid bound statement (not found): "
              + mapperInterface.getName() + "." + methodName);
        }
      } else {
        name = ms.getId();
        type = ms.getSqlCommandType();
        if (type == SqlCommandType.UNKNOWN) {
          throw new BindingException("Unknown execution method for: " + name);
        }
      }
    }
}

再来跟踪一下resolveMappedStatement()方法，代码如下：

private MappedStatement resolveMappedStatement(Class<?> mapperInterface, String methodName,
        Class<?> declaringClass, Configuration configuration) {
      // 通过mapper接口名称加点再加方法名称来唯一标识一个Sql声明。
      // 如此处是com.jingai.multidatasource.test2.dao.UserMapper.selectPage
      String statementId = mapperInterface.getName() + "." + methodName;
      // 以下代码是从Configuration的Map<String, MappedStatement> mappedStatements中判断是否有key值为statementId的MappedStatement
      if (configuration.hasStatement(statementId)) {
        return configuration.getMappedStatement(statementId);
      // 如果当前的接口类是定义方法的类，那么就返回null
      } else if (mapperInterface.equals(declaringClass)) {
        return null;
      }
      // 从父类中递归调用判断是否有对应的selectPage的MappedStatement
      for (Class<?> superInterface : mapperInterface.getInterfaces()) {
        if (declaringClass.isAssignableFrom(superInterface)) {
          // 递归调用
          MappedStatement ms = resolveMappedStatement(superInterface, methodName,
              declaringClass, configuration);
          if (ms != null) {
            return ms;
          }
        }
      }
      return null;
    }

SqlSessionFactoryBean初始化时【在SqlSessionFactoryBean.afterPropertiesSet()方法】，会调用buildSqlSessionFactory()，然后调用XMLMapperBuilder.parse()

1）调用configurationElement(XNode context)解析mapper.xml文件，查找带select|insert|update|delete的标签的信息，最终封装成MappedStatement对象，添加到Configuration的mappedStatements对象；

2）调用bindMapperForNamespace() -> Configuration.addMapper(Class type) 【configuration.addMapper(boundType)】-> MapperRegistry.addMapper(Class type)【mapperRegistry.addMapper(type)】 -> new MapperAnnotationBuilder(config, type)，执行parser.parse()。在该方法中，遍历Mapper类的方法，调用parseStatement(Method method)方法，最终封装成MappedStatement对象，添加到Configuration的mappedStatements对象；

3）如果是Mybatis-plus，Mybatis-plus创建了MybatisConfiguration继承Configuration、MybatisMapperRegistry继承MapperRegistry，重写了addMapper(Class type)，再该方法中，new MybatisMapperAnnotationBuilder(config, type)【MybatisMapperAnnotationBuilder继承MapperAnnotationBuilder】，同样执行parser.parse()方法，在该方法中，实现同2）中的功能以外，额外增加了parserInjector()的执行。该方法调用最终执行AbstractSqlInjector.inspectInject()方法，自动注入DefaultSqlInjector类中定义的方法信息，即CRUD等方法。

package com.baomidou.mybatisplus.core.injector;

import com.baomidou.mybatisplus.core.injector.methods.*;

import java.util.List;
import java.util.stream.Stream;

import static java.util.stream.Collectors.toList;

/**
 * SQL 默认注入器
 *
 * @author hubin
 * @since 2018-04-10
 */
public class DefaultSqlInjector extends AbstractSqlInjector {

    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
        return Stream.of(
            new Insert(),
            new Delete(),
            new DeleteByMap(),
            new DeleteById(),
            new DeleteBatchByIds(),
            new Update(),
            new UpdateById(),
            new SelectById(),
            new SelectBatchByIds(),
            new SelectByMap(),
            new SelectOne(),
            new SelectCount(),
            new SelectMaps(),
            new SelectMapsPage(),
            new SelectObjs(),
            new SelectList(),
            new SelectPage()
        ).collect(toList());
    }
}

划重点

1）在DataSource的配置类中，创建SqlSessionFactory的bean没有调用setConfiguration(new MybatisConfiguration())，系统默认的Configuration是Mybatis原生的Configuration，不会自动注入DefaultSqlInjector类中定义的方法信息，也就没有SelectPage()方法了；

2）从上面的源码分析中，我们还能够发现，在Mybatis的Mapper类中是不支持重载的，也就是不支持同名的方法。因为mappedStatements对象中的key是类名加方法名来唯一识别的。这点和JPA是不一样的。当然看了这些源码，如果想要执行方法的重载也是能够做到的，只需要类似Mybatis-plus一样，重新创建新的Configuration即可。不过也没有这个必要，因为可以通过Mybatis的动态拼接SQL来实现；

2 org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found:

这个异常出现的场景也是因为创建SqlSessionFactory的bean没有调用setConfiguration(new MybatisConfiguration())，而使用自定义的查询语句，返回IPage对象时报的异常。为什么会这样呢，明明执行的是自定义的查询语句，怎么跑去执行selectOne()了？

从异常信息中，可以定位到出问题的地方在MapperMethod.execute()方法，以下为关键代码

case SELECT:
        if (method.returnsVoid() && method.hasResultHandler()) {
          executeWithResultHandler(sqlSession, args);
          result = null;
        } else if (method.returnsMany()) {
          result = executeForMany(sqlSession, args);
        } else if (method.returnsMap()) {
          result = executeForMap(sqlSession, args);
        } else if (method.returnsCursor()) {
          result = executeForCursor(sqlSession, args);
        } else {
          // 由于分页的返回值是IPage，不属于上面的类型，所以自然就只能执行else了
          // 而分页返回的数据不止一条，所以就报错了
          Object param = method.convertArgsToSqlCommandParam(args);
          result = sqlSession.selectOne(command.getName(), param);
          if (method.returnsOptional()
              && (result == null || !method.getReturnType().equals(result.getClass()))) {
            result = Optional.ofNullable(result);
          }
        }
        break;

如果添加了setConfiguration(new MybatisConfiguration())，那么查询时，会执行MybatisMapperMethod.execute()。以下为关键代码：

case SELECT:
                if (method.returnsVoid() && method.hasResultHandler()) {
                    executeWithResultHandler(sqlSession, args);
                    result = null;
                } else if (method.returnsMany()) {
                    result = executeForMany(sqlSession, args);
                } else if (method.returnsMap()) {
                    result = executeForMap(sqlSession, args);
                } else if (method.returnsCursor()) {
                    result = executeForCursor(sqlSession, args);
                } else {
                    Object param = method.convertArgsToSqlCommandParam(args);
                    // TODO 这里下面改了
                    if (IPage.class.isAssignableFrom(method.getReturnType())) {
                        result = executeForIPage(sqlSession, args);
                        // TODO 这里上面改了
                    } else {
                        result = sqlSession.selectOne(command.getName(), param);
                        if (method.returnsOptional()
                            && (result == null || !method.getReturnType().equals(result.getClass()))) {
                            result = Optional.ofNullable(result);
                        }
                    }
                }
                break;

看到这个源码，大家就明白为何Mybatis-plus就可以了支持分页了。

如果是单个数据源，也就无需添加DataSource配置类。在mybatis-plus-boot-starter包中会自动注入MybatisPlusAutoConfiguration，在MybatisPlusAutoConfiguration类中的sqlSessionFactory(DataSource dataSource)方法中【方法添加了@ConditionalOnMissingBean注解，即如果定义返回了SqlSessionFactory，那么MybatisPlusAutoConfiguration中就不会再创建】，调用了applyConfiguration(factory)，在applyConfiguration(factory)方法中，获取MybatisConfiguration对象，并赋值给MybatisSqlSessionFactoryBean对象。