springboot多数据源mybatis

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

JdbcTemplate可以配置多数据源，MyBatis也可以配置，但是步骤要稍微复杂一些。

准备工作

数据库和建表

create database `multiple_data1` default CHARACTER SET utf8;
use multiple_data1;
create table z_book(
	id int(11) not null auto_increment,
	name varchar(128),
	author varchar(128),
	primary key(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
insert into z_book values (null,'图书1','作者1');

create database `multiple_data2` DEFAULT CHARACTER SET utf8;
use multiple_data2;
create table z_book(
	id int(11) not null auto_increment,
	name varchar(128),
	author varchar(128),
	primary key(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
insert into z_book values (null,'图书2','作者2');

创建项目

springboot多数据源mybatis_java

导入依赖

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</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>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

application.properties中两个数据源的配置

server.port=8099
#数据源1
spring.datasource.one.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.one.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.one.url=jdbc:mysql://localhost:3306/multiple_data1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimeZone=UTC
spring.datasource.one.username=root
spring.datasource.one.password=mysql123
#数据源2
spring.datasource.two.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.two.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.two.url=jdbc:mysql://localhost:3306/multiple_data2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimeZone=UTC
spring.datasource.two.username=root
spring.datasource.two.password=mysql123

DataSourceConfig以及Book实体类

DataSourceConfig.java

package com.shrimpking.config;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/6/6 14:43
 */
@Configuration
public class DataSourceConfig
{
    /**
     * 数据源1
     * @return
     */
    @Bean
    @ConfigurationProperties("spring.datasource.one")
    DataSource dataSourceOne()
    {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * 数据源2
     * @return
     */
    @Bean
    @ConfigurationProperties("spring.datasource.two")
    DataSource dataSourceTwo()
    {
        return DruidDataSourceBuilder.create().build();
    }
}

Book.java

package com.shrimpking.pojo;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/6/6 14:47
 */
public class Book
{
    private int id;
    private String name;
    private String author;

    public Book()
    {
    }

    public Book(String name, String author)
    {
        this.name = name;
        this.author = author;
    }

    public int getId()
    {
        return id;
    }

    public void setId(int id)
    {
        this.id = id;
    }

    public String getName()
    {
        return name;
    }

    public void setName(String name)
    {
        this.name = name;
    }

    public String getAuthor()
    {
        return author;
    }

    public void setAuthor(String author)
    {
        this.author = author;
    }

    @Override
    public String toString()
    {
        return "Book{" + "id=" + id + ", name='" + name + '\'' + ", author='" + author + '\'' + '}';
    }
}

pom.xml加入过滤条件

<resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                    <include>**/*.properties</include>
                </includes>
                <filtering>false</filtering>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.xml</include>
                    <include>**/*.properties</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>

创建MyBatis配置

配置MyBatis，主要提供SqlSessionFactory实例和SqlSessionTemplate实例，代码如下：

MybatisConfigOne.java

package com.shrimpking.config;

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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/6/6 14:51
 */
@Configuration
@MapperScan(value = "com.shrimpking.mapper1",sqlSessionFactoryRef = "sqlSessionFactory1")
public class MybatisConfigOne
{
    @Autowired
    @Qualifier("dataSourceOne")
    private DataSource dataSourceOne;

    @Bean
    SqlSessionFactory sqlSessionFactory1() throws Exception
    {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSourceOne);
        return factoryBean.getObject();
    }

    @Bean
    SqlSessionTemplate sqlSessionTemplate1() throws Exception
    {
        return new SqlSessionTemplate(sqlSessionFactory1());
    }
}

MybatisConfigTwo.java

package com.shrimpking.config;

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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/6/6 14:59
 */
@Configuration
@MapperScan(value = "com.shrimpking.mapper2",sqlSessionFactoryRef = "sqlSessionFactory2")
public class MybatisConfigTwo
{
    @Autowired
    @Qualifier("dataSourceTwo")
    private DataSource dataSourceTwo;

    @Bean
    SqlSessionFactory sqlSessionFactory2() throws Exception
    {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSourceTwo);
        return factoryBean.getObject();
    }

    @Bean
    SqlSessionTemplate sqlSessionTemplate2() throws Exception
    {
        return new SqlSessionTemplate(sqlSessionFactory2());
    }
}

代码解释：

• 在@MapperScan注解中指定Mapper接口所在的位置，同时指定SqlSessionFactory的实例名，则该位置下的Mapper将使用SqlSessionFactory实例。

• 提供SqlSessionFactory的实例，直接创建出来，同时将DataSource的实例设置给SqlSessionFactory，这里创建的SqlSessionFactory实例也就是@MapperScan注解中sqlSessionFactoryRef参数指定的实例。

• 提供一个SqlSessionTemplate实例。这是一个线程安全类，主要用来管理MyBatis中的SqlSession操作。

创建Mapper

分别在com.shrimpking.mapper1和com.shrimpking.mapper2包下创建两个不同的Mapper以及相应的Mapper映射文件，代码如下。

BookMapper1.java

package com.shrimpking.mapper1;

import com.shrimpking.pojo.Book;

import java.util.List;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/6/6 15:04
 */
public interface BookMapper1
{
    List<Book> getAllBooks();
}

BookMapper1.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.shrimpking.mapper1.BookMapper1">

    <select id="getAllBooks" resultType="com.shrimpking.pojo.Book">
        select id,name,author from z_book;
    </select>
</mapper>

BookMapper2.java

package com.shrimpking.mapper2;

import com.shrimpking.pojo.Book;

import java.util.List;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/6/6 15:07
 */
public interface BookMapper2
{
    List<Book> getAllBooks();
}

BookMapper2.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.shrimpking.mapper2.BookMapper2">

    <select id="getAllBooks" resultType="com.shrimpking.pojo.Book">
        select id,name,author from z_book
    </select>

</mapper>

创建Controller

简便起见，这里直接将Mapper注入Controller中，代码如下：

BookController.java

package com.shrimpking.controller;

import com.shrimpking.mapper1.BookMapper1;
import com.shrimpking.mapper2.BookMapper2;
import com.shrimpking.pojo.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/6/6 15:09
 */
@RestController
public class BookController
{
    @Autowired
    private BookMapper1 bookMapper1;
    @Autowired
    private BookMapper2 bookMapper2;

    @GetMapping("/btest")
    public String test()
    {
        List<Book> books1 = bookMapper1.getAllBooks();
        List<Book> books2 = bookMapper2.getAllBooks();

        System.out.println("books1:" + books1);
        System.out.println("books2:" + books2);
        return "Mybatis多数据源，查询成功";
    }
}

测试

springboot多数据源mybatis_sql_02