工具代码之-快速搜索组件(5行代码实现分页和列表查询)

工具代码之-快速搜索组件(5行代码实现分页和列表查询)

背景

近几年由于前公司工作内容的原因,之前的工作内容大多在做性能优化,交互协议都是rpc,导致笔者很久没有写一些http的接口,近几个月换了工作,团队所在的技术栈偏传统,交互协议以rest为主,又重新开始了rest接口的开发,日常工作中对于一些常用的分页和搜索接口,代码重复程度很高,写的人很难受,主要是想偷个懒,所以针对搜索和分页的场景,编写了一套工具组件,主要思路是定义通用的前端搜索和分页请求结构,后端根据该结构生成搜索sql或使用具体的业务搜索实体接收,简化前后端搜索场景的交互;最近时间快过年了,时间上还算可以,所以就抽空实现了下,和大家交流下

如何实现

实现需要解决的问题:

• 搜索结构的定义
• 列名称和sql参数的合法性校验
• 搜索结构-> 具体结构的转换
• 搜索结构 -> sql的转换

大家可以先简单思考下上面步骤的实现方式,后面我会一一说明

使用方式

详细参见: UserSearchController 使用注意点: 当使用SearchConditionBeanUtil 工具时,扩展条件参数不生效
只有使用sql工具(SearchConditionSqlUtil)构建时,扩展参数才生效

通用搜索DTO -> 搜索VO

• Controller

• 搜索实现

• 参数示例

通用搜索DTO -> sql

• Controller

• 搜索实现

不到5行代码就实现了搜索逻辑,大大节省了时间,哈哈哈

• 参数示例

• 参数转sql

转换后sql:

 WHERE id = 1 AND name like '%name%' AND age = 19 ORDER BY id ASC

方案实现

定义定义

必须参数:

• 搜索条件: 条件名称,值
• 排序条件: 排序条件名称
• 分页条件: 页码,页大小

扩展参数: 操作符,条件组合方式,条件值类型,排序条件顺序,排序类型(升降序)

• 条件实体

@Data  
@AllArgsConstructor  
@NoArgsConstructor  
public class Condition implements Serializable {  
/**  
* 字段名称  
*/  
  
private String name;  
/**  
* 字段值  
*/  
private String value;  
/**  
* 搜索条件扩展参数  
*/  
private ConditionExtInfo extInfo;  
}

扩展条件结构:

@Data  
public class ConditionExtInfo implements Serializable {  
  
/**  
* composeType 条件组合方式  
*/  
public static final String COMPOSE_TYPE_AND = "AND", COMPOSE_TYPE_OR = "OR";  
/**  
* conditionType 条件类型  
*/  
public static final String CONDITION_TYPE_SIMPLE = "simple", CONDITION_TYPE_COMPLEX = "complex";  
/**  
* 条件值类型  
*/  
public static final String VALUE_TYPE_NUMBER = "number", VALUE_TYPE_TEXT = "text";  
  
  
/**  
* 操作符 默认 "="  
* 支持: =,!=,>,>=,<,<=,like,in,not in,between and ,
* @see ConditionOperateEnum
*/  
private String operate;  
/**  
* 字段类型: number,text  
* 默认text  
*/  
private String valueType;  
/**  
* 条件类型: simple(单一条件),complex(复合条件,根据subConditions 组合)  
* 默认: simple  
*/  
private String type;  
/**  
* 子条件  
*/  
private List<Condition> subConditions;  
/**  
* 条件组合关系: AND,OR  
* 默认:AND  
*/  
private String composeType;  
}

• 排序条件结构

@Data  
@AllArgsConstructor  
@NoArgsConstructor  
public class SortCondition implements Comparable<SortCondition>, Serializable {  
  
  
/**  
* ORDER ASC  
*/  
public static final String ASC = "asc";  
  
/**  
* ORDER DESC  
*/  
public static final String DESC = "desc";  
  
/**  
* 字段名称  
*/  
private String name;  
  
/**  
* 字段排序类型：asc-升序(默认)，desc-降序  
*/  
private String type = ASC;  
  
  
private int order;  
  
  
@Override  
public int compareTo(SortCondition o) {  
if (o.getOrder() == this.getOrder()) {  
return 0;  
}  
if (this.getOrder() > o.getOrder()) {  
return 1;  
}  
return -1;  
}  
}

• 列表条件结构

