当前位置：首页 > news >正文

拼接sql字符串工具类

news 来源：原创 2024/9/30 14:18:02

申明注解

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface StrSqlAnnotation {/*** 表字段名称,如"create_name"** @return*/String filedName() default "";/*** 类型 STR-字符串 LIST-集合* {@link com.ltd.ccci.svc.transport.api.constant.CommonSymbolConstant}** @return*/String type() default CommonSqlKeyWordConstant.LIKE_IN_TYPE;}

相关常量类
a. sql拼接常量:CommonSqlKeyWordConstant

public class CommonSqlKeyWordConstant {public static final String AND = "AND ";public static final String OR = "OR";public static final String NOT = "NOT";public static final String IN = "IN";public static final String NOT_IN = "NOT IN";public static final String LIKE = "LIKE";public static final String NOT_LIKE = "NOT LIKE";public static final String EQ = "=";public static final String NE = "<>";public static final String GT = ">";public static final String GE = ">=";public static final String LT = "<";public static final String LE = "<=";/*** 搜索条件-like or eq*/public static final String LIKE_IN_TYPE = "LIKE_IN_TYPE";}

b. 符号常量:CommonSymbolConstant(可不使用)

public class CommonSymbolConstant {private CommonSymbolConstant() {}/*** 横杠*/public static final String BAR = "-";/*** 波浪号*/public static final String TILDE = "~";/*** 下划线*/public static final String UNDERLINE = "_";/*** 斜杠*/public static final String SLASH = "/";/*** 冒号*/public static final String COLON = ":";/*** 分号*/public static final String SEMICOLON = ";";/*** 逗号*/public static final String COMMA = ",";/*** 点*/public static final String POINT = ".";/*** 顿号*/public static final String PAUSE = "、";/*** 点*/public static final String EMPTY = "";/*** 空格*/public static final String BLANK = " ";/*** 单引号*/public static final String SINGLE_QUOTES = "'";/*** 正括号*/public static final String OPEN_BRACKET = "(";/*** 反括号*/public static final String CLOSE_BRACKET = ")";
}

工具类：SearchUtil

public class SearchUtil {/*** 翻译单个实体** @param obj 实体信息*/public static <T, V> QueryWrapper<V> getSearchWrapper(T obj, Class<V> ignoredClazz) {QueryWrapper<V> wrapper = new QueryWrapper<>();// 防止未传条件不拼接where条件if (!ObjectUtil.isEmpty(obj)) {try {Class<?> aClass = obj.getClass();Map<String, Field> fields = getAllFileds(aClass);for (Field field : fields.values()) {if (field.isAnnotationPresent(StrSqlAnnotation.class)) {field.setAccessible(true);StrSqlAnnotation annotation = field.getAnnotation(StrSqlAnnotation.class);String filedName = annotation.filedName();if (StringUtils.isBlank(filedName)) {filedName = StrUtil.toUnderlineCase(field.getName());}String type = annotation.type();Object object = field.get(obj);if (ObjectUtil.isNotEmpty(object)) {Class<?> filedClass = object.getClass();String str = object.toString().trim();// 日期格式单独转化if (filedClass.equals(LocalDateTime.class)) {DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(DatePattern.NORM_DATETIME_PATTERN);str = ((LocalDateTime) object).format(dateTimeFormatter);} else if (filedClass.equals(LocalDate.class)) {DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(DatePattern.NORM_DATE_PATTERN);str = ((LocalDate) object).format(dateTimeFormatter);}switch (type) {// like incase CommonSqlKeyWordConstant.LIKE_IN_TYPE -> {String[] split = str.split(",");if (split.length > 1) {wrapper.in(filedName, Arrays.asList(split));} else {wrapper.like(filedName, str);}}// incase CommonSqlKeyWordConstant.IN -> {String parse = JSONUtil.toJsonStr(object);List<String> list = JSONUtil.toList(parse, String.class);// LocalDateTime类型如果是集合传入，接收时类型为Long。时间集合建议传List<String>if (CollUtil.isNotEmpty(list)) {wrapper.in(filedName, list);}}// likecase CommonSqlKeyWordConstant.LIKE -> wrapper.like(filedName, str);// =case CommonSqlKeyWordConstant.EQ -> wrapper.eq(filedName, str);// >=case CommonSqlKeyWordConstant.GE -> wrapper.ge(filedName, str);// <=case CommonSqlKeyWordConstant.LE -> wrapper.le(filedName, str);default -> {}}}}}} catch (Exception e) {throw new ApiException(MsgUtils.getMessage("biz.common.StrFiledError"));}}return wrapper;}/*** 获取所有字段含父级** @param aClass 实体类* @return 所有字段信息*/private static Map<String, Field> getAllFileds(Class<?> aClass) {// key-字段名  value-字段信息Map<String, Field> fileds = new HashMap<>();List<Field> fieldList = new ArrayList<>();Class<?> tempClass = aClass;//当父类为null的时候说明到达了最上层的父类(Object类).while (tempClass != null) {fieldList.addAll(Arrays.asList(tempClass.getDeclaredFields()));//得到父类,然后赋给自己tempClass = tempClass.getSuperclass();}for (Field field : fieldList) {fileds.put(field.getName(), field);}return fileds;}}

使用示例
实体类注解使用

