Springboot 2.7.0 + Mybatis搭建多数据源

最新推荐文章于 2024-08-09 18:07:53 发布

我知道了恩

最新推荐文章于 2024-08-09 18:07:53 发布

阅读量542

点赞数

本文链接：https://blog.csdn.net/weixin_34848129/article/details/125372289

版权

java 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

本文介绍了如何在Spring Boot应用中使用两个独立的MySQL数据库（db_a和db_b），创建表、插入数据，并配置A/B数据源，包括ADataSourceConfig和BDataSourceConfig的详细配置。还展示了如何通过MyBatis映射XML定义DAO操作，以及在Controller中进行简单测试的例子。

摘要由CSDN通过智能技术生成

项目目录

建库

create schema db_a;
create schema db_b;

建表

db_a 执行

create table order_info
(
    id          int auto_increment comment '主键'
        primary key,
    create_time datetime null
);

INSERT INTO db_a.order_info (id, create_time) VALUES (1, '2022-06-20 11:44:30');

db_b 执行

create table user_info
(
    id          int auto_increment comment '主键'
        primary key,
    create_time datetime null
);

INSERT INTO db_b.user_info (id, create_time) VALUES (2, '2022-06-20 11:44:39');

application.properties

#A数据库
spring.datasource.a.jdbc-url=jdbc:mysql://localhost:3306/db_a?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false
spring.datasource.a.username=root
spring.datasource.a.password=12345678
spring.datasource.a.driver-class-name=com.mysql.cj.jdbc.Driver

#B数据库
spring.datasource.b.jdbc-url=jdbc:mysql://localhost:3306/db_b?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false
spring.datasource.b.username=root
spring.datasource.b.password=12345678
spring.datasource.b.driver-class-name=com.mysql.cj.jdbc.Driver

数据源配置

ADataSourceConfig

package com.duzj.multipledatasources.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
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;

/**
 * @Description
 * @Date 2022/6/20 11:52
 * @Created by duzengjie
 */
@Configuration
// 指定主数据库扫描对应的Mapper文件，生成代理对象
@MapperScan(basePackages ="com.duzj.multipledatasources.dao.a" ,sqlSessionFactoryRef = "aSqlSessionFactory")
public class ADataSourceConfig {
    /**
     * xml路径
     */
    private static final String MAPPER_LOCATION = "classpath*:mapper/a/*.xml";
    /**
     * dao层路径
     */
    private static final String TYPE_ALIASES_PACKAGE = "com.duzj.multipledatasources.dao.a";

    /**
     * @Primary 代表默认数据源
     */
    @Primary
    @Bean(name = "aDataSource")
    // 读取application.properties中的spring.datasource.a
    @ConfigurationProperties(prefix = "spring.datasource.a")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * @Primary 代表默认 事务管理器
     */
    @Bean(name = "aTransactionManager")
    @Primary
    public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("aDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     *  @Primary 代表默认 session工厂
     */
    @Bean(name = "aSqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("aDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setVfs(SpringBootVFS.class);
        //这必须重新设置 不然会报错
        sessionFactoryBean.setTypeAliasesPackage(TYPE_ALIASES_PACKAGE);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        return sessionFactoryBean.getObject();
    }

}

BDataSourceConfig

package com.duzj.multipledatasources.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
 * @Description
 * @Date 2022/6/20 11:55
 * @Created by duzengjie
 */

@Configuration
@MapperScan(basePackages = "com.duzj.multipledatasources.dao.b", sqlSessionFactoryRef = "bSqlSessionFactory")
public class BDataSourceConfig {
    /**
     * xml路径
     */
    private static final String MAPPER_LOCATION = "classpath*:mapper/b/*.xml";
    /**
     * dao层路径
     */
    private static final String TYPE_ALIASES_PACKAGE = "com.duzj.multipledatasources.dao.b";

    /**
     * 数据源
     */
    @Bean(name = "bDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.b")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }


    /**
     * 事务管理器
     */
    @Bean(name = "bTransactionManager")
    public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("bDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * session工厂
     */
    @Bean(name = "bSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("bDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setVfs(SpringBootVFS.class);
        sessionFactoryBean.setTypeAliasesPackage(TYPE_ALIASES_PACKAGE);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        return sessionFactoryBean.getObject();
    }

}

实体类

OrderInfo

package com.duzj.multipledatasources.entity.a;

import java.io.Serializable;
import java.util.Date;
import lombok.Data;

/**
 * order_info
 * @author 
 */
@Data
public class OrderInfo implements Serializable {
    /**
     * 主键
     */
    private Integer id;

