SpringBoot+Mybatis实现多数据源

SpringBoot+Mybatis实现多数据源

在开发中，尤其是现在的互联网分布式项目，不可能是只是，简单的一个数据库(数据库中的几张表)就可以解决项目的需求问题，往往项目中会使用多个数据库，可以是同种Mysql数据库的中几个 DataBases，也可能是多种数据库的 Mysql、Oracle 中的 Databases,这时候在项目中就会出现多个数据源，这时候，我们应该如何配置呢？下面就是我在SpringBoot中的一个简单实现。

开发环境

• windowns10
• jdk8
• IDEA
• Maven-3.5.4
• Mysql

在 Mysql 数据库中创建 student、teacher 库，和 student、teacher 表如下：

-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sno` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(255) DEFAULT NULL,
  `sex` char(255) DEFAULT NULL,
  `dept` varchar(255) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', 'jiuyue', '1', '计算机科学与技术', '2019-05-10', '18');

---

-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tno` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(255) DEFAULT NULL,
  `sex` char(255) DEFAULT NULL,
  `dept` varchar(255) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`tno`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', 'september', '1', '物联网', '2019-05-10', '19');

新建工程,pom赖如下

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.47</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

全局配置文件

# 公共配置
server:
  port: 80
  tomcat:
    uri-encoding: UTF-8
spring:
  profiles:
    active: dev
  datasource:
    student:
      driver-class-name: com.mysql.cj.jdbc.Driver
      type: com.alibaba.druid.pool.DruidDataSource
    teacher:
      driver-class-name: com.mysql.cj.jdbc.Driver
      type: com.alibaba.druid.pool.DruidDataSource
    druid:
      validationQuery: SELECT 1 FROM DUAL
      initialSize: 10
      minIdle: 10
      maxActive: 200
      minEvictableIdleTimeMillis: 180000
      testOnBorrow: false
      testWhileIdle: true
      removeAbandoned: true
      removeAbandonedTimeout: 1800
      logAbandoned: true
      poolPreparedStatements: true
      maxOpenPreparedStatements: 100
      filters: stat,wall,log4j
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
#mybatis
mybatis:
  type-aliases-package: com.jiuyue.multidatasource.entity
  configuration:
    map-underscore-to-camel-case: true
---
#开发配置
server:
  port: 8088
spring:
  profiles:
    active: dev
  datasource:
    student:
      url: jdbc:mysql://127.0.0.1:3306/student?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8
      username: root
      password: 1111
    teacher:
      url: jdbc:mysql://127.0.0.1:3306/teacher?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8
      username: root
      password: 1111
---

创建Druid配置类

在工程java代码目录下创建 config 的 package 在下面创建DruidDBConfig.

@Configuration
public class DruidDBConfig {
    @Bean
    public ServletRegistrationBean druidServlet(){
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean();
        servletRegistrationBean.setServlet(new StatViewServlet());
        servletRegistrationBean.addUrlMappings("/druid/*");
        //设置控制台管理用户
        servletRegistrationBean.addInitParameter("loginUsername","root");
        servletRegistrationBean.addInitParameter("loginPassword","pass");
        // 禁用HTML页面上的“Reset All”功能
        servletRegistrationBean.addInitParameter("resetEnable","false");
        //reg.addInitParameter("allow", "127.0.0.1"); //白名单
        return servletRegistrationBean;
    }
    @Bean
    public FilterRegistrationBean filterRegistrationBean(){
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        Map<String, String> initParams = new HashMap<String, String>();
        //忽略过滤的形式
        initParams.put("exclusions", "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*");
        filterRegistrationBean.setInitParameters(initParams);
        //设置过滤器过滤路径
        filterRegistrationBean.addUrlPatterns("/*");
        return filterRegistrationBean;
    }
}

创建Student数据源配置类

在工程java代码目录下config中创建StudentDataSourceConfig类.

@Configuration
@MapperScan(basePackages = "com.jiuyue.multidatasource.dao.student",sqlSessionFactoryRef = "studentSqlSessionFactory")
public class StudentDataSourceConfig {

