目录
1.1Mybatis插件(推荐)
1.2基于AOP实现(推荐)
1.3基于实现DataSource接口实现(不推荐)
1.4实现在同一model下多个Mybatis
1.1 实现多个库的事物回滚策略
1.1Mybatis插件(推荐)
1.1使用mybatis过滤器来实现分表查询 (适用于 一表查 一表写 )
**记得写完之后将插件配置 @Bean
//使用mybatis插件过滤器来实现 两库 一表查一表写
@Intercepts({
@Signature(type = Executor.class,method = "update",args = {MappedStatement.class,Object.class}),
@Signature(type = Executor.class,method = "query",args = {MappedStatement.class,Object.class, RowBounds.class
, ResultHandler.class})
})
public class DynamicDataSourcePlugin implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
//获取执行方法的所有参数 (update,query)
//例如 int update(MappedStatement var1, Object var2) throws SQLException;
//参数为 MappedStatement var1, Object var2
Object[] args = invocation.getArgs();
// MappedStatement 封装了所有的CRUD元素和SQL
//执行方法参数第一个都是MappedStatement 这个方法封装了所有的CRUD元素和SQL
MappedStatement ms = (MappedStatement) args[0];
//如果类型为读 select类型
if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)){
DyNameDataSource.threadLocal.set("r");
}else {
DyNameDataSource.threadLocal.set("w");
}
//修改当前线程要选择的数据源key
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
if (target instanceof Executor){
return Plugin.wrap(target, this);
}else {
return target;
}
}
@Override
public void setProperties(Properties properties) {
}
}
配置两个DataSource 从springboot配置文件读取
@Configuration
public class DataSourceConfig {
@Value("${master.dataSource.url}")
private String masterUrl;
@Value("${master.dataSource.driver}")
private String masterDriver;
@Value("${master.dataSource.passowrd}")
private String masterUsername;
@Value("${master.dataSource.username}")
private String masterPassword;
@Value("${slave.dataSource.url}")
private String slaveUrl;
@Value("${slave.dataSource.driver}")
private String slaveDriver;
@Value("${slave.dataSource.passowrd}")
private String slaveUsername;
@Value("${slave.dataSource.username}")
private String slavePassword;
@Bean(name = "masterDruidDataSource")
public DruidDataSource masterDruidDataSource(){
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(masterDriver);
dataSource.setUrl(masterUrl);
dataSource.setPassword(masterPassword);
dataSource.setUsername(masterUsername);
return dataSource;
}
@Bean(name = "slaveDruidDataSource")
public DruidDataSource slaveDruidDataSource(){
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(slaveDriver);
dataSource.setUrl(slaveUrl);
dataSource.setPassword(slavePassword);
dataSource.setUsername(slaveUsername);
return dataSource;
}
// 将定义的mybatis插件放入到springboot容器当中
@Bean
public Interceptor interceptor(){
return new DynamicDataSourcePlugin();
}
}
application.properties配置文件
spring.application.name=mybatis # 主节点 master.dataSource.url=jdbc:mysql://192.168.1.10:3306/test master.dataSource.driver=com.mysql.jdbc.Driver master.dataSource.passowrd=root master.dataSource.username=123456 #从节点 slave.dataSource.url=jdbc:mysql://192.168.1.14:3306/test slave.dataSource.driver=com.mysql.jdbc.Driver slave.dataSource.passowrd=root slave.dataSource.username=123456
1.2基于AOP实现(推荐)
此方法适用于 两个不同数据库进行CRUD
1.1导包
org.springframework.boot spring-boot-starter-aop
1.2创建注解类
@Target({ElementType.METHOD,ElementType.TYPE})//注解可以使用在类和方法上
@Retention(RetentionPolicy.RUNTIME) //保留方式 SOURCE(此注解不会编译到Class文件上去)
//CLASS(会保留在Class文件上但不会被JVM加载)
//RUNTIME(会被JVM加载也会保留在Class文件上)
public @interface WR {
String value() default "w";
}
1.3 创建AOP切面类
@Component
@Aspect//AOP
public class DynamicDataSourceAspect {
// 使用前置通知
//within(com.wjj.service.Impl.*)代表这个包之内 有wr注解的方法都会被增强
//@annotation(wr)有注解 @WR
@Before("within(com.wjj.service.Impl.*) && @annotation(wr) ")
public void before(JoinPoint joinPoint,WR wr){
String value = wr.value();
DyNameDataSource.threadLocal.set(value);
System.out.println("value = " + value);
}
}
1.4 配置俩 DataSource
@Configuration
public class DataSourceConfig {
@Value("${master.dataSource.url}")
private String masterUrl;
@Value("${master.dataSource.driver}")
private String masterDriver;
@Value("${master.dataSource.passowrd}")
private String masterUsername;
@Value("${master.dataSource.username}")
private String masterPassword;
@Value("${slave.dataSource.url}")
private String slaveUrl;
@Value("${slave.dataSource.driver}")
private String slaveDriver;
@Value("${slave.dataSource.passowrd}")
private String slaveUsername;
@Value("${slave.dataSource.username}")
private String slavePassword;
@Bean(name = "masterDruidDataSource")
public DruidDataSource masterDruidDataSource(){
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(masterDriver);
dataSource.setUrl(masterUrl);
dataSource.setPassword(masterPassword);
dataSource.setUsername(masterUsername);
return dataSource;
}
@Bean(name = "slaveDruidDataSource")
public DruidDataSource slaveDruidDataSource(){
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(slaveDriver);
dataSource.setUrl(slaveUrl);
dataSource.setPassword(slavePassword);
dataSource.setUsername(slaveUsername);
return dataSource;
}
1.3基于实现DataSource接口实现(不推荐)
弊端太多如果springboot使用 DataSource当中的其他方法我们又不会重写所以不推荐使用
@Primary//优先级最高
@Configuration
public class DyNameDataSource implements DataSource {
public static ThreadLocal threadLocal = new ThreadLocal<>();
@Resource(name = "masterDruidDataSource")
DataSource masterDruidDataSource;
@Resource(name = "slaveDruidDataSource")
DataSource slaveDruidDataSource;
// 决定springboot初始化使用那个 getConnection() 方法
@Override
public Connection getConnection() throws SQLException {
if (threadLocal.get().equals("w")) {
return masterDruidDataSource.getConnection();
}
return slaveDruidDataSource.getConnection();
}
@Override
public Connection getConnection(String s, String s1) throws SQLException {
return null;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
return null;
}
@Override
public void setLogWriter(PrintWriter printWriter) throws SQLException {
}
@Override
public void setLoginTimeout(int i) throws SQLException {
}
@Override
public int getLoginTimeout() throws SQLException {
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
return null;
}
@Override
public T unwrap(Class aClass) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class> aClass) throws SQLException {
return false;
}
// bean初始化执行的方法
@Override
public void afterPropertiesSet() throws Exception {
threadLocal.set("w");
}
}
1.4实现在同一model下多个Mybatis
1.1创建mybtais1
@Configuration
//1.指定扫描的包
//指定那个SqlSessionFactory库 (主库)
@MapperScan(basePackages = "com.wjj.m.mapper",sqlSessionFactoryRef = "mSqlSessionFactory")
public class MMybatisConfig {
@Value("${slave.dataSource.url}")
private String slaveUrl;
@Value("${slave.dataSource.driver}")
private String slaveDriver;
@Value("${slave.dataSource.passowrd}")
private String slaveUsername;
@Value("${slave.dataSource.username}")
private String slavePassword;
@Bean(name = "slaveDruidDataSource")
public DruidDataSource slaveDruidDataSource(){
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(slaveDriver);
dataSource.setUrl(slaveUrl);
dataSource.setPassword(slavePassword);
dataSource.setUsername(slaveUsername);
return dataSource;
}
@Bean
@Primary
public SqlSessionFactory mSqlSessionFactory(@Qualifier("slaveDruidDataSource") DataSource dataSource) throws Exception {
final SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
return sqlSessionFactoryBean.getObject();
}
@Bean
public DataSourceTransactionManager MDataSourceTransactionManager(@Qualifier("slaveDruidDataSource") DataSource dataSource){
DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
dataSourceTransactionManager.setDataSource(dataSource);
return dataSourceTransactionManager;
}
}
1.2 创建mybtais2
@Configuration
//1.指定扫描的包
//指定那个SqlSessionFactory库 (主库)
@MapperScan(basePackages = "com.wjj.w.mapper",sqlSessionFactoryRef = "wSqlSessionFactory")
public class WMybatisConfig {
@Value("${master.dataSource.url}")
private String masterUrl;
@Value("${master.dataSource.driver}")
private String masterDriver;
@Value("${master.dataSource.passowrd}")
private String masterUsername;
@Value("${master.dataSource.username}")
private String masterPassword;
@Bean(name = "masterDruidDataSource")
public DruidDataSource masterDruidDataSource(){
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(masterDriver);
dataSource.setUrl(masterUrl);
dataSource.setPassword(masterPassword);
dataSource.setUsername(masterUsername);
return dataSource;
}
@Bean
// @Primary
public SqlSessionFactory wSqlSessionFactory(@Qualifier("masterDruidDataSource")DataSource dataSource) throws Exception {
final SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
return sqlSessionFactoryBean.getObject();
}
@Bean
public DataSourceTransactionManager WDataSourceTransactionManager(@Qualifier("masterDruidDataSource") DataSource dataSource){
DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
dataSourceTransactionManager.setDataSource(dataSource);
return dataSourceTransactionManager;
}
}
1.3 Mybtais Mapper和xml文件的目录结构
1.1 实现多个库的事物回滚策略
1.1注入事物工具bean
1.1WMybatisConfig当中
@Bean(name = "WTransactionTemplate")
public TransactionTemplate WTransactionTemplate(@Qualifier("masterDruidDataSource") DataSource dataSource){
return new TransactionTemplate(WDataSourceTransactionManager(dataSource));
}
1.2MMybatisConfig当中
@Bean(name = "MTransactionTemplate")
public TransactionTemplate MTransactionTemplate(@Qualifier("slaveDruidDataSource") DataSource dataSource){
return new TransactionTemplate(MDataSourceTransactionManager(dataSource));
}
1.3ServiceImpl当中注入两个数据源
@Resource(name = "MTransactionTemplate") TransactionTemplate MTransactionTemplate; @Resource(name = "WTransactionTemplate") TransactionTemplate WTransactionTemplate;
1.4 函数式多库事物回滚策略
@Override public ListgetAllM() { //此处可以套很多层都没问题 MTransactionTemplate.execute(status -> { WTransactionTemplate.execute(status1 -> { try { //库一操作 getAll(); //库二操作 getAll2(); int a = 1 / 0; return true; } catch (Exception e) { e.printStackTrace(); //报错就回滚库一 status.setRollbackOnly(); //报错就回滚库二 status1.setRollbackOnly(); return false; } }); return true; }); return null; }



