后台实现基于注解实现分页动态模糊查询

一、后台使用 自定义注解@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 最后别忘了基类实现序列化接口