自己简单实现分表查询（按月份分表）

自己简单实现分表查询（按月份分表）

开发过程中某几个表需要按照特定规则分表，下面加单实现下。
数据库用的mysql

model类

/*
 *      Copyright (c) 2018-2028, Chill Zhuang All rights reserved.
 *
 *  Redistribution and use in source and binary forms, with or without
 *  modification, are permitted provided that the following conditions are met:
 *
 *  Redistributions of source code must retain the above copyright notice,
 *  this list of conditions and the following disclaimer.
 *  Redistributions in binary form must reproduce the above copyright
 *  notice, this list of conditions and the following disclaimer in the
 *  documentation and/or other materials provided with the distribution.
 *  Neither the name of the dreamlu.net developer nor the names of its
 *  contributors may be used to endorse or promote products derived from
 *  this software without specific prior written permission.
 *  Author: Chill 庄骞 (smallchill@163.com)
 */
package org.springblade.business.entity;

import java.util.Date;
import java.util.List;

import com.baomidou.mybatisplus.annotation.TableField;
import org.springblade.core.mp.base.BaseEntity;
import lombok.Data;
import lombok.EqualsAndHashCode;

/**
 * 表实体类
 *
 * @author BladeX
 * @since 2022-09-07
 */
@Data
@EqualsAndHashCode(callSuper = true)
public class Tbilllog extends BaseEntity {

	private static final long serialVersionUID = 1L;

	/**
	 * 开始时间
	 */
	@TableField(exist = false)
	private Date dateRangeStart;
	/**
	 * 结束时间
	 */
	@TableField(exist = false)
	private Date dateRangeEnd;

	/**
	 * 表名
	 */
	@TableField(exist = false)
	private String tableName;

	/**
	 * 表名列表
	 */
	@TableField(exist = false)
	private List<String> tableNames;

	/**
	 * 表名列表和表名
	 */
	@TableField(exist = false)
	private List<Map<String, String>> tableNamesAndParts;

	/**
	 * 表分区列表
	 */
	@TableField(exist = false)
	private List<String> partitionNames;


}

判断表名是否存在

/**
	 * @desc: 获取分表表名
	 * @author: guo-xz
	 * @date: 2022/9/21
	 * @param: tbilllog
	 * @return:
	 **/
	public void getTables(Tbilllog model) throws Exception {
		List<String> tableList = new ArrayList<>();
		List<String> partList = new ArrayList<>();
		// 判断是否存在 时间查询
		if(model.getDateRangeStart() != null){
			// 获取两个日期之间的所有月份 (年月)
			tableList = TableUtils.getMonthBetweenDate(model.getDateRangeStart(), model.getDateRangeEnd());
			partList = TableUtils.getDateBetweenDate(model.getDateRangeStart(), model.getDateRangeEnd());
		}else{
			// 获取两个日期之间的所有月份 (年月)
			tableList = TableUtils.getMonth();
			partList = TableUtils.getMonthPart();
		}
		List<Map<String, String>> list = setPart("TBILLLOG", tableList, partList);
		if(list.size() <= 0){
			throw new Exception("查询表不存在");
		}
		// 将 已存在表返回给模型
		model.setTableNamesAndParts(list);
	}
	/**
	 * @desc: 处理多表多分区
	 * @author: guo-xz
	 * @date: 2022/10/10
	 * @param: tableList
	 * @param partList
	 * @return: String
	 **/
	public List<Map<String, String>> setPart(String tableName, List<String> tableList, List<String> partList){
		// 定义一个存储已存在表的集合
		List<Map<String, String>> tableListIs = new ArrayList<>();
		// 遍历月份集合
		tableList.stream().map(item->{
			// 判断所在月份是否存在对应表
			String tableNameMounth = tableName + item;
			Integer is = baseMapper.IsTable(tableNameMounth);
			if(Func.isNotEmpty(is) && is > 0){
				// 遍历月份集合
				partList.stream().map(itemPart->{
					// 判断所在月份是否存在对应表
					Integer isPart = baseMapper.isPartition(tableNameMounth, "PART" + itemPart);
					if(Func.isNotEmpty(isPart) && isPart > 0){
						Map<String, String> mapTable = new HashMap<>();
						// 存储表名 + PARTITION + 分区名
						mapTable.put("tableName", tableNameMounth);
						mapTable.put("partName", "PART" + itemPart);
						tableListIs.add(mapTable);
					}
					return item;
				}).collect(Collectors.toList());
			}
			return item;
		}).collect(Collectors.toList());
		return tableListIs;
	}

