该文详细解释了一段SQL查询代码，涉及到对业务数据的多表联查，包括HR_EMPLOYEE、BS_ORG等表。查询中包含了动态过滤条件、排序和分页功能。过滤条件由AdvanceFilterDto对象表示，通过AdvanceFilterUtils工具类解析生成SQLWHERE子句。该过程涉及到对日期字段的特殊处理，以及对不同比较操作的支持。
对业务sql的理解
@Select("select t.*,u.full_name as createName,o2.name as secondDeptName , p.post_number as postNumber,o1.name as orgName from hr_employee t" +
" left join bs_org o1 on o1.id=t.org_id" +
" left join bs_org o2 on o2.id=t.second_dept_id " +
" left join bs_users u on u.id = t.create_id " +
" left join bs_post p on p.id = t.post_id " +
" ${filter} " +
" order by t.create_date desc" +
" limit #{current},#{size}")
List<Employee> selectOfficeCustomPage(@Param("filter") String filter, @Param("current") long current, @Param("size") long size);
上面是根据过滤条件以及当前页和每页显示的记录数返回查询的实体类对象
进行了相关的连表查询操作
该语句查询了 hr_employee 表，并通过 left join 关键字连接了 bs_org、bs_users、bs_post 等表。查询结果包括 hr_employee 表的全部字段（使用了 t.*），以及其他表的一些字段，如 u.full_name、o2.name、p.post_number、o1.name 等。
${filter} 是一个占位符，表示可以根据实际需要动态拼接 SQL 查询条件。
order by t.create_date desc 表示按照 create_date 字段倒序排序。
limit #{current},#{size} 表示查询结果的分页限制，其中 #{current} 和 #{size} 分别是 MyBatis 框架中的占位符，表示当前页码和每页显示的记录数，可以根据实际需要进行动态设置。
继续分析拿到过滤条件的过程
// 如果高级查询不为空，则解析成SQL
List<AdvanceFilterDto> filterList = JSON.parseArray(pageQuery.getAdvanceFilter(), AdvanceFilterDto.class);
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.ToString;
@ApiModel(description = "高级查询对象")
@Data
@ToString
public class AdvanceFilterDto {
/**
* 条件连接号（"and"或者"or"）
*/
@ApiModelProperty(value = "条件连接号（\"and\"或者\"or\"）")
private String join;
/**
* 左括号
*/
@ApiModelProperty(value = "左括号")
private String lb;
/**
* 查询字段名（bean属性名称）
*/
@ApiModelProperty(value = "查询字段名（bean属性名称）")
private String field;
/**
* 条件（包含：contains；等于：equal；不等于：notequal；大于：greater；大于或等于：greaterorequal；小于：less；小于或等于：lessorequal；以...开头：beginwith；以...结尾：endwith；）
*/
@ApiModelProperty(value = "条件（包含：contains；等于：equal；不等于：notequal；大于：greater；大于或等于：greaterorequal；小于：less；小于或等于：lessorequal；以...开头：beginwith；以...结尾：endwith；）")
private String op;
/**
* 查询值
*/
@ApiModelProperty(value = "查询值")
private String value;
/**
* 表别名
*/
@ApiModelProperty(value = "查询值")
private String tableAlias;
/**
* 右括号
*/
@ApiModelProperty(value = "右括号")
private String rb;
public AdvanceFilterDto() {
}
public AdvanceFilterDto(String join, String lb, String field, String op, String value, String rb, String tableAlias) {
this.join = join;
this.lb = lb;
this.field = field;
this.op = op;
this.value = value;
this.rb = rb;
this.tableAlias = tableAlias;
}
public AdvanceFilterDto(String field, String value, String op) {
this.field = field;
this.value = value;
this.op = op;
}
}
使用了一个工具类进行条件解析
filter = AdvanceFilterUtils.parseWhereSql(filterList, Employee.class, "t");
/**
* 解析高级查询对象的条件转为where里的SQL语句
*
* @param filters
* @param targetClass
* @param alias 别名
* @return
* @throws
*/
public static String parseWhereSql(List<AdvanceFilterDto> filters, Class<?> targetClass, String alias){
if (filters == null || filters.size() == 0) {
return "";
}
String aliasText = "";
if (StrUtil.isNotEmpty(alias)) {
aliasText = alias + ".";
}
StringBuffer strWhere = new StringBuffer("");
try {
Field field;
Field[] fields = targetClass.getDeclaredFields();
String fieldValue = "";
int rowIndex = 1;
for (AdvanceFilterDto filter : filters) {
if (StrUtil.isEmpty(filter.getJoin())) {
filter.setJoin("and");
}
if (rowIndex == 1 && filter.getJoin().equals("or")) {
filter.setJoin("and");
}
for (Field f : fields) {
if (StrUtil.isNotEmpty(alias)) {
aliasText = alias + ".";
}
field = targetClass.getDeclaredField(f.getName());
String dbFiledName = filter.getField();
String fieldName = CamelUtil.camel2Underline(dbFiledName);
if (field.getName().equals(dbFiledName)) {
if (rowIndex > 1) {
strWhere.append(filter.getJoin()).append(" ");
}
if (StrUtil.isNotEmpty(filter.getLb())) {
strWhere.append(filter.getLb()).append(" ");
}
fieldValue = "";
String str = fieldName != null ? fieldName : dbFiledName;
String leftName = aliasText + str;
if (field.getType().equals(Date.class) && !"notEmpty".equals(filter.getOp()) && !"isEmpty".equals(filter.getOp())) {
if (StrUtil.isBlank(filter.getValue())) {
Date d = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String dateNowStr = sdf.format(d);
filter.setValue(dateNowStr);
}
switch (filter.getOp()) {
case "contains":
fieldValue = " >='" + filter.getValue() + " 00:00:00' AND " + leftName + " <= '" + filter.getValue() + " 23:59:59' ";
break;
case "beginwith":
fieldValue = " >='" + filter.getValue() + " 00:00:00'";
break;
case "endwith":
fieldValue = " <='" + filter.getValue() + " 23:59:59' ";
break;
case "notequal":// 不等于
fieldValue = "<'" + filter.getValue() + " 00:00:00' AND " + leftName + " >'" + filter.getValue() + " 23:59:59' ";
break;
case "equal": // 等于
fieldValue = " >='" + filter.getValue() + " 00:00:00' AND " + leftName + " <= '" + filter.getValue() + " 23:59:59' ";
break;
case "greater": // 大于
fieldValue = " >'" + filter.getValue() + " 23:59:59' ";
break;
case "greaterorequal": // 大于或等于
fieldValue = " >='" + filter.getValue() + " 00:00:00' ";
break;
case "less": // 小于
fieldValue = " <'" + filter.getValue() + " 00:00:00' ";
break;
case "lessorequal": // 小于或等于
fieldValue = " <='" + filter.getValue() + " 23:59:59' ";
break;
default:
fieldValue = " >='" + filter.getValue() + " 00:00:00' AND " + leftName + " <= '" + filter.getValue() + " 23:59:59' ";
break;
}
if (field.getType().equals(Supplier.class)) {
} else if (field.getType().equals(User.class)) {
} else if (field.getType().equals(Org.class)) {
} else if (field.getType().equals(Stock.class)) {
} else if (field.getType().equals(Unit.class)) {
} else {
strWhere.append("(" + leftName).append(" ").append(" ")
.append(fieldValue + ")");
}
if (StrUtil.isNotEmpty(filter.getRb())) {
strWhere.append(filter.getRb()).append(" ");
}
rowIndex++;
continue;
}
if (!"contains".equals(filter.getOp()) && !"beginwith".equals(filter.getOp())
&& !"endwith".equals(filter.getOp())) {
strWhere.append(leftName).append(getOp(filter)).append(" '").append(filter.getValue())
.append("'");
} else {
switch (filter.getOp()) {
case "contains":
fieldValue = " '%" + filter.getValue() + "%' ";
break;
case "beginwith":
fieldValue = " '" + filter.getValue() + "%' ";
break;
case "endwith":
fieldValue = " '%" + filter.getValue() + "' ";
break;
}
if (field.getType().equals(Supplier.class)) {
} else if (field.getType().equals(User.class)) {
} else if (field.getType().equals(Org.class)) {
} else if (field.getType().equals(Stock.class)) {
} else if (field.getType().equals(Unit.class)) {
} else {
strWhere.append(leftName).append(" ").append(getOp(filter)).append(" ")
.append(fieldValue);
}
}
if (StrUtil.isNotEmpty(filter.getRb())) {
strWhere.append(filter.getRb()).append(" ");
}
rowIndex++;
}
}
}
} catch (NoSuchFieldException e) {
LOG.error("获取字段属性出错：" + e.getMessage());
} catch (SecurityException e) {
LOG.error("获取字段属性出错：" + e.getMessage());
}
return StrUtil.isNotBlank(strWhere) ? " where (" + strWhere.toString() + " ) " : "";
}
扫一扫
1467
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
