sharding-jdbc 学习 1

shardingsphere官方文档

先看看数据分片   =>  数据分片链接

使用基于java编码的规则配置

1 获取Datasource

把截图代码cv过来 

稍加改造后 

DataSourceConfig ：

package com.example.sharding_demo.fen;

import com.example.sharding_demo.configs.DataSourceUtils;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

/**
 * @author rxf113
 */
@Configuration
public class DataSourceConfig {

    @Resource
    private DataSourceUtils dataSourceUtils;

    @Bean
    public DataSource getDataSource() throws SQLException {
        // 配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>(2);
        // 配置第一个数据源
        dataSourceMap.put("ds0",dataSourceUtils.dataSource1());
        // 配置第二个数据源
        dataSourceMap.put("ds1",dataSourceUtils.dataSource2());

        // 配置Order表规则
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order","ds${0..1}.t_order${0..1}");

        // 配置分库 + 分表策略
        orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
        orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order${order_id % 2}"));

        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

        // 省略配置order_item表规则...
        // ...
        // 获取数据源对象
        return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory() {
        try {
            SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
            sqlSessionFactoryBean.setDataSource(getDataSource());
            // 加载xml
            PathMatchingResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
            sqlSessionFactoryBean.setMapperLocations(resourcePatternResolver.getResources("com/example/sharding_demo/mapper/xmls/*.xml"));
            return sqlSessionFactoryBean.getObject();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

dataSourceUtils ：

package com.example.sharding_demo.configs;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;

import javax.sql.DataSource;

/**
 * @author rxf113
 */
@Configuration
@PropertySource("classpath:datasources.properties")
public class DataSourceUtils {

    /**
     * 数据源1
     */
    @Bean(name = "datasource1")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSource dataSource1() {
        return  DataSourceBuilder.create().build();
    }


    /**
     * 数据源2
     */
    @Bean(name = "datasource2")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource dataSource2() {
        return new HikariDataSource();
    }
}

datasources.properties ：

#数据源1
#spring.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.db1.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.db1.jdbcUrl=jdbc:mysql://******/ds0?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
spring.datasource.db1.username=*****
spring.datasource.db1.password=*****
#数据源2
#spring.datasource.db3.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.db2.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.db2.jdbcUrl=jdbc:mysql://*****/ds1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
spring.datasource.db2.username=*****
spring.datasource.db2.password=*****

创建测试数据

库：ds0 、 ds1         

表：t_order0、t_order1         

字段： user_id  、order_id  、 order_name

 

数据库 截图 

 

 

 

补全项目结构：

 

插入6条数据试试

Test :

package com.example.sharding_demo;

import com.example.sharding_demo.model.TOrder;
import com.example.sharding_demo.service.OrderService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

//import org.junit.jupiter.api.Test;
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class ShardingDemoApplicationTests {

    @Resource
    private OrderService orderService;

    @Test
    public void insertTest(){
        ArrayList<TOrder> list = new ArrayList<>(6);
        for (long i = 1; i < 7; i++) {
            TOrder order = new TOrder(i,i + 100 ,"订单名称" + i);
            list.add(order);
        }
        orderService.insert(list);
    }
}

 

结果：

 根据 user_id 分库  1 3 5  %  2   = 1  分到ds1 ，   2  4  6  % 2 = 0  分到ds0

 根据 order_id分表  102  104  104   %  2   = 0  分到 t_order0 ，  101  103  105   %  2   = 1  分到 t_order1    ，没问题

 

再试试查询 

等值查询   sql:   select * from `t_order` where user_id = 3

@Test
public void selectEqual(){
    List<Map<String, Object>> list = orderService.selectEqual();
    System.out.println(list.toString());
}

 

范围查询  sql:  select * from `t_order` where user_id > 3

@Test
public void selectMultiple(){
    List<Map<String, Object>> list = orderService.selectMultiple();
    System.out.println(list.toString());
}

抛异常了 ：  这个配置不支持范围分片  

范围分片  下节再看。