分库分表插件之sharding-jdbc整合spring

随着业务复杂度的提升，数据库方面的压力也越来越大，单个库表已经不能满足我们的要求了，这个时候急需一套分库分表的解决方案。当然了，分库分表中间件也有很多，比如sharding-jdbc，mycat等，sharding-jdbc用起来非常简单，他本质就是一个jar包，直接加入项目中就能使用；如果团队有富余的人手，推荐使用myCat中间件，因为它是单独部署一个服务的，能够很好的和业务解耦。本文来介绍以一下当当网的一款分库分表中间件sharding-jdbc的使用。

一、数据库准备

使用如下sql创建多个分库和分表

CREATE DATABASE IF NOT EXISTS `db_0`;

CREATE TABLE IF NOT EXISTS `db_0`.`t_order_0` (
  `order_id` INT NOT NULL,
  `user_id`  INT NOT NULL,
  `status`   VARCHAR(50),
  PRIMARY KEY (`order_id`)
);

CREATE TABLE IF NOT EXISTS `db_0`.`t_order_1` (
  `order_id` INT NOT NULL,
  `user_id`  INT NOT NULL,
  `status`   VARCHAR(50),
  PRIMARY KEY (`order_id`)
);


CREATE DATABASE IF NOT EXISTS `db_1`;

CREATE TABLE IF NOT EXISTS `db_1`.`t_order_0` (
  `order_id` INT NOT NULL,
  `user_id`  INT NOT NULL,
  `status`   VARCHAR(50),
  PRIMARY KEY (`order_id`)
);

CREATE TABLE IF NOT EXISTS `db_1`.`t_order_1` (
  `order_id` INT NOT NULL,
  `user_id`  INT NOT NULL,
  `status`   VARCHAR(50),
  PRIMARY KEY (`order_id`)
);


CREATE DATABASE IF NOT EXISTS `db_2`;

CREATE TABLE IF NOT EXISTS `db_2`.`t_order_0` (
  `order_id` INT NOT NULL,
  `user_id`  INT NOT NULL,
  `status`   VARCHAR(50),
  PRIMARY KEY (`order_id`)
);

CREATE TABLE IF NOT EXISTS `db_2`.`t_order_1` (
  `order_id` INT NOT NULL,
  `user_id`  INT NOT NULL,
  `status`   VARCHAR(50),
  PRIMARY KEY (`order_id`)
);

/**
这个没啥实用,只是为了方便mybatis生成数据实体类
 */
CREATE TABLE IF NOT EXISTS `db_0`.`t_order` (
  `order_id` INT NOT NULL,
  `user_id`  INT NOT NULL,
  `status`   VARCHAR(50),
  PRIMARY KEY (`order_id`)
);

二、创建maven项目

创建好maven项目的结构如下：

然后修改pom.xml，加入必要的依赖内容如下：

<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.eyesmoons</groupId>
	<artifactId>sharding-jdbc-demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<dependencies>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.37</version>
		</dependency>
		<dependency>
			<groupId>com.dangdang</groupId>
			<artifactId>sharding-jdbc-core</artifactId>
			<version>1.1.0</version>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.0.12</version>
		</dependency>
		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.4</version>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>fastjson</artifactId>
			<version>1.2.7</version>
		</dependency>
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.3.0</version>
		</dependency>
		<dependency>
			<groupId>tk.mybatis</groupId>
			<artifactId>mapper</artifactId>
			<version>3.1.3</version>
		</dependency>
		<dependency>
			<groupId>com.github.pagehelper</groupId>
			<artifactId>pagehelper</artifactId>
			<version>4.0.0</version>
		</dependency>
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
			<version>3.4</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>4.2.3.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-web</artifactId>
			<version>4.2.3.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-beans</artifactId>
			<version>4.2.3.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>4.2.3.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context-support</artifactId>
			<version>4.2.3.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>4.2.3.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-tx</artifactId>
			<version>4.2.3.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-aop</artifactId>
			<version>4.2.3.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>4.2.3.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.aspectj</groupId>
			<artifactId>aspectjrt</artifactId>
			<version>1.8.7</version>
		</dependency>
		<dependency>
			<groupId>org.aspectj</groupId>
			<artifactId>aspectjweaver</artifactId>
			<version>1.8.7</version>
		</dependency>
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis-spring</artifactId>
			<version>1.2.3</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>1.7.12</version>
		</dependency>
		<dependency>
			<groupId>commons-beanutils</groupId>
			<artifactId>commons-beanutils</artifactId>
			<version>1.8.3</version>
		</dependency>
		<dependency>
			<groupId>org.apache.logging.log4j</groupId>
			<artifactId>log4j-slf4j-impl</artifactId>
			<version>2.4.1</version>
		</dependency>
		<dependency>
			<groupId>org.apache.logging.log4j</groupId>
			<artifactId>log4j-core</artifactId>
			<version>2.4.1</version>
		</dependency>
	</dependencies>
</project>

三、资源文件配置

