分库分表——多数据源切换以及读写分离

最新推荐文章于 2023-06-20 11:17:29 发布
最新推荐文章于 2023-06-20 11:17:29 发布
阅读量1k 收藏 4
点赞数
分类专栏： 高并发 java 文章标签： 读写分离 多数据源
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_39246466/article/details/121428123
版权

多数据源实现：（数据进入系统，数据会进入那个数据源哪个数据库的哪个表中）

代码中四主四从配置

application.properties配置

server.context-path=/
server.port=8001


## Spring配置:
spring.http.encoding.charset=UTF-8
spring.jackson.date-format=yyyy-MM-dd HH:mm:ss
spring.jackson.time-zone=GMT+8
spring.jackson.default-property-inclusion=NON_NULL


druid.type=com.alibaba.druid.pool.DruidDataSource
druid.order1-master.url=jdbc:mysql://192.168.11.31:3306/order1?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
druid.order1-master.driver-class-name=com.mysql.jdbc.Driver
druid.order1-master.username=root
druid.order1-master.password=root
druid.order1-master.initialSize=5
druid.order1-master.minIdle=1
druid.order1-master.maxIdle=10
druid.order1-master.maxActive=100
druid.order1-master.maxWait=60000
druid.order1-master.timeBetweenEvictionRunsMillis=60000
druid.order1-master.minEvictableIdleTimeMillis=300000
druid.order1-master.validationQuery=SELECT 1 FROM DUAL
druid.order1-master.testWhileIdle=true
druid.order1-master.testOnBorrow=false
druid.order1-master.testOnReturn=false
druid.order1-master.poolPreparedStatements=true
druid.order1-master.maxPoolPreparedStatementPerConnectionSize= 20
druid.order1-master.filters=stat,wall,log4j
druid.order1-master.useGlobalDataSourceStat=true

druid.order2-master.url=jdbc:mysql://192.168.11.31:3306/order2?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
druid.order2-master.driver-class-name=com.mysql.jdbc.Driver
druid.order2-master.username=root
druid.order2-master.password=root
druid.order2-master.initialSize=5
druid.order2-master.minIdle=1
druid.order2-master.maxIdle=10
druid.order2-master.maxActive=100
druid.order2-master.maxWait=60000
druid.order2-master.timeBetweenEvictionRunsMillis=60000
druid.order2-master.minEvictableIdleTimeMillis=300000
druid.order2-master.validationQuery=SELECT 1 FROM DUAL
druid.order2-master.testWhileIdle=true
druid.order2-master.testOnBorrow=false
druid.order2-master.testOnReturn=false
druid.order2-master.poolPreparedStatements=true
druid.order2-master.maxPoolPreparedStatementPerConnectionSize= 20
druid.order2-master.filters=stat,wall,log4j
druid.order2-master.useGlobalDataSourceStat=true

druid.order3-master.url=jdbc:mysql://192.168.11.31:3306/order3?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
druid.order3-master.driver-class-name=com.mysql.jdbc.Driver
druid.order3-master.username=root
druid.order3-master.password=root
druid.order3-master.initialSize=5
druid.order3-master.minIdle=1
druid.order3-master.maxIdle=10
druid.order3-master.maxActive=100
druid.order3-master.maxWait=60000
druid.order3-master.timeBetweenEvictionRunsMillis=60000
druid.order3-master.minEvictableIdleTimeMillis=300000
druid.order3-master.validationQuery=SELECT 1 FROM DUAL
druid.order3-master.testWhileIdle=true
druid.order3-master.testOnBorrow=false
druid.order3-master.testOnReturn=false
druid.order3-master.poolPreparedStatements=true
druid.order3-master.maxPoolPreparedStatementPerConnectionSize= 20
druid.order3-master.filters=stat,wall,log4j
druid.order3-master.useGlobalDataSourceStat=true

druid.order4-master.url=jdbc:mysql://192.168.11.31:3306/order4?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
druid.order4-master.driver-class-name=com.mysql.jdbc.Driver
druid.order4-master.username=root
druid.order4-master.password=root
druid.order4-master.initialSize=5
druid.order4-master.minIdle=1
druid.order4-master.maxIdle=10
druid.order4-master.maxActive=100
druid.order4-master.maxWait=60000
druid.order4-master.timeBetweenEvictionRunsMillis=60000
druid.order4-master.minEvictableIdleTimeMillis=300000
druid.order4-master.validationQuery=SELECT 1 FROM DUAL
druid.order4-master.testWhileIdle=true
druid.order4-master.testOnBorrow=false
druid.order4-master.testOnReturn=false
druid.order4-master.poolPreparedStatements=true
druid.order4-master.maxPoolPreparedStatementPerConnectionSize= 20
druid.order4-master.filters=stat,wall,log4j
druid.order4-master.useGlobalDataSourceStat=true

