sharding jdbc分庫分表實現(xiàn)源碼分享

點擊上方藍色字體，選擇“標星公眾號”

優(yōu)質(zhì)文章，第一時間送達

66套java從入門到精通實戰(zhàn)課程分享?

• 背景

最近在研究Mysql的分庫分表，前面的博客已經(jīng)詳細介紹了分庫分表！

由于sharding-jdbc是不支持動態(tài)進行建庫的SQL，那么就需要一次把需要的數(shù)據(jù)庫和數(shù)據(jù)表都建好

• 建庫、建表

考慮到這只是一個測試的demo，所以，只建了兩個庫和兩個表

CREATE?TABLE?`t_order_0`?(
?`id`?BIGINT(20)?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵id',
?`order_id`?VARCHAR(32)?NULL?DEFAULT?NULL?COMMENT?'順序編號',
?`user_id`?VARCHAR(32)?NULL?DEFAULT?NULL?COMMENT?'用戶編號',
?`userName`?VARCHAR(32)?NULL?DEFAULT?NULL?COMMENT?'用戶名',
?`passWord`?VARCHAR(32)?NULL?DEFAULT?NULL?COMMENT?'密碼',
?`nick_name`?VARCHAR(32)?NULL?DEFAULT?NULL,

• SpringBoot+Mybatis+sharding-jdbc框架搭建（pom文件）

????????
????????????org.springframework.boot
????????????spring-boot-starter
????????
????????
????????????org.springframework.boot
????????????spring-boot-starter-test
????????????test
????????
????????
????????????org.springframework.boot
????????????spring-boot-starter-web
????????
????????
????????????org.mybatis.spring.boot
????????????mybatis-spring-boot-starter
????????????1.1.1
????????
????????
????????????mysql
????????????mysql-connector-java
????????
????????
????????????org.springframework.boot
????????????spring-boot-devtools
????????????true
????????
?
????????
????????????org.projectlombok
????????????lombok
????????
?
????????
????????
????????????com.dangdang
????????????sharding-jdbc-core
????????????1.5.4
????????
????

• 配置文件

mybatis.config-locations=classpath:mybatis/mybatis-config.xml
?
#datasource
spring.devtools.remote.restart.enabled=false
?
#data?source1
spring.datasource.test1.driverClassName=com.mysql.jdbc.Driver
spring.datasource.test1.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test_msg1?serverTimezone=UTC
spring.datasource.test1.username=root
spring.datasource.test1.password=123456
?
#data?source2
spring.datasource.test2.driverClassName=com.mysql.jdbc.Driver
spring.datasource.test2.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test_msg2?serverTimezone=UTC
spring.datasource.test2.username=root
spring.datasource.test2.password=123456

• 啟動文件

@SpringBootApplication
@EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class})?//排除DataSourceConfiguratrion
@EnableTransactionManagement(proxyTargetClass?=?true)???//開啟事物管理功能
public?class?ShardingJdbcApplication?{
????
????public?static?void?main(String[]?args)?{
????????SpringApplication.run(ShardingJdbcApplication.class,?args);
????}
}

• 實體類（Data注解可免去set/get方法）

@Data
public?class?User?{
?
????private?Long?id;
????private?Long?order_id;
????private?Long?user_id;
????private?String?userName;
????private?String?passWord;
????private?String?nickName;
}

• Service層

@Slf4j
@Service
public?class?UserService?{
?
????@Resource
????private?UserMapper?userMapper;
?
????public?void?insert(User?user)?{
????????userMapper.insert(user);
????}
}

• Mapper層

public?interface?UserMapper?{
?
????void?insert(User?user);
}

• 數(shù)據(jù)源配置和Mybatis配置和分庫分表規(guī)則（重要）

這里，我們是將多個數(shù)據(jù)源交給sharding-jdbc進行管理，并且有默認的數(shù)據(jù)源，當沒有設置分庫分表規(guī)則的時候就可以使用默認的數(shù)據(jù)源

分表：user_id%2 = 0的數(shù)據(jù)存儲到test_msg1，為1的存儲到test_msg0

分表：order_id%2 = 0的數(shù)據(jù)存儲到t_order_0，為1的存儲到t_order_1

