一、后台使用 自定义注解@interface 实现动态sql 拼接模糊查询 主要用于mvc 接收前台 各参数不为空 并且注解作用于java bean属性时候 通过反射扫描动态拼接sql(一般用于分页模糊查询)
1）首先第一步 手动自定义注解放在接收前台的model上 java 运行时候通过反射加载标记
/** * @author zhourui * @create 2019/12/25 */ @Documented @Retention(RetentionPolicy.RUNTIME) @Target({ElementType.FIELD}) /** * SQL 语句生成属性注解 * 1、支持基础数据类型 String 和 date类型以及数组集合，其他类型类型不支持添加任何注解，加注解无任何操作 * 2、数据为空不生成语句 * 3、支持=，>,>=,<,<=,like ,in等 */ public @interface SQLGenerateFieId { /** * 符合 */ String Conform() default "="; /** * 表别名 */ String alias() default ""; /** * 数据库对应列 */ String[] column() default ""; /** * 日期格式化格式 */ String dateFormat() default "yyyy-MM-dd HH:mm:ss"; /** * 不等于!= */ String NO_equal="！="; /** * 大于 */ String Greater=">"; /** * 大于等于 */ String Greater_and_equal=">="; /** * 小于 */ String Less="<"; /** * 小于等于 */ String Less_and_equal="<="; /** * 模糊查询 */ String Like="LIKE"; /** * 多个值进行查询 */ String In="IN"; }
2）这样modelQuery就可以使用
这里注意两个注解 都是对时间进行处理的！！！
@JsonFormat(locale = "zh", timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
@DateTimeFormat( pattern = "yyyy-MM-dd HH:mm:ss")
注意有个baseQuery基类 modelQuery 都得继承 用于默认set当前页和每页条数赋值
package com.fyun.common.model.base; import java.io.Serializable; import java.util.List; import java.util.regex.Matcher; import java.util.regex.Pattern; /** * Copyright glbpay.com * <p> * Created by wsp on 2018/9/7. * Descripton: 分页查询 */ public class BaseQuery implements Serializable { private OrderItem[] items; /** * 当前页 */ private Integer currentPage; /** * 页面大小,默认返回1000条数据 */ private Integer size = 1000; public boolean check() { return currentPage != null && currentPage > 0 && size != null && size > 0; } /** * 获取 排序字段 多个,分割 */ public String getOrderCase() { String result = null; if (getItems() != null && getItems().length > 0) { StringBuffer sb = new StringBuffer(); for (OrderItem item : getItems() ) { sb.append(" " + camel2Underline(item.getColumn()) + " " + item.getType() + ","); } result = sb.toString(); result = result.substring(0, result.length() - 1); } return result; } /** * 获取 当前页 */ public Integer getCurrentPage() { return this.currentPage; } /** * 设置 当前页 */ public void setCurrentPage(Integer currentPage) { this.currentPage = currentPage; } /** * 获取 页面大小 */ public Integer getSize() { return this.size; } /** * 设置 页面大小 */ public void setSize(Integer size) { this.size = size; } public OrderItem[] getItems() { return items; } public void setItems(OrderItem[] items) { this.items = items; } /** * 驼峰法转下划线 * * @param line 源字符串 * @return 转换后的字符串 */ public static String camel2Underline(String line) { if (line == null || "".equals(line)) { return ""; } line = String.valueOf(line.charAt(0)).toUpperCase().concat(line.substring(1)); StringBuffer sb = new StringBuffer(); Pattern pattern = Pattern.compile("[A-Z]([a-z\d]+)?"); Matcher matcher = pattern.matcher(line); while (matcher.find()) { String word = matcher.group(); sb.append(word.toUpperCase()); sb.append(matcher.end() == line.length() ? "" : "_"); } return sb.toString(); } public static void main(String[] args) { BaseQuery baseQuery = new BaseQuery(); System.out.println(baseQuery.getOrderCase()); } }
3）编写一个检测 modelQuery上注解Field的sql反射类
package com.fyun.common.utils.util; import com.fyun.common.utils.annottaton.SQLGenerateFieId; import java.lang.reflect.Field; import java.math.BigDecimal; import java.util.Collection; import java.util.Date; /** * Copyright glbpay.com * <p> * Created by wsp on 2018/9/5. * Descripton: */ public class SQLGenerateUtils { public static String whereSQLGenerate(Object o) throws IllegalAccessException { if (o == null) return ""; StringBuffer sb = new StringBuffer(); for (Field f : o.getClass().getDeclaredFields()) { SQLGenerateFieId sqlField = f.getAnnotation(SQLGenerateFieId.class); if (null != sqlField) { String alias = sqlField.alias(); if (StringUtils.isEmpty(alias)) alias = ""; else alias = alias + "."; f.setAccessible(true); Object ob = f.get(o); if (ob != null && (checkType(ob) || isDate(ob))) { String value = ""; if (isDate(ob)) { value = DateUtil.format((Date) ob, sqlField.dateFormat()); } else if (ob instanceof Collection || ob.getClass().isArray()) { value = collectionToString((Collection) ob); } else { value = String.valueOf(ob); } if (StringUtils.isEmpty(value)) continue; if (sqlField.column().length == 1) {//单个字段 if (StringUtils.isNotEmpty(sqlField.column()[0])) { sb.append(" and " + alias + Underline2CamelUtils.camel2Underline(sqlField.column()[0]) + " "); } else { sb.append(" and " + alias + Underline2CamelUtils.camel2Underline(f.getName()) + " "); } symbolSql(sb, sqlField, value); } else if (sqlField.column().length > 1) {//多个字段 boolean first = true; sb.append("and ( "); for (String column : sqlField.column() ) { if (!first) { sb.append(" or "); } else { first = false; } sb.append(alias); sb.append(Underline2CamelUtils.camel2Underline(column) + " "); symbolSql(sb, sqlField, value); } sb.append(" )"); } } } } return sb.toString(); } private static void symbolSql(StringBuffer sb, SQLGenerateFieId sqlField, String value) { if (SQLGenerateFieId.Like.equalsIgnoreCase(sqlField.Conform())) { sb.append(sqlField.Conform() + "'%" + value + "%' "); } else if (SQLGenerateFieId.In.equalsIgnoreCase(sqlField.Conform())) { sb.append(sqlField.Conform() + " (" + value + ") "); } else { sb.append(sqlField.Conform() + " '" + value + "' "); } } private static String collectionToString(Collection obs) { StringBuffer sb = new StringBuffer(); if (obs.size() <= 0) return ""; for (Object ob : obs ) { if (checkType(ob)) { sb.append("'" + String.valueOf(ob) + "',"); } } String result = sb.toString(); result = result.substring(0, result.lastIndexOf(",")); return result; } private static boolean isDate(Object ob) { return ob instanceof Date; } /** * 检查符合查询的对象类型 判断符合基础类型 * * @param param * @return */ private static boolean checkType(Object param) { boolean result = false; if (param instanceof Integer) { result = true; } else if (param instanceof Short) { result = true; } else if (param instanceof String) { result = true; } else if (param instanceof Double) { result = true; } else if (param instanceof Float) { result = true; } else if (param instanceof Long) { result = true; } else if (param instanceof Boolean) { result = true; } else if (param instanceof BigDecimal) { result = true; } else if (param instanceof Collection || param.getClass().isArray()) { result = true; } return result; } }
4）根据总记录数count(1) 当前页和当前页条数查出list<item> 集合数据，这里封装page<T> 工具类，主要根据当前页计算 sql的limit
package com.fyun.common.model.base; import java.util.List; public class Page<T> extends BaseModel{ private boolean hasNext; /** * 当前页 */ private int currentIndex; /** * 每页记录数 */ private int pageSize; /** * 总记录数 */ private int totalNumber; /** * 当前页的数据记录 */ private List<T> items; private boolean notLimitPageSize = false; //不设置每页个数最大值 /** * @param totalNumber * @param currentIndex * @param pageSize * @param items */ public Page(int totalNumber, int currentIndex, int pageSize, List<T> items) { this.totalNumber = totalNumber; this.currentIndex = currentIndex; this.pageSize = pageSize; this.items = items; } /** * 设置每页个数最大值 * * @param totalNumber * @param currentIndex * @param pageSize * @param notLimitPageSize * @param items */ public Page(int totalNumber, int currentIndex, int pageSize, boolean notLimitPageSize, List<T> items) { this.totalNumber = totalNumber; this.currentIndex = currentIndex; this.pageSize = pageSize; this.notLimitPageSize = notLimitPageSize; this.items = items; } /** * @return */ public int getPageSize() { if (!notLimitPageSize && pageSize > 50) { pageSize = 50; } return pageSize; } /** * @param pageSize */ public void setPageSize(int pageSize) { this.pageSize = pageSize; } /** * @return */ public int getCurrentIndex() { if (this.currentIndex > this.getTotalPage()) { this.currentIndex = this.getTotalPage(); } if (this.currentIndex < 1) { this.currentIndex = 1; } return currentIndex; } /** * @param currentIndex */ public void setCurrentIndex(int currentIndex) { this.currentIndex = currentIndex; } /** * @return */ public int getTotalNumber() { return totalNumber; } /** * @param totalNumber */ public void setTotalNumber(int totalNumber) { this.totalNumber = totalNumber; } /** * 总页数 * * @return */ public int getTotalPage() { int size = this.totalNumber / this.pageSize; if (this.totalNumber % this.pageSize != 0) { size = size + 1; } return size; } /** * orc * * @return */ public int getStartRow() { return (this.getCurrentIndex() - 1) * this.getPageSize(); } public int getEndRow() { return this.getCurrentIndex() * this.getPageSize(); } /** * 当前页的下一页，如果当前耶大于等于最后一页 那么下一页就是最后一页。 * * @return */ public int getNextIndex() { if (this.currentIndex >= getTotalPage()) { return this.currentIndex; } else { return this.currentIndex + 1; } } /** * 当前页的上一页，如果当前页小于第一页那么上一页为0 * * @return */ public int getPreIndex() { if (this.currentIndex <= 1) { return 0; } else { return this.currentIndex - 1; } } /** * 根据 pageSize 计算，可能不满足所有场景 * * @return */ public int getNextStart() { return this.currentIndex + this.pageSize; } /** * 根据 pageSize 计算，可能不满足所有场景 * * @return */ public int getPreStart() { return this.currentIndex - this.pageSize; } /** * @return */ public List<T> getItems() { return items; } /** * @param items */ public void setItems(List<T> items) { this.items = items; } public boolean isHasNext() { hasNext = this.currentIndex * this.pageSize < this.totalNumber; return hasNext; } public void setHasNext(boolean hasNext) { this.hasNext = hasNext; } }
5）封装一个排序desc和asc 类（modelQuery需调用）主要用于sql 查询
/** * Copyright glbpay.com * <p> * Created by wsp on 2018/11/14. * Descripton: */ public class OrderItem extends BaseModel { /** * 排序字段 */ private String column; /** * 排序方式 不填是asc 升序 desc 降序 */ private String type; public String getColumn() { return column; } public void setColumn(String column) { this.column = column; } public String getType() { return type; } public void setType(String type) { this.type = type; } public OrderItem() { } public OrderItem(String column, String type) { this.column = column; this.type = type; } }
ok 最后别忘了基类实现序列化接口