druid.order1-slave.url=jdbc:mysql://192.168.11.32:3306/order1?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
druid.order1-slave.driver-class-name=com.mysql.jdbc.Driver
druid.order1-slave.username=root
druid.order1-slave.password=root
druid.order1-slave.initialSize=5
druid.order1-slave.minIdle=1
druid.order1-slave.maxIdle=10
druid.order1-slave.maxActive=100
druid.order1-slave.maxWait=60000
druid.order1-slave.timeBetweenEvictionRunsMillis=60000
druid.order1-slave.minEvictableIdleTimeMillis=300000
druid.order1-slave.validationQuery=SELECT 1 FROM DUAL
druid.order1-slave.testWhileIdle=true
druid.order1-slave.testOnBorrow=false
druid.order1-slave.testOnReturn=false
druid.order1-slave.poolPreparedStatements=true
druid.order1-slave.maxPoolPreparedStatementPerConnectionSize= 20
druid.order1-slave.filters=stat,wall,log4j
druid.order1-slave.useGlobalDataSourceStat=true

druid.order2-slave.url=jdbc:mysql://192.168.11.32:3306/order2?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
druid.order2-slave.driver-class-name=com.mysql.jdbc.Driver
druid.order2-slave.username=root
druid.order2-slave.password=root
druid.order2-slave.initialSize=5
druid.order2-slave.minIdle=1
druid.order2-slave.maxIdle=10
druid.order2-slave.maxActive=100
druid.order2-slave.maxWait=60000
druid.order2-slave.timeBetweenEvictionRunsMillis=60000
druid.order2-slave.minEvictableIdleTimeMillis=300000
druid.order2-slave.validationQuery=SELECT 1 FROM DUAL
druid.order2-slave.testWhileIdle=true
druid.order2-slave.testOnBorrow=false
druid.order2-slave.testOnReturn=false
druid.order2-slave.poolPreparedStatements=true
druid.order2-slave.maxPoolPreparedStatementPerConnectionSize= 20
druid.order2-slave.filters=stat,wall,log4j
druid.order2-slave.useGlobalDataSourceStat=true

druid.order3-slave.url=jdbc:mysql://192.168.11.32:3306/order3?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
druid.order3-slave.driver-class-name=com.mysql.jdbc.Driver
druid.order3-slave.username=root
druid.order3-slave.password=root
druid.order3-slave.initialSize=5
druid.order3-slave.minIdle=1
druid.order3-slave.maxIdle=10
druid.order3-slave.maxActive=100
druid.order3-slave.maxWait=60000
druid.order3-slave.timeBetweenEvictionRunsMillis=60000
druid.order3-slave.minEvictableIdleTimeMillis=300000
druid.order3-slave.validationQuery=SELECT 1 FROM DUAL
druid.order3-slave.testWhileIdle=true
druid.order3-slave.testOnBorrow=false
druid.order3-slave.testOnReturn=false
druid.order3-slave.poolPreparedStatements=true
druid.order3-slave.maxPoolPreparedStatementPerConnectionSize= 20
druid.order3-slave.filters=stat,wall,log4j
druid.order3-slave.useGlobalDataSourceStat=true

druid.order4-slave.url=jdbc:mysql://192.168.11.32:3306/order4?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
druid.order4-slave.driver-class-name=com.mysql.jdbc.Driver
druid.order4-slave.username=root
druid.order4-slave.password=root
druid.order4-slave.initialSize=5
druid.order4-slave.minIdle=1
druid.order4-slave.maxIdle=10
druid.order4-slave.maxActive=100
druid.order4-slave.maxWait=60000
druid.order4-slave.timeBetweenEvictionRunsMillis=60000
druid.order4-slave.minEvictableIdleTimeMillis=300000
druid.order4-slave.validationQuery=SELECT 1 FROM DUAL
druid.order4-slave.testWhileIdle=true
druid.order4-slave.testOnBorrow=false
druid.order4-slave.testOnReturn=false
druid.order4-slave.poolPreparedStatements=true
druid.order4-slave.maxPoolPreparedStatementPerConnectionSize= 20
druid.order4-slave.filters=stat,wall,log4j
druid.order4-slave.useGlobalDataSourceStat=true

