Springboot + easyui + mybatis 高级搜索功能实现

1
2
3
4
5

var advanceConfig = [{name:’name’,title:’人员姓名’,type:’input’},
{name:’sex’,title:’性别’,type:’book’,code:’sex’},
{name:’birthday’,title:’出生年月’,type:’date’,formart:’yyyy-MM’},
{name:’primaryClassification’,title:’一级分类’,type:’book’,code:’primary_classification’},
];

<script type="text/javascript"
src=”${fhs_static_url}js/My97DatePicker/WdatePicker.js”>

//记录每个index是类型是什么
var indexType = {};

var advnaceIsInit= false;

var filter_index = 0;

function openAdvance(){
advanceConfig = advanceConfig.filter(function(val){
return !(!val || val === “”);
});
for(i=0;i
if(advanceConfig[i]){
advanceConfig[i].index = (i+1);
}
}
if(!advnaceIsInit){
addFilter();
advnaceIsInit = true;
}
$(‘#advanceDialog’).dialog(‘open’).dialog(‘setTitle’, ‘高级搜索’);
}

//添加一个过滤条件
function addFilter(){
var _html = baidu.template(‘rowTemplate’,{filterIndex:filter_index});
$(‘#advanceFormDiv’).append(_html);
$.parser.parse(‘#filter’ + filter_index);
filter_index = filter_index + 1;
}

//修改右侧内容
function filterFieldChange(_tempIndex,_record){
var _tempHtml = ”;
indexType[_tempIndex] = _record;
var _needSetFilterTypeReadonly = false;
if(_record.type==’input’ || _record.type==’number’){
_tempHtml = ”;
}
else if(_record.type==’date’){
if(!_record.formart){
_record.formart = ‘yyyy-MM-dd’;
}
console.log(_record.formart);
_tempHtml = ”;
}
else if(_record.type==’book’){
_tempHtml = ‘<input type="text" class="easyui-combobox"' +
‘ url=”${fhs_basics_url}/webApi/wordbook/getData” />’;
_needSetFilterTypeReadonly = true;
}
else if(_record.type==’select’){
_tempHtml = ‘<input type="text" class="easyui-combobox"' +
‘ url=”‘ + _record.url + ‘” valueField=”‘+_record.valueField+'” textField=”‘+_record.textField+'”/>’;
_needSetFilterTypeReadonly = true;
}
if(_record.name==’searchKey’){
_needSetFilterTypeReadonly = true;
}
if(_needSetFilterTypeReadonly){
$(‘#advanceFilterType’ + _tempIndex).combobox(‘setValue’,’0′);
$(‘#advanceFilterType’ + _tempIndex).combobox(‘readonly’,true);
}else{
$(‘#advanceFilterType’ + _tempIndex).combobox(‘readonly’,false);
}
if(_record.searchKeyType ==’str’){
$(‘#advanceFilterType’ + _tempIndex).combobox(‘setValue’,’1′);
}
$(‘#advanceFilterValLable’ + _tempIndex).html(_tempHtml);
$.parser.parse(‘#advanceFilterValLable’ + _tempIndex);
}

var extAdvanceFilterParam = [];
function execAdvanceSearch(){
extAdvanceFilterParam = [];
for(i=0;i<filter_index;i++){
if($(‘#advanceFilterField’ + i).length>0 && $(‘#advanceFilterField’ + i).combobox(‘getValue’)){
var _val = ”;
if(indexType[i].type!==’book’ && indexType[i].type!==’select’){
_val = $(‘#advanceFilterVal’ + i).val();
}else{
_val = $(‘#advanceFilterVal’ + i).combobox(‘getValue’);
}
if(_val){
extAdvanceFilterParam.push({name:indexType[i].name,val:_val,
filterType:$(‘#advanceFilterType’ + i).combobox(‘getValue’),
searchKeyType:indexType[i].searchKeyType,
fieldName:indexType[i].fieldName});
}
}
}
$(‘#listGrid’).datagrid(‘load’, {
extAdvanceFilterParam:JSON.stringify({filterType:$(‘#advanceFilterType’).combobox(‘getValue’),extAdvanceFilterParamArray:extAdvanceFilterParam})
});
//reload();
console.log(extAdvanceFilterParam);
}

“>
选择字段：

<select class="easyui-combobox" data-options="
onSelect:function(_record){
var _tempIndex = ;
filterFieldChange(_tempIndex,_record);
},
data:advanceConfig,
valueField:’index’,
textField:’title’,
“>

等于
包含
不等于
大于等于
小于等于
大于
小于
以什么开始
以什么结尾

‘).remove()”>删除

过滤条件匹配：

AND(满足所有)
OR(满足一个)

添加

搜索
关闭

function closeAdvanceSearch(){
$(‘#advanceDialog’).dialog(‘close’);
}

1
2
3
4
5

/**
* 高级搜索过滤条件
*/
@Transient
private String extAdvanceFilterParam;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129

static {
SIMPLE_OPERATOR.put(POJOConstant.EQ, ” = “);
SIMPLE_OPERATOR.put(POJOConstant.LIKE, ” LIKE “);
SIMPLE_OPERATOR.put(POJOConstant.NEQ, ” != “);
SIMPLE_OPERATOR.put(POJOConstant.BIGGER_EQ, ” >= “);
SIMPLE_OPERATOR.put(POJOConstant.LESS_EQ, ” <= ");
SIMPLE_OPERATOR.put(POJOConstant.LESS, ” < ");
SIMPLE_OPERATOR.put(POJOConstant.START_WITH, ” LIKE “);
SIMPLE_OPERATOR.put(POJOConstant.END_WITH, ” LIKE “);
SIMPLE_OPERATOR.put(POJOConstant.BIGGER, ” > “);
}

/**
* 获取高级搜索的where条件
*
* @return
*/
public String getAdvanceSearchSql() {
if (extAdvanceFilterParam == null) {
return null;
}
JSONObject extAdvanceFilterParamJson = JSON.parseObject(extAdvanceFilterParam);
String filterType = ” OR “;
boolean isOr = true;
if (extAdvanceFilterParamJson.getString(“filterType”).equals(“and”)) {
filterType = ” AND “;
isOr = false;
}
JSONArray extAdvanceFilterParamArray = extAdvanceFilterParamJson.getJSONArray(“extAdvanceFilterParamArray”);
JSONObject tempAFilter = null;
Field field = null;
String sqlField = null;
String tempVal = null;
StringBuilder whereSql = new StringBuilder(isOr ? ” AND (” : ” AND “);
boolean isHashWhere = false;
for (int i = 0; i < extAdvanceFilterParamArray.size(); i++) {
tempAFilter = extAdvanceFilterParamArray.getJSONObject(i);

field = ReflectUtils.getDeclaredField(this.getClass(), tempAFilter.getString(“name”));
if (field == null) {
log.error(“字段不存在:” + field);
continue;
}
sqlField = getSqlField(field);
tempVal = formartVal(field, tempAFilter.get(“val”), tempAFilter.getString(“filterType”), tempAFilter.getString(“searchKeyType”)
, tempAFilter.getString(“fieldName”));
if (sqlField == null || tempVal == null || !SIMPLE_OPERATOR.containsKey(tempAFilter.getString(“filterType”))) {
log.error(“条件不满足，无法拼接此字段，详情请打断点:” + field);
continue;
}
if (whereSql.length() > 6) {
whereSql.append(filterType);
}
whereSql.append(sqlField + (“searchKey”.equals(field.getName()) ? ” LIKE ” : SIMPLE_OPERATOR.get(tempAFilter.getString(“filterType”))) + tempVal + ” “);
isHashWhere = true;
}
if(!isHashWhere){
return “”;
}
whereSql.append(isOr ? “)” : “”);
return whereSql.toString();
}

/**
* 格式化值
*
* @param field lambdaSett
* @param val值
* @param filterType
* @return 值的sql格式
*/
protected String formartVal(Field field, Object val, String filterType, String searchKeyType, String fieldName) {
if (val == null || “null”.equals(val)) {
return “null”;
}
Class type = field.getType();
String result = null;
// 字符串直接是字段名
if (!CheckUtils.isNullOrEmpty(searchKeyType)) {
if (“str”.equals(searchKeyType)) {
return ” CONCAT(‘%’,'”” + fieldName + “”‘,’%’,'” + val + “‘,’%’) “;
} if (“streq”.equals(searchKeyType)) {
return ” CONCAT(‘%’,'”” + fieldName + “”:”” + val + “”‘,’%’) “;
} else if (“date”.equals(searchKeyType)) {
return ” CONCAT(‘%’,'”” + fieldName + “”:”” + val + “‘,’%’) “;
} else if (“int”.equals(searchKeyType)) {
return ” CONCAT(‘%’,'”” + fieldName + “”:”” + val + “”‘,’%’) “;
}
}
//只有字符串才有like 需要特殊处理
if (type == String.class) {
if (POJOConstant.LIKE.equals(filterType)) {
result = ” CONCAT(‘%’,'” + val + “‘,’%’) “;
} else if (POJOConstant.START_WITH.equals(filterType)) {
result = ” CONCAT(‘” + val + “‘,’%’) “;
} else if (POJOConstant.END_WITH.equals(filterType)) {
result = ” CONCAT(‘%’,'” + val + “‘) “;
} else {
result = “‘” + val + “‘”;
}
} else if (type == Number.class || Number.class.isAssignableFrom(type)) {
result = ConverterUtils.toString(val);
} else if (type == Date.class || Date.class.isAssignableFrom(type)) {
Date dateVal = DateUtils.parseStr(ConverterUtils.toString(val));
return “FROM_UNIXTIME(” + (dateVal.getTime() / 1000) + “)”;
} else {
log.warn(“格式不支持:” + field + val);
return null;
}
return result;
}

public String getSqlField(Field field) {
if (field.isAnnotationPresent(TableField.class)) {
TableField tableField = field.getAnnotation(TableField.class);
if (tableField.exist()) {
return tableField.value();
} else {

return null;
}
} else if (field.isAnnotationPresent(Column.class)) {
Column column = field.getAnnotation(Column.class);
return column.name();
}
return null;
}