1.主配置文件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:component-scan base-package="com.eyesmoons"/>

    <bean id="propertiesFactoryBean"
          class="org.springframework.beans.factory.config.PropertiesFactoryBean">
        <property name="locations">
            <list>
                <value>classpath:properties/jdbc.properties</value>
            </list>
        </property>
    </bean>

    <context:property-placeholder properties-ref="propertiesFactoryBean" ignore-unresolvable="true"/>

    <!--数据源0-->
    <!--<bean id="ds_1" parent="parentDataSource">-->
    <bean id="ds_0" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc-driver}"/>
        <property name="url" value="${jdbc-url-0}"/>
        <property name="username" value="${jdbc-user-0}"/>
        <property name="password" value="${jdbc-password-0}"/>
    </bean>

    <!--数据源1-->
    <bean id="ds_1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc-driver}"/>
        <property name="url" value="${jdbc-url-1}"/>
        <property name="username" value="${jdbc-user-1}"/>
        <property name="password" value="${jdbc-password-1}"/>
    </bean>

    <!--数据源2-->
    <bean id="ds_2" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc-driver}"/>
        <property name="url" value="${jdbc-url-2}"/>
        <property name="username" value="${jdbc-user-2}"/>
        <property name="password" value="${jdbc-password-2}"/>
    </bean>

    <!--真正使用的数据源-->
    <bean id="dataSource" class="com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule">
        <constructor-arg>
            <map>
                <entry key="ds_0" value-ref="ds_0"/>
                <entry key="ds_1" value-ref="ds_1"/>
                <entry key="ds_2" value-ref="ds_2"/>
            </map>
        </constructor-arg>
    </bean>

    <!--t_order的"分表"设置:分N个表 -->
    <bean id="orderTableRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.TableRule">
        <constructor-arg value="t_order" index="0"/>
        <constructor-arg index="1">
            <list>
                <value>t_order_0</value>
                <value>t_order_1</value>
            </list>
        </constructor-arg>
        <constructor-arg index="2" ref="dataSource"/>
    </bean>

    <!--分库的sharding规则:按user_id分库 -->
    <bean id="databaseShardingStrategy"
          class="com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy">
        <constructor-arg index="0" value="user_id"/>
        <constructor-arg index="1">
            <bean class="com.eyesmoons.algorithm.SingleKeyModuloDatabaseShardingAlgorithm">
                <!--dbount的值要跟上面dataSource的个数匹配-->
                <property name="dbCount" value="3"/>
            </bean>
        </constructor-arg>
    </bean>


    <bean id="singleKeyModuloTableShardingAlgorithm"
          class="com.eyesmoons.algorithm.SingleKeyModuloTableShardingAlgorithm">
        <!--tableCount的值要跟上面t_order表设置的分表个数保持一致-->
        <property name="tableCount" value="2"/>
    </bean>

    <!--分表的规则:按order_id分表-->
    <bean id="tableShardingStrategy" class="com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy">
        <constructor-arg index="0" value="order_id"/>
        <constructor-arg index="1" ref="singleKeyModuloTableShardingAlgorithm"/>
    </bean>

    <!--sharding规则Bean-->
    <bean id="shardingRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule">
        <constructor-arg index="0" ref="dataSource"/>
        <constructor-arg index="1">
            <list>
                <ref bean="orderTableRule"/>
            </list>
        </constructor-arg>
        <constructor-arg index="2" ref="databaseShardingStrategy"/>
        <constructor-arg index="3" ref="tableShardingStrategy"/>
    </bean>

    <!--sharding数据源-->
    <bean id="shardingDataSource" class="com.dangdang.ddframe.rdb.sharding.api.ShardingDataSource">
        <constructor-arg ref="shardingRule"/>
    </bean>


    <!--mybatis配置-->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="configLocation" value="classpath:mybatis-config.xml"></property>
        <property name="dataSource" ref="shardingDataSource"/>
        <property name="mapperLocations" value="classpath:mybatis/OrderMapper.xml"/>
    </bean>

    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.eyesmoons.mapper"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    </bean>

</beans>

2.mybatis配置文件mybatis-config.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>
    <settings>
        <setting name="logImpl" value="LOG4J2"/>
    </settings>

    <plugins>
        <plugin interceptor="com.github.pagehelper.PageHelper">
            <!--下面的参数详解见http://git.oschina.net/free/Mybatis_PageHelper/blob/master/wikis/HowToUse.markdown-->
            <property name="dialect" value="mysql"/>
            <property name="reasonable" value="true"/>
            <property name="offsetAsPageNum" value="true"/>
            <property name="rowBoundsWithCount" value="true"/>
            <property name="pageSizeZero" value="true"/>
        </plugin>

        <plugin interceptor="tk.mybatis.mapper.mapperhelper.MapperInterceptor">
            <property name="mappers" value="tk.mybatis.mapper.common.Mapper"/>
            <property name="IDENTITY" value="MYSQL"/>
            <property name="notEmpty" value="true"/>
        </plugin>
    </plugins>

</configuration>

3.日志文件配置log4j2.xml