mybatis.type-aliases-package=bhz.order                
mybatis.mapper-locations=classpath:bhz/order/mapping/*.xml

logging.level.tk.mybatis=TRACE
        
pagehelper.dialect=com.github.pagehelper.dialect.helper.MySqlDialect
pagehelper.reasonable=true
pagehelper.supportMethodsArguments=true
pagehelper.params=count=countSql

application:

package bhz.order;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

	public static void main(String[] args) {
		
		SpringApplication app = new SpringApplication(Application.class);
		app.run(args);
		
	}

}

MainConfig :扫描配置

bhz.order.config.*配置数据源
package bhz.order;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;

@Configuration
@MapperScan(basePackages = "bhz.order.mapper")
@ComponentScan(basePackages = {"bhz.order.*", "bhz.order.config.*"})
public class MainConfig {

}

DataBaseContextHolder：定义多数据源：定义四主四从，将读取的数据员存储在ThreadLocal中。
package bhz.order.config.database;

public class DataBaseContextHolder {
	
	public enum DataBaseType {
		ORDER1_MASTER("order1-master"),
		ORDER2_MASTER("order2-master"),
		ORDER3_MASTER("order3-master"),
		ORDER4_MASTER("order4-master"),
		ORDER1_SLAVE("order1-slave"),
		ORDER2_SLAVE("order2-slave"),
		ORDER3_SLAVE("order3-slave"),
		ORDER4_SLAVE("order4-slave");
		private String code;
		
		private DataBaseType(String code){
			this.code = code;
		}
		
		public String getCode(){
			return code;
		}
	}
	
	private static final ThreadLocal<DataBaseType> contextHolder = new ThreadLocal<DataBaseType>();
	
	/**
	 * <B>方法名称：</B>设置数据源类型<BR>
	 * <B>概要说明：</B><BR>
	 * @author baihezhuo
	 * @since 2018年2月3日 上午11:54:42
	 * @param dataBaseType
	 */
	public static void setDataBaseType(DataBaseType dataBaseType) {
		if(dataBaseType == null) throw new NullPointerException();
		contextHolder.set(dataBaseType);
	}
	
	
	public static DataBaseType getDataBaseType(){
		return contextHolder.get() == null ? DataBaseType.ORDER1_MASTER : contextHolder.get();
	}
	
	public static void clearDataBaseType(){
		contextHolder.remove();
	}
}

注入定义的数据源

@ConfigurationProperties(prefix = "druid.order1-master")读取以druid.order1-master开头的数据源配置，
package bhz.order.config.database;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;

@Configuration		//相当于一个xml配置文件
public class DataSourceConfiguration {
	
	private static Logger LOGGER = org.slf4j.LoggerFactory.getLogger(DataSourceConfiguration.class);
	
	@Value("${druid.type}")
	private Class<? extends DataSource> dataSourceType;
	
	@Bean(name = "order1-master")
	@Primary
	@ConfigurationProperties(prefix = "druid.order1-master")
	public DataSource order1MasterDataSource() throws SQLException {
		DataSource order1MasterDataSource = DataSourceBuilder.create().type(dataSourceType).build();
		LOGGER.info("============= order1-master: {} ================", order1MasterDataSource);
		return order1MasterDataSource;
	}
	
	@Bean(name = "order2-master")
	@ConfigurationProperties(prefix = "druid.order2-master")
	public DataSource order2MasterDataSource() throws SQLException {
		DataSource order2MasterDataSource = DataSourceBuilder.create().type(dataSourceType).build();
		LOGGER.info("============= order2-master: {} ================", order2MasterDataSource);
		return order2MasterDataSource;
	}
	
	@Bean(name = "order3-master")
	@ConfigurationProperties(prefix = "druid.order3-master")
	public DataSource order3MasterDataSource() throws SQLException {
		DataSource order3MasterDataSource = DataSourceBuilder.create().type(dataSourceType).build();
		LOGGER.info("============= order3-master: {} ================", order3MasterDataSource);
		return order3MasterDataSource;
	}
	
	@Bean(name = "order4-master")
	@ConfigurationProperties(prefix = "druid.order4-master")
	public DataSource order4MasterDataSource() throws SQLException {
		DataSource order4MasterDataSource = DataSourceBuilder.create().type(dataSourceType).build();
		LOGGER.info("============= order4-master: {} ================", order4MasterDataSource);
		return order4MasterDataSource;
	}
	
	@Bean(name = "order1-slave")
	@ConfigurationProperties(prefix = "druid.order1-slave")
	public DataSource order1SlaveDataSource() throws SQLException {
		DataSource order1SlaveDataSource = DataSourceBuilder.create().type(dataSourceType).build();
		LOGGER.info("============= order1-slave: {} ================", order1SlaveDataSource);
		return order1SlaveDataSource;
	}

	@Bean(name = "order2-slave")
	@ConfigurationProperties(prefix = "druid.order2-slave")
	public DataSource order2SlaveDataSource() throws SQLException {
		DataSource order2SlaveDataSource = DataSourceBuilder.create().type(dataSourceType).build();
		LOGGER.info("============= order2-slave: {} ================", order2SlaveDataSource);
		return order2SlaveDataSource;
	}
	
	@Bean(name = "order3-slave")
	@ConfigurationProperties(prefix = "druid.order3-slave")
	public DataSource order3SlaveDataSource() throws SQLException {
		DataSource order3SlaveDataSource = DataSourceBuilder.create().type(dataSourceType).build();
		LOGGER.info("============= order3-slave: {} ================", order3SlaveDataSource);
		return order3SlaveDataSource;
	}
	
	@Bean(name = "order4-slave")
	@ConfigurationProperties(prefix = "druid.order4-slave")
	public DataSource order4SlaveDataSource() throws SQLException {
		DataSource order4SlaveDataSource = DataSourceBuilder.create().type(dataSourceType).build();
		LOGGER.info("============= order4-slave: {} ================", order4SlaveDataSource);
		return order4SlaveDataSource;
	}
	
	@Bean
	public ServletRegistrationBean druidServlet(){
		//spring boot 的方式 自己写一个servlet 
		ServletRegistrationBean reg = new ServletRegistrationBean();
		reg.setServlet(new StatViewServlet());
		reg.addUrlMappings("/druid/*");
		reg.addInitParameter("allow", "192.168.1.1");
		//reg.addInitParameter("deny", "/deny");
		LOGGER.info("============= init druid servlet ================");
		return reg;
	}
	
