sharding-jdbc示例项目

简介

开源分库分表中间件

官方介绍

Sharding-JDBC是一个开源的适用于微服务的分布式数据访问基础类库，它始终以云原生的基础开发套件为目标。

Sharding-JDBC定位为轻量级java框架，使用客户端直连数据库，以jar包形式提供服务，未使用中间层，无需额外部署，无其他依赖，DBA也无需改变原有的运维方式，可理解为增强版的JDBC驱动，旧代码迁移成本几乎为零。

Sharding-JDBC完整的实现了分库分表，读写分离和分布式主键功能，并初步实现了柔性事务。

官方链接

官网：shardingjdbc.io

文档：shardingjdbc文档

github:sharding-jdbc

示例项目

pom.xml

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 http://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.gxx</groupId>
	<artifactId>sharding</artifactId>
	<packaging>war</packaging>
	<version>0.0.1-SNAPSHOT</version>
	<name>sharding Maven Webapp</name>
	<url>http://maven.apache.org</url>
 
	<!-- 项目属性 -->
	<properties>
		<!-- jar 版本设置 -->
		<spring.version>4.0.5.RELEASE</spring.version>
		<aspectjrt.version>1.6.9</aspectjrt.version>
		<aspectjweaver.version>1.6.1</aspectjweaver.version>
		<freemarker.version>2.3.20</freemarker.version>
		<mybatis.version>3.2.7</mybatis.version>
		<mybatis-spring.version>1.2.2</mybatis-spring.version>
		<mybatis-generator-core.version>1.3.2</mybatis-generator-core.version>
		<servlet-api.version>2.4</servlet-api.version>
		<jsp-api.version>2.0</jsp-api.version>
		<jstl.version>1.2</jstl.version>
		<standard.version>1.1.2</standard.version>
		<jackson-databind.version>2.4.0</jackson-databind.version>
		<jackson-annotations.version>2.4.0</jackson-annotations.version>
		<jackson-module-jaxb-annotations.version>2.4.0</jackson-module-jaxb-annotations.version>
		<jackson-core.version>2.4.0</jackson-core.version>
		<junit.version>4.11</junit.version>
		<log4j.version>1.2.16</log4j.version>
		<mysql-connector-java.version>5.1.9</mysql-connector-java.version>
		<commons-dbcp.version>1.4</commons-dbcp.version>
		<commons-lang3.version>3.3.2</commons-lang3.version>
	</properties>
 
	<!-- scm软件配置管理 -->
	<scm>
		<connection>scm:svn:svn://121.43.104.34/gxx/trunk/sharding</connection>
		<developerConnection>scm:svn:svn://121.43.104.34/gxx/trunk/sharding</developerConnection>
	</scm>
 
	<dependencies>
		<!-- Spring -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-beans</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context-support</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-aop</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-web</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>${spring.version}</version>
		</dependency>
 
		<!-- aspectj -->
		<dependency>
			<groupId>org.aspectj</groupId>
			<artifactId>aspectjrt</artifactId>
			<version>${aspectjrt.version}</version>
		</dependency>
		<dependency>
			<groupId>org.aspectj</groupId>
			<artifactId>aspectjweaver</artifactId>
			<version>${aspectjweaver.version}</version>
		</dependency>
 
		<!-- freemarker -->
		<dependency>
			<groupId>org.freemarker</groupId>
			<artifactId>freemarker</artifactId>
			<version>${freemarker.version}</version>
		</dependency>
 
		<!-- mybatis -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>${mybatis.version}</version>
		</dependency>
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis-spring</artifactId>
			<version>${mybatis-spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.mybatis.generator</groupId>
			<artifactId>mybatis-generator-core</artifactId>
			<version>${mybatis-generator-core.version}</version>
		</dependency>
 
		<!-- j2ee基础 -->
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>servlet-api</artifactId>
			<version>${servlet-api.version}</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet.jsp</groupId>
			<artifactId>jsp-api</artifactId>
			<version>${jsp-api.version}</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
			<version>${jstl.version}</version>
		</dependency>
		<dependency>
			<groupId>taglibs</groupId>
			<artifactId>standard</artifactId>
			<version>${standard.version}</version>
		</dependency>
 
		<!-- jackson -->
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-databind</artifactId>
			<version>${jackson-databind.version}</version>
		</dependency>
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-annotations</artifactId>
			<version>${jackson-annotations.version}</version>
		</dependency>
		<dependency>
			<groupId>com.fasterxml.jackson.module</groupId>
			<artifactId>jackson-module-jaxb-annotations</artifactId>
			<version>${jackson-module-jaxb-annotations.version}</version>
		</dependency>
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-core</artifactId>
			<version>${jackson-core.version}</version>
		</dependency>
 
		<!-- log4j -->
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>${log4j.version}</version>
		</dependency>
 
		<!-- junit -->
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<scope>test</scope>
			<version>${junit.version}</version>
		</dependency>
 
		<!-- mysql -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>${mysql-connector-java.version}</version>
		</dependency>
 
		<!-- dbcp -->
		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>${commons-dbcp.version}</version>
		</dependency>
 
		<!-- sharding-jdbc -->
		<dependency>
			<groupId>com.dangdang</groupId>
			<artifactId>sharding-jdbc-core</artifactId>
			<version>1.4.2</version>
		</dependency>
		<dependency>
			<groupId>com.dangdang</groupId>
			<artifactId>sharding-jdbc-config-spring</artifactId>
			<version>1.4.0</version>
		</dependency>
 
		<!-- apache common -->
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
			<version>${commons-lang3.version}</version>
		</dependency>
	</dependencies>
 
	<!-- 设定除中央仓repo1.maven.org/maven2/)外的其他仓库,按设定顺序进行查 -->
	<repositories>
		<!-- nexus私服仓库 -->
		<repository>
			<id>nexus</id>
			<name>Team Nexus Repository</name>
			<url>http://121.43.104.34:8081/nexus/content/groups/public</url>
		</repository>
		<repository>
			<id>offical</id>
			<name>Maven Official Repository</name>
			<url>http://repo1.maven.org/maven2</url>
			<snapshots>
				<enabled>false</enabled>
			</snapshots>
		</repository>
		<repository>
			<id>spring-milestone</id>
			<name>Spring Maven MILESTONE Repository</name>
			<url>http://maven.springframework.org/milestone</url>
		</repository>
		<repository>
			<id>jboss</id>
			<name>Jboss Repository</name>
			<url>http://repository.jboss.org/nexus/content/groups/public-jboss/</url>
			<snapshots>
				<enabled>false</enabled>
			</snapshots>
		</repository>
		<repository>
			<id>java.net</id>
			<name>Java.net Repository</name>
			<url>http://download.java.net/maven/2/</url>
			<snapshots>
				<enabled>false</enabled>
			</snapshots>
		</repository>
		<repository>
			<id>spring-release</id>
			<name>Spring Maven Release Repository</name>
			<url>http://repo.springsource.org/libs-release</url>
			<snapshots>
				<enabled>false</enabled>
			</snapshots>
		</repository>
	</repositories>
 
	<build>
		<finalName>sharding</finalName>
		<plugins>
			<!-- jdk编译插件 -->
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>2.0.2</version>
				<configuration>
					<source>1.6</source>
					<target>1.6</target>
					<encoding>utf8</encoding>
				</configuration>
			</plugin>
			<!-- scm软件配置管理插件 -->
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-scm-plugin</artifactId>
				<version>1.3</version>
			</plugin>
			<!-- 版本发布插件 -->
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-release-plugin</artifactId>
				<version>2.4</version>
				<configuration>
					<tagBase>svn://121.43.104.34/gxx/tags/sharding</tagBase>
					<branchBase>svn://121.43.104.34/gxx/branches/sharding</branchBase>
				</configuration>
			</plugin>
			<!-- 原型插件 -->
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-archetype-plugin</artifactId>
				<version>2.2</version>
			</plugin>
		</plugins>
	</build>
 
	<!-- 版本发布管理 -->
	<distributionManagement>
		<!-- release稳定版本 -->
		<repository>
			<id>nexus-releases</id>
			<name>Nexus Release Repository</name>
			<url>http://121.43.104.34:8081/nexus/content/repositories/releases/</url>
		</repository>
		<!-- snapshots快照版本 -->
		<snapshotRepository>
			<id>nexus-snapshots</id>
			<name>Nexus Snapshot Repository</name>
			<url>http://121.43.104.34:8081/nexus/content/repositories/snapshots/</url>
		</snapshotRepository>
	</distributionManagement>
