Spring Boot 整合 Sharding-JDBC

概述

        ShardingSphere，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成。定位为轻量级Java框架。其实就是一个增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。内部改写了SQL的添加和查询规则。适用于任何基于Java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。

技术架构：

Spring Boot 2.6.3 、 Sharding-JDBC 3.0.0.M3、MySql 1主2从模式、 JDK1.8

导入依赖

1.  
   <!-- for spring boot -->
2.  
   <dependency>
3.  
   <groupId>io.shardingsphere</groupId>
4.  
   <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
5.  
   <version>${sharding-sphere.version}</version>
6.  
   </dependency>
7.  
    
8.  
   <!-- mybatis -->
9.  
   <dependency>
10.  
    <groupId>org.mybatis.spring.boot</groupId>
11.  
    <artifactId>mybatis-spring-boot-starter</artifactId>
12.  
    <version>1.3.2</version>
13.  
    </dependency>
14.  
     
15.  
    <dependency>
16.  
    <groupId>com.alibaba</groupId>
17.  
    <artifactId>druid</artifactId>
18.  
    <version>1.1.21</version>
19.  
    </dependency>
20.  
     
21.  
    <dependency>
22.  
    <groupId>mysql</groupId>
23.  
    <artifactId>mysql-connector-java</artifactId>
24.  
    <version>5.1.41</version>
25.  
    </dependency>
26.  
     
27.  
    <dependency>
28.  
    <groupId>org.projectlombok</groupId>
29.  
    <artifactId>lombok</artifactId>
30.  
    <version>1.18.4</version>
31.  
    </dependency>

application.yml配置

1.  
   #sharding-jdbc
2.  
   sharding.jdbc:
3.  
   datasource:
4.  
   names: ds-master,ds-slave-0,ds-slave-1
5.  
   ds-master:
6.  
   type: com.alibaba.druid.pool.DruidDataSource
7.  
   driver-class-name: com.mysql.jdbc.Driver
8.  
   url: jdbc:mysql://192.168.116.105:3306/test-01
9.  
   username: root
10.  
    password: root
11.  
    ds-slave-0:
12.  
    type: com.alibaba.druid.pool.DruidDataSource
13.  
    driver-class-name: com.mysql.jdbc.Driver
14.  
    url: jdbc:mysql://192.168.116.106:3306/test-01
15.  
    username: root
16.  
    password: root
17.  
    ds-slave-1:
18.  
    type: com.alibaba.druid.pool.DruidDataSource
19.  
    driver-class-name: com.mysql.jdbc.Driver
20.  
    url: jdbc:mysql://192.168.116.107:3306/test-01
21.  
    username: root
22.  
    password: root
23.  
    config:
24.  
    masterslave:
25.  
    name: ds_ms
26.  
    master-data-source-name: ds-master
27.  
    slave-data-source-names: ds-slave-0,ds-slave-1
28.  
    load-balance-algorithm-type: round_robin
29.  
    props:
30.  
    sql.show: true
31.  
    #mybatis
32.  
    mybatis:
33.  
    config-location: classpath:mybatis/config.xml
34.  
    mapper-locations:
35.  
    - classpath:mybatis/mappers/*.xml

MySql数据表结构

1.  
   CREATE TABLE `user` (
2.  
     `id` int(255) NOT NULL AUTO_INCREMENT,
3.  
     `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
4.  
     `age` int(11) DEFAULT NULL,
5.  
     PRIMARY KEY (`id`)
6.  
   ) ENGINE=InnoDB AUTO_INCREMENT=10158 DEFAULT CHARSET=latin1;

实体类

1.  
   package com.example.producers.domain;
2.  
    
3.  
   import lombok.Data;
4.  
    
5.  
   import java.io.Serializable;
6.  
    
7.  
   /**
8.  
   * @author lanx
9.  
   * @date 2022/2/21
10.  
    */
11.  
    @Data
12.  
    public class User implements Serializable {
13.  
    private Integer id;
14.  
    private String name;
15.  
    private Integer age;
16.  
    }

Dao层

1.  
   package com.example.producers.dao;
2.  
    
3.  
   import com.example.producers.domain.User;
4.  
   import org.apache.ibatis.annotations.Mapper;
5.  
    
6.  
   /**
7.  
   * @author lanx
8.  
   * @date 2022/2/21
9.  
   */
10.  
    @Mapper
11.  
    public interface UserMapper {
12.  
    User selectByPrimaryKey(int id);
13.  
    }

UserMapper.xml

1.  
   <?xml version="1.0" encoding="UTF-8"?>
2.  
   <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
3.  
   <mapper namespace="com.example.producers.dao.UserMapper">
4.  
   <resultMap id="BaseResultMap" type="com.example.producers.domain.User">
5.  
   <id column="id" jdbcType="INTEGER" property="id" />
6.  
   <result column="name" jdbcType="INTEGER" property="name" />
7.  
   <result column="age" jdbcType="INTEGER" property="age" />
8.  
   </resultMap>
9.  
   <sql id="Base_Column_List">
10.  
    id, name, age
11.  
    </sql>
12.  
    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
13.  
    select
14.  
    <include refid="Base_Column_List" />
15.  
    from user
16.  
    where id = #{id,jdbcType=INTEGER}
17.  
    </select>
18.  
    </mapper>
19.  
     

UserController 测试接口

1.  
   package com.example.producers.web;
2.  
    
3.  
   import com.example.producers.dao.UserMapper;
4.  
   import com.example.producers.domain.User;
5.  
   import com.example.producers.service.UserService;
6.  
   import org.springframework.beans.factory.annotation.Autowired;
7.  
   import org.springframework.web.bind.annotation.RequestMapping;
8.  
   import org.springframework.web.bind.annotation.RequestParam;
9.  
   import org.springframework.web.bind.annotation.RestController;
10.  
     
11.  
    /**
12.  
    * 用户管理
13.  
    * @author lanx
14.  
    * @date 2022/3/5
15.  
    */
16.  
    @RestController
17.  
    public class UserController {
18.  
     
19.  
    @Autowired
20.  
    private UserMapper userMapper;
21.  
     
22.  
    @RequestMapping("/getUser")
23.  
    public User getUser(@RequestParam("id")int id){
24.  
    User user = userMapper.selectByPrimaryKey(id);
25.  
    return user;
26.  
    }
27.  
    }

接口调用

如果在整合过程中出现如下错误：

Configuration property name 'sharding.jdbc.datasource.ds_master' is not vali

报错原因
ShardingSphere 在5.0以前的版本数据源的命名、自定义分片算法命名是支持下划线命名的，如 write_ds，但是在5.0以后就不支持了。

解决方法
多个单词之间不要使用"_"分割，使用 ”-“ 分割即可，如 ds-master

这篇好文章是转载于：学新通技术网