(可用)SpringBoot+Mybatis配置多数据源

SpringBoot+Mybatis配置多数据源

项目结构如下：

建库建表

1. test1

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` varchar(12) NOT NULL,
  `name` varchar(36) NOT NULL,
  `age` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '张三', '1');
INSERT INTO `student` VALUES ('2', '里斯', '2');
INSERT INTO `student` VALUES ('3', '王五', '3');
INSERT INTO `student` VALUES ('4', '赵六', '4');

2. test2

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for lesson
-- ----------------------------
DROP TABLE IF EXISTS `lesson`;
CREATE TABLE `lesson` (
  `id` varchar(12) NOT NULL,
  `name` varchar(36) NOT NULL,
  `credit` float DEFAULT '0',
  `teacher` varchar(36) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of lesson
-- ----------------------------
INSERT INTO `lesson` VALUES ('1', 'JAVA程序入门', '5', '张三');
INSERT INTO `lesson` VALUES ('2', '高等数学', '5', '赵六');

修改默认配置文件

server:
  port: 8090
spring:
  datasource:
    first:
      driverClassName: com.mysql.cj.jdbc.Driver
      username: root
      password: root
      url: jdbc:mysql://localhost:3306/test1?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8
      type: com.alibaba.druid.pool.DruidDataSource
    second:
      driverClassName: com.mysql.cj.jdbc.Driver
      username: root
      password: root
      url: jdbc:mysql://localhost:3306/test2?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8
      type: com.alibaba.druid.pool.DruidDataSource

由于配置中相当于自定了一套数据源的配置，所以不需要使用yml的spring.datasource

1. 在启动类中去除springboot自动配置数据源的功能

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})

2. 同时yml中也无法通过mybatis配置mapper的位置

#mybatis:
#  mapper-locations: classpath:com/docimp/docimp/mapper/*.xml

• 所以此时就需要在为DataSource创建SessionFactory时直接指定mapper的地址，否则报错Invalid bound statement (not found)

创建相关config

FirstDataSourceConfig

@Configuration
@MapperScan(basePackages = "com.exportppt.demo.dao.first",sqlSessionTemplateRef ="firstSqlSessionTemplate")
public class FirstDataSourceConfig {
    @Value("${spring.datasource.first.url}")
    private String url;
    @Value("${spring.datasource.first.username}")
    private String username;
    @Value("${spring.datasource.first.password}")
    private String password;
    @Value("${spring.datasource.first.driverClassName}")
    private String driverClassName;
    /**本数据源扫描的mapper路径*/
    static final String MAPPER_LOCATION = "classpath:mapper/first/*.xml";


    /**创建数据源*/
    @Bean(name = "firstDS")
    @Primary
    public DataSource getFirstDataSource() {
        DataSource build =  DataSourceBuilder.create()
                .driverClassName(driverClassName)
                .url(url)
                .username(username)
                .password(password)
                .build();
        return build;
    }


    /**创建SessionFactory*/
    @Bean(name = "firstSqlSessionFactory")
    @Primary
    public SqlSessionFactory firstSqlSessionFactory(@Qualifier("firstDS") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //设置mapper配置文件
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        return bean.getObject();
    }

    /**创建事务管理器*/
    @Bean("firstTransactionManger")
    @Primary
    public DataSourceTransactionManager firstTransactionManger(@Qualifier("firstDS") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    /**创建SqlSessionTemplate*/
    @Bean(name = "firstSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate firstSqlSessionTemplate(@Qualifier("firstSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

SecondDataSourceConfig

@Configuration
@MapperScan(basePackages = "com.exportppt.demo.dao.second",sqlSessionTemplateRef ="secondSqlSessionTemplate")
public class SecondDataSourceConfig  {
    @Value("${spring.datasource.second.url}")
    private String url;
    @Value("${spring.datasource.second.username}")
    private String username;
    @Value("${spring.datasource.second.password}")
    private String password;
    @Value("${spring.datasource.second.driverClassName}")
    private String driverClassName;
    /**本数据源扫描的mapper路径*/
    static final String MAPPER_LOCATION = "classpath:mapper/second/*.xml";

    /**创建数据源*/
    @Bean(name = "secondDS")
    public DataSource getSecondDataSource() {
        DataSource build =  DataSourceBuilder.create()
                .driverClassName(driverClassName)
                .url(url)
                .username(username)
                .password(password)
                .build();
        return build;
    }


    /**创建SessionFactory*/
    @Bean(name = "secondSqlSessionFactory")
    public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDS") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //设置mapper配置文件
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        return bean.getObject();
    }

    /**创建事务管理器*/
    @Bean("secondTransactionManger")
    public DataSourceTransactionManager secondTransactionManger(@Qualifier("secondDS") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    /**创建SqlSessionTemplate*/
    @Bean(name = "secondSqlSessionTemplate")
    public SqlSessionTemplate secondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

创建实体类entity

Student

@Data
@Accessors(chain = true)
public class Student implements Serializable {
    private String id;
    private  String name;
    private String age;
}

Lesson

@Data
@Accessors(chain = true)
public class Lesson implements Serializable {
    private String id;
    private String name;
    private String teacher;
    private  float credit;
}

创建controller

MultiController

@RestController
@RequestMapping("/multidb")
public class MultiDbController {
    
    @Autowired
    private StudentService studentService;

    @Autowired
    private LessonService lessonService;

    @GetMapping("/getLesson")
    public List<Lesson> getLessons(){
       return this.lessonService.findById(1);
    }

    @GetMapping("/getStudents")
    public List<Student> getStudents(){
        return this.lessonService.queryStudents();
    }
}

创建service

StudentService

@Service
public class StudentService {
    @Autowired
    private StudentMapper studentMapper;

    public List<Student> queryStudents() {
        return this.studentMapper.queryStudents();
    }
}

LessonService

@Service
public class LessonService {

    @Autowired
    private StudentMapper studentMapper;

    @Autowired
    private LessonMapper lessonMapper;

    public List<Lesson> findById(int i) {
       return this.lessonMapper.findById(i);
    }

    public List<Student> queryStudents() {
        return this.studentMapper.queryStudents();
    }
}

创建dao层

StudentMapper

public interface StudentMapper {
    List<Student> queryStudents();
}

LessonMapper

public interface LessonMapper {
    List<Lesson> findById(int i);
}

创建mapper层，dao层的实现

StudentMapper

<?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.exportppt.demo.dao.first.StudentMapper">
    <resultMap id="studentMap" type="com.exportppt.demo.po.Student">
        <result column="id" property="id" />
        <result column="name" property="id" />
        <result column="age" property="age" />
    </resultMap>
    <select id="queryStudents" resultMap="studentMap">
        SELECT * FROM student
    </select>
</mapper>

LessonMapper

<?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.exportppt.demo.dao.second.LessonMapper">
    <resultMap id="lessonMap" type="com.exportppt.demo.po.Lesson">
        <result column="id" property="id" />
        <result column="name" property="name" />
        <result column="credit" property="credit" />
        <result column="teacher" property="teacher" />
    </resultMap>
    <select id="findById" parameterType="int" resultMap="lessonMap">
        SELECT * FROM lesson WHERE id=#{id}
    </select>
</mapper>