月眸
月眸
GitHub Gitee cnblogs
月眸

分库分表插件之sharding-jdbc

毛毛小妖 2019-11-05 1450浏览 2条评论
首页/正文
分享到: / / / /

随着业务复杂度的提升，数据库方面的压力也越来越大，单个库表已经不能满足我们的要求了，这个时候急需一套分库分表的解决方案。当然了，分库分表中间件也有很多，比如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方法进行测试

最后修改：2019-11-05 10:39:46 © 著作权归作者所有
如果觉得我的文章对你有用，请随意赞赏
扫一扫支付
上一篇
登录即可评论哦~

评论列表

毛毛小妖 2020-05-08 10:15:52
<p>详细代码请看我的github，代码都在里边：<a target="_blank" href="https://github.com/eyesmoons/sharding-jdbc-demo">https://github.com/eyesmoons/sharding-jdbc-demo</a></p>
jackW 2020-05-08 10:13:02
<p>麻烦博主贴一下&nbsp;<span>com.eyesmoons.list.ListUtil&nbsp;类的代码呗</span></p>
回复
jackW 2020-05-08 10:13:02
<p>麻烦博主贴一下&nbsp;<span>com.eyesmoons.list.ListUtil&nbsp;类的代码呗</span></p>
回复
1

热门文章

博客信息

  • 文章数目 95
  • 标签数目 54
  • 运行天数
  • 最后活动

开心一刻

标签云

并发 mycode 算法 MQ zookeeper java jquery Hadoop JVM spring 多线程 javascript POI linux mysql 二叉树 测试 Git aop redis webService Dubbo 文件上传 oracle mongoDB echarts qq互联 排序 Lombok HashMap Dos java堆栈 springboot mybatis 模态 Lock 注解 Idea navicat css office预览 Reference bigData 链表 集合 elasticSearch nginx 达梦 Collectors kafka flume 即席查询 Hive eclipse

文章目录

Copyright © www.eyesmoons.com All Rights Reserved 京ICP备19002323号-1
网站地图