Spring Boot 集成 Shardingsphere

引入依赖

        <!-- Druid连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.20</version>
        </dependency>

        <!-- Mysql驱动依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- MybatisPlus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>

        <!-- Sharding-JDBC -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

yml 配置

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    props:
      sql-show: true
    datasource:
      names: jdbcdemo1,jdbcdemo2
      jdbcdemo1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://124.221.248.209:3306/jdbcdemo1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
        username: jdbcdemo1
        password: jdbcdemo1
      jdbcdemo2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://124.221.248.209:3306/jdbcdemo2?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
        username: jdbcdemo2
        password: jdbcdemo2
    sharding:
      default-database-strategy:
        inline:
          # 分库字段
          sharding-column: user_id
          # usee_id % 2
          algorithm-expression: jdbcdemo$->{(user_id % 2)+1}
      tables:
        t_order:
          # 节点
          actual-data-nodes: jdbcdemo$->{1..2}.t_order$->{0..1}
          table-strategy:
            inline:
              # 分表字段
              sharding-column: order_id
              algorithm-expression: t_order$->{order_id%2}
# mybatis plus
mybatis-plus:
  # 扫描包
  typeAliasesPackage: com.oy.**.entity
  # mapper 文件
  mapperLocations: classpath*:mapperxml/*Mapper.xml
  # mapper 全局配置
  configLocation: classpath:mybatis/mybatis-config.xml

mybatis 全局配置

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 全局参数 -->
    <settings>
        <!-- 使全局的映射器启用或禁用缓存 -->
        <setting name="cacheEnabled"             value="true"   />
        <!-- 允许JDBC 支持自动生成主键 -->
        <setting name="useGeneratedKeys"         value="true"   />
        <!-- 配置默认的执行器.SIMPLE就是普通执行器;REUSE执行器会重用预处理语句(prepared statements);BATCH执行器将重用语句并执行批量更新 -->
        <setting name="defaultExecutorType"      value="SIMPLE" />
		<!-- 指定 MyBatis 所用日志的具体实现 -->
        <setting name="logImpl"                  value="SLF4J"  />
        <!-- 使用驼峰命名法转换字段 -->
		 <setting name="mapUnderscoreToCamelCase" value="true"/>
	</settings>
	
</configuration>

分页插件

/**
 * @author outengfei
 * @date 2023/5/18 15:01
 */
@EnableTransactionManagement(proxyTargetClass = true)
@Configuration
public class MybatisPlusConfig
{
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor()
    {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 分页插件
        interceptor.addInnerInterceptor(paginationInnerInterceptor());
        // 乐观锁插件
        interceptor.addInnerInterceptor(optimisticLockerInnerInterceptor());
        // 阻断插件
        interceptor.addInnerInterceptor(blockAttackInnerInterceptor());
        return interceptor;
    }

    /**
     * 分页插件，自动识别数据库类型 https://baomidou.com/guide/interceptor-pagination.html
     */
    public PaginationInnerInterceptor paginationInnerInterceptor()
    {
        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
        // 设置数据库类型为mysql
        paginationInnerInterceptor.setDbType(DbType.MYSQL);
        // 设置最大单页限制数量，默认 500 条，-1 不受限制
        paginationInnerInterceptor.setMaxLimit(500L);
        return paginationInnerInterceptor;
    }

    /**
     * 乐观锁插件 https://baomidou.com/guide/interceptor-optimistic-locker.html
     */
    public OptimisticLockerInnerInterceptor optimisticLockerInnerInterceptor()
    {
        return new OptimisticLockerInnerInterceptor();
    }

    /**
     * 如果是对全表的删除或更新操作，就会终止该操作 https://baomidou.com/guide/interceptor-block-attack.html
     */
    public BlockAttackInnerInterceptor blockAttackInnerInterceptor()
    {
        return new BlockAttackInnerInterceptor();
    }
}

建表语句