	@Bean
	public FilterRegistrationBean druidFilter(){
		FilterRegistrationBean ftr = new FilterRegistrationBean();
		ftr.setFilter(new WebStatFilter());
		ftr.addUrlPatterns("/*");
		ftr.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico, /druid/*");
		LOGGER.info("============= init druid filter ================");
		return ftr;
	}
	
}

MyBatisConfiguration加载数据源，要在数据源定义装配后

@AutoConfigureAfter(value = {DataSourceConfiguration.class})表示在DataSourceConfiguration加载后执行装配。@Resource(name= "order1-master")引入定义的数据源bean,最终生成DataSource，order1-master指的是DataSourceConfiguration对应的bean的name。

代理使用方法实现动态数据源DynamicDataSource，方法bean名称dynamicDataSource（自定义继承了extends AbstractRoutingDataSource类）.DynamicDataSource 负责帮我们动态切换数据源。

将动态数据源绑定在SqlSessionFactory中

package bhz.order.config.database;

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

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import bhz.order.config.database.DataBaseContextHolder.DataBaseType;


@Configuration
//@EnableTransactionManagement
@AutoConfigureAfter(value = {DataSourceConfiguration.class})
public class MyBatisConfiguration {

	@Resource(name= "order1-master")
	private DataSource order1MasterDataSource;
	
	@Resource(name= "order2-master")
	private DataSource order2MasterDataSource;
	
	@Resource(name= "order3-master")
	private DataSource order3MasterDataSource;
	
	@Resource(name= "order4-master")
	private DataSource order4MasterDataSource;
	
	@Resource(name= "order1-slave")
	private DataSource order1SlaveDataSource;
	
	@Resource(name= "order2-slave")
	private DataSource order2SlaveDataSource;
	
	@Resource(name= "order3-slave")
	private DataSource order3SlaveDataSource;
	
	@Resource(name= "order4-slave")
	private DataSource order4SlaveDataSource;
	
	
	@Bean("dynamicDataSource")
	public DynamicDataSource roundRobinDataSourceProxy(){
		
		Map<Object, Object> targetDataSource = new HashMap<Object, Object>();
		
		targetDataSource.put(DataBaseType.ORDER1_MASTER, order1MasterDataSource);
		targetDataSource.put(DataBaseType.ORDER2_MASTER, order2MasterDataSource);
		targetDataSource.put(DataBaseType.ORDER3_MASTER, order3MasterDataSource);
		targetDataSource.put(DataBaseType.ORDER4_MASTER, order4MasterDataSource);
		targetDataSource.put(DataBaseType.ORDER1_SLAVE, order1SlaveDataSource);
		targetDataSource.put(DataBaseType.ORDER2_SLAVE, order2SlaveDataSource);
		targetDataSource.put(DataBaseType.ORDER3_SLAVE, order3SlaveDataSource);
		targetDataSource.put(DataBaseType.ORDER4_SLAVE, order4SlaveDataSource);
		
		//	实例化动态数据源
		DynamicDataSource proxy = new DynamicDataSource();
		//	盛放所以需要切换的数据源
		proxy.setTargetDataSources(targetDataSource);
		//	设置默认的数据源
		proxy.setDefaultTargetDataSource(order1MasterDataSource);
		
		return proxy;
	}
	
	@Bean(name="sqlSessionFactory")
	public SqlSessionFactory sqlSessionFactoryBean(DynamicDataSource dynamicDataSource) {
		
		System.err.println("----------------执行--------------");
		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
		bean.setDataSource(dynamicDataSource);
		// 添加XML目录
		ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
		try {
			bean.setMapperLocations(resolver.getResources("classpath:bhz/order/mapping/*.xml"));
			SqlSessionFactory sqlSessionFactory = bean.getObject();
			sqlSessionFactory.getConfiguration().setCacheEnabled(Boolean.TRUE);
			return sqlSessionFactory;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
//	@Bean
//	public DataSourceTransactionManager transactionManager(DynamicDataSource dynamicDataSource) throws Exception {
//	     return new DataSourceTransactionManager(dynamicDataSource);
//	}

	
	
	
}

自定义DynamicDataSource extends AbstractRoutingDataSource负责帮助我们切换数据源

package bhz.order.config.database;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {

	@Override
	protected Object determineCurrentLookupKey() {
		//	动态的去切换数据源的类型
		return DataBaseContextHolder.getDataBaseType();
	}

}

service多数据源使用

@SelectConnection(readOnly = true)只读，读取数据去从表。
@SelectConnection写操作，去主表
package bhz.order.service;

import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONObject;

import bhz.order.config.database.SelectConnection;
import bhz.order.entity.Order;
import bhz.order.mapper.OrderMapper;
import bhz.order.utils.FastJsonConvertUtil;
import bhz.order.utils.Pair;
import bhz.order.utils.SelectorUtil;

@Service
public class OrderService {

	@Autowired
	private OrderMapper orderMapper;
	
	@SelectConnection(readOnly = true)
	public Order shardSelectByPrimaryKey(String preFixTableName, String uuid){
		Pair<Integer/*DataBaseNumber*/, Integer/*TableNumber*/> pair = SelectorUtil.getDataBaseAndTableNumber(uuid);
		Integer tableNumber = pair.getObject2();
		String tableName = preFixTableName + "_" + tableNumber;
		return orderMapper.shardSelectByPrimaryKey(tableName, uuid);
	}
	
