网站首页 文章专栏 分库分表插件之sharding-jdbc
分库分表插件之sharding-jdbc
编辑时间：2020-05-09 20:14
作者：毛毛小妖
浏览量：585
评论数：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方法进行测试
推荐文章
最新评论
麻烦博主贴一下 com.eyesmoons.list.ListUtil 类的代码呗
—— 来自 jackW2020-05-08 10:13回复