`
jin8000608172
  • 浏览: 139581 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

学习sharding-jdbc(二)之spring+mybatis+sharding-jdbc整合

阅读更多
引用
http://blog.csdn.net/linuu/article/details/50929904



新建Maven项目


pom.xml
[html] view plain copy
<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>org.study</groupId> 
  <artifactId>sharding-jdbc-mybatis</artifactId> 
  <version>0.0.1-SNAPSHOT</version> 
  <packaging>jar</packaging> 
 
  <name>sharding-jdbc-mybatis</name> 
  <url>http://maven.apache.org</url> 
 
<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>com.dangdang</groupId> 
            <artifactId>sharding-jdbc-core</artifactId> 
            <version>1.0.0</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>mysql</groupId> 
            <artifactId>mysql-connector-java</artifactId> 
            <version>5.1.28</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> 
    </dependencies> 
</project> 

新建2个库,sharding_0和sharding_1


分别在这2个库运行sql:
[java] view plain copy
/*
Navicat MySQL Data Transfer

Source Server         : PMS
Source Server Version : 50624
Source Host           : localhost:3306
Source Database       : sharding_0

Target Server Type    : MYSQL
Target Server Version : 50624
File Encoding         : 65001

Date: 2016-03-19 14:18:22
*/ 
 
SET FOREIGN_KEY_CHECKS=0; 
 
-- ---------------------------- 
-- Table structure for t_student_0 
-- ---------------------------- 
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_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; 
 
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; 
 
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; 
Student.Java
[java] view plain copy
package com.study.dangdang.sharding.jdbc.entity; 
 
import java.io.Serializable; 
 
public class Student implements Serializable{ 
     
    /**
     * 
     */ 
    private static final long serialVersionUID = 8920597824668331209L; 
 
    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; 
    } 
     

User.java
[java] view plain copy
package com.study.dangdang.sharding.jdbc.entity; 
 
import java.io.Serializable; 
 
public class User implements Serializable{ 
     
    /**
     * 
     */ 
    private static final long serialVersionUID = 1L; 
     
 
    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 + "]"; 
    } 
     


StudentMapper.java
[java] view plain copy
package com.study.dangdang.sharding.jdbc.mapper; 
 
import java.util.List; 
 
import com.study.dangdang.sharding.jdbc.entity.Student; 
 
public interface StudentMapper { 
     
    Integer insert(Student s); 
     
    List<Student> findAll(); 
     
    List<Student> findByStudentIds(List<Integer> studentIds); 
 


UserMapper.java
[java] view plain copy
package com.study.dangdang.sharding.jdbc.mapper; 
 
import java.util.List; 
 
import com.study.dangdang.sharding.jdbc.entity.User; 
 
public interface UserMapper { 
     
    Integer insert(User u); 
     
    List<User> findAll(); 
     
    List<User> findByUserIds(List<Integer> userIds); 
     
 


StudentMapper.xml
[html] view plain copy
<?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.study.dangdang.sharding.jdbc.mapper.StudentMapper" > 
  <resultMap id="resultMap" type="com.study.dangdang.sharding.jdbc.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> 

UserMapper.xml
[html] view plain copy
<?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.study.dangdang.sharding.jdbc.mapper.UserMapper" > 
  <resultMap id="resultMap" type="com.study.dangdang.sharding.jdbc.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> 

StudentService.java
[java] view plain copy
package com.study.dangdang.sharding.jdbc.service; 
 
import com.study.dangdang.sharding.jdbc.entity.Student; 
 
public interface StudentService { 
 
    boolean insert(Student student); 
 


UserService.java
[java] view plain copy
package com.study.dangdang.sharding.jdbc.service; 
 
import java.util.List; 
 
import com.study.dangdang.sharding.jdbc.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; 
 


StudentServiceImpl.java
[java] view plain copy
package com.study.dangdang.sharding.jdbc.service.impl; 
 
import javax.annotation.Resource; 
 
import org.springframework.stereotype.Service; 
 
import com.study.dangdang.sharding.jdbc.entity.Student; 
import com.study.dangdang.sharding.jdbc.mapper.StudentMapper; 
import com.study.dangdang.sharding.jdbc.service.StudentService; 
 
@Service 
public class StudentServiceImpl implements StudentService{ 
     
    @Resource 
    public StudentMapper studentMapper; 
 
    public boolean insert(Student student) { 
        return studentMapper.insert(student) > 0 ? true : false; 
    } 
 


UserServiceImpl.java
[java] view plain copy
package com.study.dangdang.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.study.dangdang.sharding.jdbc.entity.Student; 
import com.study.dangdang.sharding.jdbc.entity.User; 
import com.study.dangdang.sharding.jdbc.mapper.StudentMapper; 
import com.study.dangdang.sharding.jdbc.mapper.UserMapper; 
import com.study.dangdang.sharding.jdbc.service.UserService; 
 
@Service 
@Transactional 
public class UserServiceImpl implements UserService { 
 
    @Resource 
    public UserMapper userMapper; 
     
    @Resource 
    public StudentMapper studentMapper; 
 
    public boolean insert(User u) { 
        return userMapper.insert(u) > 0 ? true :false; 
    } 
 
    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(); 
    } 
     


StudentSingleKeyDatabaseShardingAlgorithm.java
[java] view plain copy
package com.study.dangdang.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 StudentSingleKeyDatabaseShardingAlgorithm 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; 
    } 
 

StudentSingleKeyTableShardingAlgorithm.java
[java] view plain copy
package com.study.dangdang.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; 
 
/**
* 因为t_student实际表在每个库中只有2个,所以 %2
* @author lyncc
*
*/ 
public class StudentSingleKeyTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer>{ 
 
    /**
     * sql 中 = 操作时,table的映射
     */ 
    public String doEqualSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) { 
        for (String each : tableNames) { 
            if (each.endsWith(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(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(i % 2 + "")) { 
                    result.add(each); 
                } 
            } 
        } 
        return result; 
    } 
 

UserSingleKeyDatabaseShardingAlgorithm.java
[java] view plain copy
package com.study.dangdang.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
[java] view plain copy
package com.study.dangdang.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; 
    } 
 


spring-database.xml
[html] view plain copy
<?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 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> 

spring-sharding.xml
[html] view plain copy
<?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"> 
         
    <context:component-scan base-package="com.study.dangdang.sharding.jdbc" /> 
     
     
     
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> 
        <property name="basePackage" value="com.study.dangdang.sharding.jdbc.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_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.study.dangdang.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.study.dangdang.sharding.jdbc.algorithm.UserSingleKeyTableShardingAlgorithm" /> 
        </constructor-arg> 
    </bean> 
     
     
     
      <!-- 对t_student表的配置,进行分库配置,逻辑表名为t_user,每个库有实际的三张表 --> 
    <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_0</value> 
                <value>t_student_1</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.study.dangdang.sharding.jdbc.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.study.dangdang.sharding.jdbc.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> 
jdbc_dev.properties
[plain] view plain copy
jdbc_driver0   = com.mysql.jdbc.Driver 
jdbc_url0      = jdbc:mysql://localhost:3306/sharding_0?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true 
jdbc_username0 = root 
jdbc_password0 =  
 
jdbc_driver1   = com.mysql.jdbc.Driver 
jdbc_url1      = jdbc:mysql://localhost:3306/sharding_1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true 
jdbc_username1 = root 
jdbc_password1 =  
 
 
validationQuery=SELECT 1 

log4j.xml
[html] view plain copy
<?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>   

好了,到此为止,所有代码都贴出来了,我们开始测试:
ShardingJdbcMybatisTest.java
[java] view plain copy
package com.study.dangdang.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.study.dangdang.sharding.jdbc.entity.Student; 
import com.study.dangdang.sharding.jdbc.entity.User; 
import com.study.dangdang.sharding.jdbc.service.StudentService; 
import com.study.dangdang.sharding.jdbc.service.UserService; 
 
@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(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(); 
    } 
     
     

testUserInsert的运行结果是:

替换了我们sql中的数据源和表名

testFindAll的运行结果是:

注意:看日记,一共发出了6条sql,也就是说每个库的每个表都发出一条sql,在平常开发中,这种sql还是少执行,会很大程度上降低性能

testSQLIN的运行结果是:

注意:根据id的路由规则,定位到表后,其实此时已经知道该表中的id了,没必要用3个参数的in了,sharding-jdbc这边还不是很智能的,虽然IN也是支持索引的~有待更进一步的优化


最后事务测试也是没有问题的~
大家自己动手来一遍吧~

代码下载地址:http://download.csdn.NET/detail/linuu/9466514
分享到:
评论

相关推荐

    SpringBoot+Mybatis-Plus整合Sharding-JDBC5.1.1实现单库分表【全网最新】.doc

    ### SpringBoot+Mybatis-Plus 整合 Sharding-JDBC5.1.1 实现单库分表 #### 一、前言与背景 在现代软件开发中,随着业务量的增长,单一数据库往往难以满足高性能、高并发的需求,因此分库分表成为了一种常见的解决...

    spring+mybatis+sharding-jdbc

    【标题】"spring+mybatis+sharding-jdbc"是一个基于Spring框架,结合MyBatis持久层框架和Sharding-JDBC分库分表组件的示例项目。该项目旨在展示如何在实际开发中处理大数据量场景下的数据库扩展问题,通过水平扩展...

    spring+mybatis+sharding-jdbc 整合

    &lt;property name="url" value="jdbc:shardingjdbc:proxy:mysql://localhost:3306/sharding?serverTimezone=UTC"/&gt; ``` 5. **编写Mapper接口和XML**:如同普通的MyBatis项目,你需要创建Mapper接口和...

    spring+mybatis+sharding-jdbc 1.3.1实现分库分表案例(可直接运行)

    &lt;bean id="shardingDataSource" class="org.apache.shardingsphere.shardingjdbc.spring.boot.jta.datasource.SpringBootJtaShardingDataSource"&gt; &lt;property name="location" value="classpath:sharding-...

    Springboot2+JPA|MyBatis+Sharding-JDBC示例

    在IT行业中,Spring Boot、JPA(Java Persistence API)、MyBatis和Sharding-JDBC都是极为重要的技术组件,尤其在构建高效、可扩展的微服务架构时。下面将详细阐述这些技术及其在"Springboot2+JPA|MyBatis+Sharding-...

    springboot整合sharding-jdbc完整代码

    SpringBoot整合Sharding-JDBC是将Sharding-JDBC这一分布式数据库中间件与SpringBoot框架结合,以实现数据分片、读写分离等高级数据库管理功能。这个完整的代码示例覆盖了Sharding-JDBC的主要技术点,使开发者可以...

    当当开源sharding-jdbc-轻量级数据库分库分表中间件

    1. **高度兼容性**:Sharding-JDBC能够无缝集成到现有的基于Java的ORM框架中,如JPA、Hibernate、Mybatis、SpringJDBCTemplate等,或者直接使用JDBC进行操作,这极大地降低了代码迁移的成本。 2. **灵活性与扩展性...

    spring-boot+sharding-jdbc+mybatis+druid

    spring boot的配置文件配置无法具体配置druid连接池的各种属性(官网上我没找到),所以采用java类的方式配置,包括druid的wallFilter、statFilter、statViewServlet、webStatFilter等等

    Spring Boot 集成 Sharding-JDBC + Mybatis-Plus 实现分库分表功能

    本篇文章将详细讲解如何使用Spring Boot结合Sharding-JDBC和Mybatis-Plus实现分库分表功能。 一、Sharding-JDBC简介 Sharding-JDBC是由Apache ShardingSphere项目提供的一个轻量级数据库中间件,它作为JDBC驱动...

    Sharding-JDBC教程:Spring Boot整合Sharding-JDBC实现读写分离.docx

    ### Sharding-JDBC 教程:Spring Boot 整合 Sharding-JDBC 实现读写分离 #### 一、Sharding-JDBC 和 Spring Boot 的简介 **Sharding-JDBC** 是阿里巴巴开源的一个轻量级 Java 框架,主要用于对数据库进行分片、...

    该项目主要采用springboot2.x+sharding -spring-boot-sharding-jdbc.zip

    在SpringBoot应用中,Sharding-JDBC的使用与常规JDBC操作类似,可以直接使用JdbcTemplate或者MyBatis等ORM框架。Sharding-JDBC会自动处理分片逻辑,用户无需关心底层细节。 五、注意事项 1. 分片键的选择:选择对...

    sharding-jdbc之——分库分表实例完整源码

    标题"sharding-jdbc之——分库分表实例完整源码"指出了本主题的核心,即`Sharding-JDBC`在实现数据库分库分表中的应用。Sharding-JDBC是阿里巴巴开源的轻量级Java框架,它可以在不修改现有数据库的情况下,对数据库...

    sharding-jdbc-4.0.0-rc1 整合spring boot 2+mybatis plus 分库分表Demo

    在本项目中,我们主要探讨如何使用`sharding-jdbc-4.0.0-rc1`这个版本的ShardingSphere来整合`Spring Boot 2`框架和`MyBatis Plus`,实现数据库的分库分表功能。下面将详细阐述这一过程中的关键知识点。 1. **...

    spring4.0.2+mybatis3.2.4+sharding-jdbc1.4.2实现分库分表、主从及事物支持

    本项目基于“spring4.0.2+mybatis3.2.4+sharding-jdbc1.4.2”搭建了一个实现分库分表、主从复制以及事务管理的解决方案。下面将详细讲解这个集成框架中的关键知识点。 首先,Spring 4.0.2 是一个广泛使用的Java应用...

    sharding-jdbc-master.zip 附完整代码可供参考

    【标题】"Sharding-JDBC-Master.zip" 提供了一个完整的示例,展示了如何在实际项目中使用Sharding-JDBC实现MyBatis的分库分表功能。这个压缩包包含了Sharding-JDBC的核心代码,方便开发者参考和学习。 【描述】中...

    springBoot和sharding-jdbc、mybatis整合demo

    SpringBoot和Sharding-JDBC、MyBatis的整合是一个常见的数据分片与微服务架构中的实践,这使得大型应用能够有效地处理高并发和大数据量的情况。在这个名为"spring-boot-sharding-jdbc-master"的项目中,我们将探讨...

    sharding-jdbc.rar

    《基于Sharding-JDBC的SpringBoot+Mybatis整合实践》 在现代的互联网应用中,随着数据量的急剧增长,数据库的水平扩展成为了一个至关重要的问题。Sharding-JDBC作为一个轻量级的Java框架,提供了数据库分片的功能,...

    sharding-jdbc-demo

    【标题】"sharding-jdbc-demo" 是一个基于Sharding-JDBC、SpringBoot、MyBatis和Druid的示例项目,旨在展示如何在Java环境中整合这些组件来实现数据库分片和微服务架构。 【描述】这个项目的核心是利用Sharding-...

    Spring Mybatis Sharding-JDBC demo

    ### Spring Mybatis Sharding-JDBC 示例详解 #### 一、Sharding-JDBC 概述 在分布式系统架构中,随着业务量的增长,单个数据库往往难以承受巨大的读写压力,因此需要采用数据库分片(Sharding)技术来提高系统的可...

    sharding-jdbc

    压缩包中的“sharding jdbc”很可能包含了Sharding-JDBC的示例代码,这些示例通常会展示如何配置Sharding-JDBC、如何定义分片策略,以及如何在实际的Spring或MyBatis应用中使用这些配置。通过学习和运行这些示例,...

Global site tag (gtag.js) - Google Analytics