获取两个时间之间所有月份

package org.springblade.business.utils;

import org.springblade.core.tool.utils.Func;

import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Description:
 * @author: guo-xz
 * @Project:
 * @Date: 2022年09月21日 18:26
 */
public class TableUtils {

	/**
	 * 获取两个日期之间的所有月份 (月)
	 *
	 * @param startDate
	 * @param endDate
	 * @return：M
	 */
	public static List<String> getMonthBetweenDate(Date startDate, Date endDate){
		SimpleDateFormat sdf = new SimpleDateFormat("M");
		// 声明保存日期集合
		List<String> list = new ArrayList<String>();
		try {
			//用Calendar 进行日期比较判断
			Calendar calendar = Calendar.getInstance();
			while (startDate.getTime()<=endDate.getTime()){
				// 把日期添加到集合
				list.add(sdf.format(startDate));
				// 设置日期
				calendar.setTime(startDate);
				//把日期增加一天
				calendar.add(Calendar.DATE, 1);
				// 获取增加后的日期
				startDate=calendar.getTime();
			}
		} catch (Exception e) {

		}
		if(list != null && list.size() > 0){
			LinkedHashSet<String> hashSet = new LinkedHashSet<>(list);
			list = new ArrayList<>(hashSet);
		}
		return list;
	}
	/**
	 * 获取两个日期之间的所有月份 (年_月)
	 *
	 * @param startDate
	 * @param endDate
	 * @return：YYYY_MM
	 */
	public static List<String> getDateBetweenDate(Date startDate, Date endDate){
		SimpleDateFormat sdf = new SimpleDateFormat("YYYY_MM");
		// 声明保存日期集合
		List<String> list = new ArrayList<String>();
		try {
			//用Calendar 进行日期比较判断
			Calendar calendar = Calendar.getInstance();
			while (startDate.getTime()<=endDate.getTime()){
				// 把日期添加到集合
				list.add(sdf.format(startDate));
				// 设置日期
				calendar.setTime(startDate);
				//把日期增加一天
				calendar.add(Calendar.DATE, 1);
				// 获取增加后的日期
				startDate=calendar.getTime();
			}
		} catch (Exception e) {

		}
		if(list != null && list.size() > 0){
			LinkedHashSet<String> hashSet = new LinkedHashSet<>(list);
			list = new ArrayList<>(hashSet);
		}
		return list;
	}
	/**
	 * 获取两个日期之间的所有月份 (月)
	 *
	 * @return：M
	 */
	public static List<String> getMonth(){
		return getMonth(null);
	}

	/**
	 * 获取两个日期之间的所有月份 (月)
	 *
	 * @return：M
	 */
	public static List<String> getMonth(String tableName){
		SimpleDateFormat sdf = new SimpleDateFormat("M");
		// 声明保存日期集合
		List<String> list = new ArrayList<String>();
		Date date = new Date();
		// 把日期添加到集合
		if(Func.isNoneBlank(tableName)){
			list.add(tableName + sdf.format(date));
		}else{
			list.add(sdf.format(date));
		}
		if(list != null && list.size() > 0){
			LinkedHashSet<String> hashSet = new LinkedHashSet<>(list);
			list = new ArrayList<>(hashSet);
		}
		return list;
	}
	/**
	 * 获取两个日期之间的所有月份 (月)
	 *
	 * @return：YYYY_MM
	 */
	public static List<String> getMonthPart(){
		return getMonthPart(null);
	}