<?xml version="1.0" encoding="UTF-8"?>
<Configuration>

    <Appenders>
        <Console name="STDOUT" target="SYSTEM_OUT">
            <PatternLayout charset="UTF-8" pattern="%d{ABSOLUTE} <%c> %5p [%t]: %m%n"/>
        </Console>

        <Console name="STDERR" target="SYSTEM_ERR">
            <PatternLayout charset="UTF-8" pattern="%d{ABSOLUTE} <%c> %5p [%t]: %m%n"/>
        </Console>

        <RollingRandomAccessFile name="SERVICE_LOG_FILE" fileName="logs/order/service.log"
                                 filePattern="logs/$${date:yyyy-MM}/service-%d{yyyy-MM-dd}-%i.log.gz">
            <PatternLayout charset="UTF-8" pattern="%d{ABSOLUTE} <%c> %5p [%t]: %m%n"/>
            <Policies>
                <TimeBasedTriggeringPolicy/>
            </Policies>
            <DefaultRolloverStrategy/>
        </RollingRandomAccessFile>

    </Appenders>

    <Loggers>
        <!-- Root Logger -->
        <Root level="ERROR">
            <AppenderRef ref="STDOUT"/>
            <AppenderRef ref="SERVICE_LOG_FILE"/>
        </Root>

        <!-- HJM SOA Logger -->
        <Logger name="net.aimeizi" level="DEBUG" additivity="false">
            <AppenderRef ref="STDOUT"/>
            <AppenderRef ref="SERVICE_LOG_FILE"/>
        </Logger>

        <!-- MyBatis Logger -->
        <Logger name="org.apache.ibatis" level="DEBUG" additivity="false">
            <AppenderRef ref="STDERR"/>
        </Logger>

        <!-- JDBC Logger -->
        <Logger name="java.sql" level="DEBUG" additivity="false">
            <AppenderRef ref="STDERR"/>
        </Logger>

        <!-- - - - - - - - BEGIN: DAO LOGGERS - - - - - - - -->
        <Logger name="net.aimeizi.mapper" level="ERROR" additivity="false">
            <AppenderRef ref="STDOUT"/>
            <AppenderRef ref="SERVICE_LOG_FILE"/>
        </Logger>
        <!-- - - - - - - - END: DAO LOGGERS - - - - - - - -->

        <Logger name="com.dangdang" level="TRACE" additivity="false">
            <AppenderRef ref="STDOUT"/>
            <AppenderRef ref="SERVICE_LOG_FILE"/>
        </Logger>

    </Loggers>
</Configuration>

四.分库分表策略类编写

1.分库策略SingleKeyModuloDatabaseShardingAlgorithm.java

package com.eyesmoons.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;

public final class SingleKeyModuloDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Integer> {

    private int dbCount = 1;

    /**
     * sql 中关键字 匹配符为 =的时候，表的路由函数
     */
    @Override
    public String doEqualSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) {
        for (String each : availableTargetNames) {
            if (each.endsWith(shardingValue.getValue() % dbCount + "")) {
                return each;
            }
        }
        throw new UnsupportedOperationException();
    }

    /**
     * sql 中关键字 匹配符为 in 的时候，表的路由函数  
     */
    @Override
    public Collection<String> doInSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());
        Collection<Integer> values = shardingValue.getValues();
        for (Integer value : values) {
            for (String dataSourceName : availableTargetNames) {
                if (dataSourceName.endsWith(value % dbCount + "")) {
                    result.add(dataSourceName);
                }
            }
        }
        return result;
    }

    /**
     * sql 中关键字 匹配符为 between的时候，表的路由函数
     */
    @Override
    public Collection<String> doBetweenSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());
        Range<Integer> range = shardingValue.getValueRange();
        for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
            for (String each : availableTargetNames) {
                if (each.endsWith(i % dbCount + "")) {
                    result.add(each);
                }
            }
        }
        return result;
    }

    /**
     * 设置database分库的个数
     *
     * @param dbCount
     */
    public void setDbCount(int dbCount) {
        this.dbCount = dbCount;
    }


    public int getDbCount() {
        return dbCount;
    }
}

2.分表策略类SingleKeyModuloTableShardingAlgorithm.java

package com.eyesmoons.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 final class SingleKeyModuloTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer> {

    private int tableCount = 1;

    /**
     * sql 中 = 操作时，table的映射
     */
    @Override
    public String doEqualSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) {
        for (String each : availableTargetNames) {
            if (each.endsWith(shardingValue.getValue() % tableCount + "")) {
                return each;
            }
        }
        throw new UnsupportedOperationException();
    }

    /**
     * sql 中 in 操作时，table的映射
     */
    @Override
    public Collection<String> doInSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) {
        Collection<String> result = new LinkedHashSet<String>(availableTargetNames.size());
        Collection<Integer> values = shardingValue.getValues();
        for (Integer value : values) {
            for (String tableNames : availableTargetNames) {
                if (tableNames.endsWith(value % tableCount + "")) {
                    result.add(tableNames);
                }
            }
        }
        return result;
    }

    /**
     * sql 中 between 操作时，table的映射
     */
    @Override
    public Collection<String> doBetweenSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) {
        Collection<String> result = new LinkedHashSet<String>(availableTargetNames.size());
        Range<Integer> range = shardingValue.getValueRange();
        for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
            for (String each : availableTargetNames) {
                if (each.endsWith(i % tableCount + "")) {
                    result.add(each);
                }
            }
        }
        return result;
    }

    /**
     * 设置分表的个数
     *
     * @param tableCount
     */
    public void setTableCount(int tableCount) {
        this.tableCount = tableCount;
    }

    public int getTableCount() {
        return tableCount;
    }
}