CREATE TABLE `t_order0` (
                            `order_id` bigint(36) COLLATE utf8_bin NOT NULL,
                            `user_id` bigint(36) COLLATE utf8_bin NOT NULL,
                            `order_name` varchar(100) COLLATE utf8_bin NOT NULL,
                            PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `t_order1` (
                            `order_id` bigint(36) COLLATE utf8_bin NOT NULL,
                            `user_id` bigint(36) COLLATE utf8_bin NOT NULL,
                            `order_name` varchar(100) COLLATE utf8_bin NOT NULL,
                            PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `t_order2` (
                            `order_id` bigint(36) COLLATE utf8_bin NOT NULL,
                            `user_id` bigint(36) COLLATE utf8_bin NOT NULL,
                            `order_name` varchar(100) COLLATE utf8_bin NOT NULL,
                            PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Order

@Data
@TableName("t_order")
public class Order extends Model<Order> {

    @TableId
    private Long orderId;

    private Long userId;

    private String orderName;
    
}

查询baseQo

/**
 * @author outengfei
 * @date 2023/3/17 9:38
 */
public class BasePageQo {

    private Integer pageNo;

    private Integer pageSize;

    public int getPageNo() {
        return pageNo;
    }

    public void setPageNo(Integer pageNo) {
        this.pageNo = pageNo == null ? 1 : pageNo;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize == null ? 10 : pageSize;
    }
}

OrderQo

/**
 * @author outengfei
 * @date 2023/5/18 14:26
 */
@Data
public class OrderQo extends BasePageQo{

    private Long userId;

}

Mapper

/**
 * @author outengfei
 * @date 2023/5/18 9:35
 */
@Mapper
public interface OrderDao extends BaseMapper<Order> {

    List<Order> selectAllByUserId(@Param("userId")Long userId);

    IPage<Order> selectPageVo(@Param("page") IPage<Order> page,@Param("orderQo") OrderQo orderQo);

}

mapperxml

<?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="com.oy.jdbcdemo.mapper.OrderDao">
  <resultMap id="BaseResultMap" type="com.oy.jdbcdemo.entity.Order">
    <!--@mbg.generated-->
    <!--@Table t_order-->
    <id column="order_id" jdbcType="BIGINT" property="orderId" />
    <result column="user_id" jdbcType="BIGINT" property="userId" />
    <result column="order_name" jdbcType="VARCHAR" property="orderName" />
  </resultMap>
  <sql id="Base_Column_List">
    <!--@mbg.generated-->
    order_id, user_id, order_name
  </sql>

<!--auto generated by MybatisCodeHelper on 2023-05-18-->
  <select id="selectAllByUserId" resultMap="BaseResultMap">
    select <include refid="Base_Column_List"/>
    from t_order
    where user_id=#{userId,jdbcType=BIGINT}
  </select>

  <select id="selectPageVo" resultMap="BaseResultMap">

    select <include refid="Base_Column_List"/>
    from t_order
    <where>
        <if test="orderQo.userId != null">
            user_id = #{orderQo.userId}
        </if>
    </where>
  </select>

</mapper>

Service

/**
 * @author outengfei
 * @date 2023/5/18 9:34
 */
public interface OrderService extends IService<Order> {
	
	List<Order> selectAllByUserId(Long userId);
	
	IPage<Order> listByPage(OrderQo orderQo);

}

Impl

/**
 * @author outengfei
 * @date 2023/5/18 9:35
 */
@Service("orderService")
@RequiredArgsConstructor
public class OrderServiceImpl extends ServiceImpl<OrderDao, Order> implements OrderService {

	private final OrderDao orderDao;

	@Override
	public List<Order> selectAllByUserId(Long userId){
		 return orderDao.selectAllByUserId(userId);
	}

	@Override
	public IPage<Order> listByPage(OrderQo orderQo) {

		IPage<Order> page = new Page<>(orderQo.getPageNo(), orderQo.getPageSize());

		IPage<Order> orderIPage = orderDao.selectPageVo(page, orderQo);

		//page.setRecords(orderIPage);
		return orderIPage;
	}
	
}

Controller

@RestController
@RequestMapping("/order")
public class OrderController {

    @Resource
    private OrderService orderService;

    @GetMapping("/{id}")
    public Order getById(@PathVariable Long id) {
        return this.orderService.getById(id);
    }

    @GetMapping("")
    public List<Order> list() {
        return this.orderService.list(null);
    }
	// 分页查询
    @GetMapping("/page")
    public IPage<Order> pageList(OrderQo orderQo) {

        return this.orderService.listByPage(orderQo);
    }


    @GetMapping("/user/{id}")
    public List<Order> userList(@PathVariable Long id) {
        return this.orderService.selectAllByUserId(id);
    }

    @GetMapping("/count")
    public Long count() {
        return this.orderService.count(null);
    }

    @GetMapping("/mock")
    public String mock() {
        for (int i = 0; i < 12; i++) {
             Order order = new Order();
             String index = String.valueOf(i + 1);
            order.setOrderId(System.currentTimeMillis());
            order.setUserId(Long.valueOf(i));
            order.setOrderName(index);
            this.orderService.save(order);
        }
        return "SUCCESS";
    }

}

项目地址

gitee地址