Java应用层做过滤

数据库对某些条件的过滤支持不是很好，比如LIKE，造成查询效率不高，实践中我们设计了一个工具类，用于Java应用层过滤。

我们基于JFinal的Db+Record实现。

1. 首先设计一个记录过滤器RecordFilter，用于判断某个Record是否满足要求。

import com.jfinal.plugin.activerecord.Record;

import java.util.Iterator;
import java.util.List;
import java.util.Objects;

/**
 * @author 熊诗言 2017/01/07
 * Record过滤器
 */
@FunctionalInterface
public interface RecordFilter {
    /**
     * 看一条记录是否满足条件
     * @param record Record
     * @return 是否满足
     */
    boolean accept(Record record);
    /**
     * 过滤记录
     * @param records 源记录，同时也是过滤后的
     * @return 满足条件的记录条数
     */
    default List<Record> accept(List<Record> records){
        Objects.requireNonNull(records);
        Iterator<Record> iterator = records.iterator();
        while (iterator.hasNext()) {
            Record record = iterator.next();
            boolean acc = accept(record);
            if(!acc){
                //不满足的删除
                iterator.remove();
            }
        }
        return records;
    }
}

1. 其抽象子类AbstractRecordFilter，主要提供一些判断的方法，另外更好地实现删除一个List的remove。

package cn.zytx.common.db.sqlfilter;

import com.jfinal.plugin.activerecord.Record;

import java.sql.Time;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

/**
 * @author 熊诗言 2017/01/07
 * Record过滤器抽象类，提供一些通用的比较方法
 */
public abstract class AbstractRecordFilter implements RecordFilter{
    /**
     * 模拟LIKE
     */
    protected boolean attrContains(Record record,String attr,String except){
        return null != record.getStr(attr) && record.getStr(attr).contains(except);
    }
    protected boolean attrRegexMatch(Record record,String attr,String regex){
        return null != record.getStr(attr) && record.getStr(attr).matches(regex);
    }

    /**
     * 比较相等
     */
    protected boolean attrEquals(Record record,String attr,String except){
        return null != record.getStr(attr) && except.equals(record.getStr(attr));
    }
    protected boolean attrEquals(Record record,String attr,int except){
        return null != record.getInt(attr) && except == record.getInt(attr);
    }
    protected boolean attrEquals(Record record,String attr,long except){
        return null != record.getLong(attr) && except == record.getLong(attr);
    }
    protected boolean attrEquals(Record record,String attr,float except){
        return null != record.getFloat(attr) && except == record.getFloat(attr);
    }
    protected boolean attrEquals(Record record,String attr,double except){
        return null != record.getDouble(attr) && except == record.getDouble(attr);
    }
    protected boolean attrEquals(Record record,String attr,Date except){
        return null != record.getDate(attr) && except.getTime() == record.getDate(attr).getTime();
    }
    protected boolean attrEquals(Record record,String attr,Time except){
        return null != record.getTime(attr) && except.getTime() == record.getTime(attr).getTime();
    }
    protected boolean attrEquals(Record record,String attr,Timestamp except){
        return null != record.getTimestamp(attr) && except.compareTo(record.getTimestamp(attr)) == 0;
    }

    /**
     * Greater than or Equals 比较 >=，reverse就是 <
     */
    protected boolean attrGE(Record record,String attr,int except){
        return null != record.getInt(attr) && record.getInt(attr) >= except;
    }
    protected boolean attrGE(Record record,String attr,long except){
        return null != record.getLong(attr) && record.getLong(attr) >= except;
    }
    protected boolean attrGE(Record record,String attr,float except){
        return null != record.getFloat(attr) && record.getFloat(attr) >= except;
    }
    protected boolean attrGE(Record record,String attr,double except){
        return null != record.getDouble(attr) && record.getDouble(attr) >= except;
    }
    protected boolean attrGE(Record record,String attr,Date except){
        return null != record.getDate(attr) && record.getDate(attr).getTime() >= except.getTime();
    }
    protected boolean attrGE(Record record,String attr,Time except){
        return null != record.getTime(attr) && record.getTime(attr).getTime() >= except.getTime();
    }
    protected boolean attrGE(Record record,String attr,Timestamp except){
        return null != record.getTimestamp(attr) && record.getTimestamp(attr).getTime() >= except.getTime();
    }

    /**
     * Less than or Equals 比较 <=，reverse就是 >
     */
    protected boolean attrLE(Record record,String attr,int except){
        return null != record.getInt(attr) && record.getInt(attr) <= except;
    }
    protected boolean attrLE(Record record,String attr,long except){
        return null != record.getLong(attr) && record.getLong(attr) <= except;
    }
    protected boolean attrLE(Record record,String attr,float except){
        return null != record.getFloat(attr) && record.getFloat(attr) <= except;
    }
    protected boolean attrLE(Record record,String attr,double except){
        return null != record.getDouble(attr) && record.getDouble(attr) <= except;
    }
    protected boolean attrLE(Record record,String attr,Date except){
        return null != record.getDate(attr) && record.getDate(attr).getTime() <= except.getTime();
    }
    protected boolean attrLE(Record record,String attr,Time except){
        return null != record.getTime(attr) && record.getTime(attr).getTime() <= except.getTime();
    }
    protected boolean attrLE(Record record,String attr,Timestamp except){
        return null != record.getTimestamp(attr) && record.getTimestamp(attr).getTime() <= except.getTime();
    }

