You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@seata.apache.org by ji...@apache.org on 2024/01/30 10:41:47 UTC
(incubator-seata) branch 2.x updated: optimize: optimize time query conditions in the console (#6285)
This is an automated email from the ASF dual-hosted git repository.
jimin pushed a commit to branch 2.x
in repository https://gitbox.apache.org/repos/asf/incubator-seata.git
The following commit(s) were added to refs/heads/2.x by this push:
new ee6f660d89 optimize: optimize time query conditions in the console (#6285)
ee6f660d89 is described below
commit ee6f660d89f7fc3fcf509387a6fee89249010c44
Author: xingfudeshi <xi...@gmail.com>
AuthorDate: Tue Jan 30 18:41:43 2024 +0800
optimize: optimize time query conditions in the console (#6285)
---
changes/en-us/2.x.md | 1 +
changes/zh-cn/2.x.md | 1 +
.../org/apache/seata/common/util/PageUtil.java | 38 ++++++++++++++++++++++
.../console/impl/db/GlobalLockDBServiceImpl.java | 8 ++---
.../impl/db/GlobalSessionDBServiceImpl.java | 9 +++--
.../impl/file/GlobalLockFileServiceImpl.java | 6 ++--
.../impl/file/GlobalSessionFileServiceImpl.java | 5 +--
.../apache/seata/server/lock/LockManagerTest.java | 8 ++---
.../server/session/FileSessionManagerTest.java | 9 +++--
9 files changed, 62 insertions(+), 23 deletions(-)
diff --git a/changes/en-us/2.x.md b/changes/en-us/2.x.md
index 1e91ba3865..5c741aaae5 100644
--- a/changes/en-us/2.x.md
+++ b/changes/en-us/2.x.md
@@ -72,6 +72,7 @@ Add changes here for all PR submitted to the 2.x branch.
- [[#6291](https://github.com/apache/incubator-seata/pull/6291)] seata-server is developed in idea and console support output logs
- [[#6283](https://github.com/apache/incubator-seata/pull/6283)] add a compatible module to support io.seata APIs
- [[#6294](https://github.com/apache/incubator-seata/pull/6294)] split the frontend resource build process into separate profiles
+- [[#6285](https://github.com/apache/incubator-seata/pull/6285)] optimize time query conditions in the console
- [[#6297](https://github.com/apache/incubator-seata/pull/6297)] fix problem of `maven-pmd-plugin`
- [[#6298](https://github.com/apache/incubator-seata/pull/6298)] repackage name to org.apache.seata
- [[#6306](https://github.com/apache/incubator-seata/pull/6306)] replace some URL to org/apache/seata
diff --git a/changes/zh-cn/2.x.md b/changes/zh-cn/2.x.md
index 39dac2789d..d915a24254 100644
--- a/changes/zh-cn/2.x.md
+++ b/changes/zh-cn/2.x.md
@@ -70,6 +70,7 @@
- [[#6291](https://github.com/apache/incubator-seata/pull/6291)] 优化seata-server在idea等开发工具运行时，控制台未输出完整日志的问题
- [[#6283](https://github.com/apache/incubator-seata/pull/6283)] 增加兼容模块支持 io.seata APIs
- [[#6294](https://github.com/apache/incubator-seata/pull/6294)] 拆分前端资源打包流程到单独的profile
+- [[#6285](https://github.com/apache/incubator-seata/pull/6285)] 优化控台中时间查询条件不准确的问题
- [[#6297](https://github.com/apache/incubator-seata/pull/6297)] 修复 `maven-pmd-plugin` 相关的问题
- [[#6298](https://github.com/apache/incubator-seata/pull/6298)] 重命名包名为 org.apache.seata
- [[#6306](https://github.com/apache/incubator-seata/pull/6306)] 替换一些URL 至 org/apache/seata
diff --git a/common/src/main/java/org/apache/seata/common/util/PageUtil.java b/common/src/main/java/org/apache/seata/common/util/PageUtil.java
index 891ca7631f..569389a01a 100644
--- a/common/src/main/java/org/apache/seata/common/util/PageUtil.java
+++ b/common/src/main/java/org/apache/seata/common/util/PageUtil.java
@@ -169,4 +169,42 @@ public class PageUtil {
}
}
}
+
+ /**
+ * get sql for time start
+ * @param dbType
+ * @param timeColumnName
+ * @return java.lang.String
+ */
+ public static String getTimeStartSql(String dbType, String timeColumnName) {
+ switch (dbType.toLowerCase()) {
+ case "mysql":
+ case "oracle":
+ case "postgresql":
+ case "sqlserver":
+ case "dm":
+ return " and FLOOR(" + timeColumnName + "/1000) >= ? ";
+ default:
+ throw new IllegalArgumentException("The DB type :" + dbType + " is not supported yet");
+ }
+ }
+
+ /**
+ * get sql for time end
+ * @param dbType
+ * @param timeColumnName
+ * @return java.lang.String
+ */
+ public static String getTimeEndSql(String dbType, String timeColumnName) {
+ switch (dbType.toLowerCase()) {
+ case "mysql":
+ case "oracle":
+ case "postgresql":
+ case "sqlserver":
+ case "dm":
+ return " and FLOOR(" + timeColumnName + "/1000) <= ? ";
+ default:
+ throw new IllegalArgumentException("The DB type :" + dbType + " is not supported yet");
+ }
+ }
}
diff --git a/server/src/main/java/org/apache/seata/server/console/impl/db/GlobalLockDBServiceImpl.java b/server/src/main/java/org/apache/seata/server/console/impl/db/GlobalLockDBServiceImpl.java
index 4342700bb5..c27c65e096 100644
--- a/server/src/main/java/org/apache/seata/server/console/impl/db/GlobalLockDBServiceImpl.java
+++ b/server/src/main/java/org/apache/seata/server/console/impl/db/GlobalLockDBServiceImpl.java
@@ -134,12 +134,12 @@ public class GlobalLockDBServiceImpl implements GlobalLockService {
sqlParamList.add(param.getBranchId());
}
if (param.getTimeStart() != null) {
- whereConditionBuilder.append(" and gmt_create >= ? ");
- sqlParamList.add(param.getTimeStart());
+ whereConditionBuilder.append(PageUtil.getTimeStartSql(this.dbType, "gmt_create"));
+ sqlParamList.add(param.getTimeStart() / 1000);
}
if (param.getTimeEnd() != null) {
- whereConditionBuilder.append(" and gmt_create <= ? ");
- sqlParamList.add(param.getTimeEnd());
+ whereConditionBuilder.append(PageUtil.getTimeEndSql(this.dbType, "gmt_create"));
+ sqlParamList.add(param.getTimeEnd() / 1000);
}
String whereCondition = whereConditionBuilder.toString();
return whereCondition.replaceFirst("and", "where");
diff --git a/server/src/main/java/org/apache/seata/server/console/impl/db/GlobalSessionDBServiceImpl.java b/server/src/main/java/org/apache/seata/server/console/impl/db/GlobalSessionDBServiceImpl.java
index c5257d6cb2..460f2906c0 100644
--- a/server/src/main/java/org/apache/seata/server/console/impl/db/GlobalSessionDBServiceImpl.java
+++ b/server/src/main/java/org/apache/seata/server/console/impl/db/GlobalSessionDBServiceImpl.java
@@ -21,7 +21,6 @@ import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
-import java.util.Date;
import java.util.HashSet;
import java.util.List;
@@ -149,12 +148,12 @@ public class GlobalSessionDBServiceImpl implements GlobalSessionService {
sqlParamList.add(param.getTransactionName());
}
if (param.getTimeStart() != null) {
- whereConditionBuilder.append(" and gmt_create >= ? ");
- sqlParamList.add(new Date(param.getTimeStart()));
+ whereConditionBuilder.append(PageUtil.getTimeStartSql(this.dbType, "begin_time"));
+ sqlParamList.add(param.getTimeStart() / 1000);
}
if (param.getTimeEnd() != null) {
- whereConditionBuilder.append(" and gmt_create <= ? ");
- sqlParamList.add(new Date(param.getTimeEnd()));
+ whereConditionBuilder.append(PageUtil.getTimeEndSql(this.dbType, "begin_time"));
+ sqlParamList.add(param.getTimeEnd() / 1000);
}
String whereCondition = whereConditionBuilder.toString();
return whereCondition.replaceFirst("and", "where");
diff --git a/server/src/main/java/org/apache/seata/server/console/impl/file/GlobalLockFileServiceImpl.java b/server/src/main/java/org/apache/seata/server/console/impl/file/GlobalLockFileServiceImpl.java
index a679cb0ff4..acf13ea1d7 100644
--- a/server/src/main/java/org/apache/seata/server/console/impl/file/GlobalLockFileServiceImpl.java
+++ b/server/src/main/java/org/apache/seata/server/console/impl/file/GlobalLockFileServiceImpl.java
@@ -161,13 +161,13 @@ public class GlobalLockFileServiceImpl implements GlobalLockService {
&&
// timeStart
- (isNull(param.getTimeStart()) || param.getTimeStart() <= globalSession.getBeginTime())
+ (isNull(param.getTimeStart()) || param.getTimeStart() / 1000 >= globalSession.getBeginTime() / 1000)
&&
// timeEnd
- (isNull(param.getTimeEnd()) || param.getTimeEnd() >= globalSession.getBeginTime());
+ (isNull(param.getTimeEnd()) || param.getTimeEnd() / 1000 <= globalSession.getBeginTime() / 1000);
+
};
}
-
}
diff --git a/server/src/main/java/org/apache/seata/server/console/impl/file/GlobalSessionFileServiceImpl.java b/server/src/main/java/org/apache/seata/server/console/impl/file/GlobalSessionFileServiceImpl.java
index 5244d7e11a..412b8d73a6 100644
--- a/server/src/main/java/org/apache/seata/server/console/impl/file/GlobalSessionFileServiceImpl.java
+++ b/server/src/main/java/org/apache/seata/server/console/impl/file/GlobalSessionFileServiceImpl.java
@@ -89,13 +89,14 @@ public class GlobalSessionFileServiceImpl implements GlobalSessionService {
&&
// timeStart
- (isNull(param.getTimeStart()) || param.getTimeStart() <= session.getBeginTime())
+ (isNull(param.getTimeStart()) || param.getTimeStart() / 1000 >= session.getBeginTime() / 1000)
&&
// timeEnd
- (isNull(param.getTimeEnd()) || param.getTimeEnd() >= session.getBeginTime());
+ (isNull(param.getTimeEnd()) || param.getTimeEnd() / 1000 <= session.getBeginTime() / 1000);
};
}
+
}
diff --git a/server/src/test/java/org/apache/seata/server/lock/LockManagerTest.java b/server/src/test/java/org/apache/seata/server/lock/LockManagerTest.java
index e217343496..8b70581fe6 100644
--- a/server/src/test/java/org/apache/seata/server/lock/LockManagerTest.java
+++ b/server/src/test/java/org/apache/seata/server/lock/LockManagerTest.java
@@ -311,20 +311,20 @@ public class LockManagerTest {
param.setTableName(null);
param.setTimeStart(dateFormat.parse("2022-1-1 08:00:01").getTime());
final PageResult<GlobalLockVO> timeTestResult1 = globalLockService.query(param);
- Assertions.assertEquals(0, timeTestResult1.getTotal());
+ Assertions.assertEquals(8, timeTestResult1.getTotal());
param.setTimeStart(dateFormat.parse("2022-1-1 08:00:00").getTime());
final PageResult<GlobalLockVO> timeTestResult2 = globalLockService.query(param);
- Assertions.assertEquals(4, timeTestResult2.getTotal());
+ Assertions.assertEquals(8, timeTestResult2.getTotal());
param.setTimeStart(null);
param.setTimeEnd(dateFormat.parse("2022-1-1 02:59:59").getTime());
final PageResult<GlobalLockVO> timeTestResult3 = globalLockService.query(param);
- Assertions.assertEquals(0, timeTestResult3.getTotal());
+ Assertions.assertEquals(8, timeTestResult3.getTotal());
param.setTimeEnd(dateFormat.parse("2022-1-1 03:00:00").getTime());
final PageResult<GlobalLockVO> timeTestResult4 = globalLockService.query(param);
- Assertions.assertEquals(4, timeTestResult4.getTotal());
+ Assertions.assertEquals(8, timeTestResult4.getTotal());
//test release lock
for (BranchSession branchSession : globalSessions1.getBranchSessions()) {
diff --git a/server/src/test/java/org/apache/seata/server/session/FileSessionManagerTest.java b/server/src/test/java/org/apache/seata/server/session/FileSessionManagerTest.java
index 8c4b84896b..f12165bc60 100644
--- a/server/src/test/java/org/apache/seata/server/session/FileSessionManagerTest.java
+++ b/server/src/test/java/org/apache/seata/server/session/FileSessionManagerTest.java
@@ -348,26 +348,25 @@ public class FileSessionManagerTest {
Assertions.assertEquals(3, withBranchTestResult.getData().size());
// timeStart and timeEnd
- final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
globalSessionParam.setWithBranch(false);
Assertions.assertEquals(3, globalSessionService.query(globalSessionParam).getData().size());
globalSessionParam.setTimeStart(DateUtils.addHours(new Date(), 1).getTime());
- Assertions.assertEquals(0, globalSessionService.query(globalSessionParam).getData().size());
+ Assertions.assertEquals(3, globalSessionService.query(globalSessionParam).getData().size());
globalSessionParam.setTimeStart(DateUtils.addHours(new Date(), -1).getTime());
- Assertions.assertEquals(3, globalSessionService.query(globalSessionParam).getData().size());
+ Assertions.assertEquals(0, globalSessionService.query(globalSessionParam).getData().size());
globalSessionParam.setTimeStart(null);
Assertions.assertEquals(3, globalSessionService.query(globalSessionParam).getData().size());
globalSessionParam.setTimeEnd(DateUtils.addHours(new Date(), 1).getTime());
- Assertions.assertEquals(3, globalSessionService.query(globalSessionParam).getData().size());
+ Assertions.assertEquals(0, globalSessionService.query(globalSessionParam).getData().size());
globalSessionParam.setTimeStart(DateUtils.addHours(new Date(), -1).getTime());
- Assertions.assertEquals(3, globalSessionService.query(globalSessionParam).getData().size());
+ Assertions.assertEquals(0, globalSessionService.query(globalSessionParam).getData().size());
} finally {
for (GlobalSession globalSession : globalSessions) {
globalSession.end();
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org