	//	注解的作用: 就是对数据源的切换
	@SelectConnection
	public int shardInsert(String preFixTableName, String uuid, Order order){
		Pair<Integer/*DataBaseNumber*/, Integer/*TableNumber*/> pair = SelectorUtil.getDataBaseAndTableNumber(uuid);
		Integer tableNumber = pair.getObject2();
		String tableName = preFixTableName + "_" + tableNumber;	//order_3
		
		JSONObject json = FastJsonConvertUtil.convertObjectToJSONObject(order);
		Map<String, Object> params = FastJsonConvertUtil.convertJSONToObject(json, Map.class);
		params.put("tableName", tableName);
		return orderMapper.shardInsert(params);
	}
	
	@SelectConnection
	public int shardUpdateByPrimaryKey(String preFixTableName, String uuid, Order tradeDetail){ 
		Pair<Integer/*DataBaseNumber*/, Integer/*TableNumber*/> pair = SelectorUtil.getDataBaseAndTableNumber(uuid);
		Integer tableNumber = pair.getObject2();
		String tableName = preFixTableName + "_" + tableNumber;
		
		JSONObject json = FastJsonConvertUtil.convertObjectToJSONObject(tradeDetail);
		Map<String, Object> params = FastJsonConvertUtil.convertJSONToObject(json, Map.class);
		params.put("tableName", tableName);
		return orderMapper.shardUpdateByPrimaryKey(params);
	}

}

数据源，表的使用规则

writeDataBaseSize=4四个主数据源
writeTableSize = 5每个数据源有五个表
package bhz.order.utils;

public class SelectorUtil {
	
	private static final int writeDataBaseSize = 4;
	
	private static final int writeTableSize = 5;
	
	public static Pair<Integer, Integer> getDataBaseAndTableNumber(String uuid) {
		int hashcode = Math.abs(uuid.hashCode());
		System.err.println("hashcode: " + hashcode);
		int selectDataBaseNumber = (hashcode/writeTableSize)%writeDataBaseSize + 1;
		int selectTableNumber = hashcode%writeTableSize;
		System.err.println("----------- SelectorUtil: selectDataBaseNumber: " + selectDataBaseNumber + " ----------------");
		System.err.println("----------- SelectorUtil: selectTableNumber: " + selectTableNumber + " ----------------");
		return new Pair<Integer, Integer>(selectDataBaseNumber, selectTableNumber);
	}
	
	public static void main(String[] args) {
		String uuid = KeyUtil.generatorUUID();
		int code = Math.abs(uuid.hashCode());
		System.err.println("code: " + code);
		int selectDataBaseNumber = (code/5)%4 + 1;
		int selectTableNumber = code%5;
		System.err.println("selectDataBaseNumber: " + selectDataBaseNumber);
		System.err.println("selectTableNumber: " + selectTableNumber);
	}
	
}

自定义注解@SelectConnection

//  注解的作用: 就是对数据源的切换

@Target 定义的注解可以利用在哪些地方，默认任何地方@Target({ElementType.METHOD, ElementType.TYPE})用在方法中

@Retention(RetentionPolicy.RUNTIME)注解什么时机被执行：源码，类加载，运行时
@Documented抽取文档
@Inheritance子类是否可以继承
package bhz.order.config.database;

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

@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface SelectConnection {

	String name() default "";
	
	boolean readOnly() default false; 
	
}

自定义注解解析

@Aspect:AOP
@Around("@annotation(selectConnection)")表示注解被使用时，解析selectConnection

在方法调用之前作什么

(String)proceedingJoinPoint.getArgs()[0]表示注解方法的第一个参数
//2 开始执行方法
Object result = proceedingJoinPoint.proceed();

在方法调用之后作什么

package bhz.order.config.database;

import org.apache.commons.lang3.StringUtils;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.slf4j.Logger;
import org.springframework.core.Ordered;
import org.springframework.stereotype.Component;

import bhz.order.config.database.DataBaseContextHolder.DataBaseType;
import bhz.order.utils.Pair;
import bhz.order.utils.SelectorUtil;

@Aspect
@Component
public class SelectConnectionInterceptor implements Ordered {

	private static Logger LOGGER = org.slf4j.LoggerFactory.getLogger(SelectConnectionInterceptor.class);
	
	private static final String SUFFIX_MASTER = "-master";
	
	private static final String SUFFIX_SLAVE = "-slave";
	