五.业务逻辑编写

1.service层接口OrderService.java

package com.eyesmoons.service;

import java.util.List;

import com.eyesmoons.entity.Order;
import com.eyesmoons.entity.OrderExample;

public interface OrderService {

    /**
     * 返回所有订单
     *
     * @return
     */
    List<Order> getAllOrder();

    /**
     * 添加单条订单
     *
     * @param o
     */
    void addOrder(Order o);

    /**
     * 批量添加订单
     *
     * @param orders
     */
    void addOrders(List<Order> orders);

    /**
     * 批量更新用户的订单状态
     *
     * @param userIds
     */
    void updateOrders(List<Integer> userIds, String newOrderStatus);

    /**
     * 删除所有订单
     */
    void deleteAll();

    /**
     * 按条件查询订单总数
     *
     * @param example
     * @return
     */
    int getCount(OrderExample example);

    /**
     * 删除单条订单
     *
     * @return
     */
    void delete(Order order);

    /**
     * 更新单条订单
     */
    void update(Order order);

    /**
     * 查询最大订单号
     *
     * @param example
     */
    int getMaxOrderId(OrderExample example);

    /**
     * 查询最小订单号
     *
     * @param example
     */
    int getMinOrderId(OrderExample example);


    /**
     * 查询最大用户id
     *
     * @param example
     */
    int getMaxUserId(OrderExample example);


    /**
     * 查询最小用户id
     *
     * @param example
     */
    int getMinUserId(OrderExample example);
}

2.service层实现类OrderServiceImpl.java

package com.eyesmoons.service.impl;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.eyesmoons.algorithm.SingleKeyModuloTableShardingAlgorithm;
import com.eyesmoons.entity.Order;
import com.eyesmoons.entity.OrderExample;
import com.eyesmoons.list.ListUtil;
import com.eyesmoons.mapper.OrderMapper;
import com.eyesmoons.service.OrderService;

@Service("orderService")
public class OrderServiceImpl implements OrderService {

    @Autowired
    OrderMapper orderMapper;

    @Autowired
    SingleKeyModuloTableShardingAlgorithm singleKeyModuloTableShardingAlgorithm;


    @Override
    public List<Order> getAllOrder() {
        return orderMapper.selectByExample(null);
    }


    @Override
    public void addOrder(Order o) {
        orderMapper.insertSelective(o);
    }

    @Override
    public void addOrders(List<Order> orders) {
        Map<String, List<Order>> map = ListUtil.getMapByKeyProperty(orders, "userId");
        for (String userId : map.keySet()) {
            Map<String, List<Order>> map2 = ListUtil.getMapByModKeyProperty(map.get(userId), "orderId",
                    singleKeyModuloTableShardingAlgorithm.getTableCount());
            for (String key : map2.keySet()) {
                orderMapper.insertBatch(map2.get(key));
            }
        }
    }

    @Override
    public void updateOrders(List<Integer> userIds, String newOrderStatus) {
        Order o = new Order();
        o.setStatus(newOrderStatus);
        OrderExample example = new OrderExample();
        example.createCriteria().andUserIdIn(userIds);
        orderMapper.updateByExampleSelective(o, example);
    }

    @Override
    public void deleteAll() {
        orderMapper.deleteByExample(null);
    }

    @Override
    public int getCount(OrderExample example) {
        return orderMapper.countByExample(example);
    }

    @Override
    public void delete(Order order) {
        orderMapper.delete(order);
    }

    @Override
    public void update(Order order) {
        OrderExample example = new OrderExample();
        example.createCriteria()
                .andUserIdEqualTo(order.getUserId())
                .andOrderIdEqualTo(order.getOrderId());
        orderMapper.updateByExampleSelective(order, example);
    }

    @Override
    public int getMaxOrderId(OrderExample example) {
        return orderMapper.maxOrderIdByExample(example);
    }

    @Override
    public int getMinOrderId(OrderExample example) {
        return orderMapper.minOrderIdByExample(example);
    }

    @Override
    public int getMaxUserId(OrderExample example) {
        return orderMapper.maxUserIdByExample(example);
    }

    @Override
    public int getMinUserId(OrderExample example) {
        return orderMapper.minUserIdByExample(example);
    }
}

3.数据库接口层OrderMapper.java

package com.eyesmoons.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.eyesmoons.entity.Order;
import com.eyesmoons.entity.OrderExample;

import tk.mybatis.mapper.common.Mapper;

public interface OrderMapper extends Mapper<Order> {

    int insertBatch(List<Order> orders);

