【sharding-jdbc】一个示例

直接看代码得了

是使用配置类进行分库分表设置
还可以使用配置文件进行配置

代码详情

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.3.0.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>shardingjdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>shardingjdbc</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <!--<sharding.jdbc.version>3.0.0</sharding.jdbc.version>-->
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
        </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.3</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.22</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.16</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.5</version>
        </dependency>
    </dependencies>

    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

DataSourceConfig

package com.example.shardingjdbc.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.example.shardingjdbc.sharding.UserShardingAlgorithm;
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.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

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

@Configuration
public class DataSourceConfig {
    @Value("${datasource0.url}")
    private String url0;
    @Value("${datasource0.username}")
    private String username0;
    @Value("${datasource0.password}")
    private String password0;
    @Value("${datasource0.driver-class-name}")
    private String driverClassName0;

    @Value("${datasource1.url}")
    private String url1;
    @Value("${datasource1.username}")
    private String username1;
    @Value("${datasource1.password}")
    private String password1;
    @Value("${datasource1.driver-class-name}")
    private String driverClassName1;

    @Value(("${spring.datasource.druid.filters}"))
    private String filters;

    @Bean("dataSource")
    public DataSource dataSource() {
        try {
            DruidDataSource dataSource0 = new DruidDataSource();
            dataSource0.setDriverClassName(this.driverClassName0);
            dataSource0.setUrl(this.url0);
            dataSource0.setUsername(this.username0);
            dataSource0.setPassword(this.password0);
            dataSource0.setFilters(this.filters);

//            DruidDataSource dataSource1 = new DruidDataSource();
//            dataSource1.setDriverClassName(this.driverClassName1);
//            dataSource1.setUrl(this.url1);
//            dataSource1.setUsername(this.username1);
//            dataSource1.setPassword(this.password1);
//            dataSource1.setFilters(this.filters);

            //分库设置
            Map<String, DataSource> dataSourceMap = new HashMap<>(2);
            //添加两个数据库database0和database1
            dataSourceMap.put("ds0", dataSource0);
//            dataSourceMap.put("ds1", dataSource1);

            // 配置 t_user 表规则
            TableRuleConfiguration userRuleConfiguration = new TableRuleConfiguration("t_user", "ds${0}.t_user${0..100}");

            // 行表达式分库规则
//            userRuleConfiguration.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "ds${id % 2}"));

            // 行表达式分表规则
//            userRuleConfiguration.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "t_user${id % 2}"));
            // 标准策略（自定义）
            userRuleConfiguration.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("id", UserShardingAlgorithm.tableShardingAlgorithm));

            // Sharding全局配置
            ShardingRuleConfiguration shardingRuleConfiguration = new ShardingRuleConfiguration();
            shardingRuleConfiguration.getTableRuleConfigs().add(userRuleConfiguration);
            // 创建数据源
            DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfiguration, new Properties());
            return dataSource;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }
}

UserController

package com.example.shardingjdbc.controller;

import com.example.shardingjdbc.entity.User;
import com.example.shardingjdbc.mapper.UserMapper;
import org.apache.shardingsphere.core.strategy.keygen.SnowflakeShardingKeyGenerator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.Date;

@RestController
public class UserController {
    @Autowired
    private UserMapper userMapper;

    @Resource
    SnowflakeShardingKeyGenerator userKeyGenerator;

    @GetMapping("/user/save")
    public String save() {
        for (int i = 0; i < 10; i++) {
            Long id = (Long)userKeyGenerator.generateKey();
            User user = new User();
            user.setId(id);
            user.setName("test" + i);
            user.setCityId(i);
            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.save(user);
        }

        return "success";
    }

    @RequestMapping("/user/get/{id}")
    @ResponseBody
    public User get(@PathVariable Long id) {
        User user = userMapper.get(id);
        return user;
    }
}

User

package com.example.shardingjdbc.entity;

import lombok.Data;

import java.io.Serializable;
import java.util.Date;

@Data
public class User implements Serializable {
    private Long id;
    private String name;
    private String phone;
    private String email;
    private String password;
    private Integer cityId;
    private Date createTime;
    private Integer sex;
}

UserMapper

package com.example.shardingjdbc.mapper;

import com.example.shardingjdbc.entity.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

public interface UserMapper {
    /**
     * 保存
     */
    void save(User user);

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

UserMapper.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.example.shardingjdbc.mapper.UserMapper">
    <resultMap id="resultMap" type="com.example.shardingjdbc.entity.User">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="phone" property="phone"  />
        <result column="email" property="email"  />
        <result column="password" property="password"  />
        <result column="city_id" property="cityId"  />
        <result column="create_time" property="createTime"  />
        <result column="sex" property="sex"  />
    </resultMap>

