前言
去年的时候公司的一个项目需要配置多数据源,当时使用的是springMVC框架,网上找了一些资料,花费了一些时间也整合了;今年公司使用了新的框架,现在的项目基本都是基于SpringBoot开发。以下来记录下自己使用SpringBoot整合MybatisPlus和配置多数据源。
整合步骤目录
1. 新建SpringBoot项目。
2. 导入依赖 --> devtools,lombok,mysql,aop,mybatisplus,druid,fastjson,swagger。
3. 编写application.yml --> 项目端口,项目路径名,mybatisplus配置,mysql多数据源配置。
4. 新建DataSourceContextHolder 用于设置,获取,清空 当前线程内的数据源变量。
5. 新建DynamicDataSource 继承 AbstractRoutingDataSource 类。重写determineCurrentLookupKey(),通过 DataSourceContextHolder 获取数据源变量,用于当作lookupKey取出指定的数据源。
6. 新建DBTypeEnum 用于存放数据源名称。
7. 新建注解 DataSource,用于aop类中当作切入点来选择数据源。编写aop类 --> DataSourceAspect.java
8. 新建并配置MybatisPlusConfig,SourceSwagger2两个类。
1)MybatisPlusConfig--> mybatisplus 分页插件、SQL执行效率插件、数据源Bean,DynamicDataSource 注入 SqlSessionFactory,MultiDataSourceTransactionFactory事物支持。
2)SourceSwagger2--> 常规配置。
9. 创建UserController、StockController接口。
10. 完整项目结构
1、新建项目
idea新建source项目 然后创建springboot项目 勾选 devtools,lombok,mysql。
2、导入依赖(pom引入)
<!--mysql依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql-connector-java.version}</version>
</dependency>
<!-- Druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid.version}</version>
</dependency>
<!-- MyBatis增强工具-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatis-plus-boot-starter.version}</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>${mybatis-plus-generator.version}</version>
</dependency>
<!--aop-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<!--swagger-ui-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>${swagger.version}</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>${swagger.version}</version>
</dependency>
<!--阿里巴巴fastjosn依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>${fastjson.version}</version>
</dependency>
3、编写application.yml
server:
port: 8000
logging:
config: classpath:logback-spring.xml
spring:
datasource:
db1:
url: jdbc:mysql://127.0.0.1:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
username: root
password: root1234
# 使用Druid数据源
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
db2:
url: jdbc:mysql://127.0.0.1:3306/ksyh?autoReconnect=true&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
username: root
password: root1234
# 使用Druid数据源
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
druid:
filters: stat
maxActive: 20
initialSize: 1
maxWait: 60000
minIdle: 1
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: select 'x'
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
mybatis-plus:
mapper-locations: classpath*:com.example.source.mapper/*.xml
global-config:
db-config:
id-type: uuid
field-strategy: not_null
refresh: true
configuration:
map-underscore-to-camel-case: false
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
4、 新建DataSourceContextHolder用于设置,获取,清空当前线程内的数据源变量。
package com.example.source.config;
/**
* @author LST
* @version 1.0
* @Description: 设置、获取、清除当前数据源的方法/并切换数据源
* @date 2020-1-6 15:38
*/
public class DataSourceContextHolder {
private static final ThreadLocal<String> contextHolder = new InheritableThreadLocal<>();
/**
* 设置数据源
* @param db
*/
public static void setDataSource(String db){
contextHolder.set(db);
}
/**
* 取得当前数据源
* @return
*/
public static String getDataSource(){
return contextHolder.get();
}
/**
* 清除上下文数据
*/
public static void clear(){
contextHolder.remove();
}
}
5、新建DynamicDataSource继承AbstractRoutingDataSource 类。重写determineCurrentLookupKey(),通过DataSourceContextHolder获取数据源变量,用于当作lookupKey取出指定的数据源。
package com.example.source.config;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* @author LST
* @version 1.0
* @Description: 动态获取数据源
* @date 2020-1-6 15:45
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getDataSource();
}
}
6、 新建DBTypeEnum用于存放数据源名称。
package com.example.source.enums;
/**
* @author LST
* @version 1.0
* @Description: 切换的数据源名称
* @date 2020-1-6 15:50
*/
public enum DBTypeEnum {
db1("db1"),
db2("db2");
private String value;
DBTypeEnum(String value) {
this.value = value;
}
public String getValue() {
return value;
}
}
7、 新建注解DataSource,用于aop类中当作切入点来选择数据源。编写aop类 -->DataSourceAspect.java
1)DataSource.java
package com.example.source.config;
import com.example.source.enums.DBTypeEnum;
import java.lang.annotation.*;
/**
* @author LST
* @version 1.0
* @Description: AOP选择数据源的时候做标识,即我们这里通过AOP读取到自定义的注解决定选择数据源
* @date 2020-1-6 16:35
*/
@Target({ElementType.METHOD,ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSource {
DBTypeEnum value() default DBTypeEnum.db1;
}
2)DataSourceAspect.java
package com.example.source.aop;
import com.example.source.config.DataSourceContextHolder;
import com.example.source.config.DataSource;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
/**
* @author LST
* @version 1.0
* @Description: AOP实现的数据源切换
* @date 2020-1-6 16:22
*/
@Component
@Aspect
@Order(-100)//这是为了保证AOP在事务注解之前生效,Order的值越小,优先级越高
@Slf4j
public class DataSourceAspect {
@Pointcut("@within(com.example.source.config.DataSource) || @annotation(com.example.source.config.DataSource)")
public void pointcut() {
}
@Before("pointcut() && @annotation(dataSource)")
public void doBefore(DataSource dataSource) {
log.info("选择的数据源是:{}",dataSource.value().getValue());
DataSourceContextHolder.setDataSource(dataSource.value().getValue());
}
@After("pointcut()")
public void jarvisWxDb () {
log.info("清除上下文数据");
DataSourceContextHolder.clear();
}
}
8、 新建并配置MybatisPlusConfig,SourceSwagger2两个类。
1)MybatisPlusConfig--> mybatisplus 分页插件、SQL执行效率插件、数据源Bean,DynamicDataSource 注入 SqlSessionFactory,MultiDataSourceTransactionFactory事物支持。
package com.example.source.config;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.MybatisXMLLanguageDriver;
import com.baomidou.mybatisplus.extension.injector.LogicSqlInjector;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.PerformanceInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.example.source.enums.DBTypeEnum;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
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 javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* MybatisPlus配置
*
* @author lst
* @since 2019-12-17
*/
@Configuration
@MapperScan(basePackages = {"com.example.source.mapper"})//扫描的dao层
public class MybatisPlusConfig {
/**
* mybatis-plus分页插件
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
/**
* mybatis-plus SQL执行效率插件【生产环境可以关闭】
*/
@Bean
public PerformanceInterceptor performanceInterceptor() {
return new PerformanceInterceptor();
}
@Bean(name = "db1")
@ConfigurationProperties(prefix = "spring.datasource.db1" )
public DataSource db1 () {
return DruidDataSourceBuilder.create().build();
}
@Bean(name = "db2")
@ConfigurationProperties(prefix = "spring.datasource.db2" )
public DataSource db2 () {
return DruidDataSourceBuilder.create().build();
}
/**
* 动态数据源配置
* @return
*/
@Bean
@Primary
public DataSource multipleDataSource (@Qualifier("db1") DataSource db1,@Qualifier("db2") DataSource db2 ) {
DynamicDataSource dynamicDataSource = new DynamicDataSource();
Map< Object, Object > targetDataSources = new HashMap<>();
targetDataSources.put(DBTypeEnum.db1.getValue(), db1 );
targetDataSources.put(DBTypeEnum.db2.getValue(), db2);
dynamicDataSource.setTargetDataSources(targetDataSources);
dynamicDataSource.setDefaultTargetDataSource(db1);
return dynamicDataSource;
}
@Bean("sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory() throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
sqlSessionFactory.setDataSource(multipleDataSource(db1(),db2()));
sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/com/example/source/mapper/*.xml"));
//添加事物配置(重点)
sqlSessionFactory.setTransactionFactory(new MultiDataSourceTransactionFactory());
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.setDefaultScriptingLanguage(MybatisXMLLanguageDriver.class);
configuration.setJdbcTypeForNull(JdbcType.NULL);
configuration.setMapUnderscoreToCamelCase(true);
configuration.setCacheEnabled(false);
sqlSessionFactory.setConfiguration(configuration);
sqlSessionFactory.setPlugins(new Interceptor[]{ //PerformanceInterceptor(),OptimisticLockerInterceptor()
paginationInterceptor() //添加分页功能
});
// sqlSessionFactory.setGlobalConfig(globalConfiguration());
return sqlSessionFactory.getObject();
}
/* @Bean
public GlobalConfiguration globalConfiguration() {
GlobalConfiguration conf = new GlobalConfiguration(new LogicSqlInjector());
conf.setLogicDeleteValue("-1");
conf.setLogicNotDeleteValue("1");
conf.setIdType(0);
conf.setMetaObjectHandler(new MyMetaObjectHandler());
conf.setDbColumnUnderline(true);
conf.setRefresh(true);
return conf;
}*/
}
MultiDataSourceTransactionFactory.java事物支持
package com.example.source.config;
import org.apache.ibatis.session.TransactionIsolationLevel;
import org.apache.ibatis.transaction.Transaction;
import org.mybatis.spring.transaction.SpringManagedTransactionFactory;
import javax.sql.DataSource;
/**
* @author LST
* @version 1.0
* @Description: 添加事务支持
* @date 2020-1-6 16:49
*/
public class MultiDataSourceTransactionFactory extends SpringManagedTransactionFactory {
@Override
public Transaction newTransaction(DataSource dataSource, TransactionIsolationLevel level, boolean autoCommit) {
return new MultiDataSourceTransaction(dataSource);
}
}
MultiDataSourceTransaction.java
package com.example.source.config;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.transaction.Transaction;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.jdbc.datasource.DataSourceUtils;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.ConcurrentMap;
/**
* @author LST
* @version 1.0
* @Description: 添加事务支持
* @date 2020-1-6 17:08
*/
@Slf4j
public class MultiDataSourceTransaction implements Transaction {
private final DataSource dataSource;
private Connection mainConnection;
private String mainDatabaseIdentification;
private ConcurrentMap<String, Connection> otherConnectionMap;
private boolean isConnectionTransactional;
private boolean autoCommit;
public MultiDataSourceTransaction(DataSource dataSource) {
//notNull(dataSource, "No DataSource specified");
this.dataSource = dataSource;
otherConnectionMap = new ConcurrentHashMap<>();
mainDatabaseIdentification= DataSourceContextHolder.getDataSource();
}
/**
* {@inheritDoc}
*/
@Override
public Connection getConnection() throws SQLException {
String databaseIdentification = DataSourceContextHolder.getDataSource();
if (databaseIdentification.equals(mainDatabaseIdentification)) {
if (mainConnection != null){
return mainConnection;
} else {
openMainConnection();
mainDatabaseIdentification =databaseIdentification;
return mainConnection;
}
} else {
if (!otherConnectionMap.containsKey(databaseIdentification)) {
try {
Connection conn = dataSource.getConnection();
otherConnectionMap.put(databaseIdentification, conn);
} catch (SQLException ex) {
throw new CannotGetJdbcConnectionException("Could not get JDBC Connection", ex);
}
}
return otherConnectionMap.get(databaseIdentification);
}
}
private void openMainConnection() throws SQLException {
this.mainConnection = DataSourceUtils.getConnection(this.dataSource);
this.autoCommit = this.mainConnection.getAutoCommit();
this.isConnectionTransactional = DataSourceUtils.isConnectionTransactional(this.mainConnection, this.dataSource);
if (log.isDebugEnabled()) {
log.debug(
"JDBC Connection ["
+ this.mainConnection
+ "] will"
+ (this.isConnectionTransactional ? " " : " not ")
+ "be managed by Spring");
}
}
/**
* {@inheritDoc}
*/
@Override
public void commit() throws SQLException {
if (this.mainConnection != null && !this.isConnectionTransactional && !this.autoCommit) {
if (log.isDebugEnabled()) {
log.debug("Committing JDBC Connection [" + this.mainConnection + "]");
}
this.mainConnection.commit();
for (Connection connection : otherConnectionMap.values()) {
connection.commit();
}
}
}
/**
* {@inheritDoc}
*/
@Override
public void rollback() throws SQLException {
if (this.mainConnection != null && !this.isConnectionTransactional && !this.autoCommit) {
if (log.isDebugEnabled()) {
log.debug("Rolling back JDBC Connection [" + this.mainConnection + "]");
}
this.mainConnection.rollback();
for (Connection connection : otherConnectionMap.values()) {
connection.rollback();
}
}
}
/**
* {@inheritDoc}
*/
@Override
public void close() throws SQLException {
DataSourceUtils.releaseConnection(this.mainConnection, this.dataSource);
for (Connection connection : otherConnectionMap.values()) {
DataSourceUtils.releaseConnection(connection, this.dataSource);
}
}
@Override
public Integer getTimeout() throws SQLException {
return null;
}
}
2)SourceSwagger2--> 常规配置。
package com.example.source;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;
/**
* 接口文档
*
* @author lst
* @since 2019-12-17
*/
@Configuration
@EnableSwagger2
public class SourceSwagger2 {
@Bean
public Docket createRestBmbsApi() {
return new Docket(DocumentationType.SWAGGER_2)
.groupName("source")
.apiInfo(apiInfo())
.select()
.apis(RequestHandlerSelectors.basePackage("com.example.source.controller"))
.paths(PathSelectors.any())
.build();
}
private ApiInfo apiInfo() {
return new ApiInfoBuilder()
.title("source")
.version("1.0")
.build();
}
}
9、 创建UserController、StockController接口。
1)Stock.java、User.java、BasePlusEntity.java
package com.example.source.entity;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
/**
* @author LST
* @version 1.0
* @Description: 存货
* @date 2019-12-27 15:54
*/
@TableName("stock")
@Data
public class Stock extends BasePlusEntity<Stock>{
private static final long serialVersionUID = 1L;
/**
* 产品名称
*/
@TableField("name")
private String name;
/**
* 存货
*/
@TableField("stock")
private String stock;
}
package com.example.source.entity;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;
import java.io.Serializable;
import java.util.Date;
/**
* <p>
* 用户表
* </p>
*
* @author lst
* @since 2019-04-08
*/
@TableName("sys_user")
@Data
public class User extends BasePlusEntity<User> {
private static final long serialVersionUID = 1L;
/**
* 登录名
*/
@TableField("login_name")
private String loginName;
/**
* 密码
*/
@TableField("password")
private String password;
/**
* 用户的真实姓名
*/
@TableField("user_name")
private String userName;
/**
* 身份证号码
*/
@TableField("id_card")
private String idCard;
/**
* 民族
*/
private String nation;
/**
* 性别
*/
private String sex;
/**
* 生日
*/
private String birthday;
/**
* 政治面貌
*/
@TableField("politics_face")
private String politicsFace;
/**
* 头像地址
*/
@TableField("head_picture")
private String headPicture;
/**
* 用户状态
*/
@TableField("user_status")
private String userStatus;
/**
* 职称
*/
private String title;
/**
* 办公室
*/
@TableField("office_addr")
private String officeAddr;
/**
* 办公电话
*/
@TableField("office_phone")
private String officePhone;
/**
* 用户所在单位列表
*/
@TableField("user_orgs")
private String userOrgs;
/**
* 用户所在单位
*/
@TableField("user_org")
private String userOrg;
/**
* 手机号码
*/
@TableField("mobil_phone")
private String mobilPhone;
/**
* 邮件
*/
private String email;
/**
* 备用手机
*/
@TableField("mobil_phone2")
private String mobilPhone2;
/**
* 虚拟短号
*/
@TableField("virtual_num")
private String virtualNum;
/**
* 打印表单时预留的窗口办理人员姓名
*/
@TableField("print_user_name")
private String printUserName;
/**
* 打印表单时预留的窗口办理人员电话
*/
@TableField("print_phone")
private String printPhone;
/**
* 最后登录时间
*/
@TableField("last_login")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date lastLogin;
/**
* 登录ip.
*/
@TableField("ip")
private String ip;
/**
* 省籍
*/
private String province;
/**
* 城市
*/
private String city;
/**
* 详细地址
*/
private String address;
/**
* 邮编
*/
@TableField("post_code")
private String postCode;
@Override
protected Serializable pkVal() {
return this.id;
}
}
package com.example.source.entity;
import com.alibaba.fastjson.annotation.JSONField;
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
/**
* 基础实体类
*
* @author lst
* @since 2019-12-17
* @param <T>
*/
@Data
public class BasePlusEntity<T extends Model> extends Model implements Serializable {
private static final long serialVersionUID = 1L;
protected String id;
/**
* 备注信息
*/
private String remarks;
/**
* 创建者
*/
@TableField(value = "create_by", fill = FieldFill.INSERT)
// @JsonIgnore
protected String createBy;
/**
* 创建日期
*/
@TableField(value = "create_date", fill = FieldFill.INSERT)
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
protected Date createDate;
/**
* 更新者
*/
@TableField(value = "update_by", fill = FieldFill.INSERT_UPDATE)
// @JsonIgnore
protected String updateBy;
/**
* 更新日期
*/
@TableField(value = "update_date", fill = FieldFill.INSERT_UPDATE)
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
protected Date updateDate;
/**
* 0正常,1删除
*/
@JSONField(serialize = false)
@TableField(value = "del_flag", fill = FieldFill.INSERT)
@TableLogic
@JsonIgnore
protected String delFlag;
public BasePlusEntity(String id) {
this();
this.id = id;
}
public BasePlusEntity() {
}
}
2)StockMapper.java、UserMapper.java、StockMapper.xml、UserMapper.xml
package com.example.source.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.source.entity.Stock;
import org.springframework.stereotype.Component;
/**
* @author LST
* @version 1.0
* @Description: 存货 Mapper 接口
* @date 2019-12-27 16:02
*/
@Component
public interface StockMapper extends BaseMapper<Stock> {
}
package com.example.source.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.source.entity.User;
import com.example.source.vo.UserVO;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Component;
/**
* <p>
* 用户表 Mapper 接口
* </p>
*
* @author lst
* @since 2019-04-08
*/
@Component
public interface UserMapper extends BaseMapper<User> {
/**
* 根据分页查询用户信息
*
* @param pageInfo 分页信息
* @param loginName 登录名
* @param userName 姓名
* @param idCard 身份证号码
* @return
*/
IPage<UserVO> selectPageVO(Page<UserVO> pageInfo, @Param("loginName") String loginName, @Param("userName") String userName, @Param("idCard") String idCard);
}
<?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.example.source.mapper.UserMapper">
<!--
########################################
分页查找用户
@author nj
@version 1.0
@date 2019-04-10
########################################
-->
<select id="selectPageVO" resultType="com.example.source.vo.UserVO">
SELECT
sys_user.id AS uid,
sys_user.login_name AS loginName,
sys_user.user_name AS userName,
sys_user.id_card AS idCard,
sys_user.email AS email,
inet_ntoa(sys_user.ip) AS ip,
sys_user.last_login AS lastLogin
FROM
sys_user
<trim prefix="where" prefixOverrides="and">
sys_user.del_flag ='0'
<if test="loginName != null and loginName != '' ">
AND INSTR(sys_user.login_name,#{loginName}) > 0
</if>
<if test="userName != null and userName != '' ">
AND INSTR(sys_user.user_name,#{userName}) > 0
</if>
<if test="idCard != null and idCard != '' ">
AND INSTR(sys_user.id_card,#{idCard}) > 0
</if>
</trim>
GROUP BY sys_user.id
ORDER BY sys_user.update_date desc
</select>
</mapper>
<?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.example.source.mapper.StockMapper">
</mapper>
3)UserServiceImpl.java、UserService.java、StockServiceImpl.java、StockService.java
代码中的这个是代表这个表使用哪个库。
package com.example.source.service.impl;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.source.config.DataSource;
import com.example.source.entity.Stock;
import com.example.source.enums.DBTypeEnum;
import com.example.source.mapper.StockMapper;
import com.example.source.service.StockService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @author lst
* @version 1.0
* @Description: 存货实现层
* @date 2019-12-27 15:54
*/
@Service
@Slf4j
public class StockServiceImpl extends ServiceImpl<StockMapper, Stock> implements StockService {
@Autowired
private StockMapper stockMapper;
/**
* 存货信息列表
* @param name 商品名称
*/
@Override
@DataSource(DBTypeEnum.db2)
public List<Stock> decrByStock(String name) {
List<Stock> stockList = stockMapper.selectList(new QueryWrapper<Stock>().lambda().eq(Stock::getName, name));
return stockList;
}
}
package com.example.source.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.example.source.entity.Stock;
import java.util.List;
/**
* @author lst
* @version 1.0
* @Description: 存货服务层
* @date 2019-12-27 15:54
*/
public interface StockService extends IService<Stock> {
/**
* 存货信息列表
* @param name 商品名称
*/
List<Stock> decrByStock(String name);
}
package com.example.source.service.impl;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.source.config.DataSource;
import com.example.source.entity.User;
import com.example.source.enums.DBTypeEnum;
import com.example.source.exception.SXException;
import com.example.source.exception.ServiceExceptionEnum;
import com.example.source.mapper.UserMapper;
import com.example.source.service.UserService;
import com.example.source.vo.UserVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
/**
* @author lst
* @version 1.0
* @Description: 用户表实现层
* @date 2019年04月09日 18:23
*/
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public IPage<UserVO> listUser(String loginName, String userName, String idCard, Integer current, Integer size) {
Page<UserVO> pageInfo = new Page<>(current, size);
IPage<UserVO> page = userMapper.selectPageVO(pageInfo, loginName, userName, idCard);
return page;
}
/**
* 获取用户详情
* @param userId 用户id
* @return
*/
@Override
@DataSource(DBTypeEnum.db1)
public User getUser(String userId) {
User user = userMapper.selectOne(new QueryWrapper<User>().lambda().eq(User::getId, userId));
if(user == null){
throw new SXException(ServiceExceptionEnum.DATA_NOT_REQUESTED);
}
return user;
}
}
package com.example.source.service;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
import com.example.source.entity.User;
import com.example.source.vo.UserVO;
/**
* @author lst
* @version 1.0
* @Description: 用户表
* @date 2019年04月09日 18:23
*/
public interface UserService extends IService<User> {
/**
* 获取用户列表
*
* @param loginName 登录名
* @param userName 姓名
* @param idCard 身份证号码
* @param current 当前页数
* @param size 每页大小
* @return
*/
IPage<UserVO> listUser(String loginName, String userName, String idCard, Integer current, Integer size);
/**
* 获取用户详情
* @param userId 用户id
* @return
*/
User getUser(String userId);
}
4)StockController.java、UserController.java
package com.example.source.controller;
import com.example.source.result.RestResponse;
import com.example.source.result.ResultGenerator;
import com.example.source.service.StockService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
/**
* @author LST
* @version 1.0
* @Description:
* @date 2020-1-7 9:18
*/
@RestController
@RequestMapping("/stock")
@Api(value = "StockController", tags = "存货信息")
public class StockController {
@Autowired
private StockService stockService;
/**
* 商品列表
* @param name 商品名称
* @return
*/
@GetMapping(value = "/list", produces = "application/json; charset=utf-8")
@ApiOperation(value = "存货信息列表", notes = "存货信息列表", code = 200, produces = "application/json")
public RestResponse listPage(@RequestParam(name = "name", required = true) String name) {
return ResultGenerator.genSuccessResult(stockService.decrByStock(name));
}
}
package com.example.source.controller;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.example.source.result.RestResponse;
import com.example.source.result.ResultGenerator;
import com.example.source.service.UserService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* @author LST
* @version 1.0
* @Description: 用户信息
* @date 2020-1-6 17:48
*/
@RestController
@RequestMapping("/user")
@Api(value = "UserController", tags = "用户管理")
public class UserController {
@Autowired
private UserService userService;
/**
* 获取用户详情
* @param userId 用户id
* @return
*/
@GetMapping(value = "/{userId}", produces = "application/json; charset=utf-8")
@ApiOperation(value = "获取用户详情", notes = "获取用户详情", code = 200, produces = "application/json")
public RestResponse getUser(@PathVariable("userId") String userId) {
return ResultGenerator.genSuccessResult(userService.getUser(userId));
}
}
5)测试
第一先执行sql.sql文件,自己手动插入一些数据。
CREATE TABLE `sys_user` (
`id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`login_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '登录名',
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '密码',
`user_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '用户的真实姓名',
`id_card` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '身份证号码',
`nation` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '民族',
`sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '性别',
`birthday` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '生日',
`politics_face` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '政治面貌',
`head_picture` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '头像地址',
`user_status` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '用户状态',
`title` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '职称',
`office_addr` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '办公室',
`office_phone` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '办公电话',
`user_orgs` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '用户所在单位列表',
`user_org` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '用户所在单位',
`mobil_phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '手机号码',
`email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '邮件',
`mobil_phone2` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '备用手机',
`virtual_num` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '虚拟短号',
`province` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '省籍',
`city` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '城市',
`address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '详细地址',
`print_phone` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '打印表单时预留的窗口办理人员电话',
`print_user_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '打印表单时预留的窗口办理人员姓名',
`post_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '邮编',
`ip` bigint(11) NOT NULL DEFAULT 0 COMMENT '登录ip地址',
`last_login` datetime(0) NULL DEFAULT NULL COMMENT '最后登录时间',
`sort_by` int(11) NOT NULL DEFAULT 0,
`remarks` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '备注',
`update_date` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后更新时间',
`create_date` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`create_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`del_flag` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '0正常,1删除',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of sys_user
-- ----------------------------
INSERT INTO `sys_user` VALUES ('2433ba5dff804f8681c13b3a2ebc0616', 'admin', 'MzBEQUI0Qjk2NUE3ODg4N0E2RTY2RkZFOUZEM0EzNDg=', '管理员', '110102199204246999', '', '', '', '', '', '', '', '', '0571-400881199', '113118439,11808211,11811103,1', '113118439', '15512341234', '1@1.com', '', '', '', '', '', '1', '管理员', '', 3232243483, '2019-11-23 16:16:31', 0, '', '2019-11-25 11:37:03', '2019-04-19 05:31:45', '82d50ae31e435ceaa8b2bf030cc95363', 'admin', '0');
-- ----------------------------
-- Table structure for stock
-- ----------------------------
CREATE TABLE `stock` (
`id` varchar(64) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`stock` varchar(255) DEFAULT NULL,
`remarks` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
`update_date` datetime DEFAULT NULL COMMENT '最后更新时间',
`create_date` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` varchar(64) NOT NULL DEFAULT '',
`create_by` varchar(64) NOT NULL DEFAULT '',
`del_flag` char(1) NOT NULL DEFAULT '0' COMMENT '0正常,1删除',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='商品库存表';
第二步查询存货信息列表
第三步查询用户详细信息
控制台日志
10、 完整项目结构
以上就是SpringBoot整合多数据源的代码。需要源码可自行下载,直接根据步骤就可以正常启动使用。
源码链接:https://download.csdn.net/download/qq_33612228/12086566