MyException - 我的异常网
当前位置:我的异常网» 数据库 » 基于sharding-jdbc 之spring+mybatis+sharding-jdbc

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

www.MyException.Cn  网友分享于:2013-08-22  浏览:0次
基于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,待研究-->
Unsatisfied dependency expressed through constructor argument with index 1 of type [boolean]: Could not convert constructor argument value of type [java.util.ArrayList] to required type [boolean]
 问题解决:在rdb的配置方法里,就可以使用,而且分布式主键的包需要1.4.2版本

3.2 主配置文件applicationContext.xml

<?xml version="1.0" encoding="utf-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="
		http://www.springframework.org/schema/beans 
		http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context
		http://www.springframework.org/schema/context/spring-context.xsd">

	<context:annotation-config />
	<context:component-scan base-package="com.eshop.sharding.jdbc.*" />

	<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="location" value="classpath:bp.properties" />
	</bean>

	<import resource="spring-database.xml" />
	<import resource="spring-sharding.xml" />

</beans>
 

3.3 配置文件spring-database.xml

<?xml version="1.0" encoding="UTF-8"?>  
<beans xmlns="http://www.springframework.org/schema/beans"  
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"  
    xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"  
    xmlns:tx="http://www.springframework.org/schema/tx"  
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd  
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd  
        http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd  
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd">  
          
	<!-- 基础数据源 -->
	<bean id="abstractDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
	    <property name="driverClassName" value="${jdbc_driver_0}" /> 
        <!-- 初始化连接大小 -->  
        <property name="initialSize" value="0" />  
        <!-- 连接池最大使用连接数量 -->  
        <property name="maxActive" value="20" />  
        <!-- 连接池最小空闲 -->  
        <property name="minIdle" value="0" />  
        <!-- 获取连接最大等待时间 -->  
        <property name="maxWait" value="60000" />  
        <property name="validationQuery" value="${validationQuery}" />  
        <property name="testOnBorrow" value="false" />  
        <property name="testOnReturn" value="false" />  
        <property name="testWhileIdle" value="true" />  
        <!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->  
        <property name="timeBetweenEvictionRunsMillis" value="60000" />  
        <!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->  
        <property name="minEvictableIdleTimeMillis" value="25200000" />  
        <!-- 打开removeAbandoned功能 -->  
        <property name="removeAbandoned" value="true" />  
        <!-- 1800秒,也就是30分钟 -->  
        <property name="removeAbandonedTimeout" value="1800" />  
        <!-- 关闭abanded连接时输出错误日志 -->  
        <property name="logAbandoned" value="true" />  
        <property name="filters" value="stat" /> 
    </bean>    
      
          
    <bean id="sharding_0" class="com.alibaba.druid.pool.DruidDataSource" parent="abstractDataSource">
        <property name="url" value="${jdbc_url0}" />  
        <property name="username" value="${jdbc_username0}" />  
        <property name="password" value="${jdbc_password0}" />  
    </bean>
      
    <bean id="sharding_1" class="com.alibaba.druid.pool.DruidDataSource" parent="abstractDataSource">
        <property name="url" value="${jdbc_url1}" />  
        <property name="username" value="${jdbc_username1}" />  
        <property name="password" value="${jdbc_password1}" />  
    </bean>
  
</beans> 
 

3.4 配置文件spring-sharding.xml