    @Value("${spring.datasource.student.type}")
    private Class<? extends DataSource> dataSourceType;

    //初始化连接池
    @Bean(name = "studentDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.student")
    @Primary
    public DataSource writeDataSource(){
        return DataSourceBuilder.create().type(dataSourceType).build();
    }

    //构建 SqlSessionFactory
    @Bean(name = "studentSqlSessionFactory")
    @Primary
    public SqlSessionFactory studentSqlSessionFactory(@Qualifier("studentDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setVfs(SpringBootVFS.class);
        return bean.getObject();
    }
    //配置事务管理
    @Bean(name = "studentTransactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(@Qualifier("studentDataSource") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    //构建 SqlSessionTemplate
    @Bean(name = "studentSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("studentSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

创建teacher数据源配置类

在工程java代码目录下config中创建TeacherDataSourceConfig类.

@Configuration
@MapperScan(basePackages = "com.jiuyue.multidatasource.dao.teacher",sqlSessionFactoryRef = "teacherSqlSessionFactory")
public class TeacherDataSourceConfig {

    @Value("${spring.datasource.teacher.type}")
    private Class<? extends DataSource> dataSourceType;

    @Bean(name = "teacherDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.teacher")
    public DataSource writeDataSource(){
        return DataSourceBuilder.create().type(dataSourceType).build();
    }

    @Bean(name = "teacherSqlSessionFactory")
    public SqlSessionFactory teacherSqlSessionFactory(@Qualifier("teacherDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setVfs(SpringBootVFS.class);
        return bean.getObject();
    }

    @Bean(name = "teacherTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("teacherDataSource") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "teacherSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("teacherSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

创建实体,首先创建entity包

Student实现类

@Data
@EqualsAndHashCode(callSuper = false)
public class Student {
    private Integer sno;
    private String sname;
    private String sex;
    private String birth;
    private String age;
    private String dept;
}

Teacher实体类

@Data
@EqualsAndHashCode(callSuper = false)
public class Teacher {
    private Integer tno;
    private String tname;
    private String sex;
    private String birth;
    private String age;
    private String dept;
}

创建Service，首先创建service包

StudentService

public interface StudentService {
    List<Student> getStudentList();
}

TeacherService

public interface TeacherService {
    List<Teacher> getTeacherList();
}

ServiceImpl实现类

StudentServiceImpl

@Service
public class StudentServiceImpl implements StudentService {

    @Autowired
    private StudentDao studentDao;

    @Override
    public List<Student> getStudentList() {
        return studentDao.getStudentList();
    }
}

TeacherServiceImpl

@Service
public class TeacherServiceImpl implements TeacherService {
    @Autowired
    private TeacherDao teacherDao;

    @Override
    public List<Teacher> getTeacherList() {
        return teacherDao.getTeacherList();
    }
}

创建Dao,studentDao放在student包下，TeacherDao放在teacher包下

StudentDao

public interface StudentDao {
    @Select("select * from student")
    List<Student> getStudentList();
}

TeacherDao

public interface TeacherDao {
    @Select("select * from teacher")
    List<Teacher> getTeacherList();
}

控制器

StudentConteroller

@Controller
@RequestMapping("/student")
public class StudentConteroller {
    @Autowired
    private StudentService studentService;

    @ResponseBody
    @GetMapping("/list")
    public Object list(){
        List<Student> studentList = studentService.getStudentList();
        return studentList;
    }
}

TeacherConteroller

@Controller
@RequestMapping("/teacher")
public class TeacherConteroller {
    @Autowired
    private TeacherService teacherService;

    @ResponseBody
    @GetMapping("/list")
    public Object list(){
        List<Teacher> teacherList = teacherService.getTeacherList();
        return teacherList;
    }
}

启动项目进行测试：

http://localhost:8088/student/list
[{"sno":1,"sname":"jiuyue","sex":"1","birth":"2019-05-10","age":"18","dept":"计算机科学与技术"}]
-----------------------------------------------------------------------------------------
http://localhost:8088/teacher/list
[{"tno":1,"tname":"september","sex":"1","birth":"2019-05-10","age":"19","dept":"物联网"}]