/**
?*?@Auther:?Tinko
?*?@Date:?2018/12/19?16:27
?*?@Description:?數(shù)據(jù)源配置和Mybatis配置和分庫分表規(guī)則
?*/
@Configuration
@MapperScan(basePackages?=?"com.example.shardingjdbc.mapper",?sqlSessionTemplateRef??=?"test1SqlSessionTemplate")
public?class?DataSourceConfig?{
?
????/**
?????*?配置數(shù)據(jù)源0，數(shù)據(jù)源的名稱最好要有一定的規(guī)則，方便配置分庫的計算規(guī)則
?????*?@return
?????*/
????@Bean(name="dataSource0")
????@ConfigurationProperties(prefix?=?"spring.datasource.test1")
????public?DataSource?dataSource0(){
????????return?DataSourceBuilder.create().build();
????}
????/**
?????*?配置數(shù)據(jù)源1，數(shù)據(jù)源的名稱最好要有一定的規(guī)則，方便配置分庫的計算規(guī)則
?????*?@return
?????*/
????@Bean(name="dataSource1")
????@ConfigurationProperties(prefix?=?"spring.datasource.test2")
????public?DataSource?dataSource1(){
????????return?DataSourceBuilder.create().build();
????}
?
????/**
?????*?配置數(shù)據(jù)源規(guī)則，即將多個數(shù)據(jù)源交給sharding-jdbc管理，并且可以設置默認的數(shù)據(jù)源，
?????*?當表沒有配置分庫規(guī)則時會使用默認的數(shù)據(jù)源
?????*?@param?dataSource0
?????*?@param?dataSource1
?????*?@return
?????*/
????@Bean
????public?DataSourceRule?dataSourceRule(@Qualifier("dataSource0")?DataSource?dataSource0,
?????????????????????????????????????????@Qualifier("dataSource1")?DataSource?dataSource1){
????????Map?dataSourceMap?=?new?HashMap<>();?//設置分庫映射
????????dataSourceMap.put("dataSource0",?dataSource0);
????????dataSourceMap.put("dataSource1",?dataSource1);
????????return?new?DataSourceRule(dataSourceMap,?"dataSource0");?//設置默認庫，兩個庫以上時必須設置默認庫。默認庫的數(shù)據(jù)源名稱必須是dataSourceMap的key之一
????}
?
????/**
?????*?配置數(shù)據(jù)源策略和表策略，具體策略需要自己實現(xiàn)
?????*?@param?dataSourceRule
?????*?@return
?????*/
????@Bean
????public?ShardingRule?shardingRule(DataSourceRule?dataSourceRule){
????????//具體分庫分表策略
????????TableRule?orderTableRule?=?TableRule.builder("t_order")
????????????????.actualTables(Arrays.asList("t_order_0",?"t_order_1"))
????????????????.tableShardingStrategy(new?TableShardingStrategy("order_id",?new?ModuloTableShardingAlgorithm()))
????????????????.dataSourceRule(dataSourceRule)
????????????????.build();
?
????????//綁定表策略，在查詢時會使用主表策略計算路由的數(shù)據(jù)源，因此需要約定綁定表策略的表的規(guī)則需要一致，可以一定程度提高效率
????????List?bindingTableRules?=?new?ArrayList();
????????bindingTableRules.add(new?BindingTableRule(Arrays.asList(orderTableRule)));
????????return?ShardingRule.builder()
????????????????.dataSourceRule(dataSourceRule)
????????????????.tableRules(Arrays.asList(orderTableRule))
????????????????.bindingTableRules(bindingTableRules)
????????????????.databaseShardingStrategy(new?DatabaseShardingStrategy("user_id",?new?ModuloDatabaseShardingAlgorithm()))
????????????????.tableShardingStrategy(new?TableShardingStrategy("order_id",?new?ModuloTableShardingAlgorithm()))
????????????????.build();
????}
?
????/**
?????*?創(chuàng)建sharding-jdbc的數(shù)據(jù)源DataSource，MybatisAutoConfiguration會使用此數(shù)據(jù)源
?????*?@param?shardingRule
?????*?@return
?????*?@throws?SQLException
?????*/
????@Bean(name="dataSource")
????public?DataSource?shardingDataSource(ShardingRule?shardingRule)?throws?SQLException?{
????????return?ShardingDataSourceFactory.createDataSource(shardingRule);
????}
?
????/**
?????*?需要手動配置事務管理器
?????*?@param?dataSource
?????*?@return
?????*/
????@Bean
????public?DataSourceTransactionManager?transactitonManager(@Qualifier("dataSource")?DataSource?dataSource){
????????return?new?DataSourceTransactionManager(dataSource);
????}
?
????@Bean(name?=?"test1SqlSessionFactory")
????@Primary
????public?SqlSessionFactory?testSqlSessionFactory(@Qualifier("dataSource")?DataSource?dataSource)?throws?Exception?{
????????SqlSessionFactoryBean?bean?=?new?SqlSessionFactoryBean();
????????bean.setDataSource(dataSource);
????????bean.setMapperLocations(new?PathMatchingResourcePatternResolver().getResources("classpath:mybatis/*.xml"));
????????return?bean.getObject();
????}
?
????@Bean(name?=?"test1SqlSessionTemplate")
????@Primary
????public?SqlSessionTemplate?testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory")?SqlSessionFactory?sqlSessionFactory)?throws?Exception?{
????????return?new?SqlSessionTemplate(sqlSessionFactory);
????}
}

• 分庫規(guī)則