    private Date createTime;

    private static final long serialVersionUID = 1L;
}

UserInfo

package com.duzj.multipledatasources.entity.b;

import java.io.Serializable;
import java.util.Date;
import lombok.Data;

/**
 * user_info
 * @author 
 */
@Data
public class UserInfo implements Serializable {
    /**
     * 主键
     */
    private Integer id;

    private Date createTime;

    private static final long serialVersionUID = 1L;
}

dao层

OrderInfoDao

package com.duzj.multipledatasources.dao.a;

import com.duzj.multipledatasources.entity.a.OrderInfo;

public interface OrderInfoDao {
    int deleteByPrimaryKey(Integer id);

    int insert(OrderInfo record);

    int insertSelective(OrderInfo record);

    OrderInfo selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(OrderInfo record);

    int updateByPrimaryKey(OrderInfo record);
}

UserInfoDao

package com.duzj.multipledatasources.dao.b;

import com.duzj.multipledatasources.entity.b.UserInfo;

public interface UserInfoDao {
    int deleteByPrimaryKey(Integer id);

    int insert(UserInfo record);

    int insertSelective(UserInfo record);

    UserInfo selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(UserInfo record);

    int updateByPrimaryKey(UserInfo record);
}

xml

OrderInfoDao.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.duzj.multipledatasources.dao.a.OrderInfoDao">
  <resultMap id="BaseResultMap" type="com.duzj.multipledatasources.entity.a.OrderInfo">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
  </resultMap>
  <sql id="Base_Column_List">
    id, create_time
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from order_info
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from order_info
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.duzj.multipledatasources.entity.a.OrderInfo" useGeneratedKeys="true">
    insert into order_info (create_time)
    values (#{createTime,jdbcType=TIMESTAMP})
  </insert>
  <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.duzj.multipledatasources.entity.a.OrderInfo" useGeneratedKeys="true">
    insert into order_info
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="createTime != null">
        create_time,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="createTime != null">
        #{createTime,jdbcType=TIMESTAMP},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.duzj.multipledatasources.entity.a.OrderInfo">
    update order_info
    <set>
      <if test="createTime != null">
        create_time = #{createTime,jdbcType=TIMESTAMP},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.duzj.multipledatasources.entity.a.OrderInfo">
    update order_info
    set create_time = #{createTime,jdbcType=TIMESTAMP}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>

UserInfoDao.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.duzj.multipledatasources.dao.b.UserInfoDao">
  <resultMap id="BaseResultMap" type="com.duzj.multipledatasources.entity.b.UserInfo">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
  </resultMap>
  <sql id="Base_Column_List">
    id, create_time
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from user_info
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from user_info
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.duzj.multipledatasources.entity.b.UserInfo" useGeneratedKeys="true">
    insert into user_info (create_time)
    values (#{createTime,jdbcType=TIMESTAMP})
  </insert>
  <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.duzj.multipledatasources.entity.b.UserInfo" useGeneratedKeys="true">
    insert into user_info
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="createTime != null">
        create_time,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="createTime != null">
        #{createTime,jdbcType=TIMESTAMP},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.duzj.multipledatasources.entity.b.UserInfo">
    update user_info
    <set>
      <if test="createTime != null">
        create_time = #{createTime,jdbcType=TIMESTAMP},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.duzj.multipledatasources.entity.b.UserInfo">
    update user_info
    set create_time = #{createTime,jdbcType=TIMESTAMP}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>

controller测试

package com.duzj.multipledatasources.controller;

import com.duzj.multipledatasources.dao.a.OrderInfoDao;
import com.duzj.multipledatasources.dao.b.UserInfoDao;
import com.duzj.multipledatasources.entity.a.OrderInfo;
import com.duzj.multipledatasources.entity.b.UserInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * @Description
 * @Date 2022/6/20 13:33
 * @Created by duzengjie
 */
@RestController
public class TestController {

    @Autowired
    private UserInfoDao userInfoDao;

    @Autowired
    private OrderInfoDao orderInfoDao;

    @GetMapping("/test")
    public void test() {
        UserInfo userInfo = userInfoDao.selectByPrimaryKey(2);
        OrderInfo orderInfo = orderInfoDao.selectByPrimaryKey(1);
        System.out.println(userInfo);
        System.out.println(orderInfo);
    }
}

下载链接

https://download.csdn.net/download/weixin_34848129/85708001