@Data  
@AllArgsConstructor  
@NoArgsConstructor  
public class SearchCondition implements Serializable {  
  
/**  
* 查询条件集合  
*/  
private List<Condition> conditions;  
/**  
* 条件组合关系: AND,OR  
*/  
private String composeType;  
  
/**  
* 排序条件集合  
*/  
private List<SortCondition> sorts;  
}

• 分页条件结构

@Data  
@AllArgsConstructor  
@NoArgsConstructor  
public class PageRequest<T> implements Serializable {  
/**  
* 页码  
*/  
@Range(min = 1, message = "当前页最小为1")  
private Integer pageNum = 1;  
  
/**  
* 分页大小  
*/  
@Range(min = 1, message = "每页展示条数最小为1")  
private Integer pageSize = 10;  
  
  
/**  
* 分页查询  
*/  
private T query;  
}

通用搜索DTO -> 搜索VO

实现思路:

1. 反射将条件名称转换成搜索VO
2. 业务自行根据搜索VO,实现搜索逻辑

详细代码:

package com.taoyuanx.common.search.util;  

@Slf4j  
public class SearchConditionBeanUtil {  
  
public static <T> T convertSearchBean(Class<T> clazz, SearchCondition searchCondition) {  
try {  
T newInstance = clazz.newInstance();  
  
if (searchCondition == null || CollectionUtils.isEmpty(searchCondition.getConditions())) {  
return newInstance;  
}  
List<Condition> conditions = searchCondition.getConditions();  
conditions.forEach(filedCondition -> {  
if (StringUtils.isEmpty(filedCondition.getValue())) {  
return;  
}  
Field field = ReflectionUtils.findField(clazz, filedCondition.getName());  
if (field == null) {  
log.warn("convertSearchBean field match error,fieldName:{}", filedCondition.getName());  
return;  
}  
field.setAccessible(true);  
ReflectionUtils.setField(field, newInstance, toFieldType(field.getType(), filedCondition.getValue()));  
});  
  
// 如果条件对象存在字段[sorts]自动填充排序条件  
Field field = ReflectionUtils.findField(clazz, "sorts");  
if (field == null || CollectionUtils.isEmpty(searchCondition.getSorts())) {  
return newInstance;  
}  
field.setAccessible(true);  
//排序  
Collections.sort(searchCondition.getSorts());  
ReflectionUtils.setField(field, newInstance, searchCondition.getSorts());  
return newInstance;  
} catch (Exception e) {  
log.error("convertSearchBean error,searchCondition:{}", searchCondition, e);  
throw new RuntimeException(e);  
}  
}  
  
public static <T> PageRequest<T> convertToPageQuery(  
Class<T> clazz, PageRequest<SearchCondition> pageRequest) {  
T newQuery = convertSearchBean(clazz, pageRequest.getQuery());  
return new PageRequest<>(pageRequest.getPageNum(), pageRequest.getPageSize(), newQuery);  
}  
  
@SuppressWarnings("all")  
private static <T> T toFieldType(Class<?> type, String fieldValue) {  
/**  
* 自动转换支持的类型  
*/  
if (type.isAssignableFrom(String.class)) {  
return (T) fieldValue;  
}  
if (type.isAssignableFrom(Integer.class)) {  
return (T) Integer.valueOf(fieldValue);  
}  
if (type.isAssignableFrom(Long.class)) {  
return (T) Long.valueOf(fieldValue);  
}  
if (type.isAssignableFrom(Byte.class)) {  
return (T) Byte.valueOf(fieldValue);  
}  
if (type.isAssignableFrom(Boolean.class)) {  
return (T) Boolean.valueOf(fieldValue);  
}  
if (type.isAssignableFrom(Date.class)) {  
return (T) new Date(Long.parseLong(fieldValue));  
}  
return (T) fieldValue;  
}  
  
  
}

通用搜索DTO -> sql

实现思路:

1. 条件名称-> sql列名称(驼峰->下划线形式)
2. 搜索条件->sql (字符拼接)

详细代码:

