sharding-jdbc+ springboot+ mybatis 分库分表简单例子

sharding-jdbc定位为轻量级Java框架，在Java的JDBC层提供的额外服务。 它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

• 适用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
• 支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
• 支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库
• 具体可查看官方文档 https://shardingsphere.apache.org/document/legacy/4.x/document/cn/overview/

下面直接上例子

1.引入pom

     <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-jdbc-core</artifactId>
                <version>4.1.0</version>
            </dependency>

2.配置数据源配置文件 

server:
  port: 8986

spring:
  application:
    name: cloud-jdbc



##数据库ds0
  datasource:
    ds0:
      type: com.alibaba.druid.pool.DruidDataSource
      driverClassName: com.mysql.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3396/demo0?useUnicode=true&characterEncoding=utf8&useOldAliasMetadataBehavior=true&useSSL=false&serverTimezone=Asia/Shanghai
      ##jdbcUrl: jdbc:mysql://127.0.0.1:3396/demo0?useUnicode=true&characterEncoding=utf8&useOldAliasMetadataBehavior=true&useSSL=false&serverTimezone=Asia/Shanghai
      username: root
      password: 123456
      initialize: false
      sqlScriptEncoding: UTF-8
      initialSize: 5
      minIdle: 5
      maxActive: 10
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 600000
      minEvictableIdleTimeMillis: 3000000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true  #在检查闲置连接时同时检查连接可用性
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      filters: stat,wall,logback
  ##数据库ds1
    ds1:
      type: com.alibaba.druid.pool.DruidDataSource
      driverClassName: com.mysql.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3396/demo1?useUnicode=true&characterEncoding=utf8&useOldAliasMetadataBehavior=true&useSSL=false&serverTimezone=Asia/Shanghai
      ##jdbcUrl: jdbc:mysql://127.0.0.1:3396/demo1?useUnicode=true&characterEncoding=utf8&useOldAliasMetadataBehavior=true&useSSL=false&serverTimezone=Asia/Shanghai
      username: root
      password: 123456
      initialize: false
      sqlScriptEncoding: UTF-8
      initialSize: 5
      minIdle: 5
      maxActive: 10
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 600000
      minEvictableIdleTimeMillis: 3000000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true  #在检查闲置连接时同时检查连接可用性
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      filters: stat,wall,logback
    
##mybatis-plus
mybatis-plus:
  mapper-locations: classpath:/**/*Mapper.xml
  type-aliases-package: com.some.jdbc.domain
  # 数据库相关配置
  global-config:
    #主键类型  AUTO:"数据库ID自增", INPUT:"用户输入ID",ID_WORKER:"全局唯一ID (数字类型唯一ID)", UUID:"全局唯一ID UUID";
    id-type: ID_WORKER
    #字段策略 IGNORED:"忽略判断",NOT_NULL:"非 NULL 判断"),NOT_EMPTY:"非空判断"
    field-strategy: not_empty
    #驼峰下划线转换
    column-underline: true
    #数据库大写下划线转换
    #capital-mode: true
    #逻辑删除配置
    logic-delete-value: 0
    logic-not-delete-value: 1
    db-type: mysql
  
  #刷新mapper 调试神器
  refresh: true
  # 原生配置
  configuration:
    map-underscore-to-camel-case: true
    cache-enabled: false
    # 配置slq打印日志
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

3.配置DataSourceConfig

package com.some.jdbc.config;

import lombok.extern.slf4j.Slf4j;
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.springframework.beans.factory.annotation.Qualifier;
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.Primary;

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

@Configuration
@Slf4j
public class DataSourceConfig {
    @Bean(name = "dataSource0")
    @Qualifier("dataSource0")
    @ConfigurationProperties(prefix = "spring.datasource.ds0")
    public DataSource dataSource1() {
        return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();
    }
    @Bean(name = "dataSource1")
    @Qualifier("dataSource1")
    @ConfigurationProperties(prefix = "spring.datasource.ds1")
    public DataSource dataSource2() {
        return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();
    }

    /**
     * 数据源
     *
     * @return
     */
    @Bean(name = "dataSource")
	@Primary //默认数据源
    public DataSource dataSource(@Qualifier("dataSource0") DataSource dataSource0, @Qualifier("dataSource1") DataSource dataSource1) {

          // 配置真实数据源
        Map dataSourceMap = new HashMap<>();
        dataSourceMap.put("ds0", dataSource0);
        dataSourceMap.put("ds1", dataSource1);
          // 配置表规则
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order", "ds${0..1}.t_order${0..1}");


          // 配置分库 + 分表策略

        //Inline表达式分片策略。使用Groovy的Inline表达式，提供对SQL语句中的=和IN的分片操作支持  分库 按用户id 分库
        orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("create_userid", "ds${create_userid % 2}"));

        //按订单号分表
        orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_no", "t_order${order_no % 2}"));

		// 配置分片规则

        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);


        DataSource dataSource = null;
        try {
            dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
        return dataSource;
    }
}

4.写代码测试

package com.some.jdbc.service;

import com.some.common.utils.IdUtils;
import com.some.jdbc.domain.Order;
import com.some.jdbc.mapper.OrderMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Random;


@Service
public class OrderService {
    @Autowired
    private OrderMapper orderMapper;
    private Random r = new Random();
    public int save(Order order){
        long id = IdUtils.getId();
        //测试用 实际不会重复

        long orderNo =  Long.valueOf(r.nextInt(2));
        order.setId(id);
        order.setOrderNo(orderNo);
        //模拟用户
        order.setCreateUserid(  Long.valueOf(r.nextInt(2)));

        order.setCreateTime(LocalDateTime.now());
        return orderMapper.insert(order);
    }
    public List<Order> list(){
        return orderMapper.selectList(null);
    }


}

 

 5.查看demo0和demo1数据库，可以看到数据已经按我们配置的分片规则路由进去了

 代码地址  https://github.com/fdqzq613/myframe.git