</project>

application-context.xml

application-context.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:jpa="http://www.springframework.org/schema/data/jpa"
	xmlns:security="http://www.springframework.org/schema/security"
	xsi:schemaLocation="
		http://www.springframework.org/schema/aop 
		http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
		http://www.springframework.org/schema/security 
		http://www.springframework.org/schema/security/spring-security-3.2.xsd
		http://www.springframework.org/schema/beans 
		http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
		http://www.springframework.org/schema/data/jpa 
		http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd
		http://www.springframework.org/schema/tx 
		http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
		http://www.springframework.org/schema/context 
		http://www.springframework.org/schema/context/spring-context-4.0.xsd">
 
	<!-- Activates annotation-based bean configuration -->
	<context:annotation-config />
 
	<!-- Scans for application @Components to deploy -->
	<context:component-scan base-package="com.gxx.sharding" />
 
	<!-- 数据库配置文件位置 -->
	<context:property-placeholder location="classpath:/jdbc.properties" />
 
	<!-- 使用JDBC事务 -->
	<bean id="transactionManager"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="shardingDataSource" />
	</bean>
 
	<!-- AOP配置事物 -->
	<tx:advice id="transactionAdvice" transaction-manager="transactionManager">
		<tx:attributes>
			<tx:method name="query*" read-only="true" propagation="REQUIRED" />
			<tx:method name="delete*"  propagation="REQUIRED" />
			<tx:method name="update*" propagation="REQUIRED" />
			<tx:method name="insert*" propagation="REQUIRED" />
			<tx:method name="*" propagation="REQUIRED" />
		</tx:attributes>
	</tx:advice>
 
	<!-- 配置AOP切面 -->
	<aop:config>
        <aop:pointcut id="transactionPointcut" expression="execution(* com.gxx.sharding.service.*.*(..))"/>
        <aop:advisor pointcut-ref="transactionPointcut" advice-ref="transactionAdvice"/>
    </aop:config>
 
 
	<!-- 使用annotation注解方式配置事务 -->
	<tx:annotation-driven transaction-manager="transactionManager" />
 
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="shardingDataSource" />
		<property name="configLocation" value="classpath:mybatis.xml"></property>
		<property name="mapperLocations" value="classpath:com/gxx/sharding/base/mapping/*.xml"></property>
	</bean>
 
	<!-- 配置SQLSession模板 -->
	<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
		<constructor-arg index="0" ref="sqlSessionFactory" />
	</bean>
 
	<!--扫描basePackage下所有以@Repository注解的接口  -->
	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
        <property name="basePackage" value="com.gxx.sharding"/>
        <property name="annotationClass" value="org.springframework.stereotype.Repository"/>
    </bean>
 
</beans>

application-sharding-jdbc.xml

