基于sharding-jdbc 之spring+mybatis+sharding-jdbc整合

基于sharding-jdbc 之spring+mybatis+sharding-jdbc整合

官方介绍文档： http://dangdangdotcom.github.io/sharding-jdbc/00-overview/

本文参考： http://blog.csdn.net/clypm/article/details/54378523

 

0.环境

ip :192.168.1.121

端口号：3306数据库1：sharding_0

数据库2：sharding_1

 

1.创建多个分库

create database sharding_0;
create database sharding_1;

 

2.在各个分库上，创建多张分表

 

SET FOREIGN_KEY_CHECKS=0; 
-- 逻辑表 t_user
-- 分表：t_user_0
DROP TABLE IF EXISTS `t_user_0`; 
CREATE TABLE `t_user_0` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `user_id` int(11) NOT NULL, 
  `name` varchar(255) NOT NULL, 
  `age` int(11) NOT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
-- 分表：t_user_1
DROP TABLE IF EXISTS `t_user_1`; 
CREATE TABLE `t_user_1` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `user_id` int(11) NOT NULL, 
  `name` varchar(255) NOT NULL, 
  `age` int(11) NOT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

-- 分表： t_user_2 
DROP TABLE IF EXISTS `t_user_2`; 
CREATE TABLE `t_user_2` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `user_id` int(11) NOT NULL, 
  `name` varchar(255) NOT NULL, 
  `age` int(11) NOT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

 
DROP TABLE IF EXISTS `t_student_0`; 
CREATE TABLE `t_student_0` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `student_id` int(11) NOT NULL, 
  `name` varchar(255) NOT NULL, 
  `age` int(11) NOT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8; 
 
DROP TABLE IF EXISTS `t_student_1`; 
CREATE TABLE `t_student_1` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `student_id` int(11) NOT NULL, 
  `name` varchar(255) NOT NULL, 
  `age` int(11) NOT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8; 

-- 学生表：测试事务时使用
DROP TABLE IF EXISTS `t_student_0`;
CREATE TABLE `t_student_0` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `t_student_1`;
CREATE TABLE `t_student_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

 

3.案例：sharding-jdbc-mybatis-demo

3.1 组件

需要jar包：见 pom.xml ，加粗的标识

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.eshop</groupId>
    <artifactId>sharding-jdbc-mybatis-demo</artifactId> 
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

   <properties>  
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>  
        <spring.version>3.2.5.RELEASE</spring.version>  
        <mybatis.version>3.2.4</mybatis.version>  
    </properties>  
  
    <dependencies>  
        <dependency>  
            <groupId>junit</groupId>  
            <artifactId>junit</artifactId>  
            <version>4.10</version>  
        </dependency>  
        
        <dependency>  
            <groupId>org.springframework</groupId>  
            <artifactId>spring-orm</artifactId>  
            <version>${spring.version}</version>  
        </dependency>  
        <dependency>  
            <groupId>commons-dbcp</groupId>  
            <artifactId>commons-dbcp</artifactId>  
            <version>1.4</version>  
        </dependency>  
        <dependency>  
            <groupId>org.mybatis</groupId>  
            <artifactId>mybatis-spring</artifactId>  
            <version>1.2.2</version>  
        </dependency>  
        <dependency>  
            <groupId>org.mybatis</groupId>  
            <artifactId>mybatis</artifactId>  
            <version>${mybatis.version}</version>  
        </dependency>  
  
        <dependency>  
            <groupId>org.springframework</groupId>  
            <artifactId>spring-expression</artifactId>  
            <version>${spring.version}</version>  
        </dependency>  
        <dependency>  
            <groupId>org.springframework</groupId>  
            <artifactId>spring-aop</artifactId>  
            <version>${spring.version}</version>  
        </dependency>  
        <dependency>  
            <groupId>org.springframework</groupId>  
            <artifactId>spring-beans</artifactId>  
            <version>${spring.version}</version>  
        </dependency>  
        <dependency>  
            <groupId>org.springframework</groupId>  
            <artifactId>spring-context</artifactId>  
            <version>${spring.version}</version>  
        </dependency>  
        <dependency>  
            <groupId>org.springframework</groupId>  
            <artifactId>spring-context-support</artifactId>  
            <version>${spring.version}</version>  
        </dependency>  
        <dependency>  
            <groupId>org.springframework</groupId>  
            <artifactId>spring-test</artifactId>  
            <version>${spring.version}</version>  
        </dependency>  
        <dependency>  
            <groupId>org.springframework</groupId>  
            <artifactId>spring-tx</artifactId>  
            <version>${spring.version}</version>  
        </dependency>  
        <dependency>  
            <groupId>log4j</groupId>  
            <artifactId>log4j</artifactId>  
            <version>1.2.16</version>  
        </dependency>  
        <dependency>  
            <groupId>org.slf4j</groupId>  
            <artifactId>slf4j-log4j12</artifactId>  
            <version>1.7.5</version>  
        </dependency>  
        
        <dependency> 
        	<groupId>org.springframework</groupId> 
        	<artifactId>spring-webmvc</artifactId> 
        	<version>3.2.4.RELEASE</version> 
        </dependency>
        
        <!-- 分库分表使用的Dangdang Sharding-jdbc -->
        <dependency>  
            <groupId>com.dangdang</groupId>  
            <artifactId>sharding-jdbc-core</artifactId>  
            <version>1.4.2</version> 
        </dependency>  
        
   		<dependency>  
             <groupId>com.dangdang</groupId>  
             <artifactId>sharding-jdbc-config-spring</artifactId>  
             <version>1.4.2</version>  
        </dependency>
        
        <dependency>  
            <groupId>mysql</groupId>  
            <artifactId>mysql-connector-java</artifactId>  
            <version>5.1.28</version>  
        </dependency>    

	<dependency>
		<groupId>javax.servlet</groupId>
		<artifactId>servlet-api</artifactId>
		<version>2.5</version>
	</dependency>          
        
    </dependencies>   
</project>  

 错误分析：

 <!-- sharding-jdbc-core 现在用这个最新版本1.4.2会报错，暂时用1.0.0，待研究-->