<?xml version="1.0" encoding="UTF-8"?>  
<beans xmlns="http://www.springframework.org/schema/beans"  
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"  
    xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"  
    xmlns:tx="http://www.springframework.org/schema/tx"  
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd  
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd  
        http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd  
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd">  
          
	<bean id="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
		<property name="basePackage" value="com.eshop.sharding.jdbc.dao" />
		<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
	</bean>
    
     <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">  
        <property name="dataSource" ref="shardingDataSource"/>  
        <property name="mapperLocations" value="classpath*:mappings/*Mapper.xml"/>
    </bean>
      
    <!-- 读写分离 
    <rdb:master-slave-data-source id="dataSource_0" master-data-source-ref="sharding_0" slave-data-sources-ref="sharding_2 "/>-->

    <bean id="shardingDataSource" class="com.dangdang.ddframe.rdb.sharding.api.ShardingDataSource">  
        <constructor-arg ref="shardingRule"/>  
    </bean>
    
    <!-- 构成分库分表的规则 传入数据源集合和每个表的分库分表的具体规则 -->  
    <bean id="shardingRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule">  
        <constructor-arg index="0" ref="dataSourceRule"/>  
        <constructor-arg index="1">  
            <list>  
                <ref bean="userTableRule"/>  
                <ref bean="studentTableRule"/>  
            </list>  
        </constructor-arg>  
    </bean>   
    
    <!-- 配置好dataSourceRulue,即对数据源进行管理 -->  
    <bean id="dataSourceRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule">  
        <constructor-arg>  
            <map>  
                <entry key="sharding_0" value-ref="sharding_0"/>  
                <entry key="sharding_1" value-ref="sharding_1"/>          
            </map>  
        </constructor-arg>  
    </bean>        
      
    <!-- t_user表的分库分表配置 -->  
    <bean id="userTableRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.TableRule">  
        <constructor-arg value="t_user" index="0"/>  <!-- 逻辑表名 -->
        <constructor-arg index="1">  <!-- 物理表列表 -->
            <list>  
                <value>t_user_0</value>  
                <value>t_user_1</value>  
                <value>t_user_2</value>  
            </list>  
        </constructor-arg>  
        <constructor-arg index="2" ref="dataSourceRule"/>  <!-- 数据源管理 -->
        <constructor-arg index="3" ref="userDatabaseShardingStrategy"/> <!-- 分库策略 --> 
        <constructor-arg index="4" ref="userTableShardingStrategy"/>  <!-- 分表策略 --> 
    </bean>  
      
    <!-- t_user分库策略 -->  
    <bean id="userDatabaseShardingStrategy" class="com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy">  
        <constructor-arg index="0" value="user_id"/>  <!-- 分库字段 -->
        <constructor-arg index="1">  <!-- 分库策略 -->
            <bean class="com.eshop.sharding.jdbc.algorithm.UserSingleKeyDatabaseShardingAlgorithm" />  
        </constructor-arg>  
    </bean>  
      
    <!-- t_user 分表策略 -->  
    <bean id="userTableShardingStrategy" class="com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy">  
        <constructor-arg index="0" value="user_id"/><!-- 分表字段 -->
        <constructor-arg index="1"><!-- 分表策略 -->
            <bean class="com.eshop.sharding.jdbc.algorithm.UserSingleKeyTableShardingAlgorithm" />  
        </constructor-arg>  
    </bean>  
    
    <!-- 事务 -->  
    <bean id="transactionManager"  
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">  
        <property name="dataSource" ref="shardingDataSource" />  
    </bean>  
  
    <tx:annotation-driven transaction-manager="transactionManager" />  
          
</beans>  
 

3.5 log.xml

