Java應用程式層做過濾__Java

資料庫對某些條件的過濾支援不是很好，比如LIKE，造成查詢效率不高，實踐中我們設計了一個工具類，用於Java應用程式層過濾。

我們基於JFinal的Db+Record實現。

首先設計一個記錄過濾器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過濾器 */@FunctionalInterfacepublic 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;    }}

其抽象子類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;    }}

真正的過濾方法

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);        });    }