springboot2集成sharding-jdbc分库分表

2024-03-15 11:46:23

sharding-jdbc简介：

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

适用于任何基于Java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
基于任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer和PostgreSQL。

1、pom.xml配置

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.7.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>
  <groupId>com.mqm501</groupId>
  <artifactId>springboot-sharding</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>springboot-sharding</name>
  <description>Demo project for Spring Boot</description>

  <properties>
    <java.version>1.8</java.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>2.1.0</version>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <scope>runtime</scope>
    </dependency>
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>2.1.0</version>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <optional>true</optional>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.0</version>
    </dependency>
    <dependency>
      <groupId>io.shardingsphere</groupId>
      <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
      <version>3.0.0</version>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>

</project>

2、创建数据库语句：

CREATE TABLE `t_address` (
                             `id` bigint(20) NOT NULL AUTO_INCREMENT,
                             `code` varchar(64) DEFAULT NULL COMMENT '编码',
                             `name` varchar(64) DEFAULT NULL COMMENT '名称',
                             `pid` varchar(64) NOT NULL DEFAULT '0' COMMENT '父id',
                             `type` int(11) DEFAULT NULL COMMENT '1国家2省3市4县区',
                             `lit` int(11) DEFAULT NULL,
                             PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

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;

3、实体类：

package com.mqm501.springbootsharding.sharding.model;

import java.util.Date;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * Project: springboot-sharding Package: com.mqm501.springbootsharding.sharding.model
 * <p>Create Time: 2019年09月01日 16:19          </p>
 * <p>@author: qingmei.meng               </p>
 * <p>Description:               </p>
 **/
@NoArgsConstructor
@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;
}

package com.mqm501.springbootsharding.sharding.model;

import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * Project: springboot-sharding Package: com.mqm501.springbootsharding.sharding.model
 * <p>Create Time: 2019年09月01日 16:19          </p>
 * <p>@author: qingmei.meng               </p>
 * <p>Description:               </p>
 **/
@NoArgsConstructor
@Data
public class Address {
  private Long id;
  private String code;
  private String name;
  private String pid;
  private Integer type;
  private Integer lit;
}

4、创建Mapper

package com.mqm501.springbootsharding.sharding.mapper;

import com.mqm501.springbootsharding.sharding.model.User;
import org.apache.ibatis.annotations.Mapper;

/**
 * Project: springboot-sharding Package: com.mqm501.springbootsharding.sharding.mapper
 * <p>Create Time: 2019年09月01日 16:20          </p>
 * <p>@author: qingmei.meng               </p>
 * <p>Description:               </p>
 **/
@Mapper
public interface UserMapper {
  /**
   * 保存
   */
  void save(User user);

  /**
   * 查询
   * @param id
   * @return
   */
  User get(Long id);
}

package com.mqm501.springbootsharding.sharding.mapper;

import com.mqm501.springbootsharding.sharding.model.Address;
import org.apache.ibatis.annotations.Mapper;

/**
 * Project: springboot-sharding Package: com.mqm501.springbootsharding.sharding.mapper
 * <p>Create Time: 2019年09月01日 16:45          </p>
 * <p>@author: qingmei.meng               </p>
 * <p>Description:               </p>
 **/
@Mapper
public interface AddressMapper {
  /**
   * 保存
   */
  void save(Address address);

  /**
   * 查询
   * @param id
   * @return
   */
  Address get(Long id);
}

5、配置mapper.xml

<?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.mqm501.springbootsharding.sharding.mapper.UserMapper">
  <insert id="save" parameterType="User">
    INSERT INTO t_user(name,phone,email,city_id,sex,password)
    VALUES
    (
      #{name},#{phone},#{email},#{cityId},#{sex},#{password}
    )
  </insert>

  <select id="get" parameterType="long" resultType="User">
    select * from t_user where id = #{id}
  </select>
</mapper>

<?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.mqm501.springbootsharding.sharding.mapper.AddressMapper">
  <insert id="save" parameterType="Address">
    INSERT INTO t_address(code,name,pid,type,lit)
    VALUES
    (
      #{code},#{name},#{pid},#{type},#{lit}
    )
  </insert>

  <select id="get" parameterType="long" resultType="Address">
    select * from t_address where id = #{id}
  </select>
