11 springboot集成sharding-jdbc分表

这里只考虑分表情况

创建maven项目，pom中引入jar包

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.4.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>

<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <java.version>1.8</java.version>
</properties>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-actuator</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>


    <!-- aigorithm jdbc -->
    <dependency>
        <groupId>com.dangdang</groupId>
        <artifactId>sharding-jdbc-core</artifactId>
        <version>1.5.3</version>
    </dependency>
    <dependency>
        <groupId>com.dangdang</groupId>
        <artifactId>sharding-jdbc-config-yaml</artifactId>
        <version>1.5.3</version>
    </dependency>

    <!-- mybatis -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>1.3.1</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.2</version>
    </dependency>
</dependencies>

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

创建表，我这里就简单的创建了两个表user0和user1

CREATE TABLE user0 (
id bigint(64) NOT NULL,
name varchar(255) default NULL,
sex int(1) default NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表user1结构和user0一样，就是表名换了

这个是项目结构

项目结构

主要看sharding.yml和ShardingConfig

sharding.yml

dataSource:
  ds_0: !!com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://172.16.255.69:3306/test
    driverClassName: com.mysql.jdbc.Driver
    username: root
    password: root

tables:
  user:
    actualTables: user${0..1}
    tableStrategy:
      shardingColumns: id
      algorithmExpression: user${id.longValue() % 2}

bindingTables:
  - tableNames: user

#默认数据库分片策略
defaultDatabaseStrategy:
  shardingColumns: id
  algorithmExpression: ds_0

props:
  sql.show: true

ShardingConfig

import com.dangdang.ddframe.rdb.sharding.config.yaml.api.YamlShardingDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.io.File;

@Configuration
public class ShardingConfig {
    @Bean
    public DataSource dataSource() throws Exception {
        Resource resource = new ClassPathResource("sharding.yml");
        File file = resource.getFile();
        DataSource dataSource = new YamlShardingDataSource(file);
        return dataSource;
    }

    @Bean
    public SqlSessionFactory sqlSessionFactoryBean() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource());
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public PlatformTransactionManager transactionManager() throws Exception {
        return new DataSourceTransactionManager(dataSource());
    }
}

主要操作在UserMapper中

UserMapper
注意：这里的表名没有后缀0,1等，主要在sharding.yml中配置。

然后启动application。正确

这两个数据在两个表中，插入也一样，id为奇数插入到user1中，id为偶数插入到user0

那么问题来了，sharding-jdbc原理是什么。http://blog.csdn.net/u4110122855/article/details/50670503，下回有时间要好好看看他们的代码。

代码放在 https://github.com/lijiaccy/springboot-sharding-table