shardingjdbc，挺好的一个框架，现在改名叫shardingsphere了，话不多说，开始使用。

 <!-- sharding-jdbc -->
		<dependency>
		    <groupId>io.shardingsphere</groupId>
		    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
		    <version>3.0.0</version>
		</dependency>
		
		<dependency>
		    <groupId>io.shardingsphere</groupId>
		    <artifactId>sharding-jdbc-spring-namespace</artifactId>
		    <version>3.1.0</version>
		</dependency>

一、单库分表
其他的配置就不贴上去了，这是sharding相关的配置信息，根据sex取模分表

sharding:
  jdbc:
    datasource:
      names: ds0
      # 数据源ds0
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/ljw?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&allowMultiQueries=true
        username: root
        password: root
    config:
      sharding:
        props:
          sql.show: true
        tables:
          t_user:  #t_user表
            key-generator-column-name: id  #主键
            actual-data-nodes: ds0.t_user${0..1}    #数据节点,均匀分布
            table-strategy:  #分表策略
              inline: #行表达式
                sharding-column: sex
                algorithm-expression: t_user${sex % 2}  #根据性别按模运算分配

这样启动会报错如下：

Description:

The bean 'dataSource', defined in class path resource [io/shardingsphere/shardingjdbc/spring/boot/SpringBootConfiguration.class], could not be registered. A bean with that name has already been defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration$Hikari.class] and overriding is disabled.

Action:

Consider renaming one of the beans or enabling overriding by setting spring.main.allow-bean-definition-overriding=true

所以呢，我又增加了这一行配置spring.main.allow-bean-definition-overriding=true，我懒得弄，所以直接放在property配置文件里面了，这样启动就不会出错了

-- 表结构
CREATE TABLE `t_user0` (
  `id` bigint(20) NOT NULL,
  `name` varchar(64) DEFAULT NULL COMMENT '名称',
  `city_id` int(12) DEFAULT NULL COMMENT '城市',
  `sex` tinyint(1) DEFAULT NULL COMMENT '性别',
  `phone` varchar(32) DEFAULT NULL COMMENT '电话',
  `email` varchar(32) DEFAULT NULL COMMENT '邮箱',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `password` varchar(32) DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_user1` (
  `id` bigint(20) NOT NULL,
  `name` varchar(64) DEFAULT NULL COMMENT '名称',
  `city_id` int(12) DEFAULT NULL COMMENT '城市',
  `sex` tinyint(1) DEFAULT NULL COMMENT '性别',
  `phone` varchar(32) DEFAULT NULL COMMENT '电话',
  `email` varchar(32) DEFAULT NULL COMMENT '邮箱',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `password` varchar(32) DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

实体类

@Data
public class User {

	private Long id;
	private String name;
	private String phone;
	private String email;
	private String password;
	private Integer cityId;
    private Date createTime;
    private Integer sex;
}

/**
	 * 保存用户
	 * @return
	 */
	@GetMapping("/saveUser")
	public Result saveUser() {
		 for (int i = 0; i <35 ; i++) {
            User user=new User();
            user.setName("test"+i);
            user.setCityId(new Random().nextInt(1000));
            user.setCreateTime(new Date());
            user.setSex(i%2==0?1:2);
            user.setPhone("11111111"+i);
            user.setEmail("xxxxx");
            user.setCreateTime(new Date());
            user.setPassword("eeeeeeeeeeee");
            userMapper.saveUser(user);
	    }
		return Result.ok();
	}
	
	/**
	 * 根据用户id
	 * @param id
	 * @return
	 */
	@GetMapping("/getUser/{id}")
	public Result getUser(@PathVariable("id")String id) {
		System.err.println(id);
		User user = userMapper.getUserById(id);
		return Result.ok(user);
	}
	
	/**
	 * 分页查询
	 * @param pageNum
	 * @return
	 */
	@GetMapping("/getUserTotal/{pageNum}")
	public Result getUserTotal(@PathVariable("pageNum")int pageNum) {
		PageHelper.startPage(pageNum,10);
        List<User> list = userMapper.selectList();
        PageInfo<User> pageInfo = new PageInfo<User>(list);
        pageInfo.setList(list);
		return Result.ok(pageInfo);
	}

mapper接口

public interface UserMapper {

	@Insert(" insert into t_user(name,phone,email,city_id,sex,password) values(#{user.name},#{user.phone},#{user.email},#{user.cityId},#{user.sex},#{user.password}) ")
	int saveUser(@Param("user")User user);

	@Select(" select * from t_user where id=#{id} ")
	User getUserById(@Param("id")String id);

	@Select(" select * from t_user order by name ")
	List<User> selectList();

}

二、分库分表，在刚才的基础之上加以修改就行，其他不用动，修改一下配置信息即可，按照city_id取模分库

sharding:
  jdbc:
    datasource:
      #数据源名称，多数据源以逗号分隔
      names: ds0,ds1
      # 数据源ds0
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/ljw?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&allowMultiQueries=true
        username: root
        password: root
      # 数据源ds1
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/ljw_0?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&allowMultiQueries=true
        username: root
        password: root
    config:
      sharding:
        props:
          sql.show: true
        tables:
          #逻辑表名称(随意取名)，在项目中sql使用
          t_user:  #t_user表
            key-generator-column-name: id  #主键
            actual-data-nodes: ds0.t_user${0..1}    #数据节点,均匀分布
            table-strategy:  #分表策略
              inline: #行表达式
                sharding-column: sex
                algorithm-expression: t_user${sex % 2}  #根据性别按模运算分配
            #库分片策略
            database-strategy:
              inline:
                #分片字段的名称
                sharding-column: city_id
                #分片算法
                algorithm-expression: ds$->{city_id % 2}