微服务下项目绝对不是传统的单体数据库设计,此时就涉及到了多数据源
项目案例代码上传到git
https://gitee.com/gangye/springboot_more_databases
首先创建一个SpringBoot项目,具体项目结构
引入maven依赖
<?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.gangye</groupId>
<artifactId>springboot_much_database</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.6.RELEASE</version>
</parent>
<dependencies>
<!--<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.19</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
</project>
在application.properties文件中配置数据源的配置,以及mybatis的配置
server.port=8089
# master database
spring.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.master.jdbc-url=jdbc:mysql://localhost:3306/master_test_database?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
spring.datasource.master.username=root
spring.datasource.master.password=ok
# slave database
spring.datasource.slave.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.slave.jdbc-url=jdbc:mysql://localhost:3306/slave_test_database?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
spring.datasource.slave.username=root
spring.datasource.slave.password=ok
#mybatis的相关配置
#mybatis.mapper-locations=classpath:mapper/master/*.xml,classpath:mapper/slave/*.xml
mybatis.config-location=classpath:mybatis-config.xml
其中将url改成jdbc-url,不改的话启动后会连接数据库报错java.lang.IllegalArgumentException: jdbcUrl is required with driverClassName.
有三种方法可以解决这个错误:
具体参考:https://blog.csdn.net/MrLi_IT/article/details/80909078
由于使用了多数据源,不是单一数据源,所以就不能使用默认的框架内的配置,需要自己写配置类,当有一个数据库时,创建一个数据源配置类
package com.gangye.dataSourceConfig;
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.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;
/**
* @Classname DataSourceMasterConfig
* @Description 数据库为master_test_database的数据源配置类,由于必须有一个作为主库,所以主库时必须加上@Primary注解
* @Date 2020/5/26 16:54
* @Created by gangye
*/
@Configuration
@MapperScan(basePackages = "com.gangye.mapper.mapper1",sqlSessionTemplateRef = "primarySqlSessionTemplate")
public class DataSourceMasterConfig {
//mapper扫描xml文件的路径
static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml";
/**
* 配置数据源
* primary是设置优先,因为有多个数据源,在没有明确指定用哪个的情况下,会用带有primary的,这个注解必须有一个数据源要添加
*/
@Bean(name = "primaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.master")
@Primary
public DataSource masterDataSource(){
return DataSourceBuilder.create().build();
}
/**
* 配置SqlSessionFactory
* @param dataSource
* @return
* @throws Exception
*/
@Bean(name = "primarySqlSessionFactory")
@Primary
public SqlSessionFactory masterSqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource) throws Exception{
final SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
return sqlSessionFactoryBean.getObject();
}
/**
* 配置事务管理器
* @param dataSource
* @return
*/
@Bean(name = "primaryTransactionManager")
@Primary
public DataSourceTransactionManager masterTransactionManager(@Qualifier("primaryDataSource") DataSource dataSource){
return new DataSourceTransactionManager(dataSource);
}
/**
* 结合类注解MapperScan配置MapperScannerConfigurer
* @param sqlSessionFactory
* @return
* @throws Exception
*/
@Bean(name = "primarySqlSessionTemplate")
@Primary
public SqlSessionTemplate masterSqlSessionTemplate(@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
为第二个数据库创建配置类
package com.gangye.dataSourceConfig;
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.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;
/**
* @Classname DataSourceSecondConfig
* @Description 数据库为slave_test_database的数据源配置类,其余的数据库类似,非主数据库不需加上@Primary注解
* @Date 2020/5/27 8:44
* @Created by gangye
*/
@Configuration
@MapperScan(basePackages = "com.gangye.mapper.mapper2",sqlSessionTemplateRef = "secondSqlSessionTemplate")
public class DataSourceSecondConfig {
//mapper扫描xml文件的路径
final static String SECOND_DATASOURCE_MAPPER_LOCATION = "classpath:mapper/slave/*.xml";
/**
* 配置数据源
* @return
*/
@Bean(name = "secondDataSource")
@ConfigurationProperties(prefix = "spring.datasource.slave")
public DataSource secondDataSource(){
return DataSourceBuilder.create().build();
}
/**
* 配置SqlSessionFactory
* @param dataSource
* @return
* @throws Exception
*/
@Bean(name = "secondSqlSessionFactory")
public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDataSource") DataSource dataSource) throws Exception{
final SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(SECOND_DATASOURCE_MAPPER_LOCATION));
return sqlSessionFactoryBean.getObject();
}
/**
* 配置事务管理器
* @param dataSource
* @return
*/
@Bean(name = "secondTransactionManager")
public DataSourceTransactionManager secondTransactionManager(@Qualifier("secondDataSource") DataSource dataSource){
return new DataSourceTransactionManager(dataSource);
}
/**
* 结合类注解MapperScan配置MapperScannerConfigurer
* @param sqlSessionFactory
* @return
* @throws Exception
*/
@Bean(name = "secondSqlSessionTemplate")
public SqlSessionTemplate secondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
后续工作类似单数据源的操作,创建实体类,mapper,service以及controller,在使用事务的时候对应的事务使用对应的注解即可
创建实体类User
package com.gangye.entity;
import lombok.Data;
/**
* @Classname User
* @Description 对应master_test_database的user表的实体类
* @Date 2020/5/26 16:33
* @Created by gangye
*/
@Data
public class User {
private Integer id;
private String name;
private String description;
}
user的mapper层,亦或者dao层
package com.gangye.mapper.mapper1;
import com.gangye.entity.User;
import java.util.List;
/**
* @Classname UserMapper
* @Description user的dao层
* @Date 2020/5/26 16:40
* @Created by gangye
*/
public interface UserMapper {
User findById(Integer id);
List<User> showAll();
void insert(User user);
void update(User user);
}
对应的mapper.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.gangye.mapper.mapper1.UserMapper">
<resultMap id="BaseResultMap" type="com.gangye.entity.User">
<result column="id" property="id" />
<result column="name" property="name" />
<result column="description" property="description" />
</resultMap>
<sql id="Base_Column_List">
id, name, description
</sql>
<select id="findById" resultMap="BaseResultMap" parameterType="java.lang.Integer">
select <include refid="Base_Column_List"/> from user where id = #{id}
</select>
<select id="showAll" resultMap="BaseResultMap">
select <include refid="Base_Column_List"/> from user
</select>
<insert id="insert" parameterType="com.gangye.entity.User">
insert into user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="name != null">
name,
</if>
<if test="description !=null">
description,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=INTEGER},
</if>
<if test="name != null">
#{name,jdbcType=VARCHAR},
</if>
<if test="description != null">
#{description,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="update" parameterType="com.gangye.entity.User" >
update user
<set>
<if test="name != null and name != ''">name = #{name},</if>
<if test="description != null and description != ''">description = #{description},</if>
</set>
where id = #{id}
</update>
</mapper>
user的相关service层(省略了UserService接口),此处可以看到对应的事务注解使用的是@Transactional(transactionManager = "primaryTransactionManager")
package com.gangye.service.impl;
import com.gangye.entity.User;
import com.gangye.mapper.mapper1.UserMapper;
import com.gangye.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
/**
* @Classname UserServiceImpl
* @Description userSercice的接口实现类
* @Date 2020/5/27 9:49
* @Created by gangye
*/
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public User getUserByPrimarykey(Integer id) {
return userMapper.findById(id);
}
@Override
public List<User> showAllUsers() {
return userMapper.showAll();
}
@Override
@Transactional(transactionManager = "primaryTransactionManager")
public void insertOneUser(User user) {
userMapper.insert(user);
}
@Override
@Transactional(transactionManager = "primaryTransactionManager")
public void updateUserInfo(User user) {
userMapper.update(user);
}
}
最后控制路由
package com.gangye.controller;
import com.gangye.entity.User;
import com.gangye.service.UserService;
import com.gangye.tools.Response;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
/**
* @Classname UserController
* @Description user相关的控制路由
* @Date 2020/5/27 9:51
* @Created by gangye
*/
@RestController
@RequestMapping(value = "/userController")
public class UserController {
@Autowired
private UserService userService;
//展示所有用户
@PostMapping("/showAllUsers")
public Response showAllUsers(){
List<User> userList = userService.showAllUsers();
Response response = Response.newResponse();
return response.setData(userList);
}
//根据id查询用户信息
@PostMapping("/showUserInfo")
public Response showUserInfo(Integer id){
Response response = Response.newResponse();
User userInfo = userService.getUserByPrimarykey(id);
return response.setData(userInfo);
}
//更改用户信息
@PostMapping("/updateUserInfo")
public Response updateUserInfo(@RequestBody User user){
Response response = Response.newResponse();
if (user.getId()!=null && ! "".equals(user.getId())){
User tempUser = userService.getUserByPrimarykey(user.getId());
if (tempUser==null){
return response.setCodeAndMessage(9999,"用户不存在!");
}
userService.updateUserInfo(user);
return response.OK();
}
return response.setCodeAndMessage(9999,"更改信息失败!");
}
//新增用户
@PostMapping("/addUser")
public Response addUser(@RequestBody User userInfo){
Response response = Response.newResponse();
userService.insertOneUser(userInfo);
return response.OK();
}
}