From a68a32d9b6f23bdfdd950ab654d3776362539573 Mon Sep 17 00:00:00 2001
From: 疯狂的狮子li <15040126243@163.com>
Date: 星期四, 10 三月 2022 19:47:31 +0800
Subject: [PATCH] add 增加 DataBaseHelper 数据库助手 用于屏蔽多类型数据库sql语句差异
---
ruoyi-generator/src/main/java/com/ruoyi/generator/service/BaseGenTableServiceImpl.java | 38 +++---------
ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysUserServiceImpl.java | 3
ruoyi-common/src/main/java/com/ruoyi/common/helper/DataBaseHelper.java | 61 ++++++++++++++++++++
ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysDeptServiceImpl.java | 5 +
ruoyi-common/src/main/java/com/ruoyi/common/enums/DataBaseType.java | 49 ++++++++++++++++
ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysDataScopeServiceImpl.java | 3
6 files changed, 128 insertions(+), 31 deletions(-)
diff --git a/ruoyi-common/src/main/java/com/ruoyi/common/enums/DataBaseType.java b/ruoyi-common/src/main/java/com/ruoyi/common/enums/DataBaseType.java
new file mode 100644
index 0000000..5d0f05a
--- /dev/null
+++ b/ruoyi-common/src/main/java/com/ruoyi/common/enums/DataBaseType.java
@@ -0,0 +1,49 @@
+package com.ruoyi.common.enums;
+
+import com.ruoyi.common.utils.StringUtils;
+import lombok.AllArgsConstructor;
+import lombok.Getter;
+
+/**
+ * 鏁版嵁搴撶被鍨�
+ *
+ * @author Lion Li
+ */
+@Getter
+@AllArgsConstructor
+public enum DataBaseType {
+
+ /**
+ * MySQL
+ */
+ MY_SQL("MySQL"),
+
+ /**
+ * Oracle
+ */
+ ORACLE("Oracle"),
+
+ /**
+ * PostgreSQL
+ */
+ POSTGRE_SQL("PostgreSQL"),
+
+ /**
+ * SQL Server
+ */
+ SQL_SERVER("Microsoft SQL Server");
+
+ private final String type;
+
+ public static DataBaseType find(String databaseProductName) {
+ if (StringUtils.isBlank(databaseProductName)) {
+ return null;
+ }
+ for (DataBaseType type : values()) {
+ if (type.getType().equals(databaseProductName)) {
+ return type;
+ }
+ }
+ return null;
+ }
+}
diff --git a/ruoyi-common/src/main/java/com/ruoyi/common/helper/DataBaseHelper.java b/ruoyi-common/src/main/java/com/ruoyi/common/helper/DataBaseHelper.java
new file mode 100644
index 0000000..3d98def
--- /dev/null
+++ b/ruoyi-common/src/main/java/com/ruoyi/common/helper/DataBaseHelper.java
@@ -0,0 +1,61 @@
+package com.ruoyi.common.helper;
+
+import cn.hutool.core.convert.Convert;
+import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
+import com.ruoyi.common.enums.DataBaseType;
+import com.ruoyi.common.exception.ServiceException;
+import com.ruoyi.common.utils.spring.SpringUtils;
+import lombok.AccessLevel;
+import lombok.NoArgsConstructor;
+
+import javax.sql.DataSource;
+import java.sql.DatabaseMetaData;
+import java.sql.SQLException;
+
+/**
+ * 鏁版嵁搴撳姪鎵�
+ *
+ * @author Lion Li
+ */
+@NoArgsConstructor(access = AccessLevel.PRIVATE)
+public class DataBaseHelper {
+
+ /**
+ * 鑾峰彇褰撳墠鏁版嵁搴撶被鍨�
+ */
+ public static DataBaseType getDataBasyType() {
+ DynamicRoutingDataSource ds = (DynamicRoutingDataSource) SpringUtils.getBean(DataSource.class);
+ DataSource dataSource = ds.determineDataSource();
+ try {
+ DatabaseMetaData metaData = dataSource.getConnection().getMetaData();
+ String databaseProductName = metaData.getDatabaseProductName();
+ return DataBaseType.find(databaseProductName);
+ } catch (SQLException e) {
+ throw new ServiceException(e.getMessage());
+ }
+ }
+
+ public static boolean isMySql() {
+ return DataBaseType.MY_SQL == getDataBasyType();
+ }
+
+ public static boolean isOracle() {
+ return DataBaseType.ORACLE == getDataBasyType();
+ }
+
+ public static boolean isPostgerSql() {
+ return DataBaseType.POSTGRE_SQL == getDataBasyType();
+ }
+
+ public static boolean isSqlServer() {
+ return DataBaseType.SQL_SERVER == getDataBasyType();
+ }
+
+ public static String findInSet(Object var1, String var2) {
+ DataBaseType dataBasyType = getDataBasyType();
+ if (dataBasyType == DataBaseType.SQL_SERVER) {
+ return "charindex(" + Convert.toStr(var1) + ", " + var2 + ") <> 0";
+ }
+ return "find_in_set(" + Convert.toStr(var1) + ", " + var2 + ") <> 0";
+ }
+}
diff --git a/ruoyi-generator/src/main/java/com/ruoyi/generator/service/BaseGenTableServiceImpl.java b/ruoyi-generator/src/main/java/com/ruoyi/generator/service/BaseGenTableServiceImpl.java
index b51668f..6d3034f 100644
--- a/ruoyi-generator/src/main/java/com/ruoyi/generator/service/BaseGenTableServiceImpl.java
+++ b/ruoyi-generator/src/main/java/com/ruoyi/generator/service/BaseGenTableServiceImpl.java
@@ -3,27 +3,23 @@
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.lang.Dict;
import cn.hutool.core.util.ObjectUtil;
-import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.ruoyi.common.constant.GenConstants;
import com.ruoyi.common.core.domain.PageQuery;
import com.ruoyi.common.core.page.TableDataInfo;
import com.ruoyi.common.exception.ServiceException;
+import com.ruoyi.common.helper.DataBaseHelper;
import com.ruoyi.common.utils.JsonUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.spring.SpringUtils;
import com.ruoyi.generator.domain.GenTable;
import com.ruoyi.generator.domain.GenTableColumn;
import com.ruoyi.generator.util.VelocityUtils;
-import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Service;
-import javax.sql.DataSource;
import java.io.ByteArrayOutputStream;
import java.io.File;
-import java.sql.DatabaseMetaData;
-import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipOutputStream;
@@ -38,30 +34,18 @@
@Service
public class BaseGenTableServiceImpl implements IGenTableService {
- @Autowired
- private DataSource dataSource;
-
public BaseGenTableServiceImpl getService() {
- DynamicRoutingDataSource ds = (DynamicRoutingDataSource) this.dataSource;
- DataSource dataSource = ds.determineDataSource();
- try {
- DatabaseMetaData metaData = dataSource.getConnection().getMetaData();
- String databaseProductName = metaData.getDatabaseProductName();
- if ("MySQL".equals(databaseProductName)) {
- return SpringUtils.getBean(GenTableServiceImpl.class);
- } else if ("Oracle".equals(databaseProductName)) {
- return SpringUtils.getBean(OracleGenTableServiceImpl.class);
- } else if ("PostgreSQL".equals(databaseProductName)) {
-
- } else if ("Microsoft SQL Server".equals(databaseProductName)) {
-
- } else {
- throw new ServiceException("褰撳墠鏁版嵁搴撶被鍨嬩笉鏀寔 => " + databaseProductName);
- }
- } catch (SQLException e) {
- throw new ServiceException(e.getMessage());
+ if (DataBaseHelper.isMySql()) {
+ return SpringUtils.getBean(GenTableServiceImpl.class);
+ } else if (DataBaseHelper.isOracle()) {
+ return SpringUtils.getBean(OracleGenTableServiceImpl.class);
+ } else if (DataBaseHelper.isPostgerSql()) {
+ throw new ServiceException("褰撳墠鏁版嵁搴撶被鍨嬩笉鏀寔!!!");
+ } else if (DataBaseHelper.isSqlServer()) {
+ throw new ServiceException("褰撳墠鏁版嵁搴撶被鍨嬩笉鏀寔!!!");
+ } else {
+ throw new ServiceException("褰撳墠鏁版嵁搴撶被鍨嬩笉鏀寔!!!");
}
- return null;
}
/**
diff --git a/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysDataScopeServiceImpl.java b/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysDataScopeServiceImpl.java
index aae8f1a..47c5528 100644
--- a/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysDataScopeServiceImpl.java
+++ b/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysDataScopeServiceImpl.java
@@ -4,6 +4,7 @@
import cn.hutool.core.convert.Convert;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.ruoyi.common.core.domain.entity.SysDept;
+import com.ruoyi.common.helper.DataBaseHelper;
import com.ruoyi.system.domain.SysRoleDept;
import com.ruoyi.system.mapper.SysDeptMapper;
import com.ruoyi.system.mapper.SysRoleDeptMapper;
@@ -47,7 +48,7 @@
.select(SysDept::getDeptId)
.eq(SysDept::getDeptId, deptId)
.or()
- .apply("find_in_set({0},ancestors) <> 0", deptId));
+ .apply(DataBaseHelper.findInSet(deptId, "ancestors")));
if (CollUtil.isNotEmpty(list)) {
return list.stream().map(d -> Convert.toStr(d.getDeptId())).collect(Collectors.joining(","));
}
diff --git a/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysDeptServiceImpl.java b/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysDeptServiceImpl.java
index 3ef5eb8..6b6fddd 100644
--- a/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysDeptServiceImpl.java
+++ b/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysDeptServiceImpl.java
@@ -11,6 +11,7 @@
import com.ruoyi.common.core.domain.entity.SysRole;
import com.ruoyi.common.core.domain.entity.SysUser;
import com.ruoyi.common.exception.ServiceException;
+import com.ruoyi.common.helper.DataBaseHelper;
import com.ruoyi.common.helper.LoginHelper;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.TreeBuildUtils;
@@ -108,7 +109,7 @@
public long selectNormalChildrenDeptById(Long deptId) {
return baseMapper.selectCount(new LambdaQueryWrapper<SysDept>()
.eq(SysDept::getStatus, UserConstants.DEPT_NORMAL)
- .apply("find_in_set({0}, ancestors) <> 0", deptId));
+ .apply(DataBaseHelper.findInSet(deptId, "ancestors")));
}
/**
@@ -234,7 +235,7 @@
*/
public void updateDeptChildren(Long deptId, String newAncestors, String oldAncestors) {
List<SysDept> children = baseMapper.selectList(new LambdaQueryWrapper<SysDept>()
- .apply("find_in_set({0},ancestors) <> 0", deptId));
+ .apply(DataBaseHelper.findInSet(deptId, "ancestors")));
List<SysDept> list = new ArrayList<>();
for (SysDept child : children) {
SysDept dept = new SysDept();
diff --git a/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysUserServiceImpl.java b/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysUserServiceImpl.java
index d236c96..e5b54ab 100644
--- a/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysUserServiceImpl.java
+++ b/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysUserServiceImpl.java
@@ -15,6 +15,7 @@
import com.ruoyi.common.core.domain.entity.SysUser;
import com.ruoyi.common.core.page.TableDataInfo;
import com.ruoyi.common.exception.ServiceException;
+import com.ruoyi.common.helper.DataBaseHelper;
import com.ruoyi.common.helper.LoginHelper;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.system.domain.SysPost;
@@ -80,7 +81,7 @@
.and(ObjectUtil.isNotNull(user.getDeptId()), w -> {
List<SysDept> deptList = deptMapper.selectList(new LambdaQueryWrapper<SysDept>()
.select(SysDept::getDeptId)
- .apply("find_in_set({0},ancestors) <> 0", user.getDeptId()));
+ .apply(DataBaseHelper.findInSet(user.getDeptId(), "ancestors")));
w.eq("u.dept_id", user.getDeptId())
.or()
.in("u.dept_id", deptList.stream().map(SysDept::getDeptId).collect(Collectors.toList()));
--
Gitblit v1.9.3