<?xml version="1.0" encoding="UTF-8"?>      
<!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN" "log4j.dtd">    
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">    
  <!-- [控制台STDOUT] -->    
  <appender name="console" class="org.apache.log4j.ConsoleAppender">    
     <param name="encoding" value="GBK" />    
     <param name="target" value="System.out" />    
     <layout class="org.apache.log4j.PatternLayout">    
       <param name="ConversionPattern" value="%-5p %c{2} - %m%n" />    
     </layout>    
  </appender>    
    
  <!-- [公共Appender] -->    
  <appender name="DEFAULT-APPENDER" class="org.apache.log4j.DailyRollingFileAppender">    
     <param name="File" value="${webapp.root}/logs/common-default.log" />    
     <param name="Append" value="true" />    
     <param name="encoding" value="GBK" />    
     <param name="DatePattern" value="'.'yyyy-MM-dd'.log'" />    
     <layout class="org.apache.log4j.PatternLayout">    
    <param name="ConversionPattern" value="%d %-5p %c{2} - %m%n" />    
     </layout>    
   </appender>    
    
   <!-- [错误日志APPENDER] -->    
   <appender name="ERROR-APPENDER" class="org.apache.log4j.DailyRollingFileAppender">    
     <param name="File" value="${webapp.root}/logs/common-error.log" />    
     <param name="Append" value="true" />    
     <param name="encoding" value="GBK" />    
     <param name="threshold" value="error" />    
     <param name="DatePattern" value="'.'yyyy-MM-dd'.log'" />    
     <layout class="org.apache.log4j.PatternLayout">    
        <param name="ConversionPattern" value="%d %-5p %c{2} - %m%n" />    
     </layout>    
   </appender>    
    
   <!-- [组件日志APPENDER] -->    
   <appender name="COMPONENT-APPENDER"    
class="org.apache.log4j.DailyRollingFileAppender">    
     <param name="File" value="${webapp.root}/logs/logistics-component.log" />    
     <param name="Append" value="true" />    
     <param name="encoding" value="GBK" />    
     <param name="DatePattern" value="'.'yyyy-MM-dd'.log'" />    
     <layout class="org.apache.log4j.PatternLayout">    
    <param name="ConversionPattern" value="%d %-5p %c{2} - %m%n" />    
     </layout>    
   </appender>    
    
   <!-- [组件日志] -->    
   <logger name="LOGISTICS-COMPONENT">    
      <level value="${loggingLevel}" />    
      <appender-ref ref="COMPONENT-APPENDER" />    
      <appender-ref ref="ERROR-APPENDER" />    
   </logger>    
    
   <!-- Root Logger -->    
   <root>    
       <level value="${rootLevel}"></level>    
       <appender-ref ref="DEFAULT-APPENDER" />    
       <appender-ref ref="ERROR-APPENDER" />    
       <appender-ref ref="console" />   
       <appender-ref ref="COMPONENT-APPENDER" />   
   </root>    
</log4j:configuration>  

 

 

4.相关的类

 

4.1ShardingTmInterceptor

在跨库查询时,必须加这个类实现HandlerInterceptorAdapter,否则报错

package com.eshop.sharding.jdbc.aop;

import java.util.Date;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.AbstractPlatformTransactionManager;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.handler.HandlerInterceptorAdapter;

public class ShardingTmInterceptor extends HandlerInterceptorAdapter  {

	@Override
	public void afterCompletion(HttpServletRequest request,
			HttpServletResponse response, Object handler, Exception ex)
			throws Exception {
		// TODO Auto-generated method stub
		super.afterCompletion(request, response, handler, ex);
	}

	@Override
	public void afterConcurrentHandlingStarted(HttpServletRequest request,
			HttpServletResponse response, Object handler) throws Exception {
		// TODO Auto-generated method stub
		super.afterConcurrentHandlingStarted(request, response, handler);
	}

	@Override
	public void postHandle(HttpServletRequest request,
			HttpServletResponse response, Object handler,
			ModelAndView modelAndView) throws Exception {
		// TODO Auto-generated method stub
		super.postHandle(request, response, handler, modelAndView);
	}

	@Override
	public boolean preHandle(HttpServletRequest request,
			HttpServletResponse response, Object handler) throws Exception {
		
		AbstractApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
		//获取分库分表数据源
		DataSource shardingDataSource = (DataSource) ctx.getBean("shardingDataSource");
		//创建jdbcTemplate
		final JdbcTemplate jdbcTemplate = new JdbcTemplate(shardingDataSource);
		//获取事务管理器
		AbstractPlatformTransactionManager transactionManager = (AbstractPlatformTransactionManager) ctx.getBean("transactionManager");
		//创建事务模板
		TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);
		//执行SQL(product是逻辑表名,id是分库分表键)
		transactionTemplate.execute(new TransactionCallbackWithoutResult() {
			
			@Override
			protected void doInTransactionWithoutResult(TransactionStatus arg0) {
				//HintManager hintManager = HintManager.getInstance();
				//hintManager.setMasterRouteOnly();
				
				String sql = "insert into product(id,title,last_modified) values(?,?,?)";
				jdbcTemplate.update(sql,1L,"title",new Date());
				
				//jdbcTemplate.queryForList("select id,title from product where id = ?",1L);
				
			}
		});
		
		
		return super.preHandle(request, response, handler);
	}
	
}

 