    <insert id="save">
        insert into t_user (id, name, phone, email, password, city_id, create_time, sex)
        values (#{id}, #{name}, #{phone}, #{email}, #{password}, #{cityId}, #{createTime}, #{sex})
    </insert>

    <select id="get" resultMap="resultMap">
        select *
        from t_user
        where id = #{id}
    </select>
</mapper>

UserShardingAlgorithm

package com.example.shardingjdbc.sharding;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;

public class UserShardingAlgorithm {
    public static final DatabaseShardingAlgorithm databaseShardingAlgorithm = new DatabaseShardingAlgorithm();
    public static final TableShardingAlgorithm tableShardingAlgorithm = new TableShardingAlgorithm();

    static class DatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
        @Override
        public String doSharding(Collection<String> databaseNames, PreciseShardingValue<Long> shardingValue) {
            for (String database : databaseNames) {
                if (database.endsWith(String.valueOf(shardingValue.getValue() % 2))) {
                    return database;
                }
            }
            return "";
        }
    }

    static class TableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
        @Override
        public String doSharding(Collection<String> tableNames, PreciseShardingValue<Long> shardingValue) {
            for (String table : tableNames) {
                if (table.endsWith(String.valueOf(shardingValue.getValue() % 2))) {
                    return table;
                }
            }
            return "";
        }
    }
}

ShardingjdbcApplication

package com.example.shardingjdbc;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@MapperScan("com.example.shardingjdbc.mapper")
@SpringBootApplication
public class ShardingjdbcApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShardingjdbcApplication.class, args);
    }
}

application.properties

#################################### common config : ####################################
spring.application.name=shardingjdbc
# Ӧ�÷���web���ʶ˿�
server.port=8080

# mybatis����
mybatis.mapper-locations=classpath:com/example/shardingjdbc/mapper/*.xml
mybatis.type-aliases-package=com.example.shardingjdbc.**.entity

datasource0.url=jdbc:mysql://localhost:3306/sharding-jdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
datasource0.driver-class-name=com.mysql.cj.jdbc.Driver
datasource0.type=com.alibaba.druid.pool.DruidDataSource
datasource0.username=root
datasource0.password=123456

datasource1.url=jdbc:mysql://localhost:3306/sharding-jdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
datasource1.driver-class-name=com.mysql.cj.jdbc.Driver
datasource1.type=com.alibaba.druid.pool.DruidDataSource
datasource1.username=root
datasource1.password=123456

### 分库策略
# 分库分片健
#spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.sharding-column=id
# 分库分片算法
#spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.precise-algorithm-class-name=com.example.shardingjdbc.sharding.UserShardingAlgorithm



#
##### ���ӳ����� #######
# ���������ã���һ��stat����Ҫ��û�еĻ����ز���SQL��
spring.datasource.druid.filters=stat,wall,log4j2

##### WebStatFilter���� #######
#����StatFilter
spring.datasource.druid.web-stat-filter.enabled=true
#��ӹ��˹���
spring.datasource.druid.web-stat-filter.url-pattern=/*
#�ų�һЩ����Ҫ��url
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
#����sessionͳ�ƹ���
spring.datasource.druid.web-stat-filter.session-stat-enable=true
#ȱʡsessionStatMaxCount��1000��
spring.datasource.druid.web-stat-filter.session-stat-max-count=1000
#spring.datasource.druid.web-stat-filter.principal-session-name=
#spring.datasource.druid.web-stat-filter.principal-cookie-name=
#spring.datasource.druid.web-stat-filter.profile-enable=

##### StatViewServlet���� #######
#�������õļ��ҳ��
spring.datasource.druid.stat-view-servlet.enabled=true
#���ü��ҳ��ĵ�ַ
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
#�ر� Reset All ����
spring.datasource.druid.stat-view-servlet.reset-enable=false
#���õ�¼�û���
spring.datasource.druid.stat-view-servlet.login-username=admin
#���õ�¼����
spring.datasource.druid.stat-view-servlet.login-password=123
#�����������allowû�����û���Ϊ�գ����������з��ʣ�
spring.datasource.druid.stat-view-servlet.allow=127.0.0.1
#��������deny������allow�������deny�б��У�������allow�б��У�Ҳ�ᱻ�ܾ���
spring.datasource.druid.stat-view-servlet.deny=

建表语句

CREATE TABLE `t_user` (
  `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;