SpringBoot入门之基于Druid配置Mybatis多数据源

上一篇了解了Druid进行配置连接池的监控和慢sql处理，这篇了解下使用基于基于Druid配置Mybatis多数据源。SpringBoot默认配置数据库连接信息时只需设置url等属性信息就可以了，SpringBoot就会基于约定根据配置信息实例化对象，但是一般大型的项目都是有多个子系统或者多个数据源组成，那怎么使用SpringBoot进行Mybatis多数据源配置呢？

一、数据库准备

我们这里准备使用主从两个数据库来进行演示多数据源配置。一个主库用来写write，一个从库用来读read.至于两个数据库的数据同步问题这里暂时不考虑。两个数据库只是数据库名不一样，主库为mybatis1，从库为mybatis,表结构是一样的。

主库(write):

CREATE DATABASE `mybatis1` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

从库(read):

CREATE DATABASE `mybatis` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

二、引入依赖

这里主要引入mysql数据库、mybatis架构、Druid相关的SpringBoot依赖。下面的是由于要使用jsp显示内容所以也假如了jsp相关的依赖。

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>demo</name>
    <description>Demo project for Spring Boot</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>

    </properties>

    <dependencies>
    
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>



        <!-- https://mvnrepository.com/artifact/org.thymeleaf/thymeleaf-spring5 
        <dependency>
            <groupId>org.thymeleaf</groupId>
            <artifactId>thymeleaf-spring5</artifactId>
            <version>3.0.9.RELEASE</version>
        </dependency>
         -->

       <dependency>
            <groupId>org.apache.tomcat.embed</groupId>
            <artifactId>tomcat-embed-jasper</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>
    
        <dependency>
           <groupId>com.alibaba</groupId>
           <artifactId>druid-spring-boot-starter</artifactId>
           <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>


</project>

三、创建Mapper

从这里开始就比较坑了，为了写这篇博客昨天搞到夜里两点中，Druid官方介绍的比较少，demo也不是与mybatis相结合，就倒置怎么把mapper与数据源配置对照上也是问题，因为默认单数据源的话，配置下数据源信息以及Mapper就好，但是如果是多数据源那就要手动指定数据源在哪里，怎么和Mapper对照上。

这里先创建两个Mappe，一个是写的一个是读的。这里要注意的地方是要加上@Mapper注解。

ReadUserMapper:

package com.example.read.mapper;
import java.util.List;
import com.example.model.User;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
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.annotations.Update;

@Mapper
public interface  ReadUserMapper {

    @Select("SELECT name FROM user")

    @Results({

        @Result(property = "Name", column = "name")

    })

    List<User> getAll();

    

    @Select("SELECT name FROM user WHERE id = #{id}")

    @Results({

        @Result(property = "Name", column = "name")

    })

    User getOne(int id);



    @Insert("INSERT INTO user(name,age) VALUES(#{name}, #{age})")

    void insert(User user);

    @Update("UPDATE user SET name=#{name},age=#{age} WHERE id =#{id}")

    void update(User user);

    @Delete("DELETE FROM user WHERE id =#{id}")

    void delete(int id);
}

WriteUserMapper：

package com.example.write.mapper;
import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
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.annotations.Update;

import com.example.model.*;

@Mapper
public interface  WriteUserMapper {

    @Select("SELECT name FROM user")

    @Results({

        @Result(property = "Name", column = "name")

    })

    List<User> getAll();

    @Select("SELECT name FROM user WHERE id = #{id}")

    @Results({

        @Result(property = "Name", column = "name")

    })

    User getOne(int id);

    @Insert("INSERT INTO user(name,age) VALUES(#{name}, #{age})")

    void insert(User user);


    @Update("UPDATE user SET name=#{name},age=#{age} WHERE id =#{id}")

    void update(User user);


    @Delete("DELETE FROM user WHERE id =#{id}")

    void delete(int id);
}

四、配置数据源

如果使用SpringBoot默认配置类,可以直接在application.properties中配置就好了,它会自动扫描mapper类与数据源进行关联，但是如果是多个数据源的话，那就需要进行手动配置。这里分别创建了读DataSourceReadConfig、写DataSourceWriteConfig数据源配置类。

DataSourceWriteConfig:

package com.example.config;



import javax.sql.DataSource;

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.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;