• /**
  ?*?@Auther:?Tinko
  ?*?@Date:?2018/12/19?16:31
  ?*?@Description:?分庫規(guī)則
  ?*/
  public?class?ModuloDatabaseShardingAlgorithm?implements?SingleKeyDatabaseShardingAlgorithm?{
  ?
  ????@Override
  ????public?String?doEqualSharding(Collection?databaseNames,?ShardingValue?shardingValue)?{
  ????????for?(String?each?:?databaseNames)?{
  ????????????if?(each.endsWith(Long.parseLong(shardingValue.getValue().toString())?%?2?+?""))?{
  ????????????????return?each;
  ????????????}
  ????????}
  ????????throw?new?IllegalArgumentException();
  ????}
  ?
  ????@Override
  ????public?Collection?doInSharding(Collection?databaseNames,?ShardingValue?shardingValue)?{
  ????????Collection?result?=?new?LinkedHashSet<>(databaseNames.size());
  ????????for?(Long?value?:?shardingValue.getValues())?{
  ????????????for?(String?tableName?:?databaseNames)?{
  ????????????????if?(tableName.endsWith(value?%?2?+?""))?{
  ????????????????????result.add(tableName);
  ????????????????}
  ????????????}
  ????????}
  ????????return?result;
  ????}
  ?
  ????@Override
  ????public?Collection?doBetweenSharding(Collection?databaseNames,?ShardingValue?shardingValue)?{
  ????????Collection?result?=?new?LinkedHashSet<>(databaseNames.size());
  ????????Range?range?=?(Range)?shardingValue.getValueRange();
  ????????for?(Long?i?=?range.lowerEndpoint();?i?<=?range.upperEndpoint();?i++)?{
  ????????????for?(String?each?:?databaseNames)?{
  ????????????????if?(each.endsWith(i?%?2?+?""))?{
  ????????????????????result.add(each);
  ????????????????}
  ????????????}
  ????????}
  ????????return?result;
  ????}
  }?
  

• 分表規(guī)則

• /**
  ?*?@Auther:?Tinko
  ?*?@Date:?2018/12/19?16:30
  ?*?@Description:?分表規(guī)則
  ?*/
  public?class?ModuloTableShardingAlgorithm?implements?SingleKeyTableShardingAlgorithm?{
  ?
  ????@Override
  ????public?String?doEqualSharding(Collection?tableNames,?ShardingValue?shardingValue)?{
  ????????for?(String?each?:?tableNames)?{
  ????????????if?(each.endsWith(shardingValue.getValue()?%?2?+?""))?{
  ????????????????return?each;
  ????????????}
  ????????}
  ????????throw?new?IllegalArgumentException();
  ????}
  ?
  ????@Override
  ????public?Collection?doInSharding(Collection?tableNames,?ShardingValue?shardingValue)?{
  ????????Collection?result?=?new?LinkedHashSet<>(tableNames.size());
  ????????for?(Long?value?:?shardingValue.getValues())?{
  ????????????for?(String?tableName?:?tableNames)?{
  ????????????????if?(tableName.endsWith(value?%?2?+?""))?{
  ????????????????????result.add(tableName);
  ????????????????}
  ????????????}
  ????????}
  ????????return?result;
  ????}
  ?
  ????@Override
  ????public?Collection?doBetweenSharding(Collection?tableNames,?ShardingValue?shardingValue)?{
  ????????Collection?result?=?new?LinkedHashSet<>(tableNames.size());
  ????????Range?range?=?(Range)?shardingValue.getValueRange();
  ????????for?(Long?i?=?range.lowerEndpoint();?i?<=?range.upperEndpoint();?i++)?{
  ????????????for?(String?each?:?tableNames)?{
  ????????????????if?(each.endsWith(i?%?2?+?""))?{
  ????????????????????result.add(each);
  ????????????????}
  ????????????}
  ????????}
  ????????return?result;
  ????}
  }
  

• 與Mysql交互的配置的文件

• "1.0"?encoding="UTF-8"??>
  "-//mybatis.org//DTD?Mapper?3.0//EN"?"http://mybatis.org/dtd/mybatis-3-mapper.dtd"?>
  "com.example.shardingjdbc.mapper.UserMapper"?>
  ?
  ????"Base_Column_List"?>
  ????????id,?userName,?passWord,?user_sex,?nick_name
  ????
  ?
  ????"insert"?parameterType="com.example.shardingjdbc.entity.User"?>
  ????????INSERT?INTO
  ????????t_order
  ????????(order_id,user_id,userName,passWord)
  ????????VALUES
  ????????(#{order_id},#{user_id},#{userName},?#{passWord})
  ????
  ?
  
  

git傳送門

https://github.com/MissDistin/sharding-jdbc-1

然后，測試可行?。。?/span>

版權聲明：本文為博主原創(chuàng)文章，遵循?CC 4.0 BY-SA?版權協(xié)議，轉(zhuǎn)載請附上原文出處鏈接和本聲明。

本文鏈接：

https://blog.csdn.net/a992795427/article/details/85102918

粉絲福利：108本java從入門到大神精選電子書領取

???

?長按上方鋒哥微信二維碼?2 秒
備注「1234」即可獲取資料以及
可以進入java1234官方微信群

感謝點贊支持下哈?