public class SearchConditionSqlUtil {  
  
public static String buildConditionSql(SearchCondition searchCondition) {  
if (searchCondition == null || CollectionUtils.isEmpty(searchCondition.getConditions())) {  
return "";  
}  
StringBuilder sqlBuilder = new StringBuilder(" WHERE ");  
appendConditions(sqlBuilder, searchCondition.getConditions(), searchCondition.getComposeType());  
sqlBuilder.append(buildSortCondition(searchCondition.getSorts()));  
return sqlBuilder.toString();  
}  
  
private static void appendConditions(StringBuilder sqlBuilder, List<Condition> conditions, String composeType) {  
if (CollectionUtils.isEmpty(conditions)) {  
return;  
}  
composeType = StringUtils.equalsAny(composeType, ConditionExtInfo.COMPOSE_TYPE_AND, ConditionExtInfo.COMPOSE_TYPE_OR) ? composeType : ConditionExtInfo.COMPOSE_TYPE_AND;  
boolean first = true;  
for (Condition condition : conditions) {  
buildCondition(sqlBuilder, condition, composeType, first);  
first = false;  
}  
}  
  
private static void buildCondition(StringBuilder sqlBuilder, Condition condition, String composeType, boolean first) {  
ConditionExtInfo extInfo = condition.getExtInfo();  
String conditionType = Optional.ofNullable(extInfo).map(ConditionExtInfo::getType).orElse(ConditionExtInfo.CONDITION_TYPE_SIMPLE);  
if (!first) {  
sqlBuilder.append(" ").append(composeType).append(" ");  
}  
if (Objects.equals(conditionType, ConditionExtInfo.CONDITION_TYPE_SIMPLE)) {  
if (StringUtils.isEmpty(condition.getValue())) {  
return;  
}  
if (SqlCheckUtil.isSQLInjection(condition.getValue())) {  
throw new RuntimeException("条件参数非法");  
}  
sqlBuilder.append(getConditionSql(condition));  
} else if (Objects.equals(conditionType, ConditionExtInfo.CONDITION_TYPE_COMPLEX) && CollectionUtils.isNotEmpty(extInfo.getSubConditions())) {  
sqlBuilder.append("(");  
appendConditions(sqlBuilder, extInfo.getSubConditions(), extInfo.getComposeType());  
sqlBuilder.append(")");  
}  
}  
  
  
public static String buildSortCondition(List<SortCondition> sorts) {  
if (CollectionUtils.isEmpty(sorts)) {  
return "";  
}  
StringBuilder sqlBuilder = new StringBuilder();  
Collections.sort(sorts);  
sqlBuilder.append(" ORDER BY ");  
for (int i = 0; i < sorts.size(); i++) {  
SortCondition sort = sorts.get(i);  
sqlBuilder.append(getFieldName(sort.getName())).append(" ").append(sort.getType());  
if (i < sorts.size() - 1) {  
sqlBuilder.append(", ");  
}  
}  
return sqlBuilder.toString();  
}  
  
private static String getFieldName(String name) {  
  
String columnName = CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, name);  
if (!SqlCheckUtil.isValidSqlColumnName(columnName)) {  
throw new RuntimeException("条件参数非法");  
}  
return columnName;  
}  
  
private static String getConditionSql(Condition condition) {  
ConditionExtInfo extInfo = condition.getExtInfo();  
ConditionOperateEnum operate = extInfo == null ? ConditionOperateEnum.EQUAL : ConditionOperateEnum.getOperate(extInfo.getOperate());  
String valueType = extInfo != null && StringUtils.equalsAny(extInfo.getValueType(), ConditionExtInfo.VALUE_TYPE_NUMBER, ConditionExtInfo.VALUE_TYPE_TEXT) ? extInfo.getValueType() : ConditionExtInfo.VALUE_TYPE_TEXT;  
String conditionSql = getFieldName(condition.getName()) + " ";  
if (StringUtils.isNotEmpty(operate.fmt)) {  
return conditionSql + String.format(operate.fmt, getFmtSqlValue(condition.getValue(), valueType, operate));  
}  
return conditionSql + operate.operate + " " + autoStr(condition.getValue(), valueType);  
}  
  
private static Object[] getFmtSqlValue(String value, String valueType, ConditionOperateEnum operate) {  
if (Objects.equals(valueType, ConditionExtInfo.VALUE_TYPE_TEXT)) {  
switch (operate) {  
case LIKE:  
return new String[]{"'%" + value + "%'"};  
case LEFT_LIKE:  
return new String[]{"'" + value + "%'"};  
case RIGHT_LIKE:  
return new String[]{"'%" + value + "'"};  
case NOT_IN:  
case IN:  
return new String[]{Joiner.on(",").join(Arrays.stream(value.split(",")).map(v -> autoStr(v, valueType)).toArray())};  
case BETWEEN_AND:  
return Arrays.stream(value.split(",")).map(v -> autoStr(v, valueType)).toArray();  
  
}  
}  
return new String[]{autoStr(value, valueType)};  
}  
  
