美文网首页
springboot+mybatis整合多数据源

springboot+mybatis整合多数据源

作者: 穿越沙漠的马兰花 | 来源:发表于2021-12-24 10:43 被阅读0次

最近项目中用到多数据源，经过查询调研，把相关功能实现，现在做个简单的小总结Demo，希望后续有用多数据源的小伙伴少走弯路，废话不多说，直接开干。

整体结构：

整体架构图

一、创建sql脚本：

首先创建两个数据库：master、slave

然后master里面创建user表，slave里面创建city表

CREATE TABLE `user` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户编号',

  `user_name` varchar(25) DEFAULT NULL COMMENT '用户名称',

  `description` varchar(25) DEFAULT NULL COMMENT '描述',

  PRIMARY KEY (`id`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

CREATE TABLE `city` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '城市编号',

  `province_id` int(10) unsigned NOT NULL COMMENT '省份编号',

  `city_name` varchar(25) DEFAULT NULL COMMENT '城市名称',

  `description` varchar(25) DEFAULT NULL COMMENT '描述',

  PRIMARY KEY (`id`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

z自己随便写入两条数据就好；

二、

（1）bean：

/**

* @author yjack

* @Des

* @date 12/23/21 9:02 AM

*/

@Data

public class User {

private int id;

private StringuserName;

private  Stringdescription;

private Citycity;

}

package com.atjack.bean;

import lombok.Data;

/**

* @author yjack

* @Des

* @date 12/23/21 9:02 AM

*/

@Data

public class City {

private int id;

private int provinceId;

private StringcityName;

private Stringdescription;

}

（2）dao：分别建立master、slave包

userDao放master 、cityDao放slave里面

package com.atjack.dao.master;

import com.atjack.bean.User;

import org.apache.ibatis.annotations.Mapper;

import org.springframework.stereotype.Repository;

import java.util.List;

/**

* @author yjack

* @Des

* @date 12/23/21 9:05 AM

*/

@Mapper

@Repository

public interface UserDao {

User findByName(String userName);

List findAll();

List selectAll();

}

package com.atjack.dao.slave;

import com.atjack.bean.City;

import org.apache.ibatis.annotations.Mapper;

import org.springframework.stereotype.Repository;

import java.util.List;

/**

* @author yjack

* @Des

* @date 12/23/21 9:06 AM

*/

@Mapper

@Repository

public interface CityDao {

City findByName(String cirtName);

List findAll();

}

（3）service：

package com.atjack.service;

import com.atjack.bean.User;

/**

* @author yjack

* @Des

* @date 12/23/21 9:11 AM

*/

public interface UserService {

User findByName(String userName);

}

package com.atjack.service;

import com.atjack.bean.City;

/**

* @author yjack

* @Des

* @date 12/23/21 9:12 AM

*/

public interface CityService {

City findByName(String cirtName);

}

（4）impl

package com.atjack.serviceimpl;

import com.atjack.bean.User;

import com.atjack.dao.master.UserDao;

import com.atjack.service.UserService;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

/**

* @author yjack

* @Des

* @date 12/23/21 9:12 AM

*/

@Service

public class UserServiceImplimplements UserService {

@Autowired

    private UserDaouserDao;

public User findByName(String userName) {

return userDao.findByName(userName);

}

}

package com.atjack.serviceimpl;

import com.atjack.bean.City;

import com.atjack.dao.slave.CityDao;

import com.atjack.service.CityService;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

/**

* @author yjack

* @Des

* @date 12/23/21 9:13 AM

*/

@Service

public class CityServiceImplimplements CityService {

@Autowired

    private CityDaocityDao;

public City findByName(String cityName) {

return cityDao.findByName(cityName);

}

}

5、controller

package com.atjack.controller;

import com.atjack.bean.City;

import com.atjack.bean.User;

import com.atjack.dao.master.UserDao;

import com.atjack.dao.slave.CityDao;

import com.atjack.service.CityService;

import com.atjack.service.UserService;

import lombok.extern.slf4j.Slf4j;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**

* @author yjack

* @Des

* @date 12/23/21 9:50 AM

*/

@RestController

@Slf4j

public class UserController {

@Autowired

    private UserServiceuserService;

@Autowired

    private CityServicecityService;

@Autowired

    private CityDaocityDao;

@Autowired

    private UserDaouserDao;

@RequestMapping(value ="/findByName")

public User findByName(String userName) {

User user =userService.findByName(userName);

log.info("===" + user);

City city =cityService.findByName("北京市");

user.setCity(city);

log.info("===" + user);

return user;

}

@RequestMapping("findAllCity")

public List findAllCity() {

return cityDao.findAll();

}

@RequestMapping(value ="/findAllUser")

public List findUser() {

return userDao.findAll();

}

@RequestMapping(value ="/selectAll")

public List selectAll() {

return userDao.selectAll();

}

}

三、配置文件（最重要）

1、

application.properties：

## master 数据源配置

spring.master.datasource.url=jdbc:mysql://localhost:3306/master?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=false

spring.master.datasource.username=root

spring.master.datasource.password=root

spring.master.datasource.driver-class-name=com.mysql.jdbc.Driver

## slave 数据源配置

spring.slave.datasource.url=jdbc:mysql://localhost:3306/slave?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=false

spring.slave.datasource.username=root

spring.slave.datasource.password=root

spring.slave.datasource.driver-class-name=com.mysql.jdbc.Driver

mybatis.configuration.map-underscore-to-camel-case=true

mybatis.type-aliases-package=com.atjack.bean

2、config配置：

（1）MasterDataSourceConfig：

package com.atjack.config;

import com.alibaba.druid.pool.DruidDataSource;

import org.apache.ibatis.session.SqlSessionFactory;

import org.mybatis.spring.SqlSessionFactoryBean;

import org.mybatis.spring.annotation.MapperScan;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.beans.factory.annotation.Value;

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;

/**

* @author yjack

* @Des

* @date 12/23/21 8:42 AM

*/

@Configuration

@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef ="masterSqlSessionFactory")

public class MasterDataSourceConfig {

// 精确到 slave 目录，以便跟其他数据源隔离

    static final StringPACKAGE ="com.atjack.dao.master";

static final StringMAPPER_LOCATION ="classpath:master/*.xml";

@Value("${spring.master.datasource.url}")

private Stringurl;

@Value("${spring.master.datasource.username}")

private Stringuser;

@Value("${spring.master.datasource.password}")

private Stringpassword;

@Value("${spring.master.datasource.driver-class-name}")

private StringdriverClass;

@Bean(name ="masterDataSource")

@Primary

    public DataSource masterDataSource() {

DruidDataSource dataSource =new DruidDataSource();

dataSource.setDriverClassName(driverClass);

dataSource.setUrl(url);

dataSource.setUsername(user);

dataSource.setPassword(password);

return dataSource;

}

@Bean(name ="masterTransactionManager")

@Primary

    public DataSourceTransactionManager masterTransactionManager() {

return new DataSourceTransactionManager(masterDataSource());

}

@Bean(name ="masterSqlSessionFactory")

@Primary

    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)

throws Exception {

final SqlSessionFactoryBean sessionFactory =new SqlSessionFactoryBean();

sessionFactory.setDataSource(masterDataSource);

sessionFactory.setMapperLocations(

new PathMatchingResourcePatternResolver().getResources(MasterDataSourceConfig.MAPPER_LOCATION));

return sessionFactory.getObject();

}

}

