数据库对某些条件的过滤支持不是很好,比如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过滤器
*/
@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;
}
}
- 其抽象子类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);
});
}