4.2  持久层接口类

UserDao

package com.eshop.sharding.jdbc.dao;  
  
import java.util.List;

import com.eshop.sharding.jdbc.domain.User;
  
public interface UserDao {
      
    Integer insert(User u);  
      
    List<User> findAll();  
      
    List<User> findByUserIds(List<Integer> userIds);  
      
  
} 

 对应的Mapper文件UserDao.xml

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >  
<mapper namespace="com.eshop.sharding.jdbc.dao.UserDao" >
<!-- namespace必须写成对应的接口类 ->
  <resultMap id="resultMap" type="com.eshop.sharding.jdbc.domain.User" >  
	    <id column="id" property="id" jdbcType="INTEGER" />  
	    <result column="user_id" property="userId" jdbcType="INTEGER" />  
	    <result column="name" property="name" jdbcType="VARCHAR" />  
	    <result column="age" property="age" jdbcType="INTEGER" />  
  </resultMap> 
  
  <sql id="columnsName">  
     id,user_id,name,age  
  </sql>   
    
  <insert id="insert">  
    insert into t_user (user_id,name,age) values (#{userId},#{name},#{age})  
  </insert>  
    
  <select id="findAll" resultMap="resultMap">  
   select <include refid="columnsName"/> from t_user   
  </select>  
    
  <select id="findByUserIds" resultMap="resultMap">  
    select <include refid="columnsName"/> from t_user where user_id in (  
     <foreach collection="list" item="item" separator=",">  
        #{item}  
     </foreach>  
    )  
      
  </select>  
    
</mapper> 

 

4.3 分库/分表策略类

UserSingleKeyDatabaseShardingAlgorithm.java

package com.eshop.sharding.jdbc.algorithm;  
  
import java.util.Collection;  
import java.util.LinkedHashSet;
  
import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;  
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;  
import com.google.common.collect.Range;  
  
/** 
 * USER表分库的逻辑函数 
 * @author lyncc 
 * 
 */  
public class UserSingleKeyDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Integer>{  
  
    /** 
     * sql 中关键字 匹配符为 =的时候,表的路由函数
     */  
    public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {  
        for (String each : availableTargetNames) {  
            if (each.endsWith(shardingValue.getValue() % 2 + "")) {  
                return each;  
            }  
        }  
        throw new IllegalArgumentException();  
    }  
  
    /** 
     * sql 中关键字 匹配符为 in 的时候,表的路由函数 
     */  
    public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {  
        Collection<String> result = new LinkedHashSet<String>(availableTargetNames.size());  
        for (Integer value : shardingValue.getValues()) {  
            for (String tableName : availableTargetNames) {  
                if (tableName.endsWith(value % 2 + "")) {  
                    result.add(tableName);  
                }  
            }  
        }  
        return result;  
    }  
  
    /** 
     * sql 中关键字 匹配符为 between的时候,表的路由函数 
     */  
    public Collection<String> doBetweenSharding(Collection<String> availableTargetNames,  
            ShardingValue<Integer> shardingValue) {  
        Collection<String> result = new LinkedHashSet<String>(availableTargetNames.size());  
        Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();  
        for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {  
            for (String each : availableTargetNames) {  
                if (each.endsWith(i % 2 + "")) {  
                    result.add(each);  
                }  
            }  
        }  
        return result;  
    }  
  
}  

 

UserSingleKeyTableShardingAlgorithm.java

package com.eshop.sharding.jdbc.algorithm;  

import java.util.Collection;  
import java.util.LinkedHashSet;  
  
import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;  
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;  
import com.google.common.collect.Range;  
  
public class UserSingleKeyTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer>{  
  
    /** 
     * sql 中 = 操作时,table的映射 
     */  
    public String doEqualSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) {  
        for (String each : tableNames) {  
            if (each.endsWith(shardingValue.getValue() % 3 + "")) {  
                return each;  
            }  
        }  
        throw new IllegalArgumentException();  
    }  
  
    /** 
     * sql 中 in 操作时,table的映射 
     */  
    public Collection<String> doInSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) {  
        Collection<String> result = new LinkedHashSet<String>(tableNames.size());  
        for (Integer value : shardingValue.getValues()) {  
            for (String tableName : tableNames) {  
                if (tableName.endsWith(value % 3 + "")) {  
                    result.add(tableName);  
                }  
            }  
        }  
        return result;  
    }  
  
    /** 
     * sql 中 between 操作时,table的映射 
     */  
    public Collection<String> doBetweenSharding(Collection<String> tableNames,  
            ShardingValue<Integer> shardingValue) {  
        Collection<String> result = new LinkedHashSet<String>(tableNames.size());  
        Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();  
        for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {  
            for (String each : tableNames) {  
                if (each.endsWith(i % 3 + "")) {  
                    result.add(each);  
                }  
            }  
        }  
        return result;  
    }  
  
} 

 