	@Around("@annotation(selectConnection)")
	public Object proceed(ProceedingJoinPoint proceedingJoinPoint, SelectConnection selectConnection) throws Throwable {
		try{
			//1 执行方法前...
			LOGGER.info("--------------- select database source ---------------");
			
			String currentDataBaseName = "";
			//	如果在注解上添加了: name
			if(!StringUtils.isBlank(selectConnection.name())){
				currentDataBaseName = selectConnection.name();
			} else {
				String preFixTableName = (String)proceedingJoinPoint.getArgs()[0];		//	表名前缀  order 
				String uuid = (String) proceedingJoinPoint.getArgs()[1];	// uuid
				
				Pair<Integer/*DataBaseNumber*/, Integer/*TableNumber*/> pair = SelectorUtil.getDataBaseAndTableNumber(uuid);
				currentDataBaseName = preFixTableName + pair.getObject1(); 
			}
			
			if(selectConnection.readOnly()){
				currentDataBaseName = currentDataBaseName + SUFFIX_SLAVE;
			} else {
				currentDataBaseName = currentDataBaseName + SUFFIX_MASTER;
			}
			//order3-master
			System.err.println("----Interceptor: currentDataBaseName : " + currentDataBaseName);	
			
			//string-> order2-slave
			for(DataBaseType type: DataBaseContextHolder.DataBaseType.values()){
				if(!StringUtils.isBlank(currentDataBaseName)){
					String typeCode = type.getCode();
					if(typeCode.equals(currentDataBaseName)){
						DataBaseContextHolder.setDataBaseType(type);
						System.err.println("----Interceptor: code :" + DataBaseContextHolder.getDataBaseType().getCode());
					}
				}
			}
			
			//2 开始执行方法
			Object result = proceedingJoinPoint.proceed();
			
			//3 执行方法后
			
			return result;
		} finally {
			DataBaseContextHolder.clearDataBaseType();
			LOGGER.info("---------------clear database connection---------------");
		}
	}
	
	
	@Override
	public int getOrder() {
		// TODO Auto-generated method stub
		return -1;
	}

}

 mapper.xml