    @Schema(title = "线路编号")@StrSqlAnnotation(filedName = "no")private String no;@Schema(title = "物流供应商编码-单个查询-精确查询")@StrSqlAnnotation(type = CommonSqlKeyWordConstant.EQ)private String logisticsSupplierCode;@Schema(title = "物流合同号")@StrSqlAnnotation()private String logisticsContractNo;@Schema(title = "物流供应商编码")@StrSqlAnnotation(filedName = "logistics_supplier_code", type = CommonSqlKeyWordConstant.IN)private List<String> logisticsSupplierCodes;

LambdaQueryWrapper使用示例

private LambdaQueryWrapper<LineMainDO> dealSearchInfo(LinePageReq req, SFunction<LineMainDO, ?> sortFiled, boolean whetherAsc) {LambdaQueryWrapper<LineMainDO> lambdaQuery = SearchUtil.getSearchWrapper(req, LineMainDO.class).lambda();if (CollUtil.isEmpty(req.getSortItems())) {lambdaQuery.orderBy(true, whetherAsc, sortFiled);}return lambdaQuery;}

xml使用示例
service

    public CommonPage<ReceiptPageDTO> queryPage(ReceiptPageReq req) {QueryWrapper<ReceiptMainDO> searchWrapper = SearchUtil.getSearchWrapper(req, ReceiptMainDO.class);IPage<ReceiptPageDTO> page = receiptMainMapper.queryPage(MPPager.buildPage(req), searchWrapper);return MPCommonPage.restPage(page );}

mapper

  /*** 运输签收分页（明细联表）** @param ipage 分页信息* @param sql   高级搜索条件sql* @return 分页信息*/IPage<ReceiptPageDTO> queryPage(Page<ReceiptMainDO> ipage, @Param(Constants.WRAPPER) QueryWrapper<ReceiptMainDO> sql);

xml

 <select id="queryPage" resultType="com.ltd.ccci.svc.transport.api.dto.receipt.ReceiptPageDTO"><select id="queryPage" resultType="com.ltd.ccci.svc.transport.api.dto.receipt.ReceiptPageDTO">SELECT *FROM receipt_main m,receipt_detail d<choose><when test="ew.customSqlSegment != null and ew.customSqlSegment != ''">${ew.customSqlSegment} and</when><otherwise>where</otherwise></choose>m.id = d.receipt_main_idand d.del_flag = 0and m.del_flag = 0order by m.update_time, d.create_time, d.id</select>