基于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,待研究-->
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