private static String autoStr(String value, String valueType) {  
if (Objects.equals(valueType, ConditionExtInfo.VALUE_TYPE_TEXT)) {  
return "'" + value + "'";  
}  
return value;  
}  
  
  
}

安全控制

主要实现参数条件名称和输入参数的合法性校验,防止sql注入

public class SqlCheckUtil {  
private static final String SQL_INJECTION_PATTERN = "(?i)(SELECT|INSERT|UPDATE|DELETE|DROP|ALTER|CREATE|EXEC|UNION|--|;|'|\"|\\)|\\(|\\)|\\(|\\)|\\(|\\))";  
  
private static final Pattern PATTERN = Pattern.compile(SQL_INJECTION_PATTERN);  
  
  
private static final Set<String> SQL_KEYWORDS = new HashSet<>();  
  
static {  
String[] keywords = {  
"SELECT", "FROM", "WHERE", "AND", "OR", "NOT", "IN", "LIKE", "IS", "NULL",  
"JOIN", "INNER", "LEFT", "RIGHT", "FULL", "OUTER", "ON", "GROUP", "BY", "HAVING",  
"ORDER", "LIMIT", "OFFSET", "DISTINCT", "AS", "CASE", "WHEN", "THEN", "ELSE", "END",  
"CREATE", "ALTER", "DROP", "TABLE", "INDEX", "VIEW", "TRIGGER", "PROCEDURE", "FUNCTION",  
"INSERT", "UPDATE", "DELETE", "SET", "VALUES", "TRANSACTION", "COMMIT", "ROLLBACK",  
"SAVEPOINT", "EXPLAIN", "USE", "SHOW", "DESCRIBE", "DESC", "EXISTS", "ANY", "SOME", "ALL",  
"UNION", "INTERSECT", "EXCEPT", "MINUS", "CAST", "CONVERT", "TO_CHAR", "TO_NUMBER", "TO_DATE",  
"TO_TIMESTAMP", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "LOCALTIME", "LOCALTIMESTAMP",  
"SYSDATE", "SYSTIME", "SYSTIMESTAMP", "USER", "SESSION_USER", "SYSTEM_USER", "CURRENT_USER",  
"SCHEMA", "DATABASE", "TRUE", "FALSE", "UNKNOWN", "BOOLEAN", "INTEGER", "INT", "SMALLINT",  
"BIGINT", "TINYINT", "FLOAT", "REAL", "DOUBLE", "DECIMAL", "NUMERIC", "CHAR", "VARCHAR",  
"LONGVARCHAR", "DATE", "TIME", "TIMESTAMP", "BINARY", "VARBINARY", "LONGVARBINARY", "BLOB",  
"CLOB", "ARRAY", "STRUCT", "REF", "ROWID", "NCHAR", "NVARCHAR", "LONGNVARCHAR", "NCLOB",  
"SQLXML", "ROW", "OBJECT", "MAP", "REF", "XML", "JSON", "ENUM", "SET", "BIT", "VARBIT",  
"LONGVARBIT", "BOOLEAN", "CURSOR", "ROWTYPE", "RECORD", "TABLE", "ARRAY", "MULTISET", "SEQUENCE"  
};  
for (String keyword : keywords) {  
SQL_KEYWORDS.add(keyword.toLowerCase());  
}  
}  
  
/**  
* 校验sql参数是否非法  
*  
* @param sqlParam  
* @return  
*/  
public static boolean isSQLInjection(String sqlParam) {  
if (sqlParam == null || sqlParam.isEmpty()) {  
return false;  
}  
  
Matcher matcher = PATTERN.matcher(sqlParam);  
return matcher.find();  
}  
  
/**  
* 校验入参是否符合SQL列名的格式  
*  
* @param columnName 待校验的列名  
* @return 如果符合格式返回true，否则返回false  
*/  
public static boolean isValidSqlColumnName(String columnName) {  
if (columnName == null || columnName.isEmpty()) {  
return false;  
}  
  
// 列名不能是SQL关键字  
if (SQL_KEYWORDS.contains(columnName.toLowerCase())) {  
return false;  
}  
  
// 列名不能以数字开头  
if (Character.isDigit(columnName.charAt(0))) {  
return false;  
}  
  
// 列名只能包含字母、数字和下划线  
for (char c : columnName.toCharArray()) {  
if (!Character.isLetterOrDigit(c) && c != '_') {  
return false;  
}  
}  
  
return true;  
}  
}

仓库地址: github.com/dushitaoyua…