如何做网站的统计,阜阳建设大厦网站,赣州章贡区旅游景点,做餐饮在环保局网站备案多数据源产生的场景 一般情况下#xff0c;不会有多数据源这样的场景出现#xff0c;但老项目或者特殊需求的项目#xff0c;可能会有这样的场景 同一个应用需要访问两个数据库不用数据库中间件的读写分离 注入数据源选择的时机 声明两个数据源实例#xff0c;在getConnect…多数据源产生的场景 一般情况下不会有多数据源这样的场景出现但老项目或者特殊需求的项目可能会有这样的场景 同一个应用需要访问两个数据库不用数据库中间件的读写分离 注入数据源选择的时机 声明两个数据源实例在getConnection的时候根据业务的不同注入不同数据源的连接 环境准备 准备sql脚本建立两个库这里mysql为例 create database stu;
create database tech;
use stu;
create table student
(id varchar(50) not null comment 主键,name varchar(50) null comment 姓名,stu_no varchar(50) null comment 学号,constraint student_pk primary key (id)
);insert into student values (1,张同学,111);
insert into student values (2,李同学,222);
use tech;
create table teacher
(id varchar(50) not null comment 主键,name varchar(50) null comment 姓名,teach_no varchar(50) null comment 教师号,constraint teacher_pk primary key (id)
);insert into teacher values (1,王老师,111);
insert into teacher values (2,高老师,222);
实现DataSource方式实现多数据源
配置多数据源
server:port: 9000
spring:datasource:type: com.alibaba.druid.pool.DruidDataSourcedatasource1:url: jdbc:mysql://shilei.tech:3306/stu?useSSLtrueserverTimezoneAsia/Shanghaiusername: rootpassword: root123456driver-class-name: com.mysql.cj.jdbc.Driverdatasource2:url: jdbc:mysql://shilei.tech:3306/tech?useSSLtrueserverTimezoneAsia/Shanghaiusername: rootpassword: root123456driver-class-name: com.mysql.cj.jdbc.Driverdruid:initial-size: 5min-idle: 1max-active: 20mybatis-plus:mapper-locations: classpath:/mapper/*.xmltype-aliases-package: com.datasource.dynamicdatasource.model添加数据源配置
package com.datasource.dynamicdatasource.config;import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;import javax.sql.DataSource;/*** author sl*/
Configuration
public class DataSourceConfig {Bean(dataSource1)ConfigurationProperties(prefix spring.datasource.datasource1)public DataSource dataSource1(){DruidDataSource druidDataSource new DruidDataSource();return druidDataSource;}Bean(dataSource2)ConfigurationProperties(prefix spring.datasource.datasource2)public DataSource dataSource2(){DruidDataSource druidDataSource new DruidDataSource();return druidDataSource;}
}实现DataSource多数据源
package com.datasource.dynamicdatasource.config;import org.springframework.beans.factory.InitializingBean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;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;/*** author sl* Primary主要注入的bean*/
Configuration
Primary
public class DynamicDataSource implements DataSource {public static ThreadLocalString nameFlag new ThreadLocal();Resourceprivate DataSource dataSource1;Resourceprivate DataSource dataSource2;Overridepublic Connection getConnection() throws SQLException {if(student.equals(nameFlag.get())){return dataSource1.getConnection();}return dataSource2.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;}
}测试多数据源
package com.datasource.dynamicdatasource.controller;import com.datasource.dynamicdatasource.config.DynamicDataSource;
import com.datasource.dynamicdatasource.model.Student;
import com.datasource.dynamicdatasource.model.Teacher;
import com.datasource.dynamicdatasource.service.StudentService;
import com.datasource.dynamicdatasource.service.TeacherService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.List;/*** author sl*/
RestController
public class TestDataSourceController {Autowiredprivate StudentService studentService;Autowiredprivate TeacherService teacherService;GetMapping(/stu)public String getStu(){DynamicDataSource.nameFlag.set(student);ListStudent allStudent studentService.findAllStudent();return allStudent.toString();}GetMapping(/tech)public String getTech(){DynamicDataSource.nameFlag.set(teacher);ListTeacher allTeacher teacherService.findAllTeacher();return allTeacher.toString();}
}效果如下所示 此实现方式的弊端 实现DataSource接口我们本质上只使用了一个方法就是getConnection()这个无参的方法其他方法当内部调用时可能会导致错误我们不可能实现所有的方法所以我们继承AbstractRoutingDataSource抽象类 继承AbstractRoutingDataSource实现多数据源
AbstractRoutingDataSource的结构 可以看到AbstractRoutingDataSource继承自DataSource提供了一些实现方法 AbstractRoutingDataSource的重要属性 targetDataSources 所有数据源 (需指定) defaultTargetDataSource 默认数据源(需指定) resolvedDataSources targetDataSources 负责最终切换的数据源map 等于 tagetDataSources 继承AbstractRoutingDataSource实现多数据源
package com.datasource.dynamicdatasource.config;import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;/*** author sl* Primary主要注入的bean*/
Configuration
Primary
public class DynamicDataSource extends AbstractRoutingDataSource {public static ThreadLocalString nameFlag new ThreadLocal();Resourceprivate DataSource dataSource1;Resourceprivate DataSource dataSource2;Overrideprotected Object determineCurrentLookupKey() {// 返回当前数据源的标识return nameFlag.get();}Overridepublic void afterPropertiesSet() {// 为targetDataSources 初始化所有数据源MapObject,Object targetDataSourcesnew HashMap();targetDataSources.put(student,dataSource1);targetDataSources.put(teacher,dataSource2);super.setTargetDataSources(targetDataSources);// 设置默认数据源super.setDefaultTargetDataSource(dataSource1);// 循环给resolvedDataSources也就是最终数据源mapsuper.afterPropertiesSet();}
}determineCurrentLookupKey的作用 看一段源码就是通过determineCurrentLookupKey获取数据源的key然后去resolvedDataSources中取数据源resolvedDataSources数据源其实就是targetDataSources protected DataSource determineTargetDataSource() {Assert.notNull(this.resolvedDataSources, DataSource router not initialized);Object lookupKey determineCurrentLookupKey();DataSource dataSource this.resolvedDataSources.get(lookupKey);if (dataSource null (this.lenientFallback || lookupKey null)) {dataSource this.resolvedDefaultDataSource;}if (dataSource null) {throw new IllegalStateException(Cannot determine target DataSource for lookup key [ lookupKey ]);}return dataSource;} 测试多数据源
package com.datasource.dynamicdatasource.controller;import com.datasource.dynamicdatasource.config.DynamicDataSource;
import com.datasource.dynamicdatasource.model.Student;
import com.datasource.dynamicdatasource.model.Teacher;
import com.datasource.dynamicdatasource.service.StudentService;
import com.datasource.dynamicdatasource.service.TeacherService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.List;/*** author sl*/
RestController
public class TestDataSourceController {Autowiredprivate StudentService studentService;Autowiredprivate TeacherService teacherService;GetMapping(/stu)public String getStu(){// 默认数据源就是studentListStudent allStudent studentService.findAllStudent();return allStudent.toString();}GetMapping(/tech)public String getTech(){DynamicDataSource.nameFlag.set(teacher);ListTeacher allTeacher teacherService.findAllTeacher();return allTeacher.toString();}
}AOP自定义注解方式AbstractRoutingDataSource实现多数据源 数据源的切换还是使用AbstractRoutingDataSource只不过切换方式采用aop拦截自定义注解切换数据源这种方式也是mybatis-plus多数据源插件所采用的方式 自定义注解
package com.datasource.dynamicdatasource.annotation;import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;/*** author sl*/
Target({ElementType.TYPE, ElementType.METHOD})
Retention(RetentionPolicy.RUNTIME)
public interface MyDataSource {String value() default student;
}配置切面
dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-aop/artifactId
/dependency
package com.datasource.dynamicdatasource.aspect;import com.datasource.dynamicdatasource.annotation.MyDataSource;
import com.datasource.dynamicdatasource.config.DynamicDataSource;
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.stereotype.Component;import java.lang.reflect.Method;/*** author sl*Aspect 标识是一个切面*/
Aspect
Component
public class DatasourceAspect {/*** 切点规则*/Pointcut(annotation(com.datasource.dynamicdatasource.annotation.MyDataSource))public void pointcut() {}Before(pointcut())public void dataSourceAspect(JoinPoint joinPoint){// 获取方法Method method ((MethodSignature) joinPoint.getSignature()).getMethod();// 判断方法中是否添加了注解if(method.isAnnotationPresent(MyDataSource.class)){// 获取方法上的注解MyDataSource annotation method.getAnnotation(MyDataSource.class);String value annotation.value();// 设置数据源DynamicDataSource.nameFlag.set(value);}}
}测试自定义注解切换数据源 GetMapping(/tech)MyDataSource(teacher)public String getTech(){ListTeacher allTeacher teacherService.findAllTeacher();return allTeacher.toString();}
dynamic-datasource多数据源组件实现多数据源 官方文档及搭建指南地址多数据源 | MyBatis-Plus 引入依赖
dependencygroupIdcom.baomidou/groupIdartifactIddynamic-datasource-spring-boot-starter/artifactIdversion3.6.1/version
/dependency
配置数据源
server:port: 9000
spring:datasource:type: com.alibaba.druid.pool.DruidDataSourcedynamic:#设置默认的数据源或者数据源组,默认值即为masterprimary: master#严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源strict: falsedatasource:master:url: jdbc:mysql://shilei.tech:3306/stu?useSSLtrueserverTimezoneAsia/Shanghaiusername: rootpassword: root123456driver-class-name: com.mysql.cj.jdbc.Driverteacher:url: jdbc:mysql://shilei.tech:3306/tech?useSSLtrueserverTimezoneAsia/Shanghaiusername: rootpassword: root123456driver-class-name: com.mysql.cj.jdbc.Driverdruid:initial-size: 5min-idle: 1max-active: 20mybatis-plus:mapper-locations: classpath:/mapper/*.xmltype-aliases-package: com.datasource.dynamicdatasource.model测试数据源切换 数据源切换使用DS注解不使用此注解使用默认数据源方法上使用类上使用 package com.datasource.dynamicdatasource.controller;import com.baomidou.dynamic.datasource.annotation.DS;
import com.datasource.dynamicdatasource.model.Student;
import com.datasource.dynamicdatasource.model.Teacher;
import com.datasource.dynamicdatasource.service.StudentService;
import com.datasource.dynamicdatasource.service.TeacherService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.List;/*** author sl*/
RestController
public class TestDataSourceController {Autowiredprivate StudentService studentService;Autowiredprivate TeacherService teacherService;GetMapping(/stu)public String getStu(){ListStudent allStudent studentService.findAllStudent();return allStudent.toString();}GetMapping(/tech)DS(teacher)public String getTech(){ListTeacher allTeacher teacherService.findAllTeacher();return allTeacher.toString();}
}项目启动日志中可以看到两个数据源的加载信息 访问tech以及stu都能正常访问代表动态数据源添加成功 需要注意的问题 使用多数据源要注意事务的控制提交和回滚策略可以观看spring多数据源事务解决方案
