private boolean conflictWithScm(String talentId, String supplyId, String key) { StringBuilder selectSql = new StringBuilder() .append("SELECT ") .append(" dxr_biz_id AS id, key, dxr_biz_talentsupplycode AS talentSupplyCode, ") .append(" dxr_biz_eid AS eid, dxr_biz_talentid AS talentId, dxr_biz_talentcode AS talentCode, dxr_biz_talentname AS talentName, ") .append(" dxr_biz_supplyid AS supplyId, dxr_biz_supplyCode AS supplyCode, dxr_biz_supplyname AS supplyName, ") .append(" dxr_biz_cooperationstartdate AS cooperationStartDate, dxr_biz_cooperationstatus AS cooperationStatus, ") .append(" dxr_biz_checkstatus AS checkStatus, IFNULL(dxr_biz_syncstatus, '0') AS syncStatus, ") .append("FROM dxr.hive_dxr_biz_businessrelations ") .append("WHERE dxr_biz_talentid='").append(talentId).append("' ") .append(" AND dxr_biz_supplyid='").append(supplyId).append("' ") .append(" AND (key !='").append(key).append("' OR key='')") .append("LIMIT 1"); return null != bigdataUtil.queryFromHiveBySql(selectSql.toString(), ScmBusinessRelationship.class); }
时间: 2024-04-27 11:20:39 浏览: 18
这段代码是用来判断人才和供应商之间的业务关系是否存在冲突,其中包括人才ID,供应商ID和业务关系的key。它使用了StringBuilder来构建SQL查询语句,并调用了bigdataUtil的queryFromHiveBySql方法来执行查询。如果查询结果不为空,则说明存在冲突,返回true,否则返回false。
相关问题
for (SysRole role : user.getRoles()) { String dataScope = role.getDataScope(); if (!DATA_SCOPE_CUSTOM.equals(dataScope) && conditions.contains(dataScope)) { continue; } if (StringUtils.isNotEmpty(permission) && StringUtils.isNotEmpty(role.getPermissions()) && !StringUtils.containsAny(role.getPermissions(), Convert.toStrArray(permission))) { continue; } if (DATA_SCOPE_ALL.equals(dataScope)) { sqlString = new StringBuilder(); conditions.add(dataScope); break; } else if (DATA_SCOPE_CUSTOM.equals(dataScope)) { sqlString.append(StringUtils.format( " OR {}.dept_id IN ( SELECT dept_id FROM sys_role_dept WHERE role_id = {} ) ", deptAlias, role.getRoleId())); } else if (DATA_SCOPE_DEPT.equals(dataScope)) { sqlString.append(StringUtils.format(" OR {}.dept_id = {} ", deptAlias, user.getDeptId())); } else if (DATA_SCOPE_DEPT_AND_CHILD.equals(dataScope)) { sqlString.append(StringUtils.format( " OR {}.dept_id IN ( SELECT dept_id FROM sys_dept WHERE dept_id = {} or find_in_set( {} , ancestors ) )", deptAlias, user.getDeptId(), user.getDeptId())); } else if (DATA_SCOPE_SELF.equals(dataScope)) { if (isNotBlank(userAlias)) { sqlString.append(StringUtils.format(" OR {}.user_id = {} ", userAlias, user.getUserId())); } else { // 数据权限为仅本人且没有userAlias别名不查询任何数据 sqlString.append(StringUtils.format(" OR {}.dept_id = 0 ", deptAlias)); } } conditions.add(dataScope); } 减少循环中break和continue语句的总数,最多使用一条。
可以使用一个条件语句来代替break和continue语句。在需要退出循环的地方设置一个boolean类型的变量,然后在循环结束后根据该变量的值判断是否需要继续执行。例如:
```
boolean shouldContinue = false;
for (SysRole role : user.getRoles()) {
String dataScope = role.getDataScope();
if (!DATA_SCOPE_CUSTOM.equals(dataScope) && conditions.contains(dataScope)) {
continue;
}
if (StringUtils.isNotEmpty(permission) && StringUtils.isNotEmpty(role.getPermissions())
&& !StringUtils.containsAny(role.getPermissions(), Convert.toStrArray(permission))) {
continue;
}
if (DATA_SCOPE_ALL.equals(dataScope)) {
sqlString = new StringBuilder();
conditions.add(dataScope);
shouldContinue = true;
} else {
conditions.add(dataScope);
if (DATA_SCOPE_CUSTOM.equals(dataScope)) {
sqlString.append(StringUtils.format(
" OR {}.dept_id IN ( SELECT dept_id FROM sys_role_dept WHERE role_id = {} ) ", deptAlias,
role.getRoleId()));
} else if (DATA_SCOPE_DEPT.equals(dataScope)) {
sqlString.append(StringUtils.format(" OR {}.dept_id = {} ", deptAlias, user.getDeptId()));
} else if (DATA_SCOPE_DEPT_AND_CHILD.equals(dataScope)) {
sqlString.append(StringUtils.format(
" OR {}.dept_id IN ( SELECT dept_id FROM sys_dept WHERE dept_id = {} or find_in_set( {} , ancestors ) )",
deptAlias, user.getDeptId(), user.getDeptId()));
} else if (DATA_SCOPE_SELF.equals(dataScope)) {
if (isNotBlank(userAlias)) {
sqlString.append(StringUtils.format(" OR {}.user_id = {} ", userAlias, user.getUserId()));
} else {
// 数据权限为仅本人且没有userAlias别名不查询任何数据
sqlString.append(StringUtils.format(" OR {}.dept_id = 0 ", deptAlias));
}
}
}
if (shouldContinue) {
break;
}
}
```
Java ,druid 解析sql,并通过 feign接口查询到的表元数据,替换包含left join 的select sql语句中的*,支持mysql、hive、postgresql、oracle、sqlserver等多种数据库。需要写出代码
下面是使用Java和Druid实现解析SQL语句,并通过Feign接口查询表元数据,替换包含left join的select语句中的*的示例代码:
```java
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLJoinTableSource;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr;
import com.alibaba.druid.sql.ast.statement.SQLSelect;
import com.alibaba.druid.sql.ast.statement.SQLSelectItem;
import com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock;
import com.alibaba.druid.sql.ast.statement.SQLTableSource;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlOutputVisitor;
import com.alibaba.druid.util.JdbcConstants;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class SqlParser {
private static final String DB_TYPE_MYSQL = "mysql";
private static final String DB_TYPE_ORACLE = "oracle";
private static final String DB_TYPE_POSTGRESQL = "postgresql";
private static final String DB_TYPE_SQLSERVER = "sqlserver";
private static final String DB_TYPE_HIVE = "hive";
private static final Pattern LEFT_JOIN_PATTERN = Pattern.compile("(?i)\\b(left join)\\b");
private FeignClient feignClient; // Feign接口
public void parseAndReplaceSql(String sql, String dbType) {
// 解析SQL语句
List<SQLStatement> sqlStatements = SQLUtils.parseStatements(sql, dbType);
for (SQLStatement statement : sqlStatements) {
// 只处理SELECT语句
if (statement instanceof SQLSelect) {
SQLSelect select = (SQLSelect) statement;
SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) select.getQuery();
// 获取所有SELECT项
List<SQLSelectItem> selectItems = queryBlock.getSelectList();
// 判断是否包含LEFT JOIN
boolean hasLeftJoin = false;
SQLTableSource from = queryBlock.getFrom();
if (from instanceof SQLJoinTableSource) {
SQLJoinTableSource join = (SQLJoinTableSource) from;
if (join.getJoinType().getName().equalsIgnoreCase("left join")) {
hasLeftJoin = true;
}
}
// 如果包含LEFT JOIN,则替换SELECT项中的*
if (hasLeftJoin) {
for (int i = 0; i < selectItems.size(); i++) {
SQLSelectItem item = selectItems.get(i);
SQLExpr expr = item.getExpr();
if (expr instanceof SQLIdentifierExpr && ((SQLIdentifierExpr) expr).getName().equals("*")) {
// 调用Feign接口查询表元数据,替换SELECT项中的*
String tableName = ((SQLIdentifierExpr) from).getName(); // 获取表名
List<TableColumn> columns = feignClient.getTableColumns(tableName);
StringBuilder sb = new StringBuilder();
for (TableColumn column : columns) {
sb.append(column.getColumnName()).append(",");
}
sb.deleteCharAt(sb.length() - 1);
item.setExpr(SQLUtils.toSQLExpr(sb.toString(), dbType));
selectItems.set(i, item);
}
}
}
}
}
// 生成替换后的SQL语句
String resultSql = SQLUtils.toSQLString(sqlStatements, dbType, new MySqlOutputVisitor());
System.out.println(resultSql);
}
}
// 表元数据
class TableColumn {
private String columnName;
private String columnType;
// getter/setter
}
// Feign接口
@FeignClient(name = "metadata-service")
public interface FeignClient {
@GetMapping("/tableColumns")
List<TableColumn> getTableColumns(@RequestParam("tableName") String tableName);
}
```
需要注意的是,上述代码中的`FeignClient`是使用Spring Cloud Feign实现的,需要在项目中引入相应的依赖。同时,`getTableColumns`方法需要根据实际情况进行修改,具体实现可以参考Feign的使用文档。
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)