application-sharding-jdbc.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context" 
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:rdb="http://www.dangdang.com/schema/ddframe/rdb"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
                        http://www.springframework.org/schema/beans/spring-beans.xsd 
                        http://www.springframework.org/schema/tx 
                        http://www.springframework.org/schema/tx/spring-tx.xsd
                        http://www.springframework.org/schema/context 
                        http://www.springframework.org/schema/context/spring-context.xsd
                        http://www.dangdang.com/schema/ddframe/rdb 
                        http://www.dangdang.com/schema/ddframe/rdb/rdb.xsd">
 
	<!-- 配置真实数据源 -->
	<bean id="dataSource_00" class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="driverClassName" value="${jdbc_00.driverClassName}" />
		<property name="url" value="${jdbc_00.url}" />
		<property name="username" value="${jdbc_00.username}" />
		<property name="password" value="${jdbc_00.password}" />
	</bean>
	<bean id="dataSource_01" class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="driverClassName" value="${jdbc_01.driverClassName}" />
		<property name="url" value="${jdbc_01.url}" />
		<property name="username" value="${jdbc_01.username}" />
		<property name="password" value="${jdbc_01.password}" />
	</bean>
 
    <!-- 数据库分片策略 -->
    <rdb:strategy id="databaseShardingStrategy" sharding-columns="user_id" <!-- 分片键 -->
    	algorithm-class="com.gxx.sharding.base.UserSingleKeyDatabaseShardingAlgorithm"/>
 
    <!-- 表分片策略 -->
    <rdb:strategy id="tableShardingStrategy" sharding-columns="user_id" <!-- 分片键 -->
    	algorithm-class="com.gxx.sharding.base.UserSingleKeyTableShardingAlgorithm"/>
 
    <!-- sharding-jdbc数据源 -->
    <rdb:data-source id="shardingDataSource">
        <rdb:sharding-rule data-sources="dataSource_00,dataSource_01"><!-- 真实数据源 -->
            <rdb:table-rules>
                <rdb:table-rule logic-table="user" <!-- 逻辑表 -->
	                actual-tables="user_0,user_1" <!-- 真实表 -->
	                database-strategy="databaseShardingStrategy" <!-- 数据库分片策略 -->
	                table-strategy="tableShardingStrategy"/> <!-- 表分片策略 -->
            </rdb:table-rules>
        </rdb:sharding-rule>
    </rdb:data-source>
</beans>

mybatis.xml

mybatis.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<settings>
		<setting name="cacheEnabled" value="true" />
		<setting name="lazyLoadingEnabled" value="true" />
		<setting name="multipleResultSetsEnabled" value="true" />
		<setting name="useColumnLabel" value="true" />
		<setting name="defaultExecutorType" value="REUSE" />
		<setting name="defaultStatementTimeout" value="25000" />
		<setting name="mapUnderscoreToCamelCase" value="true" />
		<!-- 打印查询语句 -->
        <setting name="logImpl" value="STDOUT_LOGGING" />
	</settings>
</configuration>

web.xml

web.xml

<!DOCTYPE web-app PUBLIC
 "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
 "http://java.sun.com/dtd/web-app_2_3.dtd" >
 
<web-app>
    <display-name>Archetype Created Web Application</display-name>
 
    <!-- Context Configuration locations for Spring XML files -->
    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>
        	classpath*:/application-*.xml,
		</param-value>
    </context-param>
 
    <!-- log4j配置文件 -->
    <context-param>
		<param-name>log4jConfigLocation</param-name>
		<param-value>/WEB-INF/classes/log4j.xml</param-value>
	</context-param>
 
    <filter>
        <filter-name>encodingFilter</filter-name>
        <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
        <init-param>
            <param-name>encoding</param-name>
            <param-value>UTF-8</param-value>
        </init-param>
        <init-param>
            <param-name>forceEncoding</param-name>
            <param-value>true</param-value>
        </init-param>
    </filter>
 
    <filter-mapping>
        <filter-name>encodingFilter</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>
 
    <!-- serlvet listeners -->
    <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>
     <listener>
        <listener-class>org.springframework.web.util.IntrospectorCleanupListener</listener-class>
    </listener>
 
    <servlet>
        <servlet-name>dispatcher</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
       <init-param>
			<param-name>contextConfigLocation</param-name>
			<param-value>classpath*:/spring-mvc.xml</param-value>
		</init-param>
        <load-on-startup>1</load-on-startup>
    </servlet>
 
 	<servlet-mapping>
        <servlet-name>dispatcher</servlet-name>
        <url-pattern>*.htm</url-pattern>
    </servlet-mapping>
 
    <!-- welcome file list config -->
 	<welcome-file-list>
  		<welcome-file>index.jsp</welcome-file>
 	</welcome-file-list>
 
    <jsp-config>
		<jsp-property-group>
			<display-name>JSPConfiguration</display-name>
			<url-pattern>*.jsp</url-pattern>
			<page-encoding>UTF-8</page-encoding>
		</jsp-property-group>
	</jsp-config>
</web-app>

UserSingleKeyDatabaseShardingAlgorithm.java

UserSingleKeyDatabaseShardingAlgorithm.java

package com.gxx.sharding.base;
 
import java.util.Collection;
import java.util.LinkedHashSet;
 
import org.apache.log4j.Logger;
 
import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;
import com.google.common.collect.Range;
 
/**
 * 用户单字段分库算法
 * @author Gxx
 */
public class UserSingleKeyDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> {
 
	/**
	 * 日志处理器
	 */
	private Logger logger = Logger.getLogger(UserSingleKeyDatabaseShardingAlgorithm.class);
 
	/**
	 * sql equal 规则
	 * sharding_00.user_0
	 * sharding_00.user_1
	 * sharding_01.user_0
	 * sharding_01.user_1
	 */
	@Override
	public String doEqualSharding(Collection<String> availableDatabaseNames, ShardingValue<Long> shardingValue) {
		logger.info("用户单字段分库算法:sql equal 规则");
		logger.info("sharding value:" + shardingValue.getValue());
		for (String each : availableDatabaseNames) {
			if (each.endsWith(shardingValue.getValue() % 4 / 2 + "")) {
				logger.info("扫描到库：" + each);
				return each;
			}
		}
		logger.info("没有扫描到任何库");
		throw new IllegalArgumentException();
	}
 
