新建Maven项目
pom.xml
1. <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
2. xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
3. <modelVersion>4.0.0</modelVersion>
4.
5. <groupId>org.study</groupId>
6. <artifactId>sharding-jdbc-mybatis</artifactId>
7. <version>0.0.1-SNAPSHOT</version>
8. <packaging>jar</packaging>
9.
10. <name>sharding-jdbc-mybatis</name>
11. <url>http://maven.apache.org</url>
12.
13. <properties>
14. <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
15. <spring.version>3.2.5.RELEASE</spring.version>
16. <mybatis.version>3.2.4</mybatis.version>
17. </properties>
18.
19. <dependencies>
20. <dependency>
21. <groupId>junit</groupId>
22. <artifactId>junit</artifactId>
23. <version>4.10</version>
24. </dependency>
25. <dependency>
26. <groupId>com.dangdang</groupId>
27. <artifactId>sharding-jdbc-core</artifactId>
28. <version>1.0.0</version>
29. </dependency>
30. <dependency>
31. <groupId>org.springframework</groupId>
32. <artifactId>spring-orm</artifactId>
33. <version>${spring.version}</version>
34. </dependency>
35. <dependency>
36. <groupId>commons-dbcp</groupId>
37. <artifactId>commons-dbcp</artifactId>
38. <version>1.4</version>
39. </dependency>
40. <dependency>
41. <groupId>org.mybatis</groupId>
42. <artifactId>mybatis-spring</artifactId>
43. <version>1.2.2</version>
44. </dependency>
45. <dependency>
46. <groupId>org.mybatis</groupId>
47. <artifactId>mybatis</artifactId>
48. <version>${mybatis.version}</version>
49. </dependency>
50.
51. <dependency>
52. <groupId>org.springframework</groupId>
53. <artifactId>spring-expression</artifactId>
54. <version>${spring.version}</version>
55. </dependency>
56. <dependency>
57. <groupId>org.springframework</groupId>
58. <artifactId>spring-aop</artifactId>
59. <version>${spring.version}</version>
60. </dependency>
61. <dependency>
62. <groupId>org.springframework</groupId>
63. <artifactId>spring-beans</artifactId>
64. <version>${spring.version}</version>
65. </dependency>
66. <dependency>
67. <groupId>org.springframework</groupId>
68. <artifactId>spring-context</artifactId>
69. <version>${spring.version}</version>
70. </dependency>
71. <dependency>
72. <groupId>org.springframework</groupId>
73. <artifactId>spring-context-support</artifactId>
74. <version>${spring.version}</version>
75. </dependency>
76. <dependency>
77. <groupId>org.springframework</groupId>
78. <artifactId>spring-test</artifactId>
79. <version>${spring.version}</version>
80. </dependency>
81. <dependency>
82. <groupId>org.springframework</groupId>
83. <artifactId>spring-tx</artifactId>
84. <version>${spring.version}</version>
85. </dependency>
86. <dependency>
87. <groupId>mysql</groupId>
88. <artifactId>mysql-connector-java</artifactId>
89. <version>5.1.28</version>
90. </dependency>
91. <dependency>
92. <groupId>log4j</groupId>
93. <artifactId>log4j</artifactId>
94. <version>1.2.16</version>
95. </dependency>
96. <dependency>
97. <groupId>org.slf4j</groupId>
98. <artifactId>slf4j-log4j12</artifactId>
99. <version>1.7.5</version>
100. </dependency>
101. </dependencies>
102. </project>
新建2个库，sharding_0和sharding_1
分别在这2个库运行sql：
1. /*
2. Navicat MySQL Data Transfer
3.
4. Source Server : PMS
5. Source Server Version : 50624
6. Source Host : localhost:3306
7. Source Database : sharding_0
8.
9. Target Server Type : MYSQL
10. Target Server Version : 50624
11. File Encoding : 65001
12.
13. Date: 2016-03-19 14:18:22
14. */
15.
16. SET FOREIGN_KEY_CHECKS=0;
17.
18. -- ----------------------------
19. -- Table structure for t_student_0
20. -- ----------------------------
21. DROP TABLE IF EXISTS `t_student_0`;
22. CREATE TABLE `t_student_0` (
23. int(11) NOT NULL AUTO_INCREMENT,
24. int(11) NOT NULL,
25. 255) NOT NULL,
26. int(11) NOT NULL,
27. PRIMARY KEY (`id`)
28. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
29.
30. DROP TABLE IF EXISTS `t_student_1`;
31. CREATE TABLE `t_student_1` (
32. int(11) NOT NULL AUTO_INCREMENT,
33. int(11) NOT NULL,
34. 255) NOT NULL,
35. int(11) NOT NULL,
36. PRIMARY KEY (`id`)
37. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
38.
39. DROP TABLE IF EXISTS `t_user_0`;
40. CREATE TABLE `t_user_0` (
41. int(11) NOT NULL AUTO_INCREMENT,
42. int(11) NOT NULL,
43. 255) NOT NULL,
44. int(11) NOT NULL,
45. PRIMARY KEY (`id`)
46. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
47.
48. DROP TABLE IF EXISTS `t_user_1`;
49. CREATE TABLE `t_user_1` (
50. int(11) NOT NULL AUTO_INCREMENT,
51. int(11) NOT NULL,
52. 255) NOT NULL,
53. int(11) NOT NULL,
54. PRIMARY KEY (`id`)
55. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
56.
57. DROP TABLE IF EXISTS `t_user_2`;
58. CREATE TABLE `t_user_2` (
59. int(11) NOT NULL AUTO_INCREMENT,
60. int(11) NOT NULL,
61. 255) NOT NULL,
62. int(11) NOT NULL,
63. PRIMARY KEY (`id`)
64. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Student.Java
1. package com.study.dangdang.sharding.jdbc.entity;
2.
3. import java.io.Serializable;
4.
5. public class Student implements Serializable{
6.
7. /**
8. *
9. */
10. private static final long serialVersionUID = 8920597824668331209L;
11.
12. private Integer id;
13.
14. private Integer studentId;
15.
16. private String name;
17.
18. private Integer age;
19.
20. public Integer getId() {
21. return id;
22. }
23.
24. public void setId(Integer id) {
25. this.id = id;
26. }
27.
28. public Integer getStudentId() {
29. return studentId;
30. }
31.
32. public void setStudentId(Integer studentId) {
33. this.studentId = studentId;
34. }
35.
36. public String getName() {
37. return name;
38. }
39.
40. public void setName(String name) {
41. this.name = name;
42. }
43.
44. public Integer getAge() {
45. return age;
46. }
47.
48. public void setAge(Integer age) {
49. this.age = age;
50. }
51.
52. }
User.java
1. package com.study.dangdang.sharding.jdbc.entity;
2.
3. import java.io.Serializable;
4.
5. public class User implements Serializable{
6.
7. /**
8. *
9. */
10. private static final long serialVersionUID = 1L;
11.
12.
13. private Integer id;
14.
15. private Integer userId;
16.
17. private String name;
18.
19. private Integer age;
20.
21. public Integer getId() {
22. return id;
23. }
24.
25. public void setId(Integer id) {
26. this.id = id;
27. }
28.
29. public Integer getUserId() {
30. return userId;
31. }
32.
33. public void setUserId(Integer userId) {
34. this.userId = userId;
35. }
36.
37. public String getName() {
38. return name;
39. }
40.
41. public void setName(String name) {
42. this.name = name;
43. }
44.
45. public Integer getAge() {
46. return age;
47. }
48.
49. public void setAge(Integer age) {
50. this.age = age;
51. }
52.
53. @Override
54. public String toString() {
55. return "User [id=" + id + ", userId=" + userId + ", name=" + name + ", age=" + age + "]";
56. }
57.
58. }
StudentMapper.java
1. package com.study.dangdang.sharding.jdbc.mapper;
2.
3. import java.util.List;
4.
5. import com.study.dangdang.sharding.jdbc.entity.Student;
6.
7. public interface StudentMapper {
8.
9. Integer insert(Student s);
10.
11. List<Student> findAll();
12.
13. List<Student> findByStudentIds(List<Integer> studentIds);
14.
15. }
UserMapper.java
1. package com.study.dangdang.sharding.jdbc.mapper;
2.
3. import java.util.List;
4.
5. import com.study.dangdang.sharding.jdbc.entity.User;
6.
7. public interface UserMapper {
8.
9. Integer insert(User u);
10.
11. List<User> findAll();
12.
13. List<User> findByUserIds(List<Integer> userIds);
14.
15.
16. }
StudentMapper.xml
1. <?xml version="1.0" encoding="UTF-8" ?>
2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
3. <mapper namespace="com.study.dangdang.sharding.jdbc.mapper.StudentMapper" >
4. <resultMap id="resultMap" type="com.study.dangdang.sharding.jdbc.entity.Student" >
5. <id column="id" property="id" jdbcType="INTEGER" />
6. <result column="student_id" property="studentId" jdbcType="INTEGER" />
7. <result column="name" property="name" jdbcType="VARCHAR" />
8. <result column="age" property="age" jdbcType="INTEGER" />
9. </resultMap>
10.
11. <insert id="insert">
12. insert into t_student (student_id,name,age) values (#{studentId},#{name},#{age})
13. </insert>
14.
15. <select id="findAll" resultMap="resultMap">
16. <include refid="columnsName"/> from t_student
17. </select>
18.
19. <select id="findByStudentIds" resultMap="resultMap">
20. <include refid="columnsName"/> from t_student where student_id in (
21. <foreach collection="list" item="item" separator=",">
22. #{item}
23. </foreach>
24. )
25.
26. </select>
27.
28. <sql id="columnsName">
29. id,student_id,name,age
30. </sql>
31.
32.
33.
34. </mapper>
UserMapper.xml
1. <?xml version="1.0" encoding="UTF-8" ?>
2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
3. <mapper namespace="com.study.dangdang.sharding.jdbc.mapper.UserMapper" >
4. <resultMap id="resultMap" type="com.study.dangdang.sharding.jdbc.entity.User" >
5. <id column="id" property="id" jdbcType="INTEGER" />
6. <result column="user_id" property="userId" jdbcType="INTEGER" />
7. <result column="name" property="name" jdbcType="VARCHAR" />
8. <result column="age" property="age" jdbcType="INTEGER" />
9. </resultMap>
10.
11. <insert id="insert">
12. insert into t_user (user_id,name,age) values (#{userId},#{name},#{age})
13. </insert>
14.
15. <select id="findAll" resultMap="resultMap">
16. <include refid="columnsName"/> from t_user
17. </select>
18.
19. <select id="findByUserIds" resultMap="resultMap">
20. <include refid="columnsName"/> from t_user where user_id in (
21. <foreach collection="list" item="item" separator=",">
22. #{item}
23. </foreach>
24. )
25.
26. </select>
27.
28. <sql id="columnsName">
29. id,user_id,name,age
30. </sql>
31.
32.
33.
34. </mapper>
StudentService.java
1. package com.study.dangdang.sharding.jdbc.service;
2.
3. import com.study.dangdang.sharding.jdbc.entity.Student;
4.
5. public interface StudentService {
6.
7. boolean insert(Student student);
8.
9. }
UserService.java
1. package com.study.dangdang.sharding.jdbc.service;
2.
3. import java.util.List;
4.
5. import com.study.dangdang.sharding.jdbc.entity.User;
6.
7. public interface UserService {
8.
9. public boolean insert(User u);
10.
11. public List<User> findAll();
12.
13. public List<User> findByUserIds(List<Integer> ids);
14.
15. public void transactionTestSucess();
16.
17. public void transactionTestFailure() throws IllegalAccessException;
18.
19. }
StudentServiceImpl.java
1. package com.study.dangdang.sharding.jdbc.service.impl;
2.
3. import javax.annotation.Resource;
4.
5. import org.springframework.stereotype.Service;
6.
7. import com.study.dangdang.sharding.jdbc.entity.Student;
8. import com.study.dangdang.sharding.jdbc.mapper.StudentMapper;
9. import com.study.dangdang.sharding.jdbc.service.StudentService;
10.
11. @Service
12. public class StudentServiceImpl implements StudentService{
13.
14. @Resource
15. public StudentMapper studentMapper;
16.
17. public boolean insert(Student student) {
18. return studentMapper.insert(student) > 0 ? true : false;
19. }
20.
21. }
UserServiceImpl.java
1. package com.study.dangdang.sharding.jdbc.service.impl;
2.
3. import java.util.List;
4.
5. import javax.annotation.Resource;
6.
7. import org.springframework.stereotype.Service;
8. import org.springframework.transaction.annotation.Propagation;
9. import org.springframework.transaction.annotation.Transactional;
10.
11. import com.study.dangdang.sharding.jdbc.entity.Student;
12. import com.study.dangdang.sharding.jdbc.entity.User;
13. import com.study.dangdang.sharding.jdbc.mapper.StudentMapper;
14. import com.study.dangdang.sharding.jdbc.mapper.UserMapper;
15. import com.study.dangdang.sharding.jdbc.service.UserService;
16.
17. @Service
18. @Transactional
19. public class UserServiceImpl implements UserService {
20.
21. @Resource
22. public UserMapper userMapper;
23.
24. @Resource
25. public StudentMapper studentMapper;
26.
27. public boolean insert(User u) {
28. return userMapper.insert(u) > 0 ? true :false;
29. }
30.
31. public List<User> findAll() {
32. return userMapper.findAll();
33. }
34.
35. public List<User> findByUserIds(List<Integer> ids) {
36. return userMapper.findByUserIds(ids);
37. }
38.
39. @Transactional(propagation=Propagation.REQUIRED)
40. public void transactionTestSucess() {
41. new User();
42. 13);
43. 25);
44. "war3 1.27");
45. userMapper.insert(u);
46.
47. new Student();
48. 21);
49. 21);
50. "hehe");
51. studentMapper.insert(student);
52. }
53.
54. @Transactional(propagation=Propagation.REQUIRED)
55. public void transactionTestFailure() throws IllegalAccessException {
56. new User();
57. 13);
58. 25);
59. "war3 1.27 good");
60. userMapper.insert(u);
61.
62. new Student();
63. 21);
64. 21);
65. "hehe1");
66. studentMapper.insert(student);
67. throw new IllegalAccessException();
68. }
69.
70. }
StudentSingleKeyDatabaseShardingAlgorithm.java
1. package com.study.dangdang.sharding.jdbc.algorithm;
2.
3. import java.util.Collection;
4. import java.util.LinkedHashSet;
5.
6. import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
7. import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;
8. import com.google.common.collect.Range;
9.
10. /**
11. * user表分库的逻辑函数
12. * @author lyncc
13. *
14. */
15. public class StudentSingleKeyDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Integer>{
16.
17. /**
18. * sql 中关键字 匹配符为 =的时候，表的路由函数
19. */
20. public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {
21. for (String each : availableTargetNames) {
22. if (each.endsWith(shardingValue.getValue() % 2 + "")) {
23. return each;
24. }
25. }
26. throw new IllegalArgumentException();
27. }
28.
29. /**
30. * sql 中关键字 匹配符为 in 的时候，表的路由函数
31. */
32. public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {
33. new LinkedHashSet<String>(availableTargetNames.size());
34. for (Integer value : shardingValue.getValues()) {
35. for (String tableName : availableTargetNames) {
36. if (tableName.endsWith(value % 2 + "")) {
37. result.add(tableName);
38. }
39. }
40. }
41. return result;
42. }
43.
44. /**
45. * sql 中关键字 匹配符为 between的时候，表的路由函数
46. */
47. public Collection<String> doBetweenSharding(Collection<String> availableTargetNames,
48. ShardingValue<Integer> shardingValue) {
49. new LinkedHashSet<String>(availableTargetNames.size());
50. Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();
51. for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
52. for (String each : availableTargetNames) {
53. if (each.endsWith(i % 2 + "")) {
54. result.add(each);
55. }
56. }
57. }
58. return result;
59. }
60.
61. }
StudentSingleKeyTableShardingAlgorithm.java
1. package com.study.dangdang.sharding.jdbc.algorithm;
2.
3. import java.util.Collection;
4. import java.util.LinkedHashSet;
5.
6. import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
7. import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;
8. import com.google.common.collect.Range;
9.
10. /**
11. * 因为t_student实际表在每个库中只有2个，所以 %2
12. * @author lyncc
13. *
14. */
15. public class StudentSingleKeyTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer>{
16.
17. /**
18. * sql 中 = 操作时，table的映射
19. */
20. public String doEqualSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) {
21. for (String each : tableNames) {
22. if (each.endsWith(shardingValue.getValue() % 2 + "")) {
23. return each;
24. }
25. }
26. throw new IllegalArgumentException();
27. }
28.
29. /**
30. * sql 中 in 操作时，table的映射
31. */
32. public Collection<String> doInSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) {
33. new LinkedHashSet<String>(tableNames.size());
34. for (Integer value : shardingValue.getValues()) {
35. for (String tableName : tableNames) {
36. if (tableName.endsWith(value % 2 + "")) {
37. result.add(tableName);
38. }
39. }
40. }
41. return result;
42. }
43.
44. /**
45. * sql 中 between 操作时，table的映射
46. */
47. public Collection<String> doBetweenSharding(Collection<String> tableNames,
48. ShardingValue<Integer> shardingValue) {
49. new LinkedHashSet<String>(tableNames.size());
50. Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();
51. for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
52. for (String each : tableNames) {
53. if (each.endsWith(i % 2 + "")) {
54. result.add(each);
55. }
56. }
57. }
58. return result;
59. }
60.
61. }
UserSingleKeyDatabaseShardingAlgorithm.java
1. package com.study.dangdang.sharding.jdbc.algorithm;
2.
3. import java.util.Collection;
4. import java.util.LinkedHashSet;
5.
6. import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
7. import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;
8. import com.google.common.collect.Range;
9.
10. /**
11. * user表分库的逻辑函数
12. * @author lyncc
13. *
14. */
15. public class UserSingleKeyDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Integer>{
16.
17. /**
18. * sql 中关键字 匹配符为 =的时候，表的路由函数
19. */
20. public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {
21. for (String each : availableTargetNames) {
22. if (each.endsWith(shardingValue.getValue() % 2 + "")) {
23. return each;
24. }
25. }
26. throw new IllegalArgumentException();
27. }
28.
29. /**
30. * sql 中关键字 匹配符为 in 的时候，表的路由函数
31. */
32. public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {
33. new LinkedHashSet<String>(availableTargetNames.size());
34. for (Integer value : shardingValue.getValues()) {
35. for (String tableName : availableTargetNames) {
36. if (tableName.endsWith(value % 2 + "")) {
37. result.add(tableName);
38. }
39. }
40. }
41. return result;
42. }
43.
44. /**
45. * sql 中关键字 匹配符为 between的时候，表的路由函数
46. */
47. public Collection<String> doBetweenSharding(Collection<String> availableTargetNames,
48. ShardingValue<Integer> shardingValue) {
49. new LinkedHashSet<String>(availableTargetNames.size());
50. Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();
51. for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
52. for (String each : availableTargetNames) {
53. if (each.endsWith(i % 2 + "")) {
54. result.add(each);
55. }
56. }
57. }
58. return result;
59. }
60.
61. }
UserSingleKeyTableShardingAlgorithm.java
1. package com.study.dangdang.sharding.jdbc.algorithm;
2.
3. import java.util.Collection;
4. import java.util.LinkedHashSet;
5.
6. import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
7. import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;
8. import com.google.common.collect.Range;
9.
10. public class UserSingleKeyTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer>{
11.
12. /**
13. * sql 中 = 操作时，table的映射
14. */
15. public String doEqualSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) {
16. for (String each : tableNames) {
17. if (each.endsWith(shardingValue.getValue() % 3 + "")) {
18. return each;
19. }
20. }
21. throw new IllegalArgumentException();
22. }
23.
24. /**
25. * sql 中 in 操作时，table的映射
26. */
27. public Collection<String> doInSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) {
28. new LinkedHashSet<String>(tableNames.size());
29. for (Integer value : shardingValue.getValues()) {
30. for (String tableName : tableNames) {
31. if (tableName.endsWith(value % 3 + "")) {
32. result.add(tableName);
33. }
34. }
35. }
36. return result;
37. }
38.
39. /**
40. * sql 中 between 操作时，table的映射
41. */
42. public Collection<String> doBetweenSharding(Collection<String> tableNames,
43. ShardingValue<Integer> shardingValue) {
44. new LinkedHashSet<String>(tableNames.size());
45. Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();
46. for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
47. for (String each : tableNames) {
48. if (each.endsWith(i % 3 + "")) {
49. result.add(each);
50. }
51. }
52. }
53. return result;
54. }
55.
56. }
spring-database.xml
1. <?xml version="1.0" encoding="UTF-8"?>
2. <beans xmlns="http://www.springframework.org/schema/beans"
3. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
4. xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"
5. xmlns:tx="http://www.springframework.org/schema/tx"
6. xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
7. http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
8. http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd
9. >
10.
11. <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
12. <property name="locations">
13. <list>
14. <value>classpath:config/resource/jdbc_dev.properties</value>
15. </list>
16. </property>
17. </bean>
18.
19. <bean name="sharding_0" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
20. <property name="url" value="${jdbc_url0}" />
21. <property name="username" value="${jdbc_username0}" />
22. <property name="password" value="${jdbc_password0}" />
23. <!-- <property name="driverClass" value="${jdbc_driver0}" /> -->
24. <!-- 初始化连接大小 -->
25. <property name="initialSize" value="0" />
26. <!-- 连接池最大使用连接数量 -->
27. <property name="maxActive" value="20" />
28. <!-- 连接池最小空闲 -->
29. <property name="minIdle" value="0" />
30. <!-- 获取连接最大等待时间 -->
31. <property name="maxWait" value="60000" />
32. <property name="validationQuery" value="${validationQuery}" />
33. <property name="testOnBorrow" value="false" />
34. <property name="testOnReturn" value="false" />
35. <property name="testWhileIdle" value="true" />
36. <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
37. <property name="timeBetweenEvictionRunsMillis" value="60000" />
38. <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
39. <property name="minEvictableIdleTimeMillis" value="25200000" />
40. <!-- 打开removeAbandoned功能 -->
41. <property name="removeAbandoned" value="true" />
42. <!-- 1800秒，也就是30分钟 -->
43. <property name="removeAbandonedTimeout" value="1800" />
44. <!-- 关闭abanded连接时输出错误日志 -->
45. <property name="logAbandoned" value="true" />
46. <property name="filters" value="stat" />
47. </bean>
48.
49. <bean name="sharding_1" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
50. <property name="url" value="${jdbc_url1}" />
51. <property name="username" value="${jdbc_username1}" />
52. <property name="password" value="${jdbc_password1}" />
53. <!-- <property name="driverClass" value="${jdbc_driver1}" /> -->
54. <!-- 初始化连接大小 -->
55. <property name="initialSize" value="0" />
56. <!-- 连接池最大使用连接数量 -->
57. <property name="maxActive" value="20" />
58. <!-- 连接池最小空闲 -->
59. <property name="minIdle" value="0" />
60. <!-- 获取连接最大等待时间 -->
61. <property name="maxWait" value="60000" />
62. <property name="validationQuery" value="${validationQuery}" />
63. <property name="testOnBorrow" value="false" />
64. <property name="testOnReturn" value="false" />
65. <property name="testWhileIdle" value="true" />
66. <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
67. <property name="timeBetweenEvictionRunsMillis" value="60000" />
68. <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
69. <property name="minEvictableIdleTimeMillis" value="25200000" />
70. <!-- 打开removeAbandoned功能 -->
71. <property name="removeAbandoned" value="true" />
72. <!-- 1800秒，也就是30分钟 -->
73. <property name="removeAbandonedTimeout" value="1800" />
74. <!-- 关闭abanded连接时输出错误日志 -->
75. <property name="logAbandoned" value="true" />
76. <property name="filters" value="stat" />
77. </bean>
78.
79.
80. </beans>
spring-sharding.xml
1. <?xml version="1.0" encoding="UTF-8"?>
2. <beans xmlns="http://www.springframework.org/schema/beans"
3. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
4. xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"
5. xmlns:tx="http://www.springframework.org/schema/tx"
6. xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
7. http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
8. http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd
9. >
10.
11. <context:component-scan base-package="com.study.dangdang.sharding.jdbc" />
12.
13.
14.
15. <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
16. <property name="basePackage" value="com.study.dangdang.sharding.jdbc.mapper"/>
17. <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
18. </bean>
19.
20. <!-- 配置sqlSessionFactory -->
21. <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
22. <property name="dataSource" ref="shardingDataSource"/>
23. <property name="mapperLocations" value="classpath*:config/mapper/*Mapper.xml"/>
24. </bean>
25.
26.
27. <!-- 配置好dataSourceRulue,即对数据源进行管理 -->
28. <bean id="dataSourceRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule">
29. <constructor-arg>
30. <map>
31. <entry key="sharding_0" value-ref="sharding_0"/>
32. <entry key="sharding_1" value-ref="sharding_1"/>
33. </map>
34. </constructor-arg>
35. </bean>
36.
37. <!-- 对t_user表的配置，进行分库配置，逻辑表名为t_user，每个库有实际的三张表 -->
38. <bean id="userTableRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.TableRule">
39. <constructor-arg value="t_user" index="0"/>
40. <constructor-arg index="1">
41. <list>
42. <value>t_user_0</value>
43. <value>t_user_1</value>
44. <value>t_user_2</value>
45. </list>
46. </constructor-arg>
47. <constructor-arg index="2" ref="dataSourceRule"/>
48. <constructor-arg index="3" ref="userDatabaseShardingStrategy"/>
49. <constructor-arg index="4" ref="userTableShardingStrategy"/>
50. </bean>
51.
52. <!-- t_user分库策略 -->
53. <bean id="userDatabaseShardingStrategy" class="com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy">
54. <constructor-arg index="0" value="user_id"/>
55. <constructor-arg index="1">
56. <bean class="com.study.dangdang.sharding.jdbc.algorithm.UserSingleKeyDatabaseShardingAlgorithm" />
57. </constructor-arg>
58. </bean>
59.
60. <!-- t_user 分表策略 -->
61. <bean id="userTableShardingStrategy" class="com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy">
62. <constructor-arg index="0" value="user_id"/>
63. <constructor-arg index="1">
64. <bean class="com.study.dangdang.sharding.jdbc.algorithm.UserSingleKeyTableShardingAlgorithm" />
65. </constructor-arg>
66. </bean>
67.
68.
69.
70. <!-- 对t_student表的配置，进行分库配置，逻辑表名为t_user，每个库有实际的三张表 -->
71. <bean id="studentTableRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.TableRule">
72. <constructor-arg value="t_student" index="0"/>
73. <constructor-arg index="1">
74. <list>
75. <value>t_student_0</value>
76. <value>t_student_1</value>
77. </list>
78. </constructor-arg>
79. <constructor-arg index="2" ref="dataSourceRule"/>
80. <constructor-arg index="3" ref="studentDatabaseShardingStrategy"/>
81. <constructor-arg index="4" ref="studentTableShardingStrategy"/>
82. </bean>
83.
84. <!-- t_student分库策略 -->
85. <bean id="studentDatabaseShardingStrategy" class="com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy">
86. <constructor-arg index="0" value="student_id"/>
87. <constructor-arg index="1">
88. <bean class="com.study.dangdang.sharding.jdbc.algorithm.StudentSingleKeyDatabaseShardingAlgorithm" />
89. </constructor-arg>
90. </bean>
91.
92. <!-- t_student 分表策略 -->
93. <bean id="studentTableShardingStrategy" class="com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy">
94. <constructor-arg index="0" value="student_id"/>
95. <constructor-arg index="1">
96. <bean class="com.study.dangdang.sharding.jdbc.algorithm.StudentSingleKeyTableShardingAlgorithm" />
97. </constructor-arg>
98. </bean>
99.
100.
101. <!-- 构成分库分表的规则 传入数据源集合和每个表的分库分表的具体规则 -->
102. <bean id="shardingRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule">
103. <constructor-arg index="0" ref="dataSourceRule"/>
104. <constructor-arg index="1">
105. <list>
106. <ref bean="userTableRule"/>
107. <ref bean="studentTableRule"/>
108. </list>
109. </constructor-arg>
110. </bean>
111.
112. <!-- 对datasource进行封装 -->
113. <bean id="shardingDataSource" class="com.dangdang.ddframe.rdb.sharding.api.ShardingDataSource">
114. <constructor-arg ref="shardingRule"/>
115. </bean>
116.
117. <!-- 事务 -->
118. <bean id="transactionManager"
119. class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
120. <property name="dataSource" ref="shardingDataSource" />
121. </bean>
122.
123. <tx:annotation-driven transaction-manager="transactionManager" />
124.
125.
126. </beans>
jdbc_dev.properties
1. jdbc_driver0 = com.mysql.jdbc.Driver
2. jdbc_url0 = jdbc:mysql://localhost:3306/sharding_0?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
3. jdbc_username0 = root
4. jdbc_password0 =
5.
6. jdbc_driver1 = com.mysql.jdbc.Driver
7. jdbc_url1 = jdbc:mysql://localhost:3306/sharding_1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
8. jdbc_username1 = root
9. jdbc_password1 =
10.
11.
12. validationQuery=SELECT 1
log4j.xml
1. <?xml version="1.0" encoding="UTF-8"?>
2. <!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN" "log4j.dtd">
3. <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
4. <!-- [控制台STDOUT] -->
5. <appender name="console" class="org.apache.log4j.ConsoleAppender">
6. <param name="encoding" value="GBK" />
7. <param name="target" value="System.out" />
8. <layout class="org.apache.log4j.PatternLayout">
9. <param name="ConversionPattern" value="%-5p %c{2} - %m%n" />
10. </layout>
11. </appender>
12.
13. <!-- [公共Appender] -->
14. <appender name="DEFAULT-APPENDER" class="org.apache.log4j.DailyRollingFileAppender">
15. <param name="File" value="${webapp.root}/logs/common-default.log" />
16. <param name="Append" value="true" />
17. <param name="encoding" value="GBK" />
18. <param name="DatePattern" value="'.'yyyy-MM-dd'.log'" />
19. <layout class="org.apache.log4j.PatternLayout">
20. <param name="ConversionPattern" value="%d %-5p %c{2} - %m%n" />
21. </layout>
22. </appender>
23.
24. <!-- [错误日志APPENDER] -->
25. <appender name="ERROR-APPENDER" class="org.apache.log4j.DailyRollingFileAppender">
26. <param name="File" value="${webapp.root}/logs/common-error.log" />
27. <param name="Append" value="true" />
28. <param name="encoding" value="GBK" />
29. <param name="threshold" value="error" />
30. <param name="DatePattern" value="'.'yyyy-MM-dd'.log'" />
31. <layout class="org.apache.log4j.PatternLayout">
32. <param name="ConversionPattern" value="%d %-5p %c{2} - %m%n" />
33. </layout>
34. </appender>
35.
36. <!-- [组件日志APPENDER] -->
37. <appender name="COMPONENT-APPENDER"
38. class="org.apache.log4j.DailyRollingFileAppender">
39. <param name="File" value="${webapp.root}/logs/logistics-component.log" />
40. <param name="Append" value="true" />
41. <param name="encoding" value="GBK" />
42. <param name="DatePattern" value="'.'yyyy-MM-dd'.log'" />
43. <layout class="org.apache.log4j.PatternLayout">
44. <param name="ConversionPattern" value="%d %-5p %c{2} - %m%n" />
45. </layout>
46. </appender>
47.
48. <!-- [组件日志] -->
49. <logger name="LOGISTICS-COMPONENT">
50. <level value="${loggingLevel}" />
51. <appender-ref ref="COMPONENT-APPENDER" />
52. <appender-ref ref="ERROR-APPENDER" />
53. </logger>
54.
55. <!-- Root Logger -->
56. <root>
57. <level value="${rootLevel}"></level>
58. <appender-ref ref="DEFAULT-APPENDER" />
59. <appender-ref ref="ERROR-APPENDER" />
60. <appender-ref ref="console" />
61. <appender-ref ref="COMPONENT-APPENDER" />
62. </root>
63. </log4j:configuration>
好了，到此为止，所有代码都贴出来了，我们开始测试：
ShardingJdbcMybatisTest.java
1. package com.study.dangdang.sharding.jdbc;
2.
3. import java.util.Arrays;
4. import java.util.List;
5.
6. import javax.annotation.Resource;
7.
8. import org.junit.Assert;
9. import org.junit.Test;
10. import org.junit.runner.RunWith;
11. import org.springframework.test.context.ContextConfiguration;
12. import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
13.
14. import com.study.dangdang.sharding.jdbc.entity.Student;
15. import com.study.dangdang.sharding.jdbc.entity.User;
16. import com.study.dangdang.sharding.jdbc.service.StudentService;
17. import com.study.dangdang.sharding.jdbc.service.UserService;
18.
19. @RunWith(SpringJUnit4ClassRunner.class)
20. @ContextConfiguration(locations = { "classpath*:config/spring/spring-database.xml",
21. "classpath*:config/spring/spring-sharding.xml" })
22. public class ShardingJdbcMybatisTest {
23.
24. @Resource
25. public UserService userService;
26.
27. @Resource
28. public StudentService studentService;
29.
30. @Test
31. public void testUserInsert() {
32. new User();
33. 11);
34. 25);
35. "github");
36. true);
37. }
38.
39. @Test
40. public void testStudentInsert() {
41. new Student();
42. 21);
43. 21);
44. "hehe");
45. true);
46. }
47.
48. @Test
49. public void testFindAll(){
50. List<User> users = userService.findAll();
51. if(null != users && !users.isEmpty()){
52. for(User u :users){
53. System.out.println(u);
54. }
55. }
56. }
57.
58. @Test
59. public void testSQLIN(){
60. 2,10,1));
61. if(null != users && !users.isEmpty()){
62. for(User u :users){
63. System.out.println(u);
64. }
65. }
66. }
67.
68. @Test
69. public void testTransactionTestSucess(){
70. userService.transactionTestSucess();
71. }
72.
73. @Test(expected = IllegalAccessException.class)
74. public void testTransactionTestFailure() throws IllegalAccessException{
75. userService.transactionTestFailure();
76. }
77.
78.
79. }
testUserInsert的运行结果是：
替换了我们sql中的数据源和表名
testFindAll的运行结果是：
注意：看日记，一共发出了6条sql，也就是说每个库的每个表都发出一条sql，在平常开发中，这种sql还是少执行，会很大程度上降低性能
testSQLIN的运行结果是：
注意：根据id的路由规则，定位到表后，其实此时已经知道该表中的id了，没必要用3个参数的in了，sharding-jdbc这边还不是很智能的，虽然IN也是支持索引的~有待更进一步的优化
最后事务测试也是没有问题的~
大家自己动手来一遍吧~