	/**
	 * 获取两个日期之间的所有月份 (月)
	 *
	 * @return：YYYY_MM
	 */
	public static List<String> getMonthPart(String partName){
		SimpleDateFormat sdf = new SimpleDateFormat("YYYY_MM");
		// 声明保存日期集合
		List<String> list = new ArrayList<>();
		Date date = new Date();
		// 把日期添加到集合
		if(Func.isNoneBlank(partName)){
			list.add(partName + sdf.format(date));
		}else{
			list.add(sdf.format(date));
		}
		if(list != null && list.size() > 0){
			LinkedHashSet<String> hashSet = new LinkedHashSet<>(list);
			list = new ArrayList<>(hashSet);
		}
		return list;
	}
}

mapper

	/**
	 * 查询表名是否存在
	 *
	 * @param tableName
	 * @return
	 */
	Integer IsTable(@Param("tableName") String tableName);

在xml中处理

<!-- 判断表是否存在 -->
    <select id="IsTable" resultType="Integer">
        select count(1) count from information_schema.TABLES
        where TABLE_NAME = #{tableName};
    </select>

<!-- 查询-分表 -->
    <sql id="tbill_log_all">
        <choose>
            <!-- 分表-分区 -->
            <when test="ew.tableNamesAndParts != null and ew.tableNamesAndParts.size() > 0">
                (
                <foreach collection="ew.tableNamesAndParts" item="item" separator=" UNION ALL ">
                    SELECT CALLID,
                    CALLBEGIN
                    FROM ${item.tableName} PARTITION (${item.partName}) where CALLIDNUM = 1
                </foreach>
                )
            </when>
            <!-- 不分表 -->
            <otherwise>
                tbilllog
            </otherwise>
        </choose>
    </sql>

    <!-- 查询sql -->
    <sql id="call_loss">
        SELECT log.*
        FROM <include refid="tbill_log_all"></include> log
        <where>
            <include refid="query_where"></include>
        </where>
    </sql>

    <!-- 业务查询 -->
    <select id="callLossBill" resultMap="tbilllogResultMap">
        <include refid="call_loss"></include>
    </select>

完整sql演示

<select id="selectTbilllogVildePage" resultMap="tbilllogResultMap">
        select log.CALLERNO,
        log.CALLEENO,
        log.DEVICENO,
        log.DEVICETYPE,
        CASE
        WHEN log2.CALLBEGIN is null
        THEN
        log.CALLBEGIN
        ELSE
        log2.CALLBEGIN
        END CALLBEGIN,
        log.CALLEND,
        log.LEAVEREASON,
        log.WAITBEGIN,
        log.WAITEND,
        ceil( TO_NUMBER ( log.WAITEND - log.WAITBEGIN ) * 24 * 60 * 60 )  diff,
        ceil( TO_NUMBER ( log.CALLEND - (
        CASE
        WHEN log2.CALLBEGIN is null
        THEN
        log.CALLBEGIN
        ELSE
        log2.CALLBEGIN
        END
        ) ) * 24 * 60 * 60 )  callDiff
        from (
        <foreach collection="ew.tableNamesAndParts" item="item" separator=" UNION ALL ">
            SELECT CALLID,
            CALLIDNUM,
            DEVICENO,
            CALLERNO,
            CALLEENO,
            CALLTYPE,
            CALLBEGIN,
            CALLEND,
            WAITBEGIN,
            WAITEND,
            ACKBEGIN,
            ACKEND,
            DEVICETYPE,
            DEVICEIN,
            LEAVEREASON FROM ${item.tableName} PARTITION (${item.partName})
            <where>
                CALLIDNUM = -1
                <include refid="query_where_table"></include>
            </where>
        </foreach>
        ) log
        left join <include refid="tbill_log_all_vilde_part"></include> log2 on log.CALLID = log2.CALLID
    </select>