	/**
	 * sql in 规则
	 */
	@Override
	public Collection<String> doInSharding(Collection<String> availableDatabaseNames, ShardingValue<Long> shardingValue) {
		logger.info("用户单字段分库算法:sql in 规则");
		logger.info("sharding values:" + shardingValue.getValues());
		Collection<String> result = new LinkedHashSet<String>(availableDatabaseNames.size());
		for (Long value : shardingValue.getValues()) {
			logger.info("sharding value:" + value);
			for (String each : availableDatabaseNames) {
				if (each.endsWith(value % 4 / 2 + "")) {
					logger.info("扫描到库：" + each);
					result.add(each);
				}
			}
		}
		logger.info("扫描最终结果:" + result);
		return result;
	}
 
	/**
	 * sql between and 规则
	 */
	@Override
	public Collection<String> doBetweenSharding(Collection<String> availableDatabaseNames,
			ShardingValue<Long> shardingValue) {
		logger.info("用户单字段分库算法:sql between and 规则");
		logger.info("sharding value range:" + shardingValue.getValueRange());
		Collection<String> result = new LinkedHashSet<String>(availableDatabaseNames.size());
		Range<Long> range = (Range<Long>) shardingValue.getValueRange();
		for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
			logger.info("sharding value:" + i);
			for (String each : availableDatabaseNames) {
				if (each.endsWith(i % 4 / 2 + "")) {
					logger.info("扫描到库：" + each);
					result.add(each);
				}
			}
		}
		logger.info("扫描最终结果:" + result);
		return result;
	}
}

UserSingleKeyTableShardingAlgorithm.java

UserSingleKeyTableShardingAlgorithm.java

package com.gxx.sharding.base;
 
import java.util.Collection;
import java.util.LinkedHashSet;
 
import org.apache.log4j.Logger;
 
import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;
import com.google.common.collect.Range;
 
/**
 * 用户单字段分表算法
 * @author Gxx
 */
public class UserSingleKeyTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {
 
	/**
	 * 日志处理器
	 */
	private Logger logger = Logger.getLogger(UserSingleKeyTableShardingAlgorithm.class);
 
	/**
	 * sql == 规则
	 * sharding_00.user_0
	 * sharding_00.user_1
	 * sharding_01.user_0
	 * sharding_01.user_1
	 */
	public String doEqualSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) {
		logger.info("用户单字段分表算法:sql equal 规则");
		logger.info("sharding value:" + shardingValue.getValue());
		for (String each : tableNames) {
			if (each.endsWith(shardingValue.getValue() % 2 + "")) {
				logger.info("扫描到表：" + each);
				return each;
			}
		}
		logger.info("没有扫描到任何表");
		throw new IllegalArgumentException();
	}
 
	/**
	 * sql in 规则
	 */
	public Collection<String> doInSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) {
		logger.info("用户单字段分表算法:sql in 规则");
		logger.info("sharding values:" + shardingValue.getValues());
		Collection<String> result = new LinkedHashSet<String>(tableNames.size());
		for (long value : shardingValue.getValues()) {
			logger.info("sharding value:" + value);
			for (String each : tableNames) {
				if (each.endsWith(value % 2 + "")) {
					logger.info("扫描到表：" + each);
					result.add(each);
				}
			}
		}
		logger.info("扫描最终结果:" + result);
		return result;
	}
 
	/**
	 * sql between 规则
	 */
	public Collection<String> doBetweenSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) {
		logger.info("用户单字段分表算法:sql in 规则");
		logger.info("sharding value range:" + shardingValue.getValueRange());
		Collection<String> result = new LinkedHashSet<String>(tableNames.size());
		Range<Long> range = (Range<Long>) shardingValue.getValueRange();
		for (long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
			logger.info("sharding value:" + i);
			for (String each : tableNames) {
				if (each.endsWith(i % 2 + "")) {
					logger.info("扫描到表：" + each);
					result.add(each);
				}
			}
		}
		logger.info("扫描最终结果:" + result);
		return result;
	}
 
}

UserMapper.java

UserMapper.java

package com.gxx.sharding.base.dao;
 
import java.util.List;
 
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
 
import com.gxx.sharding.base.vo.User;
 
@Repository
public interface UserMapper {
    int deleteByPrimaryKey(Long userId);
 
    int insert(User user);
 
    int insertSelective(User user);
 
    User selectByPrimaryKey(Long userId);
 
    int updateByPrimaryKeySelective(User user);
 
    int updateByPrimaryKey(User user);
 
    /**
     * 根据id集合查询用户
     * @param userIdList
     * @return
     */
	List<User> queryUsersInUserId(@Param("userIdList") List<Long> userIdList);
 
	/**
	 * 根据id区间查询用户
	 * @param beginUserId
	 * @param endUserId
	 * @return
	 */
    @Select("SELECT * FROM user WHERE user_id BETWEEN #{beginUserId} AND #{endUserId} ORDER BY USER_ID")
    List<User> queryUsersBetweenUserId(@Param("beginUserId") Long beginUserId, @Param("endUserId") Long endUserId);
 
	/**
	 * 计数
	 * @return
	 */
    @Select("SELECT COUNT(user_id) FROM user")
	long countUser();
 
	/**
	 * 求和
	 * @return
	 */
    @Select("SELECT SUM(user_id) FROM user")
	long sumUser();
 
	/**
	 * 取小
	 * @return
	 */
    @Select("SELECT MIN(user_id) FROM user")
	long minUser();
 
	/**
	 * 取大
	 * @return
	 */
    @Select("SELECT MAX(user_id) FROM user")
	long maxUser();
 
	/**
	 * 平均
	 * @return
	 */
    @Select("SELECT AVG(user_id) FROM user")
	double avgUser();
 
    /**
     * 根据limit区间查询用户
     * @param beginUserId
     * @param num
     * @return
     */
    @Select("SELECT * FROM user ORDER BY user_id LIMIT #{beginUserId}, #{num}")
	List<User> queryUsersLimitUserId(@Param("beginUserId") Long beginUserId, @Param("num") Long num);
}