（2）SlaveDataSourceConfig：

package com.atjack.config;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;

import org.mybatis.spring.SqlSessionFactoryBean;

import org.mybatis.spring.annotation.MapperScan;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.beans.factory.annotation.Value;

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 com.alibaba.druid.pool.DruidDataSource;

/**

* @author yjack

* @Des

* @date 12/23/21 8:49 AM

*/

@Configuration

// 扫描 Mapper 接口并容器管理

@MapperScan(basePackages = SlaveDataSourceConfig.PACKAGE, sqlSessionFactoryRef ="slaveSqlSessionFactory")

public class SlaveDataSourceConfig {

// 精确到 slave 目录，以便跟其他数据源隔离

    static final StringPACKAGE ="com.atjack.dao.slave";

static final StringMAPPER_LOCATION ="classpath:slave/*.xml";

@Value("${spring.slave.datasource.url}")

private Stringurl;

@Value("${spring.slave.datasource.username}")

private Stringuser;

@Value("${spring.slave.datasource.password}")

private Stringpassword;

@Value("${spring.slave.datasource.driver-class-name}")

private StringdriverClass;

@Bean(name ="slaveDataSource")

public DataSource slaveDataSource() {

DruidDataSource dataSource =new DruidDataSource();

dataSource.setDriverClassName(driverClass);

dataSource.setUrl(url);

dataSource.setUsername(user);

dataSource.setPassword(password);

return dataSource;

}

@Bean(name ="slaveTransactionManager")

public DataSourceTransactionManager slaveTransactionManager() {

return new DataSourceTransactionManager(slaveDataSource());

}

@Bean(name ="slaveSqlSessionFactory")

public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource slaveDataSource)

throws Exception {

final SqlSessionFactoryBean sessionFactory =new SqlSessionFactoryBean();

sessionFactory.setDataSource(slaveDataSource);

sessionFactory.setMapperLocations(

new PathMatchingResourcePatternResolver().getResources(SlaveDataSourceConfig.MAPPER_LOCATION));

return sessionFactory.getObject();

}

}

3、mapper映射文件

在resouces目录新建两个包master、slave 为了区分两个数据库操作

UserMapper.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.atjack.dao.master.UserDao">

<sql id="query_id">

<if test="userName != null and userName !='' " >

and user_name = #{userName}

</if>

</sql>

<resultMap id="result" type="com.atjack.bean.User">

<id column="id" property="id"></id>

<result column="user_name" property="userName"></result>

<result column="description" property="description"></result>

</resultMap>

<select id="findByName" parameterType="com.atjack.bean.User" resultMap="result">

select* from user where user_name = #{userName}

</select>

<select id="findAll" resultMap="result">

select* from user

</select>

<select id="selectAll" resultMap="result">

select* from user

</select>

</mapper>

CityMapper.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.atjack.dao.slave.CityDao">

<resultMap id="resultMap" type="com.atjack.bean.City">

<id column="id" property="id"></id>

<result column="province_id" property="provinceId"></result>

<result column="city_name" property="cityName"></result>

<result column="description" property="description"></result>

</resultMap>

<select id="findByName" resultMap="resultMap">

select* from city where city_name = #{cityName}

</select>

<select id="findAll" resultMap="resultMap">

select* from city

</select>

</mapper>

OK,现在请求一下就好，端口号

http://localhost:8080/findByName?userName=tom

请求结果

http://localhost:8080/findAllCity

http://localhost:8080/findAllUser

相关文章

网友评论

      本文标题：springboot+mybatis整合多数据源

      本文链接：https://www.haomeiwen.com/subject/gdrlqrtx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|springboot+mybatis整合多数据源|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！