    int countByExample(OrderExample example);

    int maxOrderIdByExample(OrderExample example);

    int minOrderIdByExample(OrderExample example);

    int maxUserIdByExample(OrderExample example);

    int minUserIdByExample(OrderExample example);

    int deleteByExample(OrderExample example);

    List<Order> selectByExample(OrderExample example);

    int updateByExampleSelective(@Param("record") Order record, @Param("example") OrderExample example);

    int updateByExample(@Param("record") Order record, @Param("example") OrderExample example);
}

4.数据库映射层OrderMapper.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.eyesmoons.mapper.OrderMapper">
    <resultMap id="BaseResultMap" type="com.eyesmoons.entity.Order">
        <result column="order_id" property="orderId" jdbcType="INTEGER"/>
        <result column="user_id" property="userId" jdbcType="INTEGER"/>
        <result column="status" property="status" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Example_Where_Clause">
        <where>
            <foreach collection="oredCriteria" item="criteria" separator="or">
                <if test="criteria.valid">
                    <trim prefix="(" suffix=")" prefixOverrides="and">
                        <foreach collection="criteria.criteria" item="criterion">
                            <choose>
                                <when test="criterion.noValue">
                                    and ${criterion.condition}
                                </when>
                                <when test="criterion.singleValue">
                                    and ${criterion.condition} #{criterion.value}
                                </when>
                                <when test="criterion.betweenValue">
                                    and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                                </when>
                                <when test="criterion.listValue">
                                    and ${criterion.condition}
                                    <foreach collection="criterion.value" item="listItem" open="(" close=")"
                                             separator=",">
                                        #{listItem}
                                    </foreach>
                                </when>
                            </choose>
                        </foreach>
                    </trim>
                </if>
            </foreach>
        </where>
    </sql>

    <sql id="Update_By_Example_Where_Clause">
        <where>
            <foreach collection="example.oredCriteria" item="criteria" separator="or">
                <if test="criteria.valid">
                    <trim prefix="(" suffix=")" prefixOverrides="and">
                        <foreach collection="criteria.criteria" item="criterion">
                            <choose>
                                <when test="criterion.noValue">
                                    and ${criterion.condition}
                                </when>
                                <when test="criterion.singleValue">
                                    and ${criterion.condition} #{criterion.value}
                                </when>
                                <when test="criterion.betweenValue">
                                    and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                                </when>
                                <when test="criterion.listValue">
                                    and ${criterion.condition}
                                    <foreach collection="criterion.value" item="listItem" open="(" close=")"
                                             separator=",">
                                        #{listItem}
                                    </foreach>
                                </when>
                            </choose>
                        </foreach>
                    </trim>
                </if>
            </foreach>
        </where>
    </sql>

    <sql id="Base_Column_List">
        order_id, user_id, `status`
    </sql>

    <!--批量插入-->
    <insert id="insertBatch" parameterType="list">
        INSERT INTO `t_order`(
        <include refid="Base_Column_List"/>
        )
        VALUES
        <foreach collection="list" item="item" index="index" open="("
                 close=");" separator="),(">
            #{item.orderId,jdbcType=INTEGER},
            #{item.userId,jdbcType=INTEGER},
            #{item.status,jdbcType=VARCHAR}
        </foreach>
    </insert>

    <select id="countByExample" parameterType="com.eyesmoons.entity.OrderExample"
            resultType="java.lang.Integer">
        select count(0) order_count from t_order
        <if test="_parameter != null">
            <include refid="Example_Where_Clause"/>
        </if>
    </select>

    <select id="maxOrderIdByExample" parameterType="com.eyesmoons.entity.OrderExample"
            resultType="java.lang.Integer">
        select max(order_id) max_order_id from t_order
        <if test="_parameter != null">
            <include refid="Example_Where_Clause"/>
        </if>
    </select>

    <select id="minOrderIdByExample" parameterType="com.eyesmoons.entity.OrderExample"
            resultType="java.lang.Integer">
        select min(order_id) min_order_id from t_order
        <if test="_parameter != null">
            <include refid="Example_Where_Clause"/>
        </if>
    </select>

    <select id="maxUserIdByExample" parameterType="com.eyesmoons.entity.OrderExample"
            resultType="java.lang.Integer">
        select max(user_id) max_user_id from t_order
        <if test="_parameter != null">
            <include refid="Example_Where_Clause"/>
        </if>
    </select>

    <select id="minUserIdByExample" parameterType="com.eyesmoons.entity.OrderExample"
            resultType="java.lang.Integer">
        select min(user_id) min_user_id from t_order
        <if test="_parameter != null">
            <include refid="Example_Where_Clause"/>
        </if>
    </select>

</mapper>

5.数据实体类Order.java

package com.eyesmoons.entity;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;

@Table(name = "t_order")
public class Order {
    @Id
    @Column(name = "order_id")
    private Integer orderId;

    @Column(name = "user_id")
    private Integer userId;

    private String status;

    /**
     * @return order_id
     */
    public Integer getOrderId() {
        return orderId;
    }

