09年的时候,我使用hibernate的时候,看别人使用filter_eqs_xx等规则在列表中配置查询条件,感觉很方便。今天我已经启用hibernate了,因为曾经被他的lazy load受过伤,也不想对这个orm框架钻的太深,因为技术学海无涯,orm只是很小的一部分。
今天我使用mybatis,并通过mybatis生成pojo、example、dao、mapper,于是我根据mybatis generator生成代码的特性,以及参考了前人的例子,方便列表的查询。高手无所谓隐藏,我相信未来管理系统、前端开发技术越来越简单,所以这些东西藏着掖着犯不着,供程序猿们互相学习。
1 list.jsp
规则:
1.查询来自一个表,规则为:filter_likes_XX,例如filter_likes_memberName,memberName是单表中的属性
2.如果是联表,查询条件来自不同的表,规则为:filter_likes_XX__YYYExample,例如:filter_likes_memberName__SmsOrderExample,memberName是SmsOrder中的属性
3.有这么几类比较类型:EQ(等于), LIKE(like), LT(小于), GT(大于), LE(小于等于), GE(大于等于), NEQ(不等于), IN(in),LLIKE(左匹配), RLIKE(右匹配)
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<title></title>
<%@ include file="/WEB-INF/views/include/meta.jsp"%>
<%@ include file="/WEB-INF/views/include/taglib.jsp"%>
<%@ include file="/WEB-INF/views/include/easyui.jsp"%>
<%@ include file="/WEB-INF/views/include/textext.jsp"%>
</head>
<body>
<div id="tb" style="padding:5px;height:auto">
<div>
<form id="searchFrom" action="">
<input type="text" name="filter_likes_memberName__SmsOrderExample" class="easyui-textbox" data-options="width:150,prompt: '会员'"/>
<input type="text" name="filter_likes_address__SmsOrderExample" class="easyui-textbox" data-options="width:150,prompt: '会员手机号'"/>
<input type="text" name="filter_likes_address__SmsNewhomeOrderExample" class="easyui-textbox" data-options="width:150,prompt: '服务地址'"/>
<input id="appointmentTime_start" class="easyui-textbox" name="filter_ged_appointmentTime__SmsOrderExample" data-options="width:150,prompt: '预约起始时间'"/>
<input id="appointmentTime_end" class="easyui-textbox" name="filter_led_appointmentTime__SmsOrderExample" data-options="width:150,prompt: '预约截止时间'"/>
<shiro:hasPermission name="smsNewhomeOrder:find">
<a href="#" class="easyui-linkbutton" iconCls="icon-search" plain="true" id="searchFrom_find">查询</a>
<a href="#" class="easyui-linkbutton" iconCls="icon-reload" plain="true" id="searchFrom_reset">重置</a>
</shiro:hasPermission>
</form>
<table cellpadding="0" cellspacing="0">
<tr>
<shiro:hasPermission name="smsNewhomeOrder:send">
<td>
<a href="javascript:void(0)" class="easyui-linkbutton send" iconCls="icon-add" plain="true">派单</a>
</td>
<td>
<span class="toolbar-item dialog-tool-separator"></span>
</td>
</shiro:hasPermission>
</tr>
</table>
</div>
</div>
<table id="dg"></table>
<div id="dlg"></div>
<script src="${ctxResources}/pages/modules/base/smsNewhomeOrderList.js"></script>
</body>
</html>
2 controller.java
上面的list.jsp中form提交之后,调用controller之后,可以看到表单中的数据通过HttpServletRequest转换为Condition,然后通过MybatisExample生成对应的example,下面有两个例子,一个单表,一个联合表的查询。
@RequestMapping(value = "find")
@ResponseBody
public List<SmsRegion> findList(HttpServletRequest request){
List<Condition> conditions = Condition.buildFromHttpRequest(request);
MybatisExample mybatisExample = MybatisExample.getInstance();
SmsRegionExample example = (SmsRegionExample)mybatisExample.buildExampleByCondition(conditions, SmsRegionExample.class.getName());
List<SmsRegion> smsRegions = smsRegionService.selectByExample(example);
return smsRegions;
}
@RequestMapping(value = "find", method = RequestMethod.POST)
@ResponseBody
public Map<String,Object> find(HttpServletRequest request){
List<Condition> conditions = Condition.buildFromHttpRequest(request);
EasyUIPage easyUIPage = new EasyUIPage(request);
MybatisExample mybatisExample = MybatisExample.getInstance();
SmsNewhomeOrderUdfExample example = (SmsNewhomeOrderUdfExample)mybatisExample.buildExampleByCondition(conditions, easyUIPage, SmsNewhomeOrderUdfExample.class.getName());
PageList<SmsNewhomeOrderDto> smsNewhomeOrders = smsNewhomeOrderService.selectByExample(example, new PageBounds(easyUIPage.getPage(),
easyUIPage.getRows()));
return getEasyUIGrid(smsNewhomeOrders);
}
3 Condition.java
filter打头的条件封装
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import org.springframework.util.Assert;
import com.dzmsoft.framework.base.common.BaseConstant;
import com.dzmsoft.framework.base.util.ConvertUtils;
import com.dzmsoft.framework.base.util.ServletUtils;
import com.dzmsoft.framework.base.util.StringUtil;
/**
* 与具体ORM实现无关的属性过滤条件封装类, 主要记录页面中简单的搜索过滤条件.
*
* @author calvin
*/
public class Condition {
/** 多个属性间OR关系的分隔符. */
public static final String OR_SEPARATOR = "_OR_";
/** 属性比较类型. */
public enum MatchType {
EQ, LIKE, LT, GT, LE, GE, NEQ, IN,LLIKE, RLIKE;
}
/** 属性数据类型. */
public enum PropertyType {
S(String.class), I(Integer.class), L(Long.class), N(Double.class), D(Date.class), B(Boolean.class), C(BigDecimal.class);
private Class<?> clazz;
private PropertyType(Class<?> clazz) {
this.clazz = clazz;
}
public Class<?> getValue() {
return clazz;
}
}
private MatchType matchType = null;
private Object matchValue = null;
private String example = null;
private Class<?> propertyClass = null;
private String[] propertyNames = null;
public Condition() {
}
/**
* @param filterName 比较属性字符串,含待比较的比较类型、属性值类型及属性列表.
* eg. LIKES_NAME_OR_LOGIN_NAME
* @param value 待比较的值.
*/
public Condition(final String filterName, final String value) {
String firstPart = new String(StringUtil.substringBefore(filterName, "_"));
String matchTypeCode = new String(StringUtil.substring(firstPart, 0, firstPart.length() - 1).toUpperCase());
String propertyTypeCode = new String(StringUtil.substring(firstPart, firstPart.length() - 1, firstPart.length()).toUpperCase());
try {
matchType = Enum.valueOf(MatchType.class, matchTypeCode);
} catch (RuntimeException e) {
throw new IllegalArgumentException("filter名称" + filterName + "没有按规则编写,无法得到属性比较类型.", e);
}
try {
propertyClass = Enum.valueOf(PropertyType.class, propertyTypeCode).getValue();
} catch (RuntimeException e) {
throw new IllegalArgumentException("filter名称" + filterName + "没有按规则编写,无法得到属性值类型.", e);
}
String propertyNameStr = StringUtil.substringAfter(filterName, "_");
String[] strs = propertyNameStr.split(BaseConstant.Separate.TWO_UNDERLINE);
String propertyName = strs[0];
if (strs.length>1){
example = strs[1];
}
Assert.isTrue(StringUtil.isNotBlank(propertyName), "filter名称" + filterName + "没有按规则编写,无法得到属性名称.");
propertyNames = StringUtil.splitByWholeSeparator(propertyName, Condition.OR_SEPARATOR);
this.matchValue = ConvertUtils.convertStringToObject(value, propertyClass);
}
/**
* 从HttpRequest中创建PropertyFilter列表, 默认Filter属性名前缀为filter.
*
* @see #buildFromHttpRequest(HttpServletRequest, String)
*/
public static List<Condition> buildFromHttpRequest(final HttpServletRequest request) {
return buildFromHttpRequest(request, "filter");
}
/**
* 从HttpRequest中创建PropertyFilter列表
* PropertyFilter命名规则为Filter属性前缀_比较类型属性类型_属性名.
*
* eg.
* filter_EQS_name
* filter_LIKES_name_OR_email
*/
public static List<Condition> buildFromHttpRequest(final HttpServletRequest request, final String filterPrefix) {
List<Condition> filterList = new ArrayList<Condition>();
//从request中获取含属性前缀名的参数,构造去除前缀名后的参数Map.
Map<String, Object> filterParamMap = ServletUtils.getParametersStartingWith(request, filterPrefix + "_");
//分析参数Map,构造PropertyFilter列表
for (Map.Entry<String, Object> entry : filterParamMap.entrySet()) {
String filterName = entry.getKey();
String value = (String) entry.getValue();
//如果value值为空,则忽略此filter.
if (StringUtil.isNotBlank(value)) {
Condition filter = new Condition(filterName, value);
filterList.add(filter);
}
}
return filterList;
}
/**
* 获取比较值的类型.
*/
public Class<?> getPropertyClass() {
return propertyClass;
}
/**
* 获取比较方式.
*/
public MatchType getMatchType() {
return matchType;
}
/**
* 获取比较值.
*/
public Object getMatchValue() {
return matchValue;
}
/**
* 获取比较属性名称列表.
*/
public String[] getPropertyNames() {
return propertyNames;
}
/**
* 获取唯一的比较属性名称.
*/
public String getPropertyName() {
Assert.isTrue(propertyNames.length == 1, "There are not only one property in this filter.");
return propertyNames[0];
}
/**
* 是否比较多个属性.
*/
public boolean hasMultiProperties() {
return (propertyNames.length > 1);
}
public String getExample() {
return example;
}
public void setExample(String example) {
this.example = example;
}
}
4 MybatisExample
这里面无非使用到了java反射机制,没什么玄奥的,另外结合mybatis generator生成example的特性,最终通过example将查询条件传递到dao,那么问题就简单了。
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.util.Assert;
import com.dzmsoft.framework.base.common.BaseConstant;
import com.dzmsoft.framework.base.util.CheckEmptyUtil;
import com.dzmsoft.framework.base.util.StringUtil;
import com.dzmsoft.framework.base.web.mvc.pojo.EasyUIPage;
import com.dzmsoft.framework.base.web.mvc.view.Condition;
/**
* 产生mybatis generator相匹配的查询条件
*
* @author dzm
*/
public class MybatisExample {
private volatile static MybatisExample instance;
private MybatisExample() {
}
public static MybatisExample getInstance() {
if (instance == null) {
synchronized (MybatisExample.class) {
if (instance == null) {
instance = new MybatisExample();
}
}
}
return instance;
}
/**
* 构建example集合
*
* @param conditions
* @param example
* @return
* @throws NoSuchMethodException
* @throws SecurityException
* @throws IllegalAccessException
* @throws IllegalArgumentException
* @throws InvocationTargetException
*/
private Map<String, Object> getExampleMap(List<Condition> conditions, Object example)
throws NoSuchMethodException, SecurityException, IllegalAccessException,
IllegalArgumentException, InvocationTargetException {
Map<String, Object> exampleMap = new HashMap<String, Object>();
if (!CheckEmptyUtil.isEmpty(conditions)) {
for (Condition condition : conditions) {
if (!CheckEmptyUtil.isEmpty(condition.getExample())
&& !exampleMap.containsKey(condition.getExample())) {
Method getExampleMethod = example.getClass().getMethod(
"get" + condition.getExample());
Object chrildExample = getExampleMethod.invoke(example);
exampleMap.put(condition.getExample(), chrildExample);
}
}
}
return exampleMap;
}
private void buildCondition(Object example, List<Condition> conditions,
Map<String, Object> exampleMap) throws ClassNotFoundException, InstantiationException,
IllegalAccessException, NoSuchMethodException, SecurityException,
IllegalArgumentException, InvocationTargetException {
if (!CheckEmptyUtil.isEmpty(exampleMap) && !CheckEmptyUtil.isEmpty(conditions)) {
// 对查询条件,按照example进行分组
Map<String, List<Condition>> childMap = new HashMap<String, List<Condition>>();
for (String key : exampleMap.keySet()) {
List<Condition> childConditions = new ArrayList<Condition>();
for (Condition condition : conditions) {
if (key.equals(condition.getExample())) {
childConditions.add(condition);
}
}
if (!CheckEmptyUtil.isEmpty(childConditions)) {
childMap.put(key, childConditions);
}
}
//
if (!CheckEmptyUtil.isEmpty(childMap)) {
for (String key : childMap.keySet()) {
List<Condition> childList = childMap.get(key);
buildCondition(exampleMap.get(key), childList);
}
}
}
}
private void buildCondition(Object example, List<Condition> conditions)
throws NoSuchMethodException, SecurityException, IllegalAccessException,
IllegalArgumentException, InvocationTargetException {
// 单个example查询条件
Method createCriteria = example.getClass().getMethod("createCriteria");
Object criteria = createCriteria.invoke(example);
for (Condition condition : conditions) {
buildCondition(condition, criteria);
}
}
/**
* 构建查询条件
*
* @param conditions
* @param exampleName
* @return
*/
public Object buildExampleByCondition(List<Condition> conditions, String exampleName) {
Object example = null;
try {
Class<?> exampleClass = Class.forName(exampleName);
example = exampleClass.newInstance();
if (!CheckEmptyUtil.isEmpty(conditions)) {
Map<String, Object> exampleMap = getExampleMap(conditions, example);
//
if (CheckEmptyUtil.isEmpty(exampleMap)) {
// 单个example查询条件
buildCondition(example, conditions);
} else {
// 多个example查询条件
buildCondition(example, conditions, exampleMap);
}
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
return example;
}
public Object buildExampleByCondition(List<Condition> conditions, EasyUIPage page,
String exampleName) {
Object example = null;
if (conditions == null) {
return null;
}
try {
example = buildExampleByCondition(conditions, exampleName);
if (page != null && CheckEmptyUtil.isNotEmpty(page.getSort())
&& CheckEmptyUtil.isNotEmpty(page.getOrder())) {
// 构建排序条件
StringBuilder orderByClause = new StringBuilder("");
orderByClause.append(page.getSort()).append(" ").append(page.getOrder());
int periodsIndex = page.getSort().indexOf(BaseConstant.Separate.PERIODS) ;
if (periodsIndex>= 0) {
String childExampleStr = new String(page.getSort().substring(0, periodsIndex));
Method method = example.getClass().getMethod("get"+StringUtil.toUpperCaseFirstOne(childExampleStr)+"Example");
Object childExample = method.invoke(example);
Method method2 = childExample.getClass().getMethod("setOrderByClause", String.class);
method2.invoke(childExample, orderByClause.toString());
} else {
Method method = example.getClass().getMethod("setOrderByClause", String.class);
method.invoke(example, orderByClause.toString());
}
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
return example;
}
private void buildCondition(Condition condition, Object criteria) throws NoSuchMethodException,
SecurityException, IllegalAccessException, IllegalArgumentException,
InvocationTargetException {
Assert.hasText(condition.getPropertyName(), "propertyName不能为空");
StringBuilder methodName = new StringBuilder("");
StringBuilder matchValue = new StringBuilder("");
Method method = null;
Class<?> propertyClass = condition.getPropertyClass();
switch (condition.getMatchType()) {
case IN:
methodName.append("and")
.append(StringUtil.toUpperCaseFirstOne(condition.getPropertyName()))
.append("In");
method = criteria.getClass().getMethod(methodName.toString(), List.class);
String value = condition.getMatchValue()==null?"":condition.getMatchValue().toString();
String[] values = value.split(BaseConstant.Separate.COMMA);
method.invoke(criteria, Arrays.asList(values));
break;
case EQ:
methodName.append("and")
.append(StringUtil.toUpperCaseFirstOne(condition.getPropertyName()))
.append("EqualTo");
method = criteria.getClass().getMethod(methodName.toString(), propertyClass);
method.invoke(criteria, condition.getMatchValue());
break;
case NEQ:
methodName.append("and")
.append(StringUtil.toUpperCaseFirstOne(condition.getPropertyName()))
.append("NotEqualTo");
method = criteria.getClass().getMethod(methodName.toString(), propertyClass);
method.invoke(criteria, condition.getMatchValue());
break;
case LIKE:
methodName.append("and")
.append(StringUtil.toUpperCaseFirstOne(condition.getPropertyName()))
.append("Like");
method = criteria.getClass().getMethod(methodName.toString(), propertyClass);
matchValue.append("%").append(condition.getMatchValue()).append("%");
method.invoke(criteria, matchValue.toString());
break;
case LLIKE:
methodName.append("and")
.append(StringUtil.toUpperCaseFirstOne(condition.getPropertyName()))
.append("Like");
method = criteria.getClass().getMethod(methodName.toString(), propertyClass);
matchValue.append("%").append(condition.getMatchValue());
method.invoke(criteria, matchValue.toString());
break;
case RLIKE:
methodName.append("and")
.append(StringUtil.toUpperCaseFirstOne(condition.getPropertyName()))
.append("Like");
method = criteria.getClass().getMethod(methodName.toString(), propertyClass);
matchValue.append(condition.getMatchValue()).append("%");
method.invoke(criteria, matchValue.toString());
break;
case LE:
methodName.append("and")
.append(StringUtil.toUpperCaseFirstOne(condition.getPropertyName()))
.append("LessThanOrEqualTo");
method = criteria.getClass().getMethod(methodName.toString(), propertyClass);
method.invoke(criteria, condition.getMatchValue());
break;
case LT:
methodName.append("and")
.append(StringUtil.toUpperCaseFirstOne(condition.getPropertyName()))
.append("LessThan");
method = criteria.getClass().getMethod(methodName.toString(), propertyClass);
method.invoke(criteria, condition.getMatchValue());
break;
case GE:
methodName.append("and")
.append(StringUtil.toUpperCaseFirstOne(condition.getPropertyName()))
.append("GreaterThanOrEqualTo");
method = criteria.getClass().getMethod(methodName.toString(), propertyClass);
method.invoke(criteria, condition.getMatchValue());
break;
case GT:
methodName.append("and")
.append(StringUtil.toUpperCaseFirstOne(condition.getPropertyName()))
.append("GreaterThan");
method = criteria.getClass().getMethod(methodName.toString(), propertyClass);
method.invoke(criteria, condition.getMatchValue());
break;
default:
break;
}
}
}