5.事务管理:

新增Student的分库分表策略,具体见代码

 

事务接口实现类,UserServiceImpl

package com.eshop.sharding.jdbc.service.impl;  
  
import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.eshop.sharding.jdbc.dao.StudentDao;
import com.eshop.sharding.jdbc.dao.UserDao;
import com.eshop.sharding.jdbc.domain.Student;
import com.eshop.sharding.jdbc.domain.User;
import com.eshop.sharding.jdbc.service.UserService;
  
@Service  
@Transactional  
public class UserServiceImpl implements UserService {  
  
    @Resource  
    public UserDao userDao;  
    
    @Resource  
    public StudentDao studentDao;  
      
    public boolean insert(User u) {  
        return userDao.insert(u) > 0 ? true :false;  
    }  
  
    public List<User> findAll() {  
        return userDao.findAll();  
    }  
  
    public List<User> findByUserIds(List<Integer> ids) {  
        return userDao.findByUserIds(ids);  
    }  
  
    @Transactional(propagation=Propagation.REQUIRED)  
    public void transactionTestSucess() {  
        User u = new User();  
        u.setUserId(13);  
        u.setAge(25);  
        u.setName("war3 1.27");  
        userDao.insert(u);  
          
        Student student = new Student();  
        student.setStudentId(21);  
        student.setAge(21);  
        student.setName("hehe");  
        studentDao.insert(student);  
    }  
  
    @Transactional(propagation=Propagation.REQUIRED)  
    public void transactionTestFailure() throws IllegalAccessException {  
        User u = new User();  
        u.setUserId(13);  
        u.setAge(25);  
        u.setName("war3 1.27 good");  
        userDao.insert(u);  
          
        Student student = new Student();  
        student.setStudentId(21);  
        student.setAge(21);  
        student.setName("hehe1");  
        studentDao.insert(student);  
        throw new IllegalAccessException();  
    }  
     
} 

 

 

 

 

 

测试类

package com.eshop.sharding.jdbc;  
  
import java.util.Arrays;
import java.util.List;

import javax.annotation.Resource;

import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.eshop.sharding.jdbc.domain.User;
import com.eshop.sharding.jdbc.service.UserService;
  
@RunWith(SpringJUnit4ClassRunner.class)  
@ContextConfiguration(locations = "classpath*:applicationContext.xml")  
public class ShardingJdbcMybatisTest {  
  
    @Resource  
    public UserService userService;  
  