    @Override
    public List<Record> accept(List<Record> records) {
        /**
         * 从后往前遍历删除，移动的元素更少，效率更高
         */
        for (int i = records.size()-1; i >= 0 ; i--) {
            boolean acc = accept(records.get(i));
            if(!acc){
                records.remove(i);
            }
        }
        return records;
    }
}

1. 真正的过滤方法

package cn.zytx.common.db.sqlfilter;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * @author xiongshiyan at 2018/1/7
 * 查询表数据，复杂的过滤在Java应用层做，SQL中只写很容易过滤的条件，基于一个事实：数据库过滤复杂条件效率不高
 */
public class QueryListWithFilter {

    /**
     * 查询数据，传入过滤器，复杂的过滤写在过滤器中，SQL只写容易过滤的条件
     * @param sql SQL语句，可以写容易过滤的条件，像时间区间这种；SQL语句中不能包含?方法会自动加上LIMIT ?,?来分批次获取数据
     * @param pageNumber 页数
     * @param pageSize 每页数量
     * @param batchPullDataSize 每次从数据库拉取数量，此值有待测试优化，每次拉取多就可以少拉取几次，每次拉取少就多拉取几次，需要找到这个平衡点
     * @param pageTotalDataSize 页面可以放的总记录数量（基于一个事实，页面记录数量太多，也没得价值），此值如果设置很大，就退化为正常的查询。此值不宜过大，否则可能造成内存溢出
     * @param filter Java应用层过滤器，比较复杂的过滤放在Java层做，如果传进来null，则不过滤
     * @return Page<Record>
     */
    public static Page<Record> query(String sql, int pageNumber, int pageSize, int batchPullDataSize, int pageTotalDataSize, RecordFilter filter){
        Objects.requireNonNull(sql);
        if(sql.toUpperCase().contains("LIMIT")){
            throw new IllegalArgumentException("SQL语句中不能包含LIMIT语句");
        }
        sql += " LIMIT ?,?";
        if(null == filter){
            filter = new AllAcceptRecordFilter();
        }

        List<Record> resultList = getRecords(sql, batchPullDataSize, pageTotalDataSize, filter);

        //取出最多pageTotalDataSize，没有这么多就实际的条数
        /*if(resultList.size() < pageTotalDataSize){
            pageTotalDataSize = resultList.size();
        }
        resultList = resultList.subList(0,pageTotalDataSize);*/

        //计算总行数、总页数
        int totalRow = resultList.size();
        int totalPage = totalRow / pageSize;
        if(totalRow % pageSize != 0){
            ++totalPage;
        }
        //计算当页开始和结束条数
        int thisPage = (pageNumber - 1) * pageSize;
        int toIndex = thisPage + pageSize;
        //一般是末页不够的情况
        if(toIndex > resultList.size()){
            toIndex = resultList.size();
        }
        //获取当前页数据
        resultList = resultList.subList(thisPage,toIndex);
        return new Page<>(resultList, pageNumber, pageSize, totalPage, totalRow);
    }

    /**
     * 多次查询直到找到pageTotalDataSize条记录或者不够
     * @param sql SQL语句
     * @param batchPullDataSize 每次拉取数量
     * @param pageTotalDataSize 页面最多显示条数
     * @param filter 过滤器
     */
    private static List<Record> getRecords(String sql, int batchPullDataSize, int pageTotalDataSize, RecordFilter filter) {
        List<Record> resultList =  new ArrayList<>();
        int i=0;
        while(true){
            int thisPage = i * batchPullDataSize;
            List<Record> dataList = Db.find(sql,thisPage,batchPullDataSize);
            int dbCountNumber = dataList.size();
            dataList = filter.accept(dataList);
            resultList.addAll(dataList);
            if(resultList.size() >= pageTotalDataSize || dbCountNumber < batchPullDataSize){
                break;
            }
            i++;
        }
        return resultList;
    }
}

测试代码：

@Test
    public void testQueryListWithFilter(){
        String sql = "SELECT * FROM transaction_log";
        int pageNumber = 1;
        int pageSize = 3;
        int batchPullDataSize = 3;
        int pageTotalDataSize = 5;
        RecordFilter filter = (record)->{
            if(record.getInt("success") == 1){
                return true;
            }
            return false;
        };
        Page<Record> query = QueryListWithFilter.query(sql, pageNumber, pageSize, batchPullDataSize, pageTotalDataSize, filter);
        System.out.println(query.getPageNumber());
        System.out.println(query.getPageSize());
        System.out.println(query.getTotalPage());
        System.out.println(query.getTotalRow());
        List<Record> list = query.getList();
        list.forEach(record -> {
            System.out.println(record);
        });
    }