MyCat分片集群分表分库策略取模算法进行分库分表，主从备份

刺骨的言语ヽ痛彻心扉 2023-07-10 11:28 30阅读 0赞

MyCat分片集群分表分库策略

# application.yml #

spring:
      datasource:
        ###可读数据源
        select:
          jdbc-url: jdbc:mysql://192.168.184.129:8066/test
          driver-class-name: com.mysql.jdbc.Driver
          username: user
          password: 123456
        ####可写数据源  
        update:
          jdbc-url: jdbc:mysql://192.168.184.129:8066/test
          driver-class-name: com.mysql.jdbc.Driver
          username: root
          password: 123456
        type: com.alibaba.druid.pool.DruidDataSource

Controler的代码：

@RestController
    public class UserController {
    
    	@Autowired
    	private UserService userService;
    
    	@RequestMapping("/findUser")
    	public List<UserEntity> findUser() {
    		return userService.findUser();
    	}
    
    	@RequestMapping("/insertUser")
    	public List<UserEntity> insertUser(String userName) {
    		return userService.insertUser(userName);
    	}
    	@RequestMapping("/insert")
    	public List<UserEntity> insert () {
    		UserEntity  user=new UserEntity()  ;
    		for(int  i=2;i<121;i++) {
    			i++;
    			user.setId(i);
    			user.setName("name"+i);
    			user.setUserName("userName"+i);
    			userService.insert(user);
    		}
    		return userService.insert(user);
    	}
    
    }

# 使用AOP动态切换不同的数据源 #

SwitchDataSourceAOP

// 使用AOP动态切换不同的数据源
    @Aspect
    @Component
    @Lazy(false)
    @Order(0) // Order设定AOP执行顺序 使之在数据库事务上先执行
    public class SwitchDataSourceAOP {
    	// 这里切到你的方法目录
    	@Before("execution(* com.mayikt.service.*.*(..))")
    	public void process(JoinPoint joinPoint) {
    		String methodName = joinPoint.getSignature().getName();
    		if (methodName.startsWith("get") || methodName.startsWith("count") || methodName.startsWith("find")
    				|| methodName.startsWith("list") || methodName.startsWith("select") || methodName.startsWith("check")) {
    			DataSourceContextHolder.setDbType("selectDataSource");
    		} else {
    			// 切换dataSource
    			DataSourceContextHolder.setDbType("updateDataSource");
    		}
    	}
    }

# DataSourceConfig #

@Configuration
    public class DataSourceConfig {
    
    	// 创建可读数据源
    	@Bean(name = "selectDataSource")
    	@ConfigurationProperties(prefix = "spring.datasource.select") // application.properteis中对应属性的前缀
    	public DataSource dataSource1() {
    		return DataSourceBuilder.create().build();
    	}
    
    	// 创建可写数据源
    	@Bean(name = "updateDataSource")
    	@ConfigurationProperties(prefix = "spring.datasource.update") // application.properteis中对应属性的前缀
    	public DataSource dataSource2() {
    		return DataSourceBuilder.create().build();
    	}
    
    }

# DataSourceContextHolder #

@Component
    @Lazy(false)
    public class DataSourceContextHolder {
        // 采用ThreadLocal 保存本地多数据源
        private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
    
        // 设置数据源类型
        public static void setDbType(String dbType) {
            contextHolder.set(dbType);
        }
    
        public static String getDbType() {
            return contextHolder.get();
        }
    
        public static void clearDbType() {
            contextHolder.remove();
        }
    
    }

# DynamicDataSource #

//在Spring 2.0.1中引入了AbstractRoutingDataSource, 该类充当了DataSource的路由中介, 能有在运行时, 根据某种key值来动态切换到真正的DataSource上。
    
    @Component
    @Primary
    public class DynamicDataSource extends AbstractRoutingDataSource {
    	@Autowired
    	@Qualifier("selectDataSource")
    	private DataSource selectDataSource;
    
    	@Autowired
    	@Qualifier("updateDataSource")
    	private DataSource updateDataSource;
    
    	/**
    	 * 这个是主要的方法，返回的是生效的数据源名称
    	 */
    	@Override
    	protected Object determineCurrentLookupKey() {
    		System.out.println("DataSourceContextHolder：：：" + DataSourceContextHolder.getDbType());
    		return DataSourceContextHolder.getDbType();
    	}
    
    	/**
    	 * 配置数据源信息
    	 */
    	@Override
    	public void afterPropertiesSet() {
    		Map<Object, Object> map = new HashMap<>();
    		map.put("selectDataSource", selectDataSource);
    		map.put("updateDataSource", updateDataSource);
    		setTargetDataSources(map);
    		setDefaultTargetDataSource(updateDataSource);
    		super.afterPropertiesSet();
    	}
    }

# UserEntity #

public class UserEntity {
    
    	private String userName;
    	 
    	private int id;
    	private String name;
    	public String getUserName() {
    		return userName;
    	}
    	public void setUserName(String userName) {
    		this.userName = userName;
    	}
    	public int getId() {
    		return id;
    	}
    	public void setId(int id) {
    		this.id = id;
    	}
    	public String getName() {
    		return name;
    	}
    	public void setName(String name) {
    		this.name = name;
    	}
    
     
    
    }

# UserMapper #

import java.util.List;
    
    import org.apache.ibatis.annotations.Options;
    import org.apache.ibatis.annotations.Param;
    import org.apache.ibatis.annotations.Select;
    
    import com.mayikt.entity.UserEntity;
    
    public interface UserMapper {
    	@Select("SELECT * FROM  t_user ")
    	public List<UserEntity> findUser();
    
    	@Select("insert into t_user(id, userName,name) values (2,#{userName},#{userName}); ")
    	@Options(useGeneratedKeys = true, keyProperty = "id")
    	public List<UserEntity> insertUser(@Param("userName") String userName);
    	
    	@Select("insert into t_user(id, userName,name) values (#{id},#{userName},#{userName}); ")
    	@Options(useGeneratedKeys = true, keyProperty = "id")
    	public List<UserEntity> insert (UserEntity userEntity);
    }

# UserService #

import java.util.List;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import com.mayikt.entity.UserEntity;
    import com.mayikt.mapper.UserMapper;
    
    @Service
    public class UserService {
    	@Autowired
    	private UserMapper userMapper;
    
    	public List<UserEntity> findUser() {
    		return userMapper.findUser();
    	}
    
    	public List<UserEntity> insertUser(String userName) {
    		return userMapper.insertUser(userName);
    	}
    	public List<UserEntity> insert(UserEntity userName) {
    		return userMapper.insert(userName);
    	}
    
    }

SpringBoot进行运行：

@SpringBootApplication
    @MapperScan(basePackages = "com.mapper")
    public class AppMybatis {
    
    	public static void main(String[] args) {
    		SpringApplication.run(AppMybatis.class, args);
    	}
    
    }