SpringBoot主从数据源切换,Mybatis Plus注解方式关联查询

一. 项目结构

二. 主要代码

1 使用druid配置主从数据源

# 数据源配置
spring:
    datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        druid:
            # 主库数据源
            db1:
                url: jdbc:mysql://47.93.96.145:3306/xiaozhu_database?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
                username: root
                password: xiaozhu123456
            # 从库数据源（暂时本地）
            db2:
                # 从数据源开关/默认关闭
                enabled: false
                url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
                username: root
                password: root

 2 数据源配置类

package com.jlcc.cctp.config;

import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

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 com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.jlcc.cctp.config.handler.MyMetaObjectHandler;
import com.jlcc.cctp.enums.DbEnums;
import com.jlcc.cctp.tools.DynamicDataSource;

/**
 * DataSource配置类
 * 
 * @author zx
 *
 */
@Configuration
@MapperScan({ "com.jlcc.cctp.mapper*" })
public class DataSourceConfig {

	@Bean(name = "db1")
	@ConfigurationProperties(prefix = "spring.datasource.druid.db1")
	public DataSource db1() {
		return DruidDataSourceBuilder.create().build();
	}

	@Bean(name = "db2")
	@ConfigurationProperties(prefix = "spring.datasource.druid.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(DbEnums.DB1.getValue(), db1);
		targetDataSources.put(DbEnums.DB2.getValue(), db2);
		dynamicDataSource.setTargetDataSources(targetDataSources);
		dynamicDataSource.setDefaultTargetDataSource(db1);
		return dynamicDataSource;
	}

	/**
	 * 工厂方法
	 * 
	 * @return
	 * @throws Exception
	 */
	@Bean("sqlSessionFactory")
	public SqlSessionFactory sqlSessionFactory() throws Exception {
		MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
		sqlSessionFactory.setDataSource(multipleDataSource(db1(), db2()));

		MybatisConfiguration configuration = new MybatisConfiguration();

		// 全局配置
		GlobalConfig globalConfig = new GlobalConfig();
		globalConfig.setMetaObjectHandler(new MyMetaObjectHandler());
		sqlSessionFactory.setGlobalConfig(globalConfig);

		configuration.setJdbcTypeForNull(JdbcType.NULL);
		configuration.setMapUnderscoreToCamelCase(true);
		configuration.setCacheEnabled(false);
		sqlSessionFactory.setConfiguration(configuration);
		sqlSessionFactory.setPlugins(new Interceptor[] {});
		return sqlSessionFactory.getObject();
	}

}

 3 数据源枚举列举

package com.jlcc.cctp.enums;

/**
 * 数据源切换
 * 
 * @author zx
 *
 */
public enum DbEnums {
	
	DB1("db1"), DB2("db2");
	
	private String value;

	DbEnums(String value) {
		this.value = value;
	}

	public String getValue() {
		return value;
	}
}

4 AOP切入service控制数据源

package com.jlcc.cctp.aspect;

import java.util.Objects;

import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import com.jlcc.cctp.annotation.DataSourceSwitch;
import com.jlcc.cctp.enums.DbEnums;
import com.jlcc.cctp.tools.DbContextHolder;

import lombok.extern.slf4j.Slf4j;

@Aspect // 该标签把LoggerAspect类声明为一个切面
@Order(-100) // 设置切面的优先级：如果有多个切面，可通过设置优先级控制切面的执行顺序（数值越小，优先级越高）
@Component // 该标签把LoggerAspect类放到IOC容器中
@Slf4j
public class DataSourceAspect {
	@Pointcut("execution(* com.jlcc.cctp.service..*.*(..))")
	private void dbAspect() {
	}

	@Before("dbAspect()")
	public void db(JoinPoint joinPoint) {
		MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature();
		DataSourceSwitch dataSourceSwitch = methodSignature.getMethod().getAnnotation(DataSourceSwitch.class);
		if (Objects.isNull(dataSourceSwitch) || Objects.isNull(dataSourceSwitch.value())) {//注解为空，默认数据源1
			log.info("默认数据源1");
			DbContextHolder.setDbType(DbEnums.DB1);
		} else {
			switch (dataSourceSwitch.value().getValue()) {
			case "db1":
				log.info("数据源1");
				DbContextHolder.setDbType(DbEnums.DB1);
				break;
			case "db2":
				log.info("数据源2");
				DbContextHolder.setDbType(DbEnums.DB2);
				break;
			default:
				log.info("默认数据源1");
				DbContextHolder.setDbType(DbEnums.DB1);
			}
		}
	}
}

5.自定义注解用来切换数据源

package com.jlcc.cctp.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

import com.jlcc.cctp.enums.DbEnums;

/**
 * 注解用来切换数据源
 * 
 * @author zx
 *
 */
@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.METHOD })
public @interface DataSourceSwitch {
	DbEnums value() default DbEnums.DB1;//默认数据源1
}

6.service中的使用

    @Override
	@DataSourceSwitch(DbEnums.DB1)
	public List<XzOrdinaryUsers> selectUser(XzOrdinaryUsers xzOrdinaryUsers) throws Exception {
		xzOrdinaryUsers.setUserPassword(DesUtil.getEncryptDES(xzOrdinaryUsers.getUserPassword()));
		return userMapper.selectUser(xzOrdinaryUsers);
	}

7.mapper中的实体映射@Results及注解的关联查询

package com.jlcc.cctp.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jlcc.cctp.entity.XzBusinessUsers;
import com.jlcc.cctp.entity.XzOrdinaryUsers;

/**
 * 商家Mapper
 * 
 * @author zx
 *
 */
@Mapper
public interface BusinessUserMapper extends BaseMapper<XzBusinessUsers> {

	/**
	 * 查询商家列表
	 * 
	 * @param xzBusinessUsers
	 * @return
	 */
	@Select("<script> SELECT business_id, company_name, organization_code, industry_involved_name, industry_involved_tag, business_icon,user_id  "
			+ "FROM  xz_business_users WHERE  1 = 1 <if test=\"industryInvolvedTag!=null and industryInvolvedTag!=''\">and industry_involved_tag=#{industryInvolvedTag}</if> </script>")
	@Results(id = "xzBusinessUsersMap", value = { @Result(id = true, column = "business_id", property = "businessId"),
			@Result(column = "company_name", property = "companyName"),
			@Result(column = "organization_code", property = "organizationCode"),
			@Result(column = "industry_involved_name", property = "industryInvolvedName"),
			@Result(column = "industry_involved_tag", property = "industryInvolvedTag"),
			@Result(column = "business_icon", property = "businessIcon"),
			@Result(column = "user_id", property = "userId"),
			@Result(property = "xzOrdinaryUsers", one = @One(select = "com.jlcc.cctp.mapper.XiaoZhuUserMapper.selectUserById"), column = "user_id") })
	public List<XzBusinessUsers> selectBusinessUser(XzBusinessUsers xzBusinessUsers);

	
}

通过Mybatis Plus注解@TableName映射,使用BaseMapper中的方法@TableName的映射会失效,所以采用了别名的方式

package com.ssm.demo.dao;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.ssm.demo.vo.Project;

@Mapper
public interface ProjectDao extends BaseMapper<Project> {

	@Results(id = "projectList", value = {
			@Result(property = "developers", one = @One(select = "com.ssm.demo.dao.DevelopersDao.selectById"), column = "did") })
	@Select("select pid,pro_name as proName,did,cost,budget_cycle as budgetCycle,start_date as startDate,end_date as endDate,is_filings as isFilings from project")
	public List<Project> selectAllProject(Project project);
}

备注:也可能是我没用明白,有大神可以留言指点一二哈