</mapper>

6、配置controller、service

package com.mqm501.springbootsharding.sharding.controller;

import com.mqm501.springbootsharding.sharding.model.User;
import com.mqm501.springbootsharding.sharding.service.UserService;
import java.util.Date;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

/**
 * Project: springboot-sharding Package: com.mqm501.springbootsharding.sharding.controller
 * <p>Create Time: 2019年09月01日 16:18          </p>
 * <p>@author: qingmei.meng               </p>
 * <p>Description:               </p>
 **/
@Controller
public class UserController {

  @Autowired
  private UserService userService;

  @RequestMapping("/user/save")
  @ResponseBody
  public String save() {
    for (int i = 0; i <10 ; i++) {
      User user=new User();
      user.setName("test"+i);
      user.setCityId(i%2==0?1:2);
      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");
      userService.save(user);
    }

    return "success";
  }

  @RequestMapping("/user/get/{id}")
  @ResponseBody
  public User get(@PathVariable Long id) {
    User user =  userService.get(id);
    System.out.println(user.getId());
    return user;
  }
}

package com.mqm501.springbootsharding.sharding.controller;

import com.mqm501.springbootsharding.sharding.model.Address;
import com.mqm501.springbootsharding.sharding.service.AddressService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

/**
 * Project: springboot-sharding Package: com.mqm501.springbootsharding.sharding.controller
 * <p>Create Time: 2019年09月01日 16:45          </p>
 * <p>@author: qingmei.meng               </p>
 * <p>Description:               </p>
 **/
@Controller
public class AddressController {
  @Autowired
  private AddressService addressService;

  @RequestMapping("/address/save")
  @ResponseBody
  public String save() {
    for (int i = 0; i <10 ; i++) {
      Address address=new Address();
      address.setCode("code_"+i);
      address.setName("name_"+i);
      address.setPid(i+"");
      address.setType(0);
      address.setLit(i%2==0?1:2);
      addressService.save(address);
    }

    return "success";
  }

  @RequestMapping("/address/get/{id}")
  @ResponseBody
  public Address get(@PathVariable Long id) {
    return addressService.get(id);
  }
}

package com.mqm501.springbootsharding.sharding.service;

import com.mqm501.springbootsharding.sharding.model.User;

/**
 * Project: springboot-sharding Package: com.mqm501.springbootsharding.sharding.service.impl
 * <p>Create Time: 2019年09月01日 16:20          </p>
 * <p>@author: qingmei.meng               </p>
 * <p>Description:               </p>
 **/
public interface UserService {
  /**
   * 保存
   */
  void save(User user);

  /**
   * 查询
   * @param id
   * @return
   */
  User get(Long id);
}

package com.mqm501.springbootsharding.sharding.service;

import com.mqm501.springbootsharding.sharding.model.Address;

/**
 * Project: springboot-sharding Package: com.mqm501.springbootsharding.sharding.service
 * <p>Create Time: 2019年09月01日 16:44          </p>
 * <p>@author: qingmei.meng               </p>
 * <p>Description:               </p>
 **/
public interface AddressService {
  void save(Address address);
  Address get(Long id);
}

package com.mqm501.springbootsharding.sharding.service.impl;

import com.mqm501.springbootsharding.sharding.mapper.UserMapper;
import com.mqm501.springbootsharding.sharding.model.User;
import com.mqm501.springbootsharding.sharding.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * Project: springboot-sharding Package: com.mqm501.springbootsharding.sharding.service.impl
 * <p>Create Time: 2019年09月01日 16:23          </p>
 * <p>@author: qingmei.meng               </p>
 * <p>Description:               </p>
 **/
@Service("userService")
public class UserServiceImpl implements UserService {
  @Autowired
  UserMapper userMapper;//这里会报错，但是并不会影响
  /**
   * 保存
   */
  public void save(User user){
    userMapper.save(user);
  }

