思途做的旅游网站,建立网站需要投入的成本,厦门翔安建设局网站,linux安装wordpress无法访问在日常开发中我们都是以单个数据库进行开发#xff0c;在小型项目中是完全能够满足需求的。但是#xff0c;当我们牵扯到像淘宝、京东这样的大型项目的时候#xff0c;单个数据库就难以承受用户的CRUD操作。那么此时#xff0c;我们就需要使用多个数据源进行读写分离的操作…在日常开发中我们都是以单个数据库进行开发在小型项目中是完全能够满足需求的。但是当我们牵扯到像淘宝、京东这样的大型项目的时候单个数据库就难以承受用户的CRUD操作。那么此时我们就需要使用多个数据源进行读写分离的操作这种方式也是目前一种流行的数据管理方式。
1 Spring Boot配置多数据源
在YAML文件中定义数据源所需的数据
spring:datasource:type: com.alibaba.druid.pool.DruidDataSourcedefault-datasource:username: rootpassword: rooturl: jdbc:mysql://localhost:3306/default?serverTimezoneUTCuseUnicodetruecharacterEncodingutf-8driver-class-name: com.mysql.jdbc.Driverinspur-zs-datasource:username: rootpassword: rooturl: jdbc:mysql://localhost:3306/inspur-zs?serverTimezoneUTCuseUnicodetruecharacterEncodingutf-8driver-class-name: com.mysql.jdbc.Driverdruid:initial-size: 5min-idle: 1max-active: 20profiles:active: devmybatis:mapper-locations: classpath:/mapper/*.xmltype-aliases-package: com.inspur.pojoconfiguration:mapUnderscoreToCamelCase: truelog-impl: org.apache.ibatis.logging.slf4j.Slf4jImpl定义多个数据源
package com.inspur.spring.config.datasource;import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;import javax.sql.DataSource;/*** 数据源配置* ConfigurationProperties注解用于将YAML中指定的数据创建成指定的对象* 但是YAML中的数据必须要与对象对象中的属性同名不然无法由Spring Boot完成赋值。** author zhaoshuai-lc* date 2023/07/11*/
Configuration
public class DataSourceConfig {Bean(name defaultDatasource)ConfigurationProperties(prefix spring.datasource.default-datasource)public DataSource defaultDatasource() {return DruidDataSourceBuilder.create().build();}Bean(name inspurZsDatasource)ConfigurationProperties(prefix spring.datasource.inspur-zs-datasource)public DataSource inspurZsDatasource() {return DruidDataSourceBuilder.create().build();}
}由于我们要定义多个数据源所以在Spring Boot数据源自动配置类中就无法确定导入哪个数据源来完成初始化所以我们就需要禁用掉Spring Boot的数据源自动配置类然后使用我们自定义的数据源配置类来完成数据源的初始化与管理。
package com.inspur;import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.EnableAspectJAutoProxy;/*** WtMybatisStudyApplication* 由于我们要定义多个数据源所以在Spring Boot数据源自动配置类中就无法确定导入哪个数据源来完成初始化* 所以我们就需要禁用掉Spring Boot的数据源自动配置类然后使用我们自定义的数据源配置类来完成数据源的初始化与管理。** author zhaoshuai-lc* date 2023/07/11*/
SpringBootApplication(exclude {DataSourceAutoConfiguration.class})
EnableAspectJAutoProxy
public class WtMybatisStudyApplication {public static void main(String[] args) {SpringApplication.run(WtMybatisStudyApplication.class, args);}
}1.1 指定数据源-实现DataSource接口
缺点产生大量的代码冗余在代码中存在硬编码。
package com.inspur.spring.config.datasource;import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Component;import javax.annotation.Resource;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.logging.Logger;/*** 方式一* DynamicDataSource1* 实现DataSource接口我们本质上只使用了一个方法就是getConnection()这个无参的方法** author zhaoshuai-lc* date 2023/07/11*/
Component
Primary
public class DynamicDataSource1 implements DataSource {public static ThreadLocalString flag new ThreadLocal();Resourceprivate DataSource defaultDatasource;Resourceprivate DataSource inspurZsDatasource;public DynamicDataSource1() {flag.set(defaultDatasource);}Overridepublic Connection getConnection() throws SQLException {if (flag.get().equals(defaultDatasource)) {return defaultDatasource.getConnection();} else if (flag.get().equals(inspurZsDatasource)) {return inspurZsDatasource.getConnection();}return defaultDatasource.getConnection();}Overridepublic Connection getConnection(String username, String password) throws SQLException {return null;}Overridepublic T T unwrap(ClassT iface) throws SQLException {return null;}Overridepublic boolean isWrapperFor(Class? iface) throws SQLException {return false;}Overridepublic PrintWriter getLogWriter() throws SQLException {return null;}Overridepublic void setLogWriter(PrintWriter out) throws SQLException {}Overridepublic void setLoginTimeout(int seconds) throws SQLException {}Overridepublic int getLoginTimeout() throws SQLException {return 0;}Overridepublic Logger getParentLogger() throws SQLFeatureNotSupportedException {return null;}
}
实现DataSource接口我们本质上只使用了一个方法就是getConnection()这个无参的方法但是DataSource接口中所有的方法我们也都需要实现只是不用写方法体而已也就是存在了很多的 “废方法” 。 Primary注解 Order(1)用于设置此类的注入顺序。
使用 Overridepublic PageDataBsFactoryCalendar selectByExample(BsFactoryCalendarExample example) {PageDataBsFactoryCalendar pageData new PageData();DynamicDataSource1.flag.set(default-datasource);ListBsFactoryCalendar bsFactoryCalendars bsFactoryCalendarMapper.selectByExample(example);PageInfoBsFactoryCalendar pageInfo new PageInfo(bsFactoryCalendars);pageData.setRows(bsFactoryCalendars);pageData.setTotal(pageInfo.getTotal());return pageData;}1.2 指定数据源-继承AbstrictRoutingDataSource类
减少了代码的冗余但是还是会存在硬编码。
package com.inspur.spring.config.datasource;import cn.hutool.core.map.MapUtil;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.stereotype.Component;import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;/*** DynamicDataSource2* AbstrictRoutingDataSource的本质就是利用一个Map将数据源存储起来然后通过Key来得到Value来修改数据源。** author zhaoshuai-lc* date 2023/07/11*/Component
Primary
public class DynamicDataSource2 extends AbstractRoutingDataSource {public static ThreadLocalString flag new ThreadLocal();Resourceprivate DataSource defaultDatasource;Resourceprivate DataSource inspurZsDatasource;public DynamicDataSource2() {flag.set(defaultDatasource);}Overrideprotected Object determineCurrentLookupKey() {return flag.get();}Overridepublic void afterPropertiesSet() {MapObject, Object targetDataSource MapUtil.newConcurrentHashMap();// 将第一个数据源设置为默认的数据源super.setDefaultTargetDataSource(defaultDatasource);targetDataSource.put(defaultDatasource, defaultDatasource);targetDataSource.put(inspurZsDatasource, inspurZsDatasource);super.setTargetDataSources(targetDataSource);super.afterPropertiesSet();}
}
AbstrictRoutingDataSource的本质就是利用一个Map将数据源存储起来然后通过Key来得到Value来修改数据源。
使用 Overridepublic PageDataBsFactoryCalendar selectByExample(BsFactoryCalendarExample example) {PageDataBsFactoryCalendar pageData new PageData();DynamicDataSource2.flag.set(default-datasource);ListBsFactoryCalendar bsFactoryCalendars bsFactoryCalendarMapper.selectByExample(example);PageInfoBsFactoryCalendar pageInfo new PageInfo(bsFactoryCalendars);pageData.setRows(bsFactoryCalendars);pageData.setTotal(pageInfo.getTotal());return pageData;}1.3 指定数据源-使用Spring AOP 自定义注解的形式
Spring AOP 自定义注解的形式是一种推荐的写法减少代码的冗余且不存在硬编码。此方法适合对指定功能操作指定数据库的模式。
导入依赖
dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-aop/artifactId
/dependency开启AOP支持
package com.inspur;import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.EnableAspectJAutoProxy;SpringBootApplication(exclude {DataSourceAutoConfiguration.class})
EnableAspectJAutoProxy
public class WtMybatisStudyApplication {public static void main(String[] args) {SpringApplication.run(WtMybatisStudyApplication.class, args);}}
定义枚举来表示数据源的标识
package com.inspur.spring.config.datasource.enums;public enum DataSourceType {DEFAULT_DATASOURCE,INSPURZS_DATASOURCE,
}继承AbstractRoutingDataSource类
package com.inspur.spring.config.datasource;import cn.hutool.core.map.MapUtil;
import com.inspur.spring.config.datasource.enums.DataSourceType;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.stereotype.Component;import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;Primary
Component
public class DynamicDataSource3 extends AbstractRoutingDataSource {public static ThreadLocalString flag new ThreadLocal();Resourceprivate DataSource defaultDatasource;Resourceprivate DataSource inspurZsDatasource;public DynamicDataSource3() {flag.set(DataSourceType.DEFAULT_DATASOURCE.name());}Overrideprotected Object determineCurrentLookupKey() {return flag.get();}Overridepublic void afterPropertiesSet() {MapObject, Object targetDataSource MapUtil.newConcurrentHashMap();// 将第一个数据源设置为默认的数据源super.setDefaultTargetDataSource(defaultDatasource);targetDataSource.put(DataSourceType.DEFAULT_DATASOURCE.name(), defaultDatasource);targetDataSource.put(DataSourceType.INSPURZS_DATASOURCE.name(), inspurZsDatasource);super.setTargetDataSources(targetDataSource);super.afterPropertiesSet();}
}
自定义注解
package com.inspur.spring.config.datasource.annotation;import com.inspur.spring.config.datasource.enums.DataSourceType;import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;Target({ElementType.TYPE, ElementType.METHOD})
Retention(RetentionPolicy.RUNTIME)
Documented
public interface TargetDataSource {DataSourceType value() default DataSourceType.DEFAULT_DATASOURCE;
}
定义注解的实现类
package com.inspur.spring.config.datasource.annotation;import com.inspur.spring.config.datasource.DynamicDataSource3;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;import java.lang.reflect.Method;/*** TargetDataSourceAspect** author zhaoshuai-lc* date 2023/07/11*/
Component
Aspect
Slf4j
public class TargetDataSourceAspect {Before(within(com.inspur.spring.config.datasource.annotation.TargetDataSource) || annotation(com.inspur.spring.config.datasource.annotation.TargetDataSource))public void beforeNoticeUpdateDataSource(JoinPoint joinPoint) {TargetDataSource annotation null;Class? extends Object target joinPoint.getTarget().getClass();if (target.isAnnotationPresent(TargetDataSource.class)) {// 判断类上是否标注着注解annotation target.getAnnotation(TargetDataSource.class);log.info(类: {}, 标注了注解TargetDataSource, target);} else {Method method ((MethodSignature) joinPoint.getSignature()).getMethod();if (method.isAnnotationPresent(TargetDataSource.class)) {// 判断方法上是否标注着注解如果类和方法上都没有标注则报错annotation method.getAnnotation(TargetDataSource.class);log.info(方法: {}, 标注了注解TargetDataSource, method);} else {log.error(注解TargetDataSource只能用于类或者方法上, error: {} {}, target, method);throw new RuntimeException(注解TargetDataSource使用错误);}}// 切换数据源DynamicDataSource3.flag.set(annotation.value().name());}
}
使用
package com.inspur.spring.service;import com.github.pagehelper.PageInfo;
import com.inspur.spring.common.interfaceResult.PageData;
import com.inspur.spring.config.datasource.annotation.TargetDataSource;
import com.inspur.spring.config.datasource.enums.DataSourceType;
import com.inspur.spring.dao.BsFactoryCalendarMapper;
import com.inspur.spring.pojo.BsFactoryCalendar;
import com.inspur.spring.pojo.BsFactoryCalendarExample;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;import javax.annotation.Resource;
import java.util.List;Service
TargetDataSource(value DataSourceType.DEFAULT_DATASOURCE) // 方式三 多数据源设置
public class BsFactoryCalendarServiceImpl implements BsFactoryCalendarService {Resourceprivate BsFactoryCalendarMapper bsFactoryCalendarMapper;OverrideTransactionalpublic PageDataBsFactoryCalendar selectByExample(BsFactoryCalendarExample example) {PageDataBsFactoryCalendar pageData new PageData();ListBsFactoryCalendar bsFactoryCalendars bsFactoryCalendarMapper.selectByExample(example);PageInfoBsFactoryCalendar pageInfo new PageInfo(bsFactoryCalendars);pageData.setRows(bsFactoryCalendars);pageData.setTotal(pageInfo.getTotal());return pageData;}
}1.4 通过SqlSessionFactory指定的数据源来操作指定目录的XML文件
使用此方法则不会与上面所述的类有任何关系本方法会重新定义类。本方法也是一种推荐的方法适用于对指定数据库的操作也就是适合读写分离。不会存在代码冗余和存在硬编码。 配置YAML文件
spring:datasource:type: com.alibaba.druid.pool.DruidDataSourcedefault-datasource:username: rootpassword: rootjdbc-url: jdbc:mysql://localhost:3306/default?serverTimezoneUTCuseUnicodetruecharacterEncodingutf-8driver-class-name: com.mysql.jdbc.Driverinspur-zs-datasource:username: rootpassword: rootjdbc-url: jdbc:mysql://localhost:3306/inspur-zs?serverTimezoneUTCuseUnicodetruecharacterEncodingutf-8driver-class-name: com.mysql.jdbc.Drivermain:allow-bean-definition-overriding : truedruid:initial-size: 5min-idle: 1max-active: 20profiles:active: devmybatis:mapper-locations: classpath:/mapper/*.xmltype-aliases-package: com.inspur.pojoconfiguration:mapUnderscoreToCamelCase: truelog-impl: org.apache.ibatis.logging.slf4j.Slf4jImpl针对Mapper层通过SqlSessionFactory指定数据源来操作
package com.inspur.spring.config.datasource;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 org.springframework.transaction.PlatformTransactionManager;import javax.sql.DataSource;Configuration
MapperScan(basePackages com.inspur.spring.dao.defaultzs, sqlSessionFactoryRef DefaultSqlSessionFactory)
public class DefaultDatasourceConfig {PrimaryBean(name DefaultDatasource)ConfigurationProperties(prefix spring.datasource.default-datasource)public DataSource getDateSource1() {return DataSourceBuilder.create().build();}PrimaryBean(name DefaultSqlSessionFactory)public SqlSessionFactory sqlSessionFactory(Qualifier(DefaultDatasource) DataSource datasource) throws Exception {SqlSessionFactoryBean bean new SqlSessionFactoryBean();bean.setDataSource(datasource);// 设置mybatis的xml所在位置bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(classpath*:mapper/defaultzs/*.xml));return bean.getObject();}Bean(DefaultSqlSessionTemplate)Primarypublic SqlSessionTemplate sqlSessionTemplate(Qualifier(DefaultSqlSessionFactory) SqlSessionFactory factory) {return new SqlSessionTemplate(factory);}Beanpublic PlatformTransactionManager transactionManager(Qualifier(DefaultDatasource) DataSource dataSource) {return new DataSourceTransactionManager(dataSource);}
}
package com.inspur.spring.config.datasource;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 org.springframework.transaction.PlatformTransactionManager;import javax.sql.DataSource;Configuration
MapperScan(basePackages com.inspur.spring.dao.inspurzs, sqlSessionFactoryRef InspurZsSqlSessionFactory)
public class InspurZsDatasourceConfig {PrimaryBean(value InspurZsDatasource)ConfigurationProperties(prefix spring.datasource.inspur-zs-datasource)public DataSource getDateSource1() {return DataSourceBuilder.create().build();}PrimaryBean(value InspurZsSqlSessionFactory)public SqlSessionFactory sqlSessionFactory(Qualifier(InspurZsDatasource) DataSource datasource) throws Exception {SqlSessionFactoryBean bean new SqlSessionFactoryBean();bean.setDataSource(datasource);// 设置mybatis的xml所在位置bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(classpath*:mapper/inspurzs/*.xml));return bean.getObject();}Bean(value InspurZsSqlSessionTemplate)Primarypublic SqlSessionTemplate sqlSessionTemplate(Qualifier(InspurZsSqlSessionFactory) SqlSessionFactory factory) {return new SqlSessionTemplate(factory);}Beanpublic PlatformTransactionManager transactionManager(Qualifier(InspurZsDatasource) DataSource dataSource) {return new DataSourceTransactionManager(dataSource);}
}
MapperScan注解中的basePackages指向的是指定的Dao层。 MapperScan注解中sqlSessionFactoryRef 用来指定使用某个SqlSessionFactory来操作数据源。 bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(classpath*:mapper/inspurzs/*.xml));使用此种方法不会存在任何代码的冗余以及硬编码的存在但是需要分层明确。唯一的不足就是添加一个数据源就需要重新写一个类而这个类中的代码大部分又是相同的。