<?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="bhz.order.mapper.OrderMapper" >
  <resultMap id="BaseResultMap" type="bhz.order.entity.Order" >
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="order_id" property="orderId" jdbcType="VARCHAR" />
    <result column="order_type" property="orderType" jdbcType="VARCHAR" />
    <result column="city_id" property="cityId" jdbcType="VARCHAR" />
    <result column="platform_id" property="platformId" jdbcType="VARCHAR" />
    <result column="platform_order_id" property="platformOrderId" jdbcType="VARCHAR" />
    <result column="poi_id" property="poiId" jdbcType="VARCHAR" />
    <result column="sender_address" property="senderAddress" jdbcType="VARCHAR" />
    <result column="sender_phone" property="senderPhone" jdbcType="VARCHAR" />
    <result column="sender_lng" property="senderLng" jdbcType="INTEGER" />
    <result column="sender_lat" property="senderLat" jdbcType="INTEGER" />
    <result column="sender_name" property="senderName" jdbcType="VARCHAR" />
    <result column="receiver_address" property="receiverAddress" jdbcType="VARCHAR" />
    <result column="receiver_phone" property="receiverPhone" jdbcType="VARCHAR" />
    <result column="receiver_lng" property="receiverLng" jdbcType="INTEGER" />
    <result column="receiver_lat" property="receiverLat" jdbcType="INTEGER" />
    <result column="receiver_name" property="receiverName" jdbcType="VARCHAR" />
    <result column="remark" property="remark" jdbcType="VARCHAR" />
    <result column="pkg_price" property="pkgPrice" jdbcType="DECIMAL" />
    <result column="create_by" property="createBy" jdbcType="VARCHAR" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="update_by" property="updateBy" jdbcType="VARCHAR" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, order_id, order_type, city_id, platform_id, platform_order_id, poi_id, sender_address, 
    sender_phone, sender_lng, sender_lat, sender_name, receiver_address, receiver_phone, 
    receiver_lng, receiver_lat, receiver_name, remark, pkg_price, create_by, create_time, 
    update_by, update_time
  </sql>
  
  <select id="shardSelectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String" >
    select 
    <include refid="Base_Column_List" />
    from ${tableName}
    where order_id = #{orderId,jdbcType=VARCHAR}
  </select>
  
  <delete id="shardDeleteByPrimaryKey" parameterType="java.lang.String" >
    delete from ${tableName}
    where order_id = #{orderId,jdbcType=VARCHAR}
  </delete>
  
  <insert id="shardInsert" parameterType="bhz.order.entity.Order" >
    insert into ${tableName} (id, order_id, order_type, 
      city_id, platform_id, platform_order_id, 
      poi_id, sender_address, sender_phone, 
      sender_lng, sender_lat, sender_name, 
      receiver_address, receiver_phone, receiver_lng, 
      receiver_lat, receiver_name, remark, 
      pkg_price, create_by, create_time, 
      update_by, update_time)
    values (#{id,jdbcType=BIGINT}, #{orderId,jdbcType=VARCHAR}, #{orderType,jdbcType=VARCHAR}, 
      #{cityId,jdbcType=VARCHAR}, #{platformId,jdbcType=VARCHAR}, #{platformOrderId,jdbcType=VARCHAR}, 
      #{poiId,jdbcType=VARCHAR}, #{senderAddress,jdbcType=VARCHAR}, #{senderPhone,jdbcType=VARCHAR}, 
      #{senderLng,jdbcType=INTEGER}, #{senderLat,jdbcType=INTEGER}, #{senderName,jdbcType=VARCHAR}, 
      #{receiverAddress,jdbcType=VARCHAR}, #{receiverPhone,jdbcType=VARCHAR}, #{receiverLng,jdbcType=INTEGER}, 
      #{receiverLat,jdbcType=INTEGER}, #{receiverName,jdbcType=VARCHAR}, #{remark,jdbcType=VARCHAR}, 
      #{pkgPrice,jdbcType=DECIMAL}, #{createBy,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP}, 
      #{updateBy,jdbcType=VARCHAR}, #{updateTime,jdbcType=TIMESTAMP})
  </insert>
  

  <update id="shardUpdateByPrimaryKey" parameterType="bhz.order.entity.Order" >
    update ${tableName}
    set order_id = #{orderId,jdbcType=VARCHAR},
      order_type = #{orderType,jdbcType=VARCHAR},
      city_id = #{cityId,jdbcType=VARCHAR},
      platform_id = #{platformId,jdbcType=VARCHAR},
      platform_order_id = #{platformOrderId,jdbcType=VARCHAR},
      poi_id = #{poiId,jdbcType=VARCHAR},
      sender_address = #{senderAddress,jdbcType=VARCHAR},
      sender_phone = #{senderPhone,jdbcType=VARCHAR},
      sender_lng = #{senderLng,jdbcType=INTEGER},
      sender_lat = #{senderLat,jdbcType=INTEGER},
      sender_name = #{senderName,jdbcType=VARCHAR},
      receiver_address = #{receiverAddress,jdbcType=VARCHAR},
      receiver_phone = #{receiverPhone,jdbcType=VARCHAR},
      receiver_lng = #{receiverLng,jdbcType=INTEGER},
      receiver_lat = #{receiverLat,jdbcType=INTEGER},
      receiver_name = #{receiverName,jdbcType=VARCHAR},
      remark = #{remark,jdbcType=VARCHAR},
      pkg_price = #{pkgPrice,jdbcType=DECIMAL},
      create_by = #{createBy,jdbcType=VARCHAR},
      create_time = #{createTime,jdbcType=TIMESTAMP},
      update_by = #{updateBy,jdbcType=VARCHAR},
      update_time = #{updateTime,jdbcType=TIMESTAMP}
    where id = #{id,jdbcType=BIGINT}
  </update>
</mapper>

读写分离

择业
关注
  • 0
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
分库分表，多数据源的切换
06-16
通过使用sharding-jdbc分库分表，以及多数据源的切换
Spring配置动态数据源实现读写分离的方法
08-31
主要介绍了利用Spring配置动态数据源实现读写分离的方法，文中通过示例代码介绍的很详细，相信对大家的理解和学习具有一定的参考借鉴价值，藕需要的朋友可以一起学习学习。
spring,mybatis,druid,多数据源配置
04-19 553
一、引入包 <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.9</version> </dependency> 二、application-dev.yml spring: datasource: ...
springboot shardingsphere druid 动态数据源切换及分库分表
AstarCloud
06-22 3402
分库分表多数据源切换教程场景 ，在实际开发中，如果表的数据过大我们需要把一张表拆分成多张表，也可以垂直切分把一个库拆分成多个库，这里就是通过ShardingSphere实现分库分表功能。
shardingsphere 5.X结合druid实现读写分离，分库分表
m0_71817461的博客
11-21 3383
shardingsphere 5.X结合druid实现读写分离，分库分表
SpringBoot 2.3 整合最新版 ShardingJdbc + Druid + MyBatis 实现分库分表
lyosaki88的专栏
06-12 2622
今天项目不忙，想搞一下shardingJDBC分库分表看看，主要想实现以下几点: 舍弃xml配置，使用.yml或者.properties文件+java的方式配置spring。 使用 Druid 作为数据库连接池，同时开启监控界面，并支持监控多数据源。 不依赖 com.dangdang 的 sharding-jdbc-core 包。此包过于古老，最后一次更新在2016年。目测只是封装了一层，意义不大。感觉如果不是dangdang公司内部开发，没必要用这个包。（且本人实测不能和最新的Druid包一起用，ins
分库分表番外：多数据源/动态数据源实现
admin522043032的博客
09-15 1023
比如在电商网站中可能会有这样的需求：根据用户所在城市不同，查询不同城市的商品数据。而在后台，这些不同城市的数据被分配在不同的数据库当中。很多人想当然的就觉得需要使用ShardingJDBC来实现多数据源管理。这种场景就是一个典型的多数据源切换的场景。但是我们仔细分析这样的场景，他跟分库分表其实并不太相同。ShardingJDBC固然可以使用Hint策略实现快速的数据库分库查询。
多数据源+数据库分库分表
Amarone'blog
07-28 953
shardingjdbc dynamic-datasource springboot jpa druid
若依3.6.1 pageHelper在动态切换数据源问题
北风浊酒
12-29 1248
在使用若依3.6.1ruoyi-cloud 时，新版若以采用了mybatis-plus的多数据源处理方式封装，在切换多数据源时，不生效，去掉事物注解仍为解决问题。
Spring Boot 多数据源及事务解决方案
m0_71777195的博客
06-20 3788
3.2.1 定义管理接口通过继承即可实现。为了方便对数据源进行操作，我们定义一个接口，为业务提供操作数据源的统一接口。该接口主要是对数据表中定义的数据源，提供基础管理功能。3.2.2 自定义数据源的实现如下图所示：根据前面的分析，是在容器启动的时候，执行注入数据源对象，完成之后无法对数据源进行修改。则实现接口，可以将数据表中的数据源加载到dataSources。3.2.3 切面处理这一块的处理跟配置文件数据源方案处理方式相同，都是通过AOP技术切换lookup key。
shardingsphere分库分表集成多数据源
扭曲的影子
03-12 4791
如果有分库分表，又有切换数据源的需求可以使用多数据源dynamic-datasource与shardingsphere集成的场景方案。3、添加一个多数据测试库yz-order和表sys_order。新增yz-log数据库和oper_log_xxx四张表。入到yz-log库oper_log_delete表。入到yz-log库oper_log_post表。入到yz-log库oper_log_get表。入到yz-log库oper_log_put表。入到yz-order库sys_order表。
基于Mycat实现Mysql读写分离以及分库分表.doc
09-22
内容详细，亲测可用，包含图文介绍
.NET Core实现分表分库、读写分离的通用 Repository功能
10-17
主要介绍了.NETCore 下支持分表分库、读写分离的通用 Repository，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
Mycat读写分离、主从切换、分库分表的操作记录- 线上操作手册
01-17
本篇文章详细介绍了Mycat读写分离、主从切换、分库分表的配置说明， 可以作为线上操作手册，在此分享，希望能帮助到有用到的朋友。
SpringBoot多数据源切换.zip
11-24
SpringBoot多数据源切换.zip
001 分库分表_多数据源切换读写分离优化（封装组件，采用地域分表算法）
诸般世界
11-12 239
1.数据库层面Mysql（MariaDB），实现读写分离、主从切换、数据源切换： 首先实现读写分离，就意味着需要有两个数据源，当写操作的时候对主库（master）使用，当读操作的时候对从库（slave）使用。那么在启动数据库连接池的时候即要启动两个，在实际使用时可以在方法上加上自定义注解的形式来区分读写。 2.实现解析： （1）配置好两个druid数据源，然后要区分开两个数据源...
MyBatis分页插件PageHelper拼接分页关键词错误，导致报sql语句错误，进行分页语句拼接时拼接了其他数据源的分页关键词
qq_47455677的博客
05-17 431
项目正常启动，进行查询同一类型的数据源的正常运行，但一进行查询其他数据源的就报sql语句错误，查看执行的sql语句，PageHelper拼接的分页关键词有误，我的是查询sqlserver数据库，分页的关键词是TOP，但是实际拼接了MySql的分页关键词LIMIT。由于我的项目用到了两种数据源，也就是多种数据源了，不是单一数据源，PageHelper默认不会自动切换数据源进行分页配置。
sharingjdbc5.x多数据源分库分表读写分离自动创表实现（pgsql）(一)
u014730578的博客
02-02 688
shardingjdbc 分库分表读写分离
微服务 高并发 多数据源 读写分离 分库分表
zhaoyang10的专栏
03-11 1527
数据库分库分表策略的具体实现方案 https://mp.weixin.qq.com/s?__biz=MzUxOTAxODc2Mg==&amp;mid=2247483791&amp;idx=4&amp;sn=d96a8696003bf193f2250d9fe266106a&amp;chksm=f98141eacef6c8fccb3b01c9b7b787b96ed25f8fa51974c5a2e375...
mycat读写分离分库分表
最新发布
09-07
对于实现读写分离、分库分表的需求，可以使用一些数据库中间件或者框架来实现。以下是一个常见的方案： 1. 读写分离：可以使用数据库中间件（如MySQL Proxy、MyCat、Cobar等）来实现读写分离。这些中间件会将写操作路由到主库上，而读操作则会根据一定的策略分配到多个从库上，以分担主库的读压力。 2. 分库分表：可以使用数据库中间件或者框架（如Sharding-JDBC、MyCat等）来实现分库分表。这些工具可以通过配置规则来将数据按照一定的策略进行分片存储，使数据分散到多个数据库中的不同表上。常见的策略包括按照数据范围、按照哈希值等进行分片。 需要注意的是，读写分离和分库分表都会带来一定的复杂性和额外的维护成本。在设计和实施时需要考虑到应用的具体业务特点和访问模式，并进行细致的规划和测试。 此外，还需要注意这些工具的配置和使用方法可能会有所不同，建议在具体实施前参考相关文档进行详细了解和调研。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

择业

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值