@Configuration
@MapperScan(basePackages = "com.example.write.mapper", sqlSessionTemplateRef  = "writeSqlSessionTemplate")
public class DataSourceWriteConfig {
    @Bean(name = "writeDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid.write")
    @Qualifier("writeDataSource")
    @Primary
    public DataSource writeDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "writeSqlSessionFactory")
    @Primary
    public SqlSessionFactory writeSqlSessionFactory(@Qualifier("writeDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    @Bean(name = "writeTransactionManager")
    @Primary
    public DataSourceTransactionManager writeTransactionManager(@Qualifier("writeDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "writeSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate writeSqlSessionTemplate(@Qualifier("writeSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

DataSourceReadConfig:

package com.example.config;



import javax.sql.DataSource;

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.jdbc.datasource.DataSourceTransactionManager;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;

@Configuration
@MapperScan(basePackages = "com.example.read.mapper", sqlSessionTemplateRef  = "readSqlSessionTemplate")
public class DataSourceReadConfig {
    @Bean(name = "readDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid.read")
    @Qualifier("readDataSource")
    public DataSource readDataSource() {
        return DruidDataSourceBuilder.create().build();
    }
 
    @Bean(name = "readSqlSessionFactory")
    
    public SqlSessionFactory readSqlSessionFactory(@Qualifier("readDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource((javax.sql.DataSource) dataSource);
        return bean.getObject();
    }

    @Bean(name = "readTransactionManager")
   
    public DataSourceTransactionManager readTransactionManager(@Qualifier("readDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "readSqlSessionTemplate")
    
    public SqlSessionTemplate readSqlSessionTemplate(@Qualifier("readSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

 这部分是遇到坑最多的地方，由于Druid官方github上并没有具体参考的demo,看其他的博客又与gitgub介绍的有出入，比如获取配置类中获取DataSource方法中，其他我看使用的是DataSourceBuilder，但Druid GitHub上的是DruidDataSourceBuilder,不知道是不是版本的问题，如果使用DataSourceBuilder，配置多数据库时不起作用。github上也有这句话:Spring Boot 2.X 版本不再支持配置继承，多数据源的话每个数据源的所有配置都需要单独配置，否则配置不会生效。还有就是DataSource引入的包名，我开始引入的并不是import javax.sql.DataSource;这个也是一个坑。

五、Druid多数据源配置

这里也遇到了坑，由于在配置数据源类中并未使用DruidDataSourceBuilder，而是使用的DataSourceBuilder，这就导致下面配置的没用，而且在设置数据库url还报错，需要使用jdbc-url.

spring.mvc.view.prefix=/view/

spring.mvc.view.suffix=.jsp
mybatis.type-aliases-package=com.example.model
#mybatis.config-location=classpath:mybatis/mybatis-config.xml
#mybatis.mapper-locations=classpath:mybatis/mapper/*.xml


spring.datasource.druid.read.web-stat-filter.enabled=true
spring.datasource.druid.read.web-stat-filter.url-pattern=/*
spring.datasource.druid.read.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
spring.datasource.druid.read.web-stat-filter.session-stat-enable=true
spring.datasource.druid.read.web-stat-filter.session-stat-max-count=1000
spring.datasource.druid.read.stat-view-servlet.enabled= true
spring.datasource.druid.read.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.read.stat-view-servlet.reset-enable=true
spring.datasource.druid.read.stat-view-servlet.login-username=druid
spring.datasource.druid.read.stat-view-servlet.login-password=123456
spring.datasource.druid.read.stat-view-servlet.allow=127.0.0.1
spring.datasource.druid.read.stat-view-servlet.deny=192.168.0.19
spring.datasource.druid.read.aop-patterns=com.example.read.mapper.*

spring.datasource.druid.read.url =jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.druid.read.username = root
spring.datasource.druid.read.password = 123456
spring.datasource.druid.read.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.read.type=com.alibaba.druid.pool.DruidDataSource


spring.datasource.druid.write.max-active=20
spring.datasource.druid.write.initial-size=1
spring.datasource.druid.write.max-wait=60000
spring.datasource.druid.write.pool-prepared-statements=true
spring.datasource.druid.write.max-pool-prepared-statement-per-connection-size=20
spring.datasource.druid.write.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
spring.datasource.druid.write.min-idle=1
spring.datasource.druid.write.time-between-eviction-runs-millis=60000
spring.datasource.druid.write.min-evictable-idle-time-millis=300000
spring.datasource.druid.write.validation-query=select 1 from dual
spring.datasource.druid.write.test-while-idle=true
spring.datasource.druid.write.test-on-borrow=true
spring.datasource.druid.write.test-on-return=true

spring.datasource.druid.write.web-stat-filter.enabled=true
spring.datasource.druid.write.web-stat-filter.url-pattern=/*
spring.datasource.druid.write.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
spring.datasource.druid.write.web-stat-filter.session-stat-enable=true
spring.datasource.druid.write.web-stat-filter.session-stat-max-count=1000
spring.datasource.druid.write.stat-view-servlet.enabled= true
spring.datasource.druid.write.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.write.stat-view-servlet.reset-enable=true
spring.datasource.druid.write.stat-view-servlet.login-username=druid
spring.datasource.druid.write.stat-view-servlet.login-password=123456
spring.datasource.druid.write.stat-view-servlet.allow=127.0.0.1
spring.datasource.druid.write.stat-view-servlet.deny=192.168.0.19
spring.datasource.druid.write.url =jdbc:mysql://127.0.0.1:3306/mybatis1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.druid.write.aop-patterns=com.example.write.mapper.*
spring.datasource.druid.write.username = root
spring.datasource.druid.write.password = 123456
spring.datasource.druid.write.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.write.type=com.alibaba.druid.pool.DruidDataSource

六、多数据源的使用

这里并未设置Service层，而是直接在Controller中使用。在Controller中会装配一个写的mapper一个读的mapper,分别进行查询和新增操作。

package com.example.demo;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import com.example.model.User;

import com.example.read.mapper.ReadUserMapper;
import com.example.write.mapper.WriteUserMapper;

@Controller
@RequestMapping("/user")
public class UserController {
    
    @Autowired
    private WriteUserMapper userMapperWrite;
    
    @Autowired
    private ReadUserMapper userMapperRead;
    
    @RequestMapping(value = "/alluser.do",method = RequestMethod.GET)
    public String getallusers(Model model) {
       List<User> users=userMapperRead.getAll();
       model.addAttribute("users", users);
       return "userlist";
    }
    @RequestMapping(value = "/insert.do",method = RequestMethod.GET)
    public String adduser(Model model) {
       User user=new User();
       user.setName("cuiyw");
       user.setAge(27);    
       userMapperWrite.insert(user);
       List<User> users=userMapperWrite.getAll();
       model.addAttribute("users", users);
       return "userlist";
    }
}

七、指定数据源配置文件位置

上面基本把配置信息都配置好了，但是如果这样运行还是会报错误,它还是不能找到这个mapper,此时需要在main方法文件增加注解@ComponentScan(basePackages={"com.example.config","com.example.demo"})，让它扫描配置文件的包，然后在配置文件的包里面有配置@MapperScan来查找到mapper。

package com.example.demo;

import org.springframework.boot.SpringApplication;
//import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
//import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;
//@EnableAutoConfiguration(exclude= {DataSourceAutoConfiguration.class})

@ComponentScan(basePackages={"com.example.config","com.example.demo"})
@SpringBootApplication
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
}

 

Description:

Field userMapperWrite in com.example.demo.UserController required a bean of type 'com.example.write.mapper.WriteUserMapper' that could not be found.

Action:
Consider defining a bean of type 'com.example.write.mapper.WriteUserMapper' in your configuration.

八、其他问题

1.这里还遇到404找不到路径的错误，这里还需要在@ComponentScan注解加上Controller对应的包，所以上面代码有@ComponentScan(basePackages={"com.example.config","com.example.demo"})。

This application has no explicit mapping for /error, so you are seeing this as a fallback.
Sun Jul 22 23:58:27 CST 2018
There was an unexpected error (type=Not Found, status=404).
No message available

2.设置手动配置问题

由于开始使用的是DataSourceBuilder，但在application.properties还是使用spring.datasource.druid.read这种方法进行配置，并没spring.datasource.url这样配置，导致报下面的错误。因为使用的是DataSourceBuilder所以SpringBoot还是认为用的默认配置，所以就找spring.datasource.url，此时可以使用@EnableAutoConfiguration(exclude= {DataSourceAutoConfiguration.class})注解来设置手动注解。

九、测试

这里还是分别输入http://localhost:8080/user/alluser.do，http://localhost:8080/user/insert.do，然后查看两个数据库user表的数据是否有没有改变，读数据库数据未变，写数据库数据增加。Druid的数据源监测也是有两条数据源信息。