自己简单实现分表查询(按月份分表)
开发过程中某几个表需要按照特定规则分表,下面加单实现下。
数据库用的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>