From 7a4f36c00edde00a77937b92a95dbaa85453d235 Mon Sep 17 00:00:00 2001
From: 疯狂的狮子li <15040126243@163.com>
Date: 星期三, 24 十一月 2021 11:24:15 +0800
Subject: [PATCH] update 优化时间查询语句
---
ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysOperLogServiceImpl.java | 62 +++++++++-----------
ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysLogininforServiceImpl.java | 32 ++++------
ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysDictTypeServiceImpl.java | 38 +++++-------
ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysConfigServiceImpl.java | 30 +++------
4 files changed, 65 insertions(+), 97 deletions(-)
diff --git a/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysConfigServiceImpl.java b/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysConfigServiceImpl.java
index 788aa77..bbd09a1 100644
--- a/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysConfigServiceImpl.java
+++ b/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysConfigServiceImpl.java
@@ -35,15 +35,11 @@
public TableDataInfo<SysConfig> selectPageConfigList(SysConfig config) {
Map<String, Object> params = config.getParams();
LambdaQueryWrapper<SysConfig> lqw = new LambdaQueryWrapper<SysConfig>()
- .like(StringUtils.isNotBlank(config.getConfigName()), SysConfig::getConfigName, config.getConfigName())
- .eq(StringUtils.isNotBlank(config.getConfigType()), SysConfig::getConfigType, config.getConfigType())
- .like(StringUtils.isNotBlank(config.getConfigKey()), SysConfig::getConfigKey, config.getConfigKey())
- .apply(StringUtils.isNotEmpty(params.get("beginTime")),
- "date_format(create_time,'%y%m%d') >= date_format({0},'%y%m%d')",
- params.get("beginTime"))
- .apply(StringUtils.isNotEmpty(params.get("endTime")),
- "date_format(create_time,'%y%m%d') <= date_format({0},'%y%m%d')",
- params.get("endTime"));
+ .like(StringUtils.isNotBlank(config.getConfigName()), SysConfig::getConfigName, config.getConfigName())
+ .eq(StringUtils.isNotBlank(config.getConfigType()), SysConfig::getConfigType, config.getConfigType())
+ .like(StringUtils.isNotBlank(config.getConfigKey()), SysConfig::getConfigKey, config.getConfigKey())
+ .between(params.get("beginTime") != null && params.get("endTime") != null,
+ SysConfig::getCreateTime, params.get("beginTime"), params.get("endTime"));
return PageUtils.buildDataInfo(page(PageUtils.buildPage(), lqw));
}
@@ -72,7 +68,7 @@
return configValue;
}
SysConfig retConfig = baseMapper.selectOne(new LambdaQueryWrapper<SysConfig>()
- .eq(SysConfig::getConfigKey, configKey));
+ .eq(SysConfig::getConfigKey, configKey));
if (StringUtils.isNotNull(retConfig)) {
RedisUtils.setCacheObject(getCacheKey(configKey), retConfig.getConfigValue());
return retConfig.getConfigValue();
@@ -104,15 +100,11 @@
public List<SysConfig> selectConfigList(SysConfig config) {
Map<String, Object> params = config.getParams();
LambdaQueryWrapper<SysConfig> lqw = new LambdaQueryWrapper<SysConfig>()
- .like(StringUtils.isNotBlank(config.getConfigName()), SysConfig::getConfigName, config.getConfigName())
- .eq(StringUtils.isNotBlank(config.getConfigType()), SysConfig::getConfigType, config.getConfigType())
- .like(StringUtils.isNotBlank(config.getConfigKey()), SysConfig::getConfigKey, config.getConfigKey())
- .apply(StringUtils.isNotEmpty(params.get("beginTime")),
- "date_format(create_time,'%y%m%d') >= date_format({0},'%y%m%d')",
- params.get("beginTime"))
- .apply(StringUtils.isNotEmpty(params.get("endTime")),
- "date_format(create_time,'%y%m%d') <= date_format({0},'%y%m%d')",
- params.get("endTime"));
+ .like(StringUtils.isNotBlank(config.getConfigName()), SysConfig::getConfigName, config.getConfigName())
+ .eq(StringUtils.isNotBlank(config.getConfigType()), SysConfig::getConfigType, config.getConfigType())
+ .like(StringUtils.isNotBlank(config.getConfigKey()), SysConfig::getConfigKey, config.getConfigKey())
+ .between(params.get("beginTime") != null && params.get("endTime") != null,
+ SysConfig::getCreateTime, params.get("beginTime"), params.get("endTime"));
return baseMapper.selectList(lqw);
}
diff --git a/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysDictTypeServiceImpl.java b/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysDictTypeServiceImpl.java
index e01e624..b520d41 100644
--- a/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysDictTypeServiceImpl.java
+++ b/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysDictTypeServiceImpl.java
@@ -41,15 +41,11 @@
public TableDataInfo<SysDictType> selectPageDictTypeList(SysDictType dictType) {
Map<String, Object> params = dictType.getParams();
LambdaQueryWrapper<SysDictType> lqw = new LambdaQueryWrapper<SysDictType>()
- .like(StringUtils.isNotBlank(dictType.getDictName()), SysDictType::getDictName, dictType.getDictName())
- .eq(StringUtils.isNotBlank(dictType.getStatus()), SysDictType::getStatus, dictType.getStatus())
- .like(StringUtils.isNotBlank(dictType.getDictType()), SysDictType::getDictType, dictType.getDictType())
- .apply(StringUtils.isNotEmpty(params.get("beginTime")),
- "date_format(create_time,'%y%m%d') >= date_format({0},'%y%m%d')",
- params.get("beginTime"))
- .apply(StringUtils.isNotEmpty(params.get("endTime")),
- "date_format(create_time,'%y%m%d') <= date_format({0},'%y%m%d')",
- params.get("endTime"));
+ .like(StringUtils.isNotBlank(dictType.getDictName()), SysDictType::getDictName, dictType.getDictName())
+ .eq(StringUtils.isNotBlank(dictType.getStatus()), SysDictType::getStatus, dictType.getStatus())
+ .like(StringUtils.isNotBlank(dictType.getDictType()), SysDictType::getDictType, dictType.getDictType())
+ .between(params.get("beginTime") != null && params.get("endTime") != null,
+ SysDictType::getCreateTime, params.get("beginTime"), params.get("endTime"));
return PageUtils.buildDataInfo(page(PageUtils.buildPage(), lqw));
}
@@ -63,15 +59,11 @@
public List<SysDictType> selectDictTypeList(SysDictType dictType) {
Map<String, Object> params = dictType.getParams();
return list(new LambdaQueryWrapper<SysDictType>()
- .like(StringUtils.isNotBlank(dictType.getDictName()), SysDictType::getDictName, dictType.getDictName())
- .eq(StringUtils.isNotBlank(dictType.getStatus()), SysDictType::getStatus, dictType.getStatus())
- .like(StringUtils.isNotBlank(dictType.getDictType()), SysDictType::getDictType, dictType.getDictType())
- .apply(StringUtils.isNotEmpty(params.get("beginTime")),
- "date_format(create_time,'%y%m%d') >= date_format({0},'%y%m%d')",
- params.get("beginTime"))
- .apply(StringUtils.isNotEmpty(params.get("endTime")),
- "date_format(create_time,'%y%m%d') <= date_format({0},'%y%m%d')",
- params.get("endTime")));
+ .like(StringUtils.isNotBlank(dictType.getDictName()), SysDictType::getDictName, dictType.getDictName())
+ .eq(StringUtils.isNotBlank(dictType.getStatus()), SysDictType::getStatus, dictType.getStatus())
+ .like(StringUtils.isNotBlank(dictType.getDictType()), SysDictType::getDictType, dictType.getDictType())
+ .between(params.get("beginTime") != null && params.get("endTime") != null,
+ SysDictType::getCreateTime, params.get("beginTime"), params.get("endTime")));
}
/**
@@ -137,7 +129,7 @@
for (Long dictId : dictIds) {
SysDictType dictType = selectDictTypeById(dictId);
if (dictDataMapper.selectCount(new LambdaQueryWrapper<SysDictData>()
- .eq(SysDictData::getDictType, dictType.getDictType())) > 0) {
+ .eq(SysDictData::getDictType, dictType.getDictType())) > 0) {
throw new ServiceException(String.format("%1$s宸插垎閰�,涓嶈兘鍒犻櫎", dictType.getDictName()));
}
RedisUtils.deleteObject(getCacheKey(dictType.getDictType()));
@@ -201,8 +193,8 @@
public int updateDictType(SysDictType dict) {
SysDictType oldDict = getById(dict.getDictId());
dictDataMapper.update(null, new LambdaUpdateWrapper<SysDictData>()
- .set(SysDictData::getDictType, dict.getDictType())
- .eq(SysDictData::getDictType, oldDict.getDictType()));
+ .set(SysDictData::getDictType, dict.getDictType())
+ .eq(SysDictData::getDictType, oldDict.getDictType()));
int row = baseMapper.updateById(dict);
if (row > 0) {
List<SysDictData> dictDatas = dictDataMapper.selectDictDataByType(dict.getDictType());
@@ -221,8 +213,8 @@
public String checkDictTypeUnique(SysDictType dict) {
Long dictId = StringUtils.isNull(dict.getDictId()) ? -1L : dict.getDictId();
long count = count(new LambdaQueryWrapper<SysDictType>()
- .eq(SysDictType::getDictType, dict.getDictType())
- .ne(SysDictType::getDictId, dictId));
+ .eq(SysDictType::getDictType, dict.getDictType())
+ .ne(SysDictType::getDictId, dictId));
if (count > 0) {
return UserConstants.NOT_UNIQUE;
}
diff --git a/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysLogininforServiceImpl.java b/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysLogininforServiceImpl.java
index dda5b5d..0ce33be 100644
--- a/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysLogininforServiceImpl.java
+++ b/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysLogininforServiceImpl.java
@@ -90,16 +90,12 @@
public TableDataInfo<SysLogininfor> selectPageLogininforList(SysLogininfor logininfor) {
Map<String, Object> params = logininfor.getParams();
LambdaQueryWrapper<SysLogininfor> lqw = new LambdaQueryWrapper<SysLogininfor>()
- .like(StringUtils.isNotBlank(logininfor.getIpaddr()), SysLogininfor::getIpaddr, logininfor.getIpaddr())
- .eq(StringUtils.isNotBlank(logininfor.getStatus()), SysLogininfor::getStatus, logininfor.getStatus())
- .like(StringUtils.isNotBlank(logininfor.getUserName()), SysLogininfor::getUserName, logininfor.getUserName())
- .apply(StringUtils.isNotEmpty(params.get("beginTime")),
- "date_format(login_time,'%y%m%d') >= date_format({0},'%y%m%d')",
- params.get("beginTime"))
- .apply(StringUtils.isNotEmpty(params.get("endTime")),
- "date_format(login_time,'%y%m%d') <= date_format({0},'%y%m%d')",
- params.get("endTime"));
- return PageUtils.buildDataInfo(page(PageUtils.buildPage("info_id","desc"), lqw));
+ .like(StringUtils.isNotBlank(logininfor.getIpaddr()), SysLogininfor::getIpaddr, logininfor.getIpaddr())
+ .eq(StringUtils.isNotBlank(logininfor.getStatus()), SysLogininfor::getStatus, logininfor.getStatus())
+ .like(StringUtils.isNotBlank(logininfor.getUserName()), SysLogininfor::getUserName, logininfor.getUserName())
+ .between(params.get("beginTime") != null && params.get("endTime") != null,
+ SysLogininfor::getLoginTime, params.get("beginTime"), params.get("endTime"));
+ return PageUtils.buildDataInfo(page(PageUtils.buildPage("info_id", "desc"), lqw));
}
/**
@@ -123,16 +119,12 @@
public List<SysLogininfor> selectLogininforList(SysLogininfor logininfor) {
Map<String, Object> params = logininfor.getParams();
return list(new LambdaQueryWrapper<SysLogininfor>()
- .like(StringUtils.isNotBlank(logininfor.getIpaddr()),SysLogininfor::getIpaddr,logininfor.getIpaddr())
- .eq(StringUtils.isNotBlank(logininfor.getStatus()),SysLogininfor::getStatus,logininfor.getStatus())
- .like(StringUtils.isNotBlank(logininfor.getUserName()),SysLogininfor::getUserName,logininfor.getUserName())
- .apply(StringUtils.isNotEmpty(params.get("beginTime")),
- "date_format(login_time,'%y%m%d') >= date_format({0},'%y%m%d')",
- params.get("beginTime"))
- .apply(StringUtils.isNotEmpty(params.get("endTime")),
- "date_format(login_time,'%y%m%d') <= date_format({0},'%y%m%d')",
- params.get("endTime"))
- .orderByDesc(SysLogininfor::getInfoId));
+ .like(StringUtils.isNotBlank(logininfor.getIpaddr()), SysLogininfor::getIpaddr, logininfor.getIpaddr())
+ .eq(StringUtils.isNotBlank(logininfor.getStatus()), SysLogininfor::getStatus, logininfor.getStatus())
+ .like(StringUtils.isNotBlank(logininfor.getUserName()), SysLogininfor::getUserName, logininfor.getUserName())
+ .between(params.get("beginTime") != null && params.get("endTime") != null,
+ SysLogininfor::getLoginTime, params.get("beginTime"), params.get("endTime"))
+ .orderByDesc(SysLogininfor::getInfoId));
}
/**
diff --git a/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysOperLogServiceImpl.java b/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysOperLogServiceImpl.java
index c39ced0..c08b634 100644
--- a/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysOperLogServiceImpl.java
+++ b/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysOperLogServiceImpl.java
@@ -47,23 +47,19 @@
public TableDataInfo<SysOperLog> selectPageOperLogList(SysOperLog operLog) {
Map<String, Object> params = operLog.getParams();
LambdaQueryWrapper<SysOperLog> lqw = new LambdaQueryWrapper<SysOperLog>()
- .like(StringUtils.isNotBlank(operLog.getTitle()), SysOperLog::getTitle, operLog.getTitle())
- .eq(operLog.getBusinessType() != null && operLog.getBusinessType() > 0,
- SysOperLog::getBusinessType, operLog.getBusinessType())
- .func(f -> {
- if (ArrayUtil.isNotEmpty(operLog.getBusinessTypes())) {
- f.in(SysOperLog::getBusinessType, Arrays.asList(operLog.getBusinessTypes()));
- }
- })
- .eq(operLog.getStatus() != null,
- SysOperLog::getStatus, operLog.getStatus())
- .like(StringUtils.isNotBlank(operLog.getOperName()), SysOperLog::getOperName, operLog.getOperName())
- .apply(StringUtils.isNotEmpty(params.get("beginTime")),
- "date_format(oper_time,'%y%m%d') >= date_format({0},'%y%m%d')",
- params.get("beginTime"))
- .apply(StringUtils.isNotEmpty(params.get("endTime")),
- "date_format(oper_time,'%y%m%d') <= date_format({0},'%y%m%d')",
- params.get("endTime"));
+ .like(StringUtils.isNotBlank(operLog.getTitle()), SysOperLog::getTitle, operLog.getTitle())
+ .eq(operLog.getBusinessType() != null && operLog.getBusinessType() > 0,
+ SysOperLog::getBusinessType, operLog.getBusinessType())
+ .func(f -> {
+ if (ArrayUtil.isNotEmpty(operLog.getBusinessTypes())) {
+ f.in(SysOperLog::getBusinessType, Arrays.asList(operLog.getBusinessTypes()));
+ }
+ })
+ .eq(operLog.getStatus() != null,
+ SysOperLog::getStatus, operLog.getStatus())
+ .like(StringUtils.isNotBlank(operLog.getOperName()), SysOperLog::getOperName, operLog.getOperName())
+ .between(params.get("beginTime") != null && params.get("endTime") != null,
+ SysOperLog::getOperTime, params.get("beginTime"), params.get("endTime"));
return PageUtils.buildDataInfo(page(PageUtils.buildPage("oper_id", "desc"), lqw));
}
@@ -88,24 +84,20 @@
public List<SysOperLog> selectOperLogList(SysOperLog operLog) {
Map<String, Object> params = operLog.getParams();
return list(new LambdaQueryWrapper<SysOperLog>()
- .like(StringUtils.isNotBlank(operLog.getTitle()), SysOperLog::getTitle, operLog.getTitle())
- .eq(operLog.getBusinessType() != null && operLog.getBusinessType() > 0,
- SysOperLog::getBusinessType, operLog.getBusinessType())
- .func(f -> {
- if (ArrayUtil.isNotEmpty(operLog.getBusinessTypes())) {
- f.in(SysOperLog::getBusinessType, Arrays.asList(operLog.getBusinessTypes()));
- }
- })
- .eq(operLog.getStatus() != null && operLog.getStatus() > 0,
- SysOperLog::getStatus, operLog.getStatus())
- .like(StringUtils.isNotBlank(operLog.getOperName()), SysOperLog::getOperName, operLog.getOperName())
- .apply(StringUtils.isNotEmpty(params.get("beginTime")),
- "date_format(oper_time,'%y%m%d') >= date_format({0},'%y%m%d')",
- params.get("beginTime"))
- .apply(StringUtils.isNotEmpty(params.get("endTime")),
- "date_format(oper_time,'%y%m%d') <= date_format({0},'%y%m%d')",
- params.get("endTime"))
- .orderByDesc(SysOperLog::getOperId));
+ .like(StringUtils.isNotBlank(operLog.getTitle()), SysOperLog::getTitle, operLog.getTitle())
+ .eq(operLog.getBusinessType() != null && operLog.getBusinessType() > 0,
+ SysOperLog::getBusinessType, operLog.getBusinessType())
+ .func(f -> {
+ if (ArrayUtil.isNotEmpty(operLog.getBusinessTypes())) {
+ f.in(SysOperLog::getBusinessType, Arrays.asList(operLog.getBusinessTypes()));
+ }
+ })
+ .eq(operLog.getStatus() != null && operLog.getStatus() > 0,
+ SysOperLog::getStatus, operLog.getStatus())
+ .like(StringUtils.isNotBlank(operLog.getOperName()), SysOperLog::getOperName, operLog.getOperName())
+ .between(params.get("beginTime") != null && params.get("endTime") != null,
+ SysOperLog::getOperTime, params.get("beginTime"), params.get("endTime"))
+ .orderByDesc(SysOperLog::getOperId));
}
/**
--
Gitblit v1.9.3