UserMapper.xml

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.gxx.sharding.base.dao.UserMapper" >
  <resultMap id="BaseResultMap" type="com.gxx.sharding.base.vo.User" >
    <id column="user_id" property="userId" jdbcType="BIGINT" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="password" property="password" jdbcType="VARCHAR" />
    <result column="create_date" property="createDate" jdbcType="VARCHAR" />
    <result column="create_time" property="createTime" jdbcType="VARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    user_id, name, password, create_date, create_time
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
    select 
    <include refid="Base_Column_List" />
    from user
    where user_id = #{userId,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
    delete from user
    where user_id = #{userId,jdbcType=BIGINT}
  </delete>
  <insert id="insert" parameterType="com.gxx.sharding.base.vo.User" >
    insert into user (user_id, name, password, 
      create_date, create_time)
    values (#{userId,jdbcType=BIGINT}, #{name,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, 
      #{createDate,jdbcType=VARCHAR}, #{createTime,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.gxx.sharding.base.vo.User" >
    insert into user
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="userId != null" >
        user_id,
      </if>
      <if test="name != null" >
        name,
      </if>
      <if test="password != null" >
        password,
      </if>
      <if test="createDate != null" >
        create_date,
      </if>
      <if test="createTime != null" >
        create_time,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="userId != null" >
        #{userId,jdbcType=BIGINT},
      </if>
      <if test="name != null" >
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="password != null" >
        #{password,jdbcType=VARCHAR},
      </if>
      <if test="createDate != null" >
        #{createDate,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null" >
        #{createTime,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.gxx.sharding.base.vo.User" >
    update user
    <set >
      <if test="name != null" >
        name = #{name,jdbcType=VARCHAR},
      </if>
      <if test="password != null" >
        password = #{password,jdbcType=VARCHAR},
      </if>
      <if test="createDate != null" >
        create_date = #{createDate,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null" >
        create_time = #{createTime,jdbcType=VARCHAR},
      </if>
    </set>
    where user_id = #{userId,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.gxx.sharding.base.vo.User" >
    update user
    set name = #{name,jdbcType=VARCHAR},
      password = #{password,jdbcType=VARCHAR},
      create_date = #{createDate,jdbcType=VARCHAR},
      create_time = #{createTime,jdbcType=VARCHAR}
    where user_id = #{userId,jdbcType=BIGINT}
  </update>
 
  <select id="queryUsersInUserId" resultType="com.gxx.sharding.base.vo.User">
    SELECT * FROM user
    <if test="userIdList !=null and userIdList.size != 0">
	  WHERE user_id IN 
	  <foreach item="temp" index="index" collection="userIdList"
		  open="(" separator="," close=")">
	    #{temp}
	  </foreach>
    </if>
    ORDER BY USER_ID
  </select>
 
</mapper>

User.java

User.java

package com.gxx.sharding.base.vo;
 
public class User {
    private Long userId;
 
    private String name;
 
    private String password;
 
    private String createDate;
 
    private String createTime;
 
    public Long getUserId() {
        return userId;
    }
 
    public void setUserId(Long userId) {
        this.userId = userId;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name == null ? null : name.trim();
    }
 
    public String getPassword() {
        return password;
    }
 
    public void setPassword(String password) {
        this.password = password == null ? null : password.trim();
    }
 
    public String getCreateDate() {
        return createDate;
    }
 
    public void setCreateDate(String createDate) {
        this.createDate = createDate == null ? null : createDate.trim();
    }
 
    public String getCreateTime() {
        return createTime;
    }
 
    public void setCreateTime(String createTime) {
        this.createTime = createTime == null ? null : createTime.trim();
    }
 
	@Override
	public String toString() {
		return "User [userId=" + userId + ", name=" + name + ", password=" + password + ", createDate=" + createDate
				+ ", createTime=" + createTime + "]";
	}
}

UserController.java

UserController.java

package com.gxx.sharding.web.user;
 
import java.util.ArrayList;
import java.util.List;
 
import javax.servlet.http.HttpServletRequest;
 
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
 
import com.gxx.sharding.base.utils.DateUtils;
import com.gxx.sharding.base.vo.User;
import com.gxx.sharding.dto.UserDto;
import com.gxx.sharding.service.UserService;
 
/**
 * UserController
 * @author gxx
 */
@Controller
@RequestMapping("/user/")
public class UserController {
	/**
	 * 日志处理器
	 */
	private final Logger logger = Logger.getLogger(UserController.class);
 
	@Autowired
	private UserService userService;
 
	@RequestMapping(value = "/preRegistFtl", method = RequestMethod.GET)
	public String preRegistFtl() {
		return "user/preRegistFtl";
	}
 
	@RequestMapping(value = "/preRegistJsp", method = RequestMethod.GET)
	public String preRegistJsp() {
		return "user/preRegistJsp";
	}
 
	@RequestMapping(value = "/preQueryFtl", method = RequestMethod.GET)
	public String preQueryFtl() {
		return "user/preQueryFtl";
	}
 
	/**
	 * 注册
	 * @param request
	 * @param userDto
	 * @return
	 */
	@RequestMapping(value = "/registJsp",produces="application/json")
	public @ResponseBody UserDto registJsp(HttpServletRequest request, UserDto userDto) {
		logger.info("用户注册：姓名[" + userDto.getName() + "]，密码[" + userDto.getPassword() + "]");
		/**
		 * 1.判用户名是否存在
		 */
		User user = null;//userService.getUserByName(userDto.getName());
		if(user != null){
			userDto.setSuccess(Boolean.FALSE.booleanValue());
			userDto.setMessage("用户名[" + userDto.getName() + "]已存在!");
			return userDto;
		}
		/**
		 * 2.创建用户对象 并 新增用户
		 */
		user = new User();
		user.setUserId(userDto.getId().longValue());
		user.setName(userDto.getName());
		user.setPassword(userDto.getPassword());
		user.setCreateDate("20150618");
		user.setCreateTime("000000");
		userService.doSaveUser(user);
		/**
		 * 3.返回结果
		 */
		userDto.setSuccess(Boolean.TRUE.booleanValue());
		userDto.setMessage("注册成功!");
		return userDto;
	}
 
	/**
	 * 注册
	 * @param request
	 * @param userDto
	 * @return
	 */
	@RequestMapping(value = "/registFtl")
	public String registFtl(HttpServletRequest request, UserDto userDto) {
		logger.info("用户注册：姓名[" + userDto.getName() + "]，密码[" + userDto.getPassword() + "]");
		/**
		 * 1.判用户名是否存在
		 */
		User user = null;//userService.getUserByName(userDto.getName());
		if(user != null){
			userDto.setSuccess(Boolean.FALSE.booleanValue());
			userDto.setMessage("用户名[" + userDto.getName() + "]已存在!");
			return "user/result";
		}
		/**
		 * 2.创建用户对象 并 新增用户
		 */
		user = new User();
		user.setUserId(userDto.getId().longValue());
		user.setName(userDto.getName());
		user.setPassword(userDto.getPassword());
		user.setCreateDate("20150618");
		user.setCreateTime("000000");
		userService.doSaveUser(user);
		/**
		 * 3.返回结果
		 */
		userDto.setSuccess(Boolean.TRUE.booleanValue());
		userDto.setMessage("注册成功!");
		return "user/result";
	}
 
	/**
	 * 查询
	 * @param request
	 * @param userDto
	 * @return
	 */
	@RequestMapping(value = "/queryFtl")
	public String queryFtl(HttpServletRequest request, UserDto userDto) {
		logger.info("用户查询：id值[" + userDto.getIdValue() + "]，查询类型[" + userDto.getType() + "]");
 
		String message = "查询成功！";
 
		/**
		 * 1.根据查询类型区分
		 */
		if("equal".equals(userDto.getType())) {
			User user = userService.getUserByUserId(Long.parseLong(userDto.getIdValue()));
			logger.info("根据id[" + userDto.getIdValue() + "]查询用户:" + user);
			message += user;
		} else if("update".equals(userDto.getType())) {
			User user = userService.getUserByUserId(Long.parseLong(userDto.getIdValue()));
			user.setCreateDate(DateUtils.getCurrentDate());
			user.setCreateTime(DateUtils.getCurrentTime());
			userService.doUpdateUser(user);
			logger.info("根据id[" + userDto.getIdValue() + "]修改用户:" + user);
			message += user;
		} else if("delete".equals(userDto.getType())) {
			User user = userService.getUserByUserId(Long.parseLong(userDto.getIdValue()));
			userService.doDeleteUser(user);
			logger.info("根据id[" + userDto.getIdValue() + "]删除用户:" + user);
			message += user;
		} else if("in".equals(userDto.getType())) {
			String[] idArray = userDto.getIdValue().split(",");
			List<Long> userIdList = new ArrayList<Long>();
			for(String idString : idArray) {
				userIdList.add(Long.parseLong(idString));
			}
			List<User> userList = userService.queryUsersInUserId(userIdList);
			logger.info("根据id集合[" + userDto.getIdValue() + "]查询用户:" + userList.size());
			for(int i=0;i<userList.size();i++) {
				if(i > 0) {
					message += ",";
				}
				message += userList.get(i);
			}
		} else if("between".equals(userDto.getType())) {
			String[] idArray = userDto.getIdValue().split(",");
			Long beginUserId = Long.parseLong(idArray[0]);
			Long endUserId = Long.parseLong(idArray[1]);
			List<User> userList = userService.queryUsersBetweenUserId(beginUserId, endUserId);
			logger.info("根据id区间[" + userDto.getIdValue() + "]查询用户:" + userList.size());
			for(int i=0;i<userList.size();i++) {
				if(i > 0) {
					message += ",";
				}
				message += userList.get(i);
			}
		} else if("all".equals(userDto.getType())) {
			List<User> userList = userService.queryUsersInUserId(null);
			logger.info("查询所有用户:" + userList.size());
			for(int i=0;i<userList.size();i++) {
				if(i > 0) {
					message += ",";
				}
				message += userList.get(i);
			}
		} else if("count".equals(userDto.getType())) {
			long result = userService.countUser();
			logger.info("查询count:" + result);
			message += "count=[" + result + "]";
		} else if("sum".equals(userDto.getType())) {
			long result = userService.sumUser();
			logger.info("查询sum:" + result);
			message += "sum=[" + result + "]";
		} else if("min".equals(userDto.getType())) {
			long result = userService.minUser();
			logger.info("查询min:" + result);
			message += "min=[" + result + "]";
		} else if("max".equals(userDto.getType())) {
			long result = userService.maxUser();
			logger.info("查询max:" + result);
			message += "max=[" + result + "]";
		} else if("avg".equals(userDto.getType())) {
			double result = userService.avgUser();
			logger.info("查询avg:" + result);
			message += "avg=[" + result + "]";
		} else if("limit".equals(userDto.getType())) {
			String[] idArray = userDto.getIdValue().split(",");
			Long beginUserId = Long.parseLong(idArray[0]);
			Long count = Long.parseLong(idArray[1]);
			List<User> userList = userService.queryUsersLimitUserId(beginUserId, count);
			logger.info("根据limit区间[" + userDto.getIdValue() + "]查询用户:" + userList.size());
			for(int i=0;i<userList.size();i++) {
				if(i > 0) {
					message += ",";
				}
				message += userList.get(i);
			}
		} else {
			/**
			 * 2.查询类型不存在
			 */
			userDto.setSuccess(Boolean.FALSE.booleanValue());
			userDto.setMessage("查询类型不存在!");
			return "user/result";
		}
 
		/**
		 * 3.返回结果
		 */
		userDto.setSuccess(Boolean.TRUE.booleanValue());
		userDto.setMessage(message);
		return "user/result";
	}
}

preRegistFtl.ftl

preRegistFtl.ftl

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
		<title>注册页面</title>
	</head>
	<body>
		<form action="/sharding/user/registFtl.htm" method="post">
			<table border="1">
				<tr><td>ID：</td><td><input type="text" name="id"/></td></tr>
				<tr><td>用户名：</td><td><input type="text" name="name"/></td></tr>
				<tr><td>密码：</td><td><input type="password" name="password"/></td></tr>
				<tr><td colspan="2" align="center"><input type="submit" value="提交"/></td></tr>
			</table>
		</form>
	</body>
</html>

preQueryFtl.ftl

preQueryFtl.ftl

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
		<title>查询页面</title>
	</head>
	<body>
		<form action="/sharding/user/queryFtl.htm" method="post">
			<table border="1">
				<tr>
					<td>查询类型：</td>
					<td>
						<select name="type">
							<option value='equal'>equal</option>
							<option value='update'>update</option>
							<option value='delete'>delete</option>
							<option value='in'>in</option>
							<option value='between'>between and</option>
							<option value='all'>all</option>
							<option value='count'>count</option>
							<option value='sum'>sum</option>
							<option value='min'>min</option>
							<option value='max'>max</option>
							<option value='avg'>avg</option>
							<option value='limit'>limit</option>
						</select>
					</td>
				</tr>
				<tr><td>ID值(可以逗号分隔)：</td><td><input type="text" name="idValue"/></td></tr>
				<tr><td colspan="2" align="center"><input type="submit" value="提交"/></td></tr>
			</table>
		</form>
	</body>
</html>

result.ftl

result.ftl

<html>
<body>
	<p>This is my result:<br> ${userDto.success?string('true','false')}</p>  
	<p>This is my message:<br> ${userDto.message}</p>  
</body>
</html>

打包下载

sharding-gxx.zip

库+表

2个库，4张表

sharding_00.user_0;
sharding_01.user_0;
sharding_00.user_1;
sharding_01.user_1;

初始化sql

init_sharding_jdbc.sql

CREATE DATABASE sharding_00;
CREATE DATABASE sharding_01;
 
USE sharding_00;
CREATE TABLE `user_0` (
  `user_id` BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` VARCHAR(50) NOT NULL COMMENT '姓名',
  `password` VARCHAR(32) NOT NULL COMMENT '密码',
  `create_date` VARCHAR(8) NOT NULL COMMENT '创建日期',
  `create_time` VARCHAR(6) NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='用户表_0';
CREATE TABLE `user_1` (
  `user_id` BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` VARCHAR(50) NOT NULL COMMENT '姓名',
  `password` VARCHAR(32) NOT NULL COMMENT '密码',
  `create_date` VARCHAR(8) NOT NULL COMMENT '创建日期',
  `create_time` VARCHAR(6) NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='用户表_1';
 
USE sharding_01;
CREATE TABLE `user_0` (
  `user_id` BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` VARCHAR(50) NOT NULL COMMENT '姓名',
  `password` VARCHAR(32) NOT NULL COMMENT '密码',
  `create_date` VARCHAR(8) NOT NULL COMMENT '创建日期',
  `create_time` VARCHAR(6) NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='用户表_0';
CREATE TABLE `user_1` (
  `user_id` BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` VARCHAR(50) NOT NULL COMMENT '姓名',
  `password` VARCHAR(32) NOT NULL COMMENT '密码',
  `create_date` VARCHAR(8) NOT NULL COMMENT '创建日期',
  `create_time` VARCHAR(6) NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='用户表_1';

分片键

user表的分片键：user_id

注意：分片键user_id，sql里必须是小写的，写成USER_ID，会造成：sharding-jdbc找不到分片键，那会所有的分库+分表都会执行一遍！

分片算法

分库算法：user_id % 4 / 2 → sharding_00,sharding_01

分表算法：user_id % 2 → user_0, user_1

比如：

user_id	算库	库	算表	表
1	1%4/2=0	sharding_00	1%2=1	user_1
2	2%4/2=1	sharding_01	2%2=0	user_0
3	3%4/2=1	sharding_01	3%2=1	user_1
4	4%4/2=0	sharding_00	4%2=0	user_0
5	5%4/2=0	sharding_00	5%2=1	user_1

测试场景

注意：insert场景访问该地址：http://localhost:8080/sharding/user/preRegistFtl.htm

其他场景访问该地址，类型选择下拉框：http://localhost:8080/sharding/user/preQueryFtl.htm

insert场景

#代码中逻辑SQL
INSERT INTO USER (user_id, name, password, create_date, create_time) VALUES (15, '关向辉', '123456', '20150618', '000000');
->
#sharding-jdbc解析SQL
INSERT INTO sharding_01.user_1 (user_id, name, password, create_date, create_time) VALUES (15, '关向辉', '123456', '20150618', '000000');

select场景

#代码中逻辑SQL
SELECT user_id, name, password, create_date, create_time FROM USER WHERE user_id = 12;
->
#sharding-jdbc解析SQL
SELECT user_id, name, password, create_date, create_time FROM sharding_00.user_0 WHERE user_id = 12;

update场景

#代码中逻辑SQL
UPDATE USER SET name = 'gxx9', password = 'qweqwe', create_date = '20170912', create_time = '161409' WHERE user_id = 9;
->
#sharding-jdbc解析SQL
UPDATE sharding_00.user_1 SET name = 'gxx9', password = 'qweqwe', create_date = '20170912', create_time = '161409' WHERE user_id = 9;

delete场景

#代码中逻辑SQL
DELETE FROM USER WHERE user_id = 8;
->
#sharding-jdbc解析SQL
DELETE FROM sharding_00.user_0 WHERE user_id = 8;

in场景

#代码中逻辑SQL
SELECT * FROM USER WHERE user_id IN ( 3 , 6 ) ORDER BY USER_ID;
->
#sharding-jdbc解析SQL
SELECT * FROM sharding_01.user_1 WHERE user_id IN (3, 6) ORDER BY USER_ID;
SELECT * FROM sharding_01.user_0 WHERE user_id IN (3, 6) ORDER BY USER_ID;

in场景

#代码中逻辑SQL
SELECT * FROM USER WHERE user_id IN ( 3 , 4 ) ORDER BY USER_ID;
->
#sharding-jdbc解析SQL
SELECT * FROM sharding_00.user_1 WHERE user_id IN (3, 4) ORDER BY USER_ID;
SELECT * FROM sharding_00.user_0 WHERE user_id IN (3, 4) ORDER BY USER_ID;
SELECT * FROM sharding_01.user_0 WHERE user_id IN (3, 4) ORDER BY USER_ID;
SELECT * FROM sharding_01.user_1 WHERE user_id IN (3, 4) ORDER BY USER_ID;

between场景

#代码中逻辑SQL
SELECT * FROM USER WHERE user_id BETWEEN 2 AND 3 ORDER BY USER_ID;
->
#sharding-jdbc解析SQL
SELECT * FROM sharding_01.user_0 WHERE user_id BETWEEN 2 AND 3 ORDER BY USER_ID;
SELECT * FROM sharding_01.user_1 WHERE user_id BETWEEN 2 AND 3 ORDER BY USER_ID;

between场景

#代码中逻辑SQL
SELECT * FROM USER WHERE user_id BETWEEN 3 AND 4 ORDER BY USER_ID;
->
#sharding-jdbc解析SQL
SELECT * FROM sharding_00.user_0 WHERE user_id BETWEEN 3 AND 4 ORDER BY USER_ID;
SELECT * FROM sharding_00.user_1 WHERE user_id BETWEEN 3 AND 4 ORDER BY USER_ID;
SELECT * FROM sharding_01.user_1 WHERE user_id BETWEEN 3 AND 4 ORDER BY USER_ID;
SELECT * FROM sharding_01.user_0 WHERE user_id BETWEEN 3 AND 4 ORDER BY USER_ID;

无分片键场景

#代码中逻辑SQL
SELECT * FROM USER ORDER BY USER_ID;
->
#sharding-jdbc解析SQL
SELECT * FROM sharding_00.user_0 ORDER BY USER_ID;
SELECT * FROM sharding_00.user_1 ORDER BY USER_ID;
SELECT * FROM sharding_01.user_0 ORDER BY USER_ID;
SELECT * FROM sharding_01.user_1 ORDER BY USER_ID;

count场景

#代码中逻辑SQL
SELECT COUNT(user_id) FROM USER;
->
#sharding-jdbc解析SQL
SELECT COUNT(user_id) FROM sharding_00.user_0;
SELECT COUNT(user_id) FROM sharding_00.user_1;
SELECT COUNT(user_id) FROM sharding_01.user_1;
SELECT COUNT(user_id) FROM sharding_01.user_0;

sum场景

#代码中逻辑SQL
SELECT SUM(user_id) FROM USER;
->
#sharding-jdbc解析SQL
SELECT SUM(user_id) FROM sharding_00.user_0;
SELECT SUM(user_id) FROM sharding_00.user_1;
SELECT SUM(user_id) FROM sharding_01.user_0;
SELECT SUM(user_id) FROM sharding_01.user_1;

min场景

#代码中逻辑SQL
SELECT MIN(user_id) FROM USER;
->
#sharding-jdbc解析SQL
SELECT MIN(user_id) FROM sharding_00.user_0;
SELECT MIN(user_id) FROM sharding_00.user_1;
SELECT MIN(user_id) FROM sharding_01.user_0;
SELECT MIN(user_id) FROM sharding_01.user_1;

max场景

#代码中逻辑SQL
SELECT MAX(user_id) FROM USER;
->
#sharding-jdbc解析SQL
SELECT MAX(user_id) FROM sharding_00.user_0;
SELECT MAX(user_id) FROM sharding_00.user_0;
SELECT MAX(user_id) FROM sharding_01.user_1;
SELECT MAX(user_id) FROM sharding_01.user_1;

avg场景

#代码中逻辑SQL
SELECT AVG(user_id) FROM USER; 
->
#sharding-jdbc解析SQL
SELECT AVG(user_id), COUNT(user_id) AS sharding_gen_1, SUM(user_id) AS sharding_gen_2 FROM sharding_00.user_0;
SELECT AVG(user_id), COUNT(user_id) AS sharding_gen_1, SUM(user_id) AS sharding_gen_2 FROM sharding_00.user_1;
SELECT AVG(user_id), COUNT(user_id) AS sharding_gen_1, SUM(user_id) AS sharding_gen_2 FROM sharding_01.user_0;
SELECT AVG(user_id), COUNT(user_id) AS sharding_gen_1, SUM(user_id) AS sharding_gen_2 FROM sharding_01.user_1;

limit场景

#代码中逻辑SQL
SELECT * FROM USER ORDER BY user_id LIMIT 4, 2; 
->
#sharding-jdbc解析SQL
SELECT * FROM sharding_00.user_0 ORDER BY user_id LIMIT 0, 6;
SELECT * FROM sharding_00.user_0 ORDER BY user_id LIMIT 0, 6;
SELECT * FROM sharding_01.user_1 ORDER BY user_id LIMIT 0, 6;
SELECT * FROM sharding_01.user_1 ORDER BY user_id LIMIT 0, 6;

目录

sharding-jdbc示例项目

简介

官方介绍

官方链接

示例项目

pom.xml

application-context.xml

application-sharding-jdbc.xml

mybatis.xml

web.xml

UserSingleKeyDatabaseShardingAlgorithm.java

UserSingleKeyTableShardingAlgorithm.java

UserMapper.java

UserMapper.xml

User.java

UserController.java

preRegistFtl.ftl

preQueryFtl.ftl

result.ftl

打包下载

库+表

初始化sql

分片键

分片算法

测试场景

insert场景

select场景

update场景

delete场景

in场景

in场景

between场景

between场景

无分片键场景

count场景

sum场景

min场景

max场景

avg场景

limit场景