    /**
     * @param orderId
     */
    public void setOrderId(Integer orderId) {
        this.orderId = orderId;
    }

    /**
     * @return user_id
     */
    public Integer getUserId() {
        return userId;
    }

    /**
     * @param userId
     */
    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    /**
     * @return status
     */
    public String getStatus() {
        return status;
    }

    /**
     * @param status
     */
    public void setStatus(String status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return "Order{" +
                "orderId=" + orderId +
                ", userId=" + userId +
                ", status='" + status + '\'' +
                '}';
    }
}

6.查询条件封装类OrderExample.java

package com.eyesmoons.entity;

import java.util.ArrayList;
import java.util.List;

public class OrderExample {
    protected String orderByClause;

    protected boolean distinct;

    protected List<Criteria> oredCriteria;

    public OrderExample() {
        oredCriteria = new ArrayList<Criteria>();
    }

    public void setOrderByClause(String orderByClause) {
        this.orderByClause = orderByClause;
    }

    public String getOrderByClause() {
        return orderByClause;
    }

    public void setDistinct(boolean distinct) {
        this.distinct = distinct;
    }

    public boolean isDistinct() {
        return distinct;
    }

    public List<Criteria> getOredCriteria() {
        return oredCriteria;
    }

    public void or(Criteria criteria) {
        oredCriteria.add(criteria);
    }

    public Criteria or() {
        Criteria criteria = createCriteriaInternal();
        oredCriteria.add(criteria);
        return criteria;
    }

    public Criteria createCriteria() {
        Criteria criteria = createCriteriaInternal();
        if (oredCriteria.size() == 0) {
            oredCriteria.add(criteria);
        }
        return criteria;
    }

    protected Criteria createCriteriaInternal() {
        Criteria criteria = new Criteria();
        return criteria;
    }

    public void clear() {
        oredCriteria.clear();
        orderByClause = null;
        distinct = false;
    }

    protected abstract static class GeneratedCriteria {
        protected List<Criterion> criteria;

        protected GeneratedCriteria() {
            super();
            criteria = new ArrayList<Criterion>();
        }

        public boolean isValid() {
            return criteria.size() > 0;
        }

        public List<Criterion> getAllCriteria() {
            return criteria;
        }

        public List<Criterion> getCriteria() {
            return criteria;
        }

        protected void addCriterion(String condition) {
            if (condition == null) {
                throw new RuntimeException("Value for condition cannot be null");
            }
            criteria.add(new Criterion(condition));
        }

        protected void addCriterion(String condition, Object value, String property) {
            if (value == null) {
                throw new RuntimeException("Value for " + property + " cannot be null");
            }
            criteria.add(new Criterion(condition, value));
        }

        protected void addCriterion(String condition, Object value1, Object value2, String property) {
            if (value1 == null || value2 == null) {
                throw new RuntimeException("Between values for " + property + " cannot be null");
            }
            criteria.add(new Criterion(condition, value1, value2));
        }

        public Criteria andOrderIdIsNull() {
            addCriterion("order_id is null");
            return (Criteria) this;
        }

        public Criteria andOrderIdIsNotNull() {
            addCriterion("order_id is not null");
            return (Criteria) this;
        }

        public Criteria andOrderIdEqualTo(Integer value) {
            addCriterion("order_id =", value, "orderId");
            return (Criteria) this;
        }

        public Criteria andOrderIdNotEqualTo(Integer value) {
            addCriterion("order_id <>", value, "orderId");
            return (Criteria) this;
        }

        public Criteria andOrderIdGreaterThan(Integer value) {
            addCriterion("order_id >", value, "orderId");
            return (Criteria) this;
        }

        public Criteria andOrderIdGreaterThanOrEqualTo(Integer value) {
            addCriterion("order_id >=", value, "orderId");
            return (Criteria) this;
        }

        public Criteria andOrderIdLessThan(Integer value) {
            addCriterion("order_id <", value, "orderId");
            return (Criteria) this;
        }

        public Criteria andOrderIdLessThanOrEqualTo(Integer value) {
            addCriterion("order_id <=", value, "orderId");
            return (Criteria) this;
        }

        public Criteria andOrderIdIn(List<Integer> values) {
            addCriterion("order_id in", values, "orderId");
            return (Criteria) this;
        }

        public Criteria andOrderIdNotIn(List<Integer> values) {
            addCriterion("order_id not in", values, "orderId");
            return (Criteria) this;
        }

        public Criteria andOrderIdBetween(Integer value1, Integer value2) {
            addCriterion("order_id between", value1, value2, "orderId");
            return (Criteria) this;
        }

        public Criteria andOrderIdNotBetween(Integer value1, Integer value2) {
            addCriterion("order_id not between", value1, value2, "orderId");
            return (Criteria) this;
        }

        public Criteria andUserIdIsNull() {
            addCriterion("user_id is null");
            return (Criteria) this;
        }

        public Criteria andUserIdIsNotNull() {
            addCriterion("user_id is not null");
            return (Criteria) this;
        }

        public Criteria andUserIdEqualTo(Integer value) {
            addCriterion("user_id =", value, "userId");
            return (Criteria) this;
        }

