sharding-jdbc之——分库分表实例

本文介绍如何使用Sharding-JDBC实现数据库的分库分表操作,包括创建数据库、项目搭建、配置文件、分库分表逻辑及测试等步骤。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

转载请注明出处:http://blog.youkuaiyun.com/l1028386804/article/details/79368021

一、概述

之前,我们介绍了利用Mycat进行分库分表操作,Mycat分表操作是利用分库来进行的,单个库中的分表操作可结合MySQL的分区进行,这也是Mycat官方提倡的方式。那么,如何利用Mycat真正实现数据库的分库分表,可以私信我。今天,我们来看看sharding-jdbc,sharding-jdbc也是一款分库分表的“中间件”,不过,它并不向Mycat那样作为一个真正的中间件,它是一款以jar包的形式整合到业务中的插件,这就决定了它是轻量级的,用法也是十分简单的。

二、分库分表实战

1、创建数据库

首先我们创建相应的数据库

create database sharding_0;
create database sharding_1;

这样我们就创建了两个数据库sharding_0和sharding_1;

接下来我们在两个库中创建相应的数据表,在两个库中分别进行如下SQL:

SET FOREIGN_KEY_CHECKS=0;
 
-- ----------------------------
-- Table structure for t_student_00
-- ----------------------------
DROP TABLE IF EXISTS `t_student_00`;
CREATE TABLE `t_student_00` (
  `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;
 
-- ----------------------------
-- Table structure for t_student_01
-- ----------------------------
DROP TABLE IF EXISTS `t_student_01`;
CREATE TABLE `t_student_01` (
  `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 AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
 
-- ----------------------------
-- Table structure for t_user_00
-- ----------------------------
DROP TABLE IF EXISTS `t_user_00`;
CREATE TABLE `t_user_00` (
  `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;
 
-- ----------------------------
-- Table structure for t_user_01
-- ----------------------------
DROP TABLE IF EXISTS `t_user_01`;
CREATE TABLE `t_user_01` (
  `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 AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
 
-- ----------------------------
-- Table structure for t_user_02
-- ----------------------------
DROP TABLE IF EXISTS `t_user_02`;
CREATE TABLE `t_user_02` (
  `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 AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

这样,我们的数据库就准备好了。

2、创建项目

接下来,我们就创建一个Maven项目,项目结构如下:

3、配置pom.xml

<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>
	<parent>
		<groupId>dubbo-tcc</groupId>
		<artifactId>tcc-parent</artifactId>
		<version>0.0.1-SNAPSHOT</version>
	</parent>

	<artifactId>tcc-order</artifactId>
	<packaging>war</packaging>

	<dependencies>

		<dependency>
			<groupId>dubbo-tcc</groupId>
			<artifactId>tcc-service-api</artifactId>
			<version>0.0.1-SNAPSHOT</version>
		</dependency>

		<dependency>
			<groupId>org.mengyun</groupId>
			<artifactId>tcc-transaction-spring</artifactId>
			<version>1.2.4.23</version>
		</dependency>

		<dependency>
			<groupId>org.mengyun</groupId>
			<artifactId>tcc-transaction-dubbo</artifactId>
			<version>1.2.4.23</version>
		</dependency>

		<!-- Spring -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-beans</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-aspects</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context-support</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-tx</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-aop</artifactId>
		</dependency>

		<!-- dubbo相关 -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>dubbo</artifactId>
			<exclusions>
				<exclusion>
					<artifactId>spring</artifactId>
					<groupId>org.springframework</groupId>
				</exclusion>
				<exclusion>
					<artifactId>netty</artifactId>
					<groupId>org.jboss.netty</groupId>
				</exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>org.apache.zookeeper</groupId>
			<artifactId>zookeeper</artifactId>
		</dependency>
		<dependency>
			<groupId>com.github.sgroschupf</groupId>
			<artifactId>zkclient</artifactId>
		</dependency>

		<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>
		<!-- MySql -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>${mysql.version}</version>
		</dependency>
		<!-- 连接池 -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>${druid.version}</version>
		</dependency>

		<!-- JSP相关 -->
		<dependency>
			<groupId>jstl</groupId>
			<artifactId>jstl</artifactId>
			<version>${jstl.version}</version>
		</dependency>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>servlet-api</artifactId>
			<version>${servlet-api.version}</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jsp-api</artifactId>
			<version>${jsp-api.version}</version>
			<scope>provided</scope>
		</dependency>

		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<scope>test</scope>
		</dependency>

	</dependencies>

</project>

4、创建数据库映射类

这里,我们创建两个数据库映射类:User类和Student类。

4-1、User类

package com.th.sharding.entity;

import java.io.Serializable;

public class User implements Serializable {

	private static final long serialVersionUID = 215524951712753197L;

	private Integer id;

	private Integer userId;

	private String name;

	private Integer age;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public Integer getUserId() {
		return userId;
	}

	public void setUserId(Integer userId) {
		this.userId = userId;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", userId=" + userId + ", name=" + name + ", age=" + age + "]";
	}

}

4-2、Student类

package com.th.sharding.entity;

import java.io.Serializable;

public class Student implements Serializable {

	private static final long serialVersionUID = 4461939595448576648L;

	private Integer id;

	private Integer studentId;

	private String name;

	private Integer age;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public Integer getStudentId() {
		return studentId;
	}

	public void setStudentId(Integer studentId) {
		this.studentId = studentId;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}

	@Override
	public String toString() {
		return "Student [id=" + id + ", studentId=" + studentId + ", name=" + name + ", age=" + age + "]";
	}

}

5、创建Mapper类

5-1、UserMapper类

package com.th.sharding.mapper;

import java.util.List;

import com.th.sharding.entity.User;

public interface UserMapper {
	Integer insert(User u);

	List<User> findAll();

	List<User> findByUserIds(List<Integer> userIds);
}

5-2、StudentMapper类

package com.th.sharding.mapper;

import java.util.List;

import com.th.sharding.entity.Student;

public interface StudentMapper {
	Integer insert(Student s);

	List<Student> findAll();

	List<Student> findByStudentIds(List<Integer> studentIds);
}

6、创建service类

6-1、UserService类

package com.th.sharding.service;

import java.util.List;

import com.th.sharding.entity.User;

public interface UserService {
	public boolean insert(User u);

	public List<User> findAll();

	public List<User> findByUserIds(List<Integer> ids);

	public void transactionTestSucess();

	public void transactionTestFailure() throws IllegalAccessException;

}

6-2、StudentService类

package com.th.sharding.service;

import com.th.sharding.entity.Student;

public interface StudentService {
	boolean insert(Student student);
}

7、创建service的实现类

7-1、UserServiceImpl类

package com.th.sharding.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.th.sharding.entity.Student;
import com.th.sharding.entity.User;
import com.th.sharding.mapper.StudentMapper;
import com.th.sharding.mapper.UserMapper;
import com.th.sharding.service.UserService;

@Service
public class UserServiceImpl implements UserService {
	@Resource
	public UserMapper userMapper;

	@Resource
	public StudentMapper studentMapper;

	public boolean insert(User u) {
		return userMapper.insert(u) > 0;
	}

	public List<User> findAll() {
		return userMapper.findAll();
	}

	public List<User> findByUserIds(List<Integer> ids) {
		return userMapper.findByUserIds(ids);
	}

	@Transactional(propagation = Propagation.REQUIRED)
	public void transactionTestSucess() {
		User u = new User();
		u.setUserId(13);
		u.setAge(25);
		u.setName("war3 1.27");
		userMapper.insert(u);

		Student student = new Student();
		student.setStudentId(21);
		student.setAge(21);
		student.setName("hehe");
		studentMapper.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");
		userMapper.insert(u);

		Student student = new Student();
		student.setStudentId(21);
		student.setAge(21);
		student.setName("hehe1");
		studentMapper.insert(student);
		throw new IllegalAccessException();
	}
}

7-2、StudentServiceImpl类

package com.th.sharding.service.impl;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.th.sharding.entity.Student;
import com.th.sharding.mapper.StudentMapper;
import com.th.sharding.service.StudentService;

@Service
public class StudentServiceImpl implements StudentService {

	@Resource
	public StudentMapper studentMapper;

	public boolean insert(Student student) {
		return studentMapper.insert(student) > 0;
	}

}

8、创建分库逻辑

8-1、User分库逻辑UserSingleKeyDatabaseShardingAlgorithm类

package com.th.sharding.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;

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;
	}

}

8-2、Student分库逻辑StudentSingleKeyDatabaseShardingAlgorithm

package com.th.sharding.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;

public class StudentSingleKeyDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Integer> {
	/**
	 * sql 中关键字 匹配符为 =的时候,表的路由函数
	 */
	@Override
	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 的时候,表的路由函数
	 */
	@Override
	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的时候,表的路由函数
	 */
	@Override
	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;
	}

}

9、创建分表逻辑

9-1、User分表逻辑UserSingleKeyTableShardingAlgorithm

package com.th.sharding.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(("0".concat(String.valueOf(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(("0".concat(String.valueOf(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(("0".concat(String.valueOf(i % 3))))) {
					result.add(each);
				}
			}
		}
		return result;
	}

}

9-2、创建Student分表逻辑StudentSingleKeyTableShardingAlgorithm

package com.th.sharding.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 StudentSingleKeyTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer> {
	/**
	 * sql 中 = 操作时,table的映射
	 */
	public String doEqualSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) {
		for (String each : tableNames) {
			if (each.endsWith("0".concat(String.valueOf(shardingValue.getValue() % 2)))) {
				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("0".concat(String.valueOf(value % 2)))) {
					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("0".concat(String.valueOf(i % 2)))) {
					result.add(each);
				}
			}
		}
		return result;
	}

}

10、创建Mapper.xml

10-1、创建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.th.sharding.mapper.UserMapper" >  
  <resultMap id="resultMap" type="com.th.sharding.entity.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>  
    
  <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>  
    
  <sql id="columnsName">  
     id,user_id,name,age  
  </sql>      
</mapper>  

10-2、创建StudentMapper.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.th.sharding.mapper.StudentMapper" >  
  <resultMap id="resultMap" type="com.th.sharding.entity.Student" >  
    <id column="id" property="id" jdbcType="INTEGER" />  
    <result column="student_id" property="studentId" jdbcType="INTEGER" />  
    <result column="name" property="name" jdbcType="VARCHAR" />  
    <result column="age" property="age" jdbcType="INTEGER" />  
  </resultMap>  
    
  <insert id="insert">  
    insert into t_student (student_id,name,age) values (#{studentId},#{name},#{age})  
  </insert>  
    
  <select id="findAll" resultMap="resultMap">  
   select <include refid="columnsName"/> from t_student  
  </select>  
    
  <select id="findByStudentIds" resultMap="resultMap">  
    select <include refid="columnsName"/> from t_student where student_id in (  
     <foreach collection="list" item="item" separator=",">  
        #{item}  
     </foreach>  
    )  
      
  </select>  
    
  <sql id="columnsName">  
     id,student_id,name,age  
  </sql>      
</mapper>  

11、创建jdbc_dev.properties

jdbc_driver0=com.mysql.cj.jdbc.Driver
jdbc_url0=jdbc:mysql://localhost:3306/sharding_0?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT
jdbc_username0=root
jdbc_password0=123456

jdbc_driver1=com.mysql.cj.jdbc.Driver
jdbc_url1=jdbc:mysql://localhost:3306/sharding_1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT
jdbc_username1=root
jdbc_password1=123456

validationQuery=SELECT 1

12、创建spring配置文件

12-1、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-4.0.xsd  
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.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-4.0.xsd">  
          
    <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">  
        <property name="locations">  
            <list>  
                <value>classpath:config/resource/jdbc_dev.properties</value>  
            </list>  
        </property>  
    </bean>  
          
    <bean name="sharding_0" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">  
        <property name="url" value="${jdbc_url0}" />  
        <property name="username" value="${jdbc_username0}" />  
        <property name="password" value="${jdbc_password0}" />  
<!--         <property name="driverClass" value="${jdbc_driver0}" /> -->  
        <!-- 初始化连接大小 -->  
        <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 name="sharding_1" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">  
        <property name="url" value="${jdbc_url1}" />  
        <property name="username" value="${jdbc_username1}" />  
        <property name="password" value="${jdbc_password1}" />  
<!--         <property name="driverClass" value="${jdbc_driver1}" /> -->  
        <!-- 初始化连接大小 -->  
        <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>  
      
  
</beans>  

12-2、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-4.0.xsd  
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.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-4.0.xsd">  
          
    <context:component-scan base-package="com.th.sharding" /> 
     
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">  
        <property name="basePackage" value="com.th.sharding.mapper"/>  
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>  
    </bean>  
      
    <!-- 配置sqlSessionFactory -->  
     <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">  
        <property name="dataSource" ref="shardingDataSource"/>  
        <property name="mapperLocations" value="classpath*:config/mapper/*Mapper.xml"/>  
    </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表的配置,进行分库配置,逻辑表名为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_00</value>  
                <value>t_user_01</value>  
                <value>t_user_02</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.th.sharding.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.th.sharding.algorithm.UserSingleKeyTableShardingAlgorithm" />  
        </constructor-arg>  
    </bean>  
      
      
      
      <!-- 对t_student表的配置,进行分库配置,逻辑表名为t_student,每个库有实际的三张表 -->  
    <bean id="studentTableRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.TableRule">  
        <constructor-arg value="t_student" index="0"/>  
        <constructor-arg index="1">  
            <list>  
                <value>t_student_00</value>  
                <value>t_student_01</value>  
            </list>  
        </constructor-arg>  
        <constructor-arg index="2" ref="dataSourceRule"/>  
        <constructor-arg index="3" ref="studentDatabaseShardingStrategy"/>  
        <constructor-arg index="4" ref="studentTableShardingStrategy"/>  
    </bean>  
      
     <!-- t_student分库策略 -->  
    <bean id="studentDatabaseShardingStrategy" class="com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy">  
        <constructor-arg index="0" value="student_id"/>  
        <constructor-arg index="1">  
            <bean class="com.th.sharding.algorithm.StudentSingleKeyDatabaseShardingAlgorithm" />  
        </constructor-arg>  
    </bean>  
      
    <!-- t_student 分表策略 -->  
    <bean id="studentTableShardingStrategy" class="com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy">  
        <constructor-arg index="0" value="student_id"/>  
        <constructor-arg index="1">  
            <bean class="com.th.sharding.algorithm.StudentSingleKeyTableShardingAlgorithm" />  
        </constructor-arg>  
    </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>  
      
    <!-- 对datasource进行封装 -->  
    <bean id="shardingDataSource" class="com.dangdang.ddframe.rdb.sharding.api.ShardingDataSource">  
        <constructor-arg ref="shardingRule"/>  
    </bean>  
  
    <!-- 事务 -->  
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">  
        <property name="dataSource" ref="shardingDataSource" />  
    </bean>  
  
    <tx:annotation-driven transaction-manager="transactionManager" />  
          
</beans>  

13、创建log4j.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="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="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="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>    

14、创建测试类ShardingJdbcMybatisTest

package com.th.test;

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.th.sharding.entity.Student;
import com.th.sharding.entity.User;
import com.th.sharding.service.StudentService;
import com.th.sharding.service.UserService;

/**
 * 测试分库分表规则
 * 
 * @author
 *
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath*:config/spring/spring-database.xml", "classpath*:config/spring/spring-sharding.xml" })
public class ShardingJdbcMybatisTest {

	@Resource
	public UserService userService;

	@Resource
	public StudentService studentService;

	@Test
	public void testUserInsert() {
		User u = new User();
		u.setUserId(11);
		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(1,13));
		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();
	}
}

三、测试

我们进行ShardingJdbcMybatisTest类,查看数据表数据,即可看到我们的程序利用sharding-jdbc实现了分库分表操作。

四、温馨提示

大家可以到链接http://download.youkuaiyun.com/download/l1028386804/10258290下载完整的sharding-jdbc分库分表实例源代码

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值