最近项目中用到多数据源，经过查询调研，把相关功能实现，现在做个简单的小总结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
网友评论