  /**
   * 查询
   * @param id
   * @return
   */
  public User get(Long id){
    return userMapper.get(id);
  }
}

package com.mqm501.springbootsharding.sharding.service.impl;

import com.mqm501.springbootsharding.sharding.mapper.AddressMapper;
import com.mqm501.springbootsharding.sharding.model.Address;
import com.mqm501.springbootsharding.sharding.service.AddressService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * Project: springboot-sharding Package: com.mqm501.springbootsharding.sharding.service.impl
 * <p>Create Time: 2019年09月01日 16:44          </p>
 * <p>@author: qingmei.meng               </p>
 * <p>Description:               </p>
 **/
@Service(value = "addressService")
public class AddressServiceImpl implements AddressService {
  @Autowired
  private AddressMapper addressMapper;//这里会报错，但是并不会影响
  /**
   * 保存
   */
  @Override
  public void save(Address address){
    addressMapper.save(address);
  }

  /**
   * 查询
   * @param id
   * @return
   */
  @Override
  public Address get(Long id){
    return addressMapper.get(id);
  }
}

7、设置分片规则，分库分表事例：

#url相关配置，这里配置url的基本url
server.port=8081
### MYBATIS
mybatis.typeAliasesPackage=com.mqm501.springbootsharding.*.model
mybatis.mapperLocations=classpath:mapper/*.xml
mybatis.configuration.mapUnderscoreToCamelCase=true

#sharding-jdbc
sharding.jdbc.datasource.names=ds0,ds1

sharding.jdbc.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds0.url=jdbc:mysql://localhost:3306/mytest?useAffectedRows=true&useUnicode=true&characterEncoding=utf-8&useSSL=false
sharding.jdbc.datasource.ds0.username=root
sharding.jdbc.datasource.ds0.password=mysql1234

sharding.jdbc.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds1.url=jdbc:mysql://localhost:3306/mytest2?useAffectedRows=true&useUnicode=true&characterEncoding=utf-8&useSSL=false
sharding.jdbc.datasource.ds1.username=root
sharding.jdbc.datasource.ds1.password=mysql1234

#t_user 不分库分表
sharding.jdbc.config.sharding.tables.t_user.key-generator-column-name=id
sharding.jdbc.config.sharding.tables.t_user.actual-data-nodes=ds$->{0..1}.t_user$->{0..1}
sharding.jdbc.config.sharding.tables.t_user.database-strategy.inline.sharding-column=city_id
sharding.jdbc.config.sharding.tables.t_user.database-strategy.inline.algorithm-expression=ds$->{city_id % 2}
sharding.jdbc.config.sharding.tables.t_user.table-strategy.inline.sharding-column=sex
sharding.jdbc.config.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user$->{sex % 2}
#t_user 不分库分表
sharding.jdbc.config.sharding.tables.t_address.key-generator-column-name=id
sharding.jdbc.config.sharding.tables.t_address.actual-data-nodes=ds$->{0..1}.t_address
sharding.jdbc.config.sharding.tables.t_address.database-strategy.inline.sharding-column=lit
sharding.jdbc.config.sharding.tables.t_address.database-strategy.inline.algorithm-expression=ds$->{lit % 2}

sharding.jdbc.config.props.sql.show=true

码农公寓

springboot2集成sharding-jdbc分库分表

sharding-jdbc简介：

1、pom.xml配置

2、创建数据库语句：

3、实体类：

4、创建Mapper

5、配置mapper.xml

6、配置controller、service

7、设置分片规则，分库分表事例：

8、官方文档：https://shardingsphere.apache.org/document/legacy/3.x/document/cn/overview/

9、源码地址：https://github.com/mqm501/keepup.git

码农公寓

sharding-jdbc简介：

1、pom.xml配置

2、创建数据库语句：

3、实体类：

4、创建Mapper

5、配置mapper.xml

6、配置controller、service

7、设置分片规则，分库分表事例：

8、官方文档：https://shardingsphere.apache.org/document/legacy/3.x/document/cn/overview/

9、源码地址：https://github.com/mqm501/keepup.git

相关文章