spring+mybatis+druid数据源+sharding-jdbc分库分表

spring+mybatis+druid数据源+sharding-jdbc分库分表

首先我们看下如何让spring与mybatis集成，我使用的是MySQL数据库，建库建表语句如下：

drop database if exists demodb00;
CREATE database demodb00 DEFAULT CHARACTER SET utf8;

CREATE TABLE demodb00.user (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(100) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY id_UNIQUE (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

编写实体类UserEntity.java

package net.aty.spring.entity;


public class UserEntity {

    private int id;

    private String name;

    private int age;

    public UserEntity() {

    }

    public UserEntity(int id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public int getAge() {
        return age;
    }

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

    public String getName() {

        return name;
    }

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


    public int getId() {
        return id;
    }

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

    @Override
    public String toString() {
        return "UserEntity{" +
                "id=" + id +
                ", name='" + name + ''' +
                ", age=" + age +
                '}';
    }
}

编写mapper类UserMapper.java

package net.aty.spring.mapper;


import net.aty.spring.entity.UserEntity;

public interface UserMapper {

    int insertOne(UserEntity user);

    UserEntity selectByPk(int id);
}

编写mapper映射文件：UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="net.aty.spring.mapper.UserMapper">

    <insert id="insertOne" parameterType="net.aty.spring.entity.UserEntity">
        INSERT INTO user(id, name,age) VALUE(#{id},#{name},#{age})
    </insert>

    <select id="selectByPk" resultType="net.aty.spring.entity.UserEntity" parameterType="java.lang.Integer">
        select * from user where id=#{id}
    </select>

</mapper>

编写mybatis配置文件：mybatis.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 配置mybatis的缓存，延迟加载等等一系列属性 -->
    <settings>
        <!-- 全局映射器启用缓存 -->
        <setting name="cacheEnabled" value="true"/>
        <!-- 查询时，关闭关联对象即时加载以提高性能 -->
        <setting name="lazyLoadingEnabled" value="false"/>
        <!-- 设置关联对象加载的形态，此处为按需加载字段(加载字段由SQL指 定)，不会加载关联表的所有字段，以提高性能 -->
        <setting name="aggressiveLazyLoading" value="true"/>
        <!-- 对于未知的SQL查询，允许返回不同的结果集以达到通用的效果 -->
        <setting name="multipleResultSetsEnabled" value="true"/>
        <!-- 允许使用列标签代替列名 -->
        <setting name="useColumnLabel" value="true"/>
        <!-- 允许使用自定义的主键值(比如由程序生成的UUID 32位编码作为键值)，数据表的PK生成策略将被覆盖 -->
        <!-- <setting name="useGeneratedKeys" value="true" /> -->
        <!-- 给予被嵌套的resultMap以字段-属性的映射支持 -->
        <setting name="autoMappingBehavior" value="FULL"/>
        <!-- 对于批量更新操作缓存SQL以提高性能 -->
        <setting name="defaultExecutorType" value="REUSE"/>
        <!-- 数据库超过25000秒仍未响应则超时 -->
        <setting name="defaultStatementTimeout" value="25000"/>

    </settings>

</configuration>

配置druid数据源，以及整合mybatis的spring-mybatis.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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd">

    <!--配置数据源-->
    <bean id="dataSourceResource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init"
          destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="username" value="root"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/demodb00"/>
        <property name="password" value="root"/>
        <property name="maxActive" value="10"/>
        <property name="initialSize" value="1"/>
        <property name="maxWait" value="60000"/>
        <property name="minIdle" value="1"/>
        <property name="timeBetweenEvictionRunsMillis" value="60000"/>
        <property name="minEvictableIdleTimeMillis" value="300000"/>
        <property name="validationQuery" value="SELECT 'x'"/>
        <property name="testWhileIdle" value="true"/>
        <property name="testOnBorrow" value="false"/>
        <property name="testOnReturn" value="false"/>
        <property name="poolPreparedStatements" value="true"/>
        <property name="maxPoolPreparedStatementPerConnectionSize" value="50"/>
        <property name="maxOpenPreparedStatements" value="100"/>
        <property name="proxyFilters">
            <list>
                <ref bean="statFilter"/>
                <ref bean="logFilter"/>
            </list>
        </property>
    </bean>

    <bean id="statFilter" class="com.alibaba.druid.filter.logging.Slf4jLogFilter">
        <property name="statementExecutableSqlLogEnable" value="false"/>
        <property name="dataSourceLogEnabled" value="false"/>
    </bean>
    <bean id="logFilter" class="com.alibaba.druid.filter.stat.StatFilter">
        <property name="slowSqlMillis" value="50"/>
        <property name="logSlowSql" value="false"/>
        <property name="mergeSql" value="true"/>
    </bean>



    <!--整合spring与mybatis-->

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSourceResource"/>
        <property name="configLocation" value="classpath:/META-INF/mybatis.xml"></property>
        <property name="typeAliasesPackage" value="net.aty.spring.mybatis.entity"></property>
        <property name="mapperLocations" value="classpath*:/META-INF/mybatis/*.xml"></property>
    </bean>
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="net.aty.spring.mapper"></property>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    </bean>

</beans>

编写测试代码，可以发现能够正常读取和修改数据库记录

package net.aty.spring;

import net.aty.spring.entity.UserEntity;
import net.aty.spring.mapper.UserMapper;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Main {
    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("META-INF/spring-mybatis.xml");

        UserMapper mapper = context.getBean(UserMapper.class);
        mapper.insertOne(new UserEntity(1, "xx", 11));
        mapper.insertOne(new UserEntity(2, "xxzzz", 11));

        System.out.println(mapper.selectByPk(1));
        System.out.println(mapper.selectByPk(2));
    }
}

上面我们完成了spring与mybatis的整合，随着业务的变化，数据越来越多，我们需要分库分表。我们项目使用的当当中间件：sharding-jdbc，github地址是：https://github.com/dangdangdotcom/sharding-jdbc。假设我们分为demodb00和demodb01这2个数据库，每个库分user_0和user_1这2张表。

sharding-jdbc.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:rdb="http://www.dangdang.com/schema/ddframe/rdb"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                        http://www.springframework.org/schema/beans/spring-beans.xsd
                        http://www.dangdang.com/schema/ddframe/rdb
                        http://www.dangdang.com/schema/ddframe/rdb/rdb.xsd">

    <bean id="statFilter" class="com.alibaba.druid.filter.logging.Slf4jLogFilter">
        <property name="statementExecutableSqlLogEnable" value="false"/>
        <property name="dataSourceLogEnabled" value="false"/>
    </bean>
    <bean id="logFilter" class="com.alibaba.druid.filter.stat.StatFilter">
        <property name="slowSqlMillis" value="50"/>
        <property name="logSlowSql" value="false"/>
        <property name="mergeSql" value="true"/>
    </bean>

    <bean id="master0" class="com.alibaba.druid.pool.DruidDataSource" init-method="init"
          destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="username" value="root"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/demodb00"/>
        <property name="password" value="root"/>
        <property name="maxActive" value="10"/>
        <property name="initialSize" value="1"/>
        <property name="maxWait" value="60000"/>
        <property name="minIdle" value="1"/>
        <property name="timeBetweenEvictionRunsMillis" value="60000"/>
        <property name="minEvictableIdleTimeMillis" value="300000"/>
        <property name="validationQuery" value="SELECT 'x'"/>
        <property name="testWhileIdle" value="true"/>
        <property name="testOnBorrow" value="false"/>
        <property name="testOnReturn" value="false"/>
        <property name="poolPreparedStatements" value="true"/>
        <property name="maxPoolPreparedStatementPerConnectionSize" value="50"/>
        <property name="maxOpenPreparedStatements" value="100"/>
        <property name="proxyFilters">
            <list>
                <ref bean="statFilter"/>
                <ref bean="logFilter"/>
            </list>
        </property>
    </bean>

    <bean id="master1" class="com.alibaba.druid.pool.DruidDataSource" init-method="init"
          destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="username" value="root"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/demodb01"/>
        <property name="password" value="root"/>
        <property name="maxActive" value="10"/>
        <property name="initialSize" value="1"/>
        <property name="maxWait" value="60000"/>
        <property name="minIdle" value="1"/>
        <property name="timeBetweenEvictionRunsMillis" value="60000"/>
        <property name="minEvictableIdleTimeMillis" value="300000"/>
        <property name="validationQuery" value="SELECT 'x'"/>
        <property name="testWhileIdle" value="true"/>
        <property name="testOnBorrow" value="false"/>
        <property name="testOnReturn" value="false"/>
        <property name="poolPreparedStatements" value="true"/>
        <property name="maxPoolPreparedStatementPerConnectionSize" value="50"/>
        <property name="maxOpenPreparedStatements" value="100"/>
        <property name="proxyFilters">
            <list>
                <ref bean="statFilter"/>
                <ref bean="logFilter"/>
            </list>
        </property>
    </bean>

    <rdb:master-slave-data-source id="rbb_0" master-data-source-ref="master0" slave-data-sources-ref="master0"/>
    <rdb:master-slave-data-source id="rbb_1" master-data-source-ref="master1" slave-data-sources-ref="master1"/>

    <rdb:strategy id="idDbSharding" sharding-columns="id"
                  algorithm-class="net.aty.spring.DbAlgorithm"/>

    <rdb:strategy id="idTbSharding" sharding-columns="id"
                  algorithm-class="net.aty.spring.TbAlgorithm"/>

    <rdb:data-source id="wholeDataSource">
        <rdb:sharding-rule data-sources="rbb_0,rbb_1">
            <rdb:table-rules>
                <rdb:table-rule logic-table="user" actual-tables="user_${0..1}"
                                database-strategy="idDbSharding" table-strategy="idTbSharding"/>
            </rdb:table-rules>
        </rdb:sharding-rule>
    </rdb:data-source>

</beans>

这个文件中定义了2个库的数据源，以及我们的分库分表规则，我是按照id分库分表的：

import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;

import java.util.Collection;

public class DbAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Integer> {

    @Override
    public String doEqualSharding(Collection<String> collection, ShardingValue<Integer> shardingValue) {
        int id = shardingValue.getValue();

        int index = id % 2;

        for (String each : collection) {
            if (each.endsWith(index + "")) {
                return each;
            }
        }
        throw new UnsupportedOperationException();
    }

    @Override
    public Collection<String> doInSharding(Collection<String> collection, ShardingValue<Integer> shardingValue) {
        return null;
    }

    @Override
    public Collection<String> doBetweenSharding(Collection<String> collection, ShardingValue<Integer> shardingValue) {
        return null;
    }
}

import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;

import java.util.Collection;

public class TbAlgorithm implements SingleKeyTableShardingAlgorithm<Integer> {


    @Override
    public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {
        int id = shardingValue.getValue();

        int index = id % 2;

        for (String each : availableTargetNames) {
            if (each.endsWith(index + "")) {
                return each;
            }
        }
        throw new UnsupportedOperationException();
    }

    @Override
    public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {
        return null;
    }

    @Override
    public Collection<String> doBetweenSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {
        return null;
    }
}

接下来我们只需要修改上面的spring-mybatis.xml，就可以使用分库分表了，不需要改动已有的代码。可以看到只需要修改数据源，就可以完成sharding-jdbc与mybatis、spring的集成，非常方便。

修改了这个数据源后，直接运行上面的测试代码，可以看到分库分表生效了。id=1的记录插入了rbb_1这个数据源，user_1这张表；id=2的记录插入了rbb_0这个数据源，user_0这张表。

至此就完成了spring与mybatis以及sharding-jdbc分库分表的集成，最后给出项目使用的pom配置：

<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/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>net.aty.mybatis</groupId>
    <artifactId>mybatis-demo</artifactId>
    <packaging>jar</packaging>
    <version>1.0-SNAPSHOT</version>
    <name>mybatis-demo</name>
    <url>http://maven.apache.org</url>

    <properties>
        <spring.version>4.3.3.RELEASE</spring.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.21</version>
        </dependency>

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.26</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.26</version>
        </dependency>


        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.3.0</version>
        </dependency>


        <dependency>
            <groupId>com.dangdang</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>1.3.3</version>
        </dependency>
        <dependency>
            <groupId>com.dangdang</groupId>
            <artifactId>sharding-jdbc-config-spring</artifactId>
            <version>1.3.3</version>
        </dependency>


        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.5.1</version>
                <configuration>
                    <encoding>UTF-8</encoding>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

 

顶

1

踩