        public Criteria andUserIdNotEqualTo(Integer value) {
            addCriterion("user_id <>", value, "userId");
            return (Criteria) this;
        }

        public Criteria andUserIdGreaterThan(Integer value) {
            addCriterion("user_id >", value, "userId");
            return (Criteria) this;
        }

        public Criteria andUserIdGreaterThanOrEqualTo(Integer value) {
            addCriterion("user_id >=", value, "userId");
            return (Criteria) this;
        }

        public Criteria andUserIdLessThan(Integer value) {
            addCriterion("user_id <", value, "userId");
            return (Criteria) this;
        }

        public Criteria andUserIdLessThanOrEqualTo(Integer value) {
            addCriterion("user_id <=", value, "userId");
            return (Criteria) this;
        }

        public Criteria andUserIdIn(List<Integer> values) {
            addCriterion("user_id in", values, "userId");
            return (Criteria) this;
        }

        public Criteria andUserIdNotIn(List<Integer> values) {
            addCriterion("user_id not in", values, "userId");
            return (Criteria) this;
        }

        public Criteria andUserIdBetween(Integer value1, Integer value2) {
            addCriterion("user_id between", value1, value2, "userId");
            return (Criteria) this;
        }

        public Criteria andUserIdNotBetween(Integer value1, Integer value2) {
            addCriterion("user_id not between", value1, value2, "userId");
            return (Criteria) this;
        }

        public Criteria andStatusIsNull() {
            addCriterion("`status` is null");
            return (Criteria) this;
        }

        public Criteria andStatusIsNotNull() {
            addCriterion("`status` is not null");
            return (Criteria) this;
        }

        public Criteria andStatusEqualTo(String value) {
            addCriterion("`status` =", value, "status");
            return (Criteria) this;
        }

        public Criteria andStatusNotEqualTo(String value) {
            addCriterion("`status` <>", value, "status");
            return (Criteria) this;
        }

        public Criteria andStatusGreaterThan(String value) {
            addCriterion("`status` >", value, "status");
            return (Criteria) this;
        }

        public Criteria andStatusGreaterThanOrEqualTo(String value) {
            addCriterion("`status` >=", value, "status");
            return (Criteria) this;
        }

        public Criteria andStatusLessThan(String value) {
            addCriterion("`status` <", value, "status");
            return (Criteria) this;
        }

        public Criteria andStatusLessThanOrEqualTo(String value) {
            addCriterion("`status` <=", value, "status");
            return (Criteria) this;
        }

        public Criteria andStatusLike(String value) {
            addCriterion("`status` like", value, "status");
            return (Criteria) this;
        }

        public Criteria andStatusNotLike(String value) {
            addCriterion("`status` not like", value, "status");
            return (Criteria) this;
        }

        public Criteria andStatusIn(List<String> values) {
            addCriterion("`status` in", values, "status");
            return (Criteria) this;
        }

        public Criteria andStatusNotIn(List<String> values) {
            addCriterion("`status` not in", values, "status");
            return (Criteria) this;
        }

        public Criteria andStatusBetween(String value1, String value2) {
            addCriterion("`status` between", value1, value2, "status");
            return (Criteria) this;
        }

        public Criteria andStatusNotBetween(String value1, String value2) {
            addCriterion("`status` not between", value1, value2, "status");
            return (Criteria) this;
        }
    }

    public static class Criteria extends GeneratedCriteria {

        protected Criteria() {
            super();
        }
    }

    public static class Criterion {
        private String condition;

        private Object value;

        private Object secondValue;

        private boolean noValue;

        private boolean singleValue;

        private boolean betweenValue;

        private boolean listValue;

        private String typeHandler;

        public String getCondition() {
            return condition;
        }

        public Object getValue() {
            return value;
        }

        public Object getSecondValue() {
            return secondValue;
        }

        public boolean isNoValue() {
            return noValue;
        }

        public boolean isSingleValue() {
            return singleValue;
        }

        public boolean isBetweenValue() {
            return betweenValue;
        }

        public boolean isListValue() {
            return listValue;
        }

        public String getTypeHandler() {
            return typeHandler;
        }

        protected Criterion(String condition) {
            super();
            this.condition = condition;
            this.typeHandler = null;
            this.noValue = true;
        }

        protected Criterion(String condition, Object value, String typeHandler) {
            super();
            this.condition = condition;
            this.value = value;
            this.typeHandler = typeHandler;
            if (value instanceof List<?>) {
                this.listValue = true;
            } else {
                this.singleValue = true;
            }
        }

        protected Criterion(String condition, Object value) {
            this(condition, value, null);
        }

        protected Criterion(String condition, Object value, Object secondValue, String typeHandler) {
            super();
            this.condition = condition;
            this.value = value;
            this.secondValue = secondValue;
            this.typeHandler = typeHandler;
            this.betweenValue = true;
        }

        protected Criterion(String condition, Object value, Object secondValue) {
            this(condition, value, secondValue, null);
        }
    }
}

六.测试类TestOrder.java