    @Test  
    public void testUserInsert() {  
        User u = new User();
        //为=号时,分库规则shardingValue.getValue() % 2 + "" ,分表规则 shardingValue.getValue() % 3 + ""
        //u.setUserId(11);  //11%2=1 11%3=2 sharding_1.t_user_2
        u.setUserId(12);	//12%2=0 12%3=0 sharding_0.t_user_0
        //u.setUserId(13);	//13%2=1 13%3=1 sharding_1.t_user_1    
        //u.setUserId(14);	//14%2=0 14%3=2 sharding_0.t_user_2
        u.setAge(25);  
        u.setName("github"); 
  
        Assert.assertEquals(userService.insert(u), true);  
    }  
      
//    @Test  
//    public void testStudentInsert() {  
//        Student student = new Student();  
//        student.setStudentId(21);  
//        student.setAge(21);  
//        student.setName("hehe");  
//        Assert.assertEquals(studentService.insert(student), true);  
//    }  
  
    @Test  
    public void testFindAll(){  
        List<User> users = userService.findAll();  
        if(null != users && !users.isEmpty()){  
            for(User u :users){  
                System.out.println(u);  
            }  
        }  
    }  
      
    @Test  
    public void testSQLIN(){  
        List<User> users = userService.findByUserIds(Arrays.asList(2,10,1));  
        if(null != users && !users.isEmpty()){  
            for(User u :users){  
                System.out.println(u);  
            }  
        }  
    }  
      
    @Test  
    public void testTransactionTestSucess(){  
        userService.transactionTestSucess();  
    }  
    
      
    @Test(expected = IllegalAccessException.class)  
    public void testTransactionTestFailure() throws IllegalAccessException{  
        userService.transactionTestFailure();  
    }  
      
      
}  

 

 

事务好像成问题

 

 6.最新的基于rdb的写法

参考配置:https://github.com/dangdangdotcom/sharding-jdbc/blob/master/sharding-jdbc-example/sharding-jdbc-example-mybatis/src/main/resources/META-INF/mybatis/mysql/shardingContext.xml

http://blog.csdn.net/farrell_zeng/article/details/52957274

http://www.cnblogs.com/zwt1990/p/6762135.html

 见工程:sharding-jdbc-mybatis-rdb-dxfl-demo

  <rdb:table-rule logic-table="t_user" actual-tables="t_order_${0..2}" database-strategy="userDatabaseShardingStrategy" table-strategy="userTableShardingStrategy">

报错:Invalid bean definition with name 'shardingDataSource' defined in null: Could not resolve placeholder '0..2' in string value "t_student_${0..2}"

 Cloud not resolve placeholder … in string value …异常的解决方法?

 

 在读取配置文件时, <context:property-placeholder location="classpath:bp.properties" ignore-unresolvable="true" />

7.主从配置

 

8.分布式主键

http://blog.csdn.net/u012768474/article/details/52767520

http://dangdangdotcom.github.io/sharding-jdbc/02-guide/key-generator/

http://blog.csdn.net/tianyaleixiaowu/article/details/70242971

 

先看官方的说法,http://dangdangdotcom.github.io/sharding-jdbc/02-guide/id-generator/ 
传统数据库软件开发中,主键自动生成技术是基本需求。而各大数据库对于该需求也提供了相应的支持,比如MySQL的自增键。 对于MySQL而言,分库分表之后,不同表生成全局唯一的Id是非常棘手的问题。因为同一个逻辑表内的不同实际表之间的自增键是无法互相感知的, 这样会造成重复Id的生成。我们当然可以通过约束表生成键的规则来达到数据的不重复,但是这需要引入额外的运维力量来解决重复性问题,并使框架缺乏扩展性。 
目前有许多第三方解决方案可以完美解决这个问题,比如UUID等依靠特定算法自生成不重复键,或者通过引入Id生成服务等。 但也正因为这种多样性导致了Sharding-JDBC如果强依赖于任何一种方案就会限制其自身的发展。 
基于以上的原因,最终采用了以JDBC接口来实现对于生成Id的访问,而将底层具体的Id生成实现分离出来 
其实最终要解决的问题就是各库各表中的数据,主键不能重复。官方提供的statement什么的没看懂,我就直接用它提供的通用主键生成器来生成主键了。

 

1.提供了一个类IdGenerator,这个类能生成一个保证不重复的Long型数字,我们就用它做主键

 

<dependency>
    <groupId>com.dangdang</groupId>
    <artifactId>sharding-jdbc-self-id-generator</artifactId>
    <version>${sharding-jdbc.version}</version>
</dependency>
 注:最新版本提供了该方法:1.4.2

 

 

2.在数据库中应该用大于等于64bit的数字类型的字段来保存该值,比如在MySQL中应该使用BIGINT。

其二进制表示形式包含四部分,从高位到低位分表为:1bit符号位(为0),41bit时间位,10bit工作进程位,12bit序列位。

用int转换会变成负数

 

可以新建类

 

package com.eshop.sharding.jdbc.idGenerator;

import org.springframework.stereotype.Service;

import com.dangdang.ddframe.rdb.sharding.id.generator.IdGenerator;
import com.dangdang.ddframe.rdb.sharding.id.generator.self.CommonSelfIdGenerator;

@Service
public class IdGeneratorTool {
	
    public IdGenerator getIdGenerator() {
        return new CommonSelfIdGenerator();
    }

}
    	long id = idGenerator.getIdGenerator().generateId().longValue();
    	u.setId(id);
 

 

 

一些最佳实践的说明

1. 在设计库和表时优先考虑使用0,可以配合取余来使用


2.做主键的字段一般设计成数字型


3.SingleKeyTableShardingAlgorithm的类型当然也可以定义为String,问题是使用between时,不好用;

SingleKeyTableShardingAlgorithm的类型设置成Integer类型

4.在写分库分表规则时:
(shardingValue.getValue()!=null && each.endsWith( String.valueOf(shardingValue.getValue()).substring(0, 6)))
这种方式可取:String.valueOf(shardingValue.getValue())
这种方式不可取:shardingValue.getValue().toString()
非维度查询也可以

当你需要使用某个维度进行操作,可以带条件,否则别带
做法:在batis配置文件里设置<if test="orderId != null" >AND order_id = #{orderId,jdbcType=INTEGER} </if>


至今总结:sharding-jdbc:可以维度和非维度查询;带维度条件则在维度条件下获取;否则在全库全表获取数据聚合。


可以尝试:分页,排序等等


统计时:
异构数据库:比如跨库join;
全局表:比如跨库join

 

 

 

文章评论

60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
程序员和编码员之间的区别
程序员和编码员之间的区别
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
如何成为一名黑客
如何成为一名黑客
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
中美印日四国程序员比较
中美印日四国程序员比较
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
老程序员的下场
老程序员的下场
程序员的鄙视链
程序员的鄙视链
那些争议最大的编程观点
那些争议最大的编程观点
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
总结2014中国互联网十大段子
总结2014中国互联网十大段子
程序员都该阅读的书
程序员都该阅读的书
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
代码女神横空出世
代码女神横空出世
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
我是如何打败拖延症的
我是如何打败拖延症的
旅行,写作,编程
旅行,写作,编程
我的丈夫是个程序员
我的丈夫是个程序员
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
Java程序员必看电影
Java程序员必看电影
 程序员的样子
程序员的样子
程序员必看的十大电影
程序员必看的十大电影
10个调试和排错的小建议
10个调试和排错的小建议
编程语言是女人
编程语言是女人
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
漫画:程序员的工作
漫画:程序员的工作
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
程序员应该关注的一些事儿
程序员应该关注的一些事儿
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
鲜为人知的编程真相
鲜为人知的编程真相
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
为什么程序员都是夜猫子
为什么程序员都是夜猫子
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有