基于sharding-jdbc 之spring+mybatis+sharding-jdbc整合
官方介绍文档： http://dangdangdotcom.github.io/sharding-jdbc/00-overview/
本文参考： http://blog.csdn.net/clypm/article/details/54378523
0.环境
ip :192.168.1.121
端口号：3306数据库1：sharding_0
数据库2：sharding_1
1.创建多个分库
create database sharding_0; create database sharding_1;
2.在各个分库上，创建多张分表
SET FOREIGN_KEY_CHECKS=0; -- 逻辑表 t_user -- 分表：t_user_0 DROP TABLE IF EXISTS `t_user_0`; CREATE TABLE `t_user_0` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 分表：t_user_1 DROP TABLE IF EXISTS `t_user_1`; CREATE TABLE `t_user_1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 分表： t_user_2 DROP TABLE IF EXISTS `t_user_2`; CREATE TABLE `t_user_2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `t_student_0`; CREATE TABLE `t_student_0` ( `id` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `t_student_1`; CREATE TABLE `t_student_1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 学生表：测试事务时使用 DROP TABLE IF EXISTS `t_student_0`; CREATE TABLE `t_student_0` ( `id` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `t_student_1`; CREATE TABLE `t_student_1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3.案例：sharding-jdbc-mybatis-demo
3.1 组件
需要jar包：见 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/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.eshop</groupId>
<artifactId>sharding-jdbc-mybatis-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<spring.version>3.2.5.RELEASE</spring.version>
<mybatis.version>3.2.4</mybatis.version>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.10</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</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-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-test</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring.version}</version>
</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>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>3.2.4.RELEASE</version>
</dependency>
<!-- 分库分表使用的Dangdang 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.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.28</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>2.5</version>
</dependency>
</dependencies>
</project>
错误分析：
<!-- sharding-jdbc-core 现在用这个最新版本1.4.2会报错，暂时用1.0.0，待研究-->
Unsatisfied dependency expressed through constructor argument with index 1 of type [boolean]: Could not convert constructor argument value of type [java.util.ArrayList] to required type [boolean]
问题解决：在rdb的配置方法里，就可以使用，而且分布式主键的包需要1.4.2版本
3.2 主配置文件applicationContext.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" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <context:annotation-config /> <context:component-scan base-package="com.eshop.sharding.jdbc.*" /> <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="location" value="classpath:bp.properties" /> </bean> <import resource="spring-database.xml" /> <import resource="spring-sharding.xml" /> </beans>
3.3 配置文件spring-database.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:mybatis-spring="http://mybatis.org/schema/mybatis-spring"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd">
<!-- 基础数据源 -->
<bean id="abstractDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<property name="driverClassName" value="${jdbc_driver_0}" />
<!-- 初始化连接大小 -->
<property name="initialSize" value="0" />
<!-- 连接池最大使用连接数量 -->
<property name="maxActive" value="20" />
<!-- 连接池最小空闲 -->
<property name="minIdle" value="0" />
<!-- 获取连接最大等待时间 -->
<property name="maxWait" value="60000" />
<property name="validationQuery" value="${validationQuery}" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<property name="testWhileIdle" value="true" />
<!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="25200000" />
<!-- 打开removeAbandoned功能 -->
<property name="removeAbandoned" value="true" />
<!-- 1800秒，也就是30分钟 -->
<property name="removeAbandonedTimeout" value="1800" />
<!-- 关闭abanded连接时输出错误日志 -->
<property name="logAbandoned" value="true" />
<property name="filters" value="stat" />
</bean>
<bean id="sharding_0" class="com.alibaba.druid.pool.DruidDataSource" parent="abstractDataSource">
<property name="url" value="${jdbc_url0}" />
<property name="username" value="${jdbc_username0}" />
<property name="password" value="${jdbc_password0}" />
</bean>
<bean id="sharding_1" class="com.alibaba.druid.pool.DruidDataSource" parent="abstractDataSource">
<property name="url" value="${jdbc_url1}" />
<property name="username" value="${jdbc_username1}" />
<property name="password" value="${jdbc_password1}" />
</bean>
</beans>
3.4 配置文件spring-sharding.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:mybatis-spring="http://mybatis.org/schema/mybatis-spring"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd">
<bean id="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.eshop.sharding.jdbc.dao" />
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="shardingDataSource"/>
<property name="mapperLocations" value="classpath*:mappings/*Mapper.xml"/>
</bean>
<!-- 读写分离
<rdb:master-slave-data-source id="dataSource_0" master-data-source-ref="sharding_0" slave-data-sources-ref="sharding_2 "/>-->
<bean id="shardingDataSource" class="com.dangdang.ddframe.rdb.sharding.api.ShardingDataSource">
<constructor-arg ref="shardingRule"/>
</bean>
<!-- 构成分库分表的规则 传入数据源集合和每个表的分库分表的具体规则 -->
<bean id="shardingRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule">
<constructor-arg index="0" ref="dataSourceRule"/>
<constructor-arg index="1">
<list>
<ref bean="userTableRule"/>
<ref bean="studentTableRule"/>
</list>
</constructor-arg>
</bean>
<!-- 配置好dataSourceRulue,即对数据源进行管理 -->
<bean id="dataSourceRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule">
<constructor-arg>
<map>
<entry key="sharding_0" value-ref="sharding_0"/>
<entry key="sharding_1" value-ref="sharding_1"/>
</map>
</constructor-arg>
</bean>
<!-- t_user表的分库分表配置 -->
<bean id="userTableRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.TableRule">
<constructor-arg value="t_user" index="0"/> <!-- 逻辑表名 -->
<constructor-arg index="1"> <!-- 物理表列表 -->
<list>
<value>t_user_0</value>
<value>t_user_1</value>
<value>t_user_2</value>
</list>
</constructor-arg>
<constructor-arg index="2" ref="dataSourceRule"/> <!-- 数据源管理 -->
<constructor-arg index="3" ref="userDatabaseShardingStrategy"/> <!-- 分库策略 -->
<constructor-arg index="4" ref="userTableShardingStrategy"/> <!-- 分表策略 -->
</bean>
<!-- t_user分库策略 -->
<bean id="userDatabaseShardingStrategy" class="com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy">
<constructor-arg index="0" value="user_id"/> <!-- 分库字段 -->
<constructor-arg index="1"> <!-- 分库策略 -->
<bean class="com.eshop.sharding.jdbc.algorithm.UserSingleKeyDatabaseShardingAlgorithm" />
</constructor-arg>
</bean>
<!-- t_user 分表策略 -->
<bean id="userTableShardingStrategy" class="com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy">
<constructor-arg index="0" value="user_id"/><!-- 分表字段 -->
<constructor-arg index="1"><!-- 分表策略 -->
<bean class="com.eshop.sharding.jdbc.algorithm.UserSingleKeyTableShardingAlgorithm" />
</constructor-arg>
</bean>
<!-- 事务 -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="shardingDataSource" />
</bean>
<tx:annotation-driven transaction-manager="transactionManager" />
</beans>
3.5 log.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN" "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<!-- [控制台STDOUT] -->
<appender name="console" class="org.apache.log4j.ConsoleAppender">
<param name="encoding" value="GBK" />
<param name="target" value="System.out" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%-5p %c{2} - %m%n" />
</layout>
</appender>
<!-- [公共Appender] -->
<appender name="DEFAULT-APPENDER" class="org.apache.log4j.DailyRollingFileAppender">
<param name="File" value="${webapp.root}/logs/common-default.log" />
<param name="Append" value="true" />
<param name="encoding" value="GBK" />
<param name="DatePattern" value="'.'yyyy-MM-dd'.log'" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d %-5p %c{2} - %m%n" />
</layout>
</appender>
<!-- [错误日志APPENDER] -->
<appender name="ERROR-APPENDER" class="org.apache.log4j.DailyRollingFileAppender">
<param name="File" value="${webapp.root}/logs/common-error.log" />
<param name="Append" value="true" />
<param name="encoding" value="GBK" />
<param name="threshold" value="error" />
<param name="DatePattern" value="'.'yyyy-MM-dd'.log'" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d %-5p %c{2} - %m%n" />
</layout>
</appender>
<!-- [组件日志APPENDER] -->
<appender name="COMPONENT-APPENDER"
class="org.apache.log4j.DailyRollingFileAppender">
<param name="File" value="${webapp.root}/logs/logistics-component.log" />
<param name="Append" value="true" />
<param name="encoding" value="GBK" />
<param name="DatePattern" value="'.'yyyy-MM-dd'.log'" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d %-5p %c{2} - %m%n" />
</layout>
</appender>
<!-- [组件日志] -->
<logger name="LOGISTICS-COMPONENT">
<level value="${loggingLevel}" />
<appender-ref ref="COMPONENT-APPENDER" />
<appender-ref ref="ERROR-APPENDER" />
</logger>
<!-- Root Logger -->
<root>
<level value="${rootLevel}"></level>
<appender-ref ref="DEFAULT-APPENDER" />
<appender-ref ref="ERROR-APPENDER" />
<appender-ref ref="console" />
<appender-ref ref="COMPONENT-APPENDER" />
</root>
</log4j:configuration>
4.相关的类
4.1ShardingTmInterceptor
在跨库查询时，必须加这个类实现HandlerInterceptorAdapter，否则报错
package com.eshop.sharding.jdbc.aop;
import java.util.Date;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.AbstractPlatformTransactionManager;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.handler.HandlerInterceptorAdapter;
public class ShardingTmInterceptor extends HandlerInterceptorAdapter {
@Override
public void afterCompletion(HttpServletRequest request,
HttpServletResponse response, Object handler, Exception ex)
throws Exception {
// TODO Auto-generated method stub
super.afterCompletion(request, response, handler, ex);
}
@Override
public void afterConcurrentHandlingStarted(HttpServletRequest request,
HttpServletResponse response, Object handler) throws Exception {
// TODO Auto-generated method stub
super.afterConcurrentHandlingStarted(request, response, handler);
}
@Override
public void postHandle(HttpServletRequest request,
HttpServletResponse response, Object handler,
ModelAndView modelAndView) throws Exception {
// TODO Auto-generated method stub
super.postHandle(request, response, handler, modelAndView);
}
@Override
public boolean preHandle(HttpServletRequest request,
HttpServletResponse response, Object handler) throws Exception {
AbstractApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
//获取分库分表数据源
DataSource shardingDataSource = (DataSource) ctx.getBean("shardingDataSource");
//创建jdbcTemplate
final JdbcTemplate jdbcTemplate = new JdbcTemplate(shardingDataSource);
//获取事务管理器
AbstractPlatformTransactionManager transactionManager = (AbstractPlatformTransactionManager) ctx.getBean("transactionManager");
//创建事务模板
TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);
//执行SQL(product是逻辑表名，id是分库分表键)
transactionTemplate.execute(new TransactionCallbackWithoutResult() {
@Override
protected void doInTransactionWithoutResult(TransactionStatus arg0) {
//HintManager hintManager = HintManager.getInstance();
//hintManager.setMasterRouteOnly();
String sql = "insert into product(id,title,last_modified) values(?,?,?)";
jdbcTemplate.update(sql,1L,"title",new Date());
//jdbcTemplate.queryForList("select id,title from product where id = ?",1L);
}
});
return super.preHandle(request, response, handler);
}
}
4.2 持久层接口类
UserDao
package com.eshop.sharding.jdbc.dao;
import java.util.List;
import com.eshop.sharding.jdbc.domain.User;
public interface UserDao {
Integer insert(User u);
List<User> findAll();
List<User> findByUserIds(List<Integer> userIds);
}
对应的Mapper文件UserDao.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.eshop.sharding.jdbc.dao.UserDao" > <!-- namespace必须写成对应的接口类 -> <resultMap id="resultMap" type="com.eshop.sharding.jdbc.domain.User" > <id column="id" property="id" jdbcType="INTEGER" /> <result column="user_id" property="userId" jdbcType="INTEGER" /> <result column="name" property="name" jdbcType="VARCHAR" /> <result column="age" property="age" jdbcType="INTEGER" /> </resultMap> <sql id="columnsName"> id,user_id,name,age </sql> <insert id="insert"> insert into t_user (user_id,name,age) values (#{userId},#{name},#{age}) </insert> <select id="findAll" resultMap="resultMap"> select <include refid="columnsName"/> from t_user </select> <select id="findByUserIds" resultMap="resultMap"> select <include refid="columnsName"/> from t_user where user_id in ( <foreach collection="list" item="item" separator=","> #{item} </foreach> ) </select> </mapper>
4.3 分库/分表策略类
UserSingleKeyDatabaseShardingAlgorithm.java
package com.eshop.sharding.jdbc.algorithm;
import java.util.Collection;
import java.util.LinkedHashSet;
import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;
import com.google.common.collect.Range;
/**
* USER表分库的逻辑函数
* @author lyncc
*
*/
public class UserSingleKeyDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Integer>{
/**
* sql 中关键字 匹配符为 =的时候，表的路由函数
*/
public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {
for (String each : availableTargetNames) {
if (each.endsWith(shardingValue.getValue() % 2 + "")) {
return each;
}
}
throw new IllegalArgumentException();
}
/**
* sql 中关键字 匹配符为 in 的时候，表的路由函数
*/
public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {
Collection<String> result = new LinkedHashSet<String>(availableTargetNames.size());
for (Integer value : shardingValue.getValues()) {
for (String tableName : availableTargetNames) {
if (tableName.endsWith(value % 2 + "")) {
result.add(tableName);
}
}
}
return result;
}
/**
* sql 中关键字 匹配符为 between的时候，表的路由函数
*/
public Collection<String> doBetweenSharding(Collection<String> availableTargetNames,
ShardingValue<Integer> shardingValue) {
Collection<String> result = new LinkedHashSet<String>(availableTargetNames.size());
Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();
for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
for (String each : availableTargetNames) {
if (each.endsWith(i % 2 + "")) {
result.add(each);
}
}
}
return result;
}
}
UserSingleKeyTableShardingAlgorithm.java
package com.eshop.sharding.jdbc.algorithm;
import java.util.Collection;
import java.util.LinkedHashSet;
import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;
import com.google.common.collect.Range;
public class UserSingleKeyTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer>{
/**
* sql 中 = 操作时，table的映射
*/
public String doEqualSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) {
for (String each : tableNames) {
if (each.endsWith(shardingValue.getValue() % 3 + "")) {
return each;
}
}
throw new IllegalArgumentException();
}
/**
* sql 中 in 操作时，table的映射
*/
public Collection<String> doInSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) {
Collection<String> result = new LinkedHashSet<String>(tableNames.size());
for (Integer value : shardingValue.getValues()) {
for (String tableName : tableNames) {
if (tableName.endsWith(value % 3 + "")) {
result.add(tableName);
}
}
}
return result;
}
/**
* sql 中 between 操作时，table的映射
*/
public Collection<String> doBetweenSharding(Collection<String> tableNames,
ShardingValue<Integer> shardingValue) {
Collection<String> result = new LinkedHashSet<String>(tableNames.size());
Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();
for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
for (String each : tableNames) {
if (each.endsWith(i % 3 + "")) {
result.add(each);
}
}
}
return result;
}
}
5.事务管理：
新增Student的分库分表策略，具体见代码
事务接口实现类，UserServiceImpl
package com.eshop.sharding.jdbc.service.impl;
import java.util.List;
import javax.annotation.Resource;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import com.eshop.sharding.jdbc.dao.StudentDao;
import com.eshop.sharding.jdbc.dao.UserDao;
import com.eshop.sharding.jdbc.domain.Student;
import com.eshop.sharding.jdbc.domain.User;
import com.eshop.sharding.jdbc.service.UserService;
@Service
@Transactional
public class UserServiceImpl implements UserService {
@Resource
public UserDao userDao;
@Resource
public StudentDao studentDao;
public boolean insert(User u) {
return userDao.insert(u) > 0 ? true :false;
}
public List<User> findAll() {
return userDao.findAll();
}
public List<User> findByUserIds(List<Integer> ids) {
return userDao.findByUserIds(ids);
}
@Transactional(propagation=Propagation.REQUIRED)
public void transactionTestSucess() {
User u = new User();
u.setUserId(13);
u.setAge(25);
u.setName("war3 1.27");
userDao.insert(u);
Student student = new Student();
student.setStudentId(21);
student.setAge(21);
student.setName("hehe");
studentDao.insert(student);
}
@Transactional(propagation=Propagation.REQUIRED)
public void transactionTestFailure() throws IllegalAccessException {
User u = new User();
u.setUserId(13);
u.setAge(25);
u.setName("war3 1.27 good");
userDao.insert(u);
Student student = new Student();
student.setStudentId(21);
student.setAge(21);
student.setName("hehe1");
studentDao.insert(student);
throw new IllegalAccessException();
}
}
测试类
package com.eshop.sharding.jdbc;
import java.util.Arrays;
import java.util.List;
import javax.annotation.Resource;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import com.eshop.sharding.jdbc.domain.User;
import com.eshop.sharding.jdbc.service.UserService;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath*:applicationContext.xml")
public class ShardingJdbcMybatisTest {
@Resource
public UserService userService;
@Test
public void testUserInsert() {
User u = new User();
//为=号时，分库规则shardingValue.getValue() % 2 + "" ，分表规则 shardingValue.getValue() % 3 + ""
//u.setUserId(11); //11%2=1 11%3=2 sharding_1.t_user_2
u.setUserId(12); //12%2=0 12%3=0 sharding_0.t_user_0
//u.setUserId(13); //13%2=1 13%3=1 sharding_1.t_user_1
//u.setUserId(14); //14%2=0 14%3=2 sharding_0.t_user_2
u.setAge(25);
u.setName("github");
Assert.assertEquals(userService.insert(u), true);
}
// @Test
// public void testStudentInsert() {
// Student student = new Student();
// student.setStudentId(21);
// student.setAge(21);
// student.setName("hehe");
// Assert.assertEquals(studentService.insert(student), true);
// }
@Test
public void testFindAll(){
List<User> users = userService.findAll();
if(null != users && !users.isEmpty()){
for(User u :users){
System.out.println(u);
}
}
}
@Test
public void testSQLIN(){
List<User> users = userService.findByUserIds(Arrays.asList(2,10,1));
if(null != users && !users.isEmpty()){
for(User u :users){
System.out.println(u);
}
}
}
@Test
public void testTransactionTestSucess(){
userService.transactionTestSucess();
}
@Test(expected = IllegalAccessException.class)
public void testTransactionTestFailure() throws IllegalAccessException{
userService.transactionTestFailure();
}
}
事务好像成问题
6.最新的基于rdb的写法
参考配置：https://github.com/dangdangdotcom/sharding-jdbc/blob/master/sharding-jdbc-example/sharding-jdbc-example-mybatis/src/main/resources/META-INF/mybatis/mysql/shardingContext.xml
http://blog.csdn.net/farrell_zeng/article/details/52957274
http://www.cnblogs.com/zwt1990/p/6762135.html
见工程：sharding-jdbc-mybatis-rdb-dxfl-demo
<rdb:table-rule logic-table="t_user" actual-tables="t_order_${0..2}" database-strategy="userDatabaseShardingStrategy" table-strategy="userTableShardingStrategy">
报错：Invalid bean definition with name 'shardingDataSource' defined in null: Could not resolve placeholder '0..2' in string value "t_student_${0..2}"
Cloud not resolve placeholder … in string value …异常的解决方法?
在读取配置文件时， <context:property-placeholder location="classpath:bp.properties" ignore-unresolvable="true" />
7.主从配置
8.分布式主键
http://blog.csdn.net/u012768474/article/details/52767520
http://dangdangdotcom.github.io/sharding-jdbc/02-guide/key-generator/
http://blog.csdn.net/tianyaleixiaowu/article/details/70242971
先看官方的说法，http://dangdangdotcom.github.io/sharding-jdbc/02-guide/id-generator/
传统数据库软件开发中，主键自动生成技术是基本需求。而各大数据库对于该需求也提供了相应的支持，比如MySQL的自增键。 对于MySQL而言，分库分表之后，不同表生成全局唯一的Id是非常棘手的问题。因为同一个逻辑表内的不同实际表之间的自增键是无法互相感知的， 这样会造成重复Id的生成。我们当然可以通过约束表生成键的规则来达到数据的不重复，但是这需要引入额外的运维力量来解决重复性问题，并使框架缺乏扩展性。
目前有许多第三方解决方案可以完美解决这个问题，比如UUID等依靠特定算法自生成不重复键，或者通过引入Id生成服务等。 但也正因为这种多样性导致了Sharding-JDBC如果强依赖于任何一种方案就会限制其自身的发展。
基于以上的原因，最终采用了以JDBC接口来实现对于生成Id的访问，而将底层具体的Id生成实现分离出来。
其实最终要解决的问题就是各库各表中的数据，主键不能重复。官方提供的statement什么的没看懂，我就直接用它提供的通用主键生成器来生成主键了。
1.提供了一个类IdGenerator，这个类能生成一个保证不重复的Long型数字，我们就用它做主键
<dependency>
<groupId>com.dangdang</groupId>
<artifactId>sharding-jdbc-self-id-generator</artifactId>
<version>${sharding-jdbc.version}</version>
</dependency>
注：最新版本提供了该方法：1.4.2
2.在数据库中应该用大于等于64bit的数字类型的字段来保存该值，比如在MySQL中应该使用BIGINT。
其二进制表示形式包含四部分，从高位到低位分表为：1bit符号位(为0)，41bit时间位，10bit工作进程位，12bit序列位。
用int转换会变成负数
可以新建类
package com.eshop.sharding.jdbc.idGenerator;
import org.springframework.stereotype.Service;
import com.dangdang.ddframe.rdb.sharding.id.generator.IdGenerator;
import com.dangdang.ddframe.rdb.sharding.id.generator.self.CommonSelfIdGenerator;
@Service
public class IdGeneratorTool {
public IdGenerator getIdGenerator() {
return new CommonSelfIdGenerator();
}
}
long id = idGenerator.getIdGenerator().generateId().longValue();
u.setId(id);
一些最佳实践的说明
1. 在设计库和表时优先考虑使用0，可以配合取余来使用
2.做主键的字段一般设计成数字型
3.SingleKeyTableShardingAlgorithm的类型当然也可以定义为String,问题是使用between时，不好用；
SingleKeyTableShardingAlgorithm的类型设置成Integer类型
4.在写分库分表规则时：
(shardingValue.getValue()!=null && each.endsWith( String.valueOf(shardingValue.getValue()).substring(0, 6)))
这种方式可取：String.valueOf(shardingValue.getValue())
这种方式不可取：shardingValue.getValue().toString()
非维度查询也可以
当你需要使用某个维度进行操作，可以带条件，否则别带
做法：在batis配置文件里设置<if test="orderId != null" >AND order_id = #{orderId,jdbcType=INTEGER} </if>
至今总结：sharding-jdbc:可以维度和非维度查询；带维度条件则在维度条件下获取；否则在全库全表获取数据聚合。
可以尝试：分页，排序等等
统计时：
异构数据库：比如跨库join;
全局表：比如跨库join
相关推荐
spring+mybatis+sharding-jdbc实现的一个小demo，仅供参考
spring+mybatis+sharding-jdbc 1.3.1实现分库分表案例（可直接运行）
因为原项目中提到的 sharding-jdbc-mybatis-plus-spring-boot-starter 没有再更新，所以产生了此项目 dependencies com.baomidou:mybatis-plus:2.2.0 sharding-jdbc-core-spring-boot-starter:2.0.3 spring-boot-...
主要介绍了Spring Boot 集成 Sharding-JDBC + Mybatis-Plus 实现分库分表功能,本文通过实例代码给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下
sharding-jdbc-4.0.0-rc1 整合spring boot 2+mybatis plus 分库分表Demo 可以直接跑起来
下载后要jdk1.7 ，sql也在conf下，文档在http://blog.csdn.net/linuu/article/details/50929904
spring boot的配置文件配置无法具体配置druid连接池的各种属性（官网上我没找到），所以采用java类的方式配置，包括druid的wallFilter、statFilter、statViewServlet、webStatFilter等等
spring4.0.2+mybatis3.2.4+sharding-jdbc1.4.2实现分库分表、主从及事物支持
基于springCloud+RocketMQ+Sharding-jdbc+Elastic-job微服务万信金融项目.zip ## 技术解决方案 1、微服务技术应用于P2P金融业务解决方案 2、接口规范SpringBoot+Swagger 3、持久层编码 MyBatis Plus 4、...
springboot整合sharding-jdbc完整代码，基本上涵盖了sharding-jdbc所有技术点，代码只需要简单修改下配置即可运行起来
这是sharding-jdbc spring boot的一个demo程序。分别使用了controller的单元测试及服务的单元测试resources / sql下的sql语句在两个不同的数据库下执行。
Java+Springboot+mybatis+sharding jdbc 实现分库分表
学习spring与sharding-jdbc时苦于无demo，自行研究过于麻烦，用别人的代码，整理后，本地跑通。对理解还是有一定的帮助的。自己去找，会浪费大量时间。按照步骤就可以。如有问题，请看日志，认真解决。
sharding-jdbc.jar 当当网分库分表框架，可以实现与spring，与mybatis集成，还有与mybatis与spring集成的小例子。
主要介绍了SpringBoot 2.0 整合sharding-jdbc中间件，实现数据分库分表，本文图文并茂给大家介绍的非常详细，具有一定的参考借鉴价值 ,需要的朋友可以参考下
绝对可以直接用，SSM框架整合，maven项目ssm源码 Spring+SpringMVC+Mybatis+Bootstrap shardingjdbc
分库分表中间件Sharding-JDBC是当当网开源的一个项目，社区活跃，该资源是自己参照网上的例子编写的一个使用Sharding-JDBC进行分库、分表的例子，该例子是个maven项目使用eclipse导入即可运行，对Sharding_Jdbc初学...
2：集成shardingjdbc分库分表 3：集成shardingjdbc读写分离 4：集成人大金仓数据库 5：重写了JPA-saveandflush方法 6：修复了JPA更新数据为null的问题 7：解压后导入idea，创建数据库即可运行测试
springcloud集成seata1.4.0,nacos1.4.0,sharding-jdbc,mybatis-plus实践-附件资源
项目源码，包含springmvc+mybatis+shardingjdbc2，运行到myeclipse2017可以直接跑起来，自己建立数据库和表