package com.eyesmoons;

import java.util.List;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.eyesmoons.entity.Order;
import com.eyesmoons.entity.OrderExample;
import com.eyesmoons.service.OrderService;
import com.google.common.collect.Lists;

public class TestOrder {

    public static void main(String[] args) {
        ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
        OrderService orderService = ctx.getBean(OrderService.class);
        testGetAllOrder(orderService);
        ctx.close();
    }
 
    /**
     * 描述: 添加订单数据
     * @author shengyu
     * @param orderService 
     * @date:2019年11月4日 下午5:54:30
     */
    private static void testAddOrder(OrderService orderService){
    	orderService.deleteAll();
    	
    	orderService.addOrder(buildOrder(1, 1, "NEW"));
        orderService.addOrder(buildOrder(2, 1, "NEW"));
        orderService.addOrder(buildOrder(3, 1, "NEW"));
        orderService.addOrder(buildOrder(4, 1, "NEW"));

        orderService.addOrder(buildOrder(1, 2, "NEW"));
        orderService.addOrder(buildOrder(2, 2, "NEW"));
        orderService.addOrder(buildOrder(3, 2, "NEW"));
        orderService.addOrder(buildOrder(4, 2, "NEW"));

        orderService.addOrder(buildOrder(5, 3, "NEW"));
        orderService.addOrder(buildOrder(6, 3, "NEW"));
        orderService.addOrder(buildOrder(7, 3, "NEW"));
        orderService.addOrder(buildOrder(8, 3, "NEW"));
    }
    
    /**
     * 描述: 更新单条订单数据
     * @author shengyu
     * @param orderService 
     * @date:2019年11月4日 下午5:54:44
     */
    private static void testUpdateOrder(OrderService orderService){
    	Order o = new Order();
        o.setOrderId(1);
        o.setUserId(1);
        o.setStatus("UPDATED");
        orderService.update(o);
    }
    
    /**
     * 描述: 批量更新订单数据
     * @author shengyu 
     * @date:2019年11月4日 下午5:55:54
     */
    private static void testupdateOrders(OrderService orderService){
    	orderService.updateOrders(Lists.newArrayList(1, 2), "UPDATED");
    }
    
    /**
     * 描述: 返回所有订单
     * @author shengyu
     * @param orderService 
     * @date:2019年11月4日 下午5:57:00
     */
    private static void testGetAllOrder(OrderService orderService){
    	List<Order> orders = orderService.getAllOrder();
    	orders.forEach((order)->{
    		System.out.println(order.toString());
    	});
    }
    
    /**
     * 描述: 删除所有订单
     * @author shengyu
     * @param orderService 
     * @date:2019年11月4日 下午5:58:23
     */
    public static void testDeleteAll(OrderService orderService){
    	orderService.deleteAll();
    }
    
    /**
     * 描述: 查询订单总数
     * @author shengyu
     * @param orderService 
     * @date:2019年11月4日 下午5:59:52
     */
    public static void testGetCount(OrderService orderService){
    	OrderExample example = new OrderExample();
        int count = orderService.getCount(example);
        System.out.println(String.format("count => %d", count));
        example.createCriteria()
                .andUserIdBetween(1, 2)
                .andOrderIdBetween(2, 4);
        count = orderService.getCount(example);
        System.out.println(String.format("count => %d", count));
    }
    
    /**
     * 描述: 返回最大订单号
     * @author shengyu
     * @param orderService 
     * @date:2019年11月4日 下午6:01:01
     */
    public static void testMaxCount(OrderService orderService){
    	int maxOrderId = orderService.getMaxOrderId(null);
        System.out.println(String.format("maxOrderId => %d", maxOrderId));
        OrderExample example = new OrderExample();
        example.createCriteria()
                .andUserIdEqualTo(2);
        maxOrderId = orderService.getMaxOrderId(example);
        System.out.println(String.format("maxOrderId => %d", maxOrderId));
    }
    
    /**
     * 描述: 返回最大用户id
     * @author shengyu
     * @param orderService 
     * @date:2019年11月4日 下午6:01:56
     */
    public static void testGetMaxUserId(OrderService orderService){
    	int maxUserId = orderService.getMaxUserId(null);
        System.out.println(String.format("maxUserId => %d", maxUserId));
        OrderExample example = new OrderExample();
        example.createCriteria()
                .andOrderIdBetween(3, 6);
        maxUserId = orderService.getMaxUserId(example);
        System.out.println(String.format("maxUserId => %d", maxUserId));
    }

    private static Order buildOrder(int orderId, int userId, String status) {
        Order o = new Order();
        o.setOrderId(orderId);
        o.setUserId(userId);
        o.setStatus(status);
        return o;
    }
}

七.完整代码下载

https://github.com/eyesmoons/sharding-jdbc-demo

代码使用步骤：

1.将项目导入eclipse

2.使用sql下的init.sql创建数据库和数据表

3.修改jdbc.properties中的数据库连接

4.运行TestOrder类中的main方法进行测试

如果觉得我的文章对你有用，请随意赞赏

扫一扫支付