当我在用javaee编写条件分页查询的时候,平时开发中老是写差不多的代码,比较繁琐,故自己总结了一套解决方案。
这是一套专门针对Oracle数据库和SpringMvc和Mybatis框架中条件、分页查询的方案。本文重点不是讲分页,而是将如何使用我自己写的工具类快速多种条件查询。
优点:
1、使用超级简单,该方案帮你解决了mybatis中的where 条件的sql语句,因此你不需要写 where语句。
2、后期拓展非常方便,只需要在jsp页面中编写html标签,添加条件即可。
3、比较强大,几乎所有Oracle的数据类型都支持(二进制除外),还可进行排序操作。
使用方法:
步骤一、首先编写jsp或html页面,按照规定格式写表单的name属性,
格式:name = “前缀 _ 查询类型关键字 _ 要查询数据库中的字段名” , 如下代码
联系人
编号范围
最小值:
最大值:
如上:name="search_LIKE_contact" 的含义 :
search: 前缀,可任意合法命名的字符串,每个条件的前缀要一致。
下划线 "_": 分隔符
LIKE :表示要查询数据库中某列包含某个值的记录。
contact :表示要查询的字段是contact ,
需要注意的是,如果表中字段名中有下划线,要字 段名转为驼峰命名法,比如:表中字段名:create_date 转为页面中name属性中的:createDate。
如果针对日期进行查询 :
比如 年份如下 :
EQ :表示等于
@:是当要查询日期时,在查询类型关键字后加的分隔符,@后跟上日期的格式
yyyy :表示 年, mm :表示 月份, dd :表示天数
HH24 :表示24小时制的小时, mi:表示分钟 ,ss:表示秒数
这个日期格式完全是按照Oracle中的日期格式,这里是通用格式,详细的格式查询Oracle的日期格式。
如果还需要进行排序操作如下:
按照ID排序
降序
升序
按照创建时间
降序
升序
ORDER :是排序的关键字 , desc :表示 降序, asc :表示升序
注意:该方法支持多种排序条件,不过,排序的条件是没有主次的,即没判断第一排条件是什么,第二个条件是什么,故不推荐用这种方案写多种排序条件的。
步骤二:在springMMVC的Handler中调用
@RequestMapping("list")
public String list(@RequestParam(value="pageNow",required=false,defaultValue="1") int pageNow, @RequestParam(value="pageSize",required=false,defaultValue="10")int pageSize,
HttpServletRequest request){
Map parameterMap = WebUtils.getParametersStartingWith(request, "search_");
request.setAttribute("parameterMap", parameterMap);
parameterMap.put("NOTEQ_status",1);
//获取当前用户的id
User user = (User)request.getSession().getAttribute("user");
parameterMap.put("EQ_designeeId", user.getId());
String string = MapUtil.mapToString(parameterMap,"search_");
System.out.println(string);
request.setAttribute("condition", string);
Page page = salesService.getPageCondition(parameterMap, pageNow, pageSize);
System.out.println("页面数据"+page);
request.setAttribute("page", page);
return "plan/plan_list";
}
上面代码中使用到了 这个工具类
import org.springframework.web.util.WebUtils;
获取查询条件的Map集合parameterMap,然后调用service层中的salesService.getPageCondition(parameterMap, pageNow, pageSize)方法,传入表单中的条件查询的集合、当前页码pageNow和每页显示的数据pageSize ; 然后 在service层中调用我封装的条件查询类ParamsMapUtil,传入查询条件的parameterMap集合,返回sql语句中的where语句。
String where = ParamsMapUtil.paramsToWhere(parameterMap)
具体 代码如下:
@Transactional(readOnly=true)
public Page getPageCondition(Map map,int pageNow, int pageSize){
Page page = new Page();
Map newMap = new HashMap();
//使用我自己封装的方法 返回where语句
String where = ParamsMapUtil.paramsToWhere(map);
int pageStart = (pageNow -1) * pageSize+1;
int pageEnd = pageStart + pageSize -1;
newMap.put("pageStart", pageStart);
newMap.put("pageEnd", pageEnd);
newMap.put("where", where);
List list = salesMapper.pageListOrderByIdAndWhere(newMap);
page.setpageNow(pageNow);
page.setPageSize(pageSize);
page.setTotalRecord(salesMapper.totalCountAndCondition(newMap));
page.setList(list);
return page;
}
因为在上面代码中同时处理了分页,所以还将分页用的数据放入新建的map集合,并且加入ParamsMapUtil类返回的where语句。将这个map集合传入XxxxMapper.java的方法中,我这里是
pageListOrderByIdAndWhere(newMap)。
即步骤三:
/*
* 分页的查询
* 使用自己封装的ParamsMapUtil这个类
* 因为是将分页的pageStart和pageEnd 和 查询条件 where 封装到一个map中
* 故要加@Param("map") 这个注解,然后在xml文件中通过${map.where} 这种方式取值即可。
*/
public List pageListOrderByIdAndWhere(@Param("map")Map map);
sql语句如下:
select id,source,CUST_NAME,RATE,title,contact,contact_Tel,description,CREATE_DATE ,status
from(select tmp.*,rownum rn from
(select * from sales_chances ${map.where} ) tmp
where rownum <= ${map.pageEnd} ) t2 where t2.rn >= ${map.pageStart}
只需要用${map.where} 这样类似的语句写在表名的后面,即可取出生成的where语句, ${map.pageEnd}是取出分页用的最大的rowNum,${map.pageStart}是取出分页用的最小的rowNum.
这样最终就完成了整个操作,因为该工具类主要是将页面中查询条件组成的map集合转换为对应的SQL中的where语句,所以,以后需要添加条件查询或者修改条件查询,只要修改JSP页面即可,其他的代码不需要动,尤其是sql语句,如果是之前的方法,还需要修改mysbatis中的sql语句,相比之下,该工具类显得十分方便。
**如下是该工具类的全部代码(类名被我改为ParamsToSqlUtil)**:
public class ParamsToSqlUtil {
/*
* 以下定义了JSP页面中的查询关键字的常量
* 比如:search_LIKE_username 中的LIKE
*/
//包含
public static final String LIKE = "LIKE";
//等于
public static final String EQ = "EQ";
//大于
public static final String GT = "GT";
//大于等于
public static final String GE = "GE";
//小于
public static final String LT = "LT";
//小于等于
public static final String LE = "LE";
//不等于
public static final String NOTEQ = "NOTEQ";
//排序
public static final String ORDER = "ORDER";
/*以下是sql语句中的关键字*/
public static final String AND = " AND ";
//Sql中 WHERE 关键字
public static final String WHERE = " WHERE ";
//名字中的分隔符
public static final String SEP = "_";
//定义一个Map集合存放上述常量和SQL语句中的对应关系
public static Map conditionMap = new HashMap<>();
static{
conditionMap.put(LIKE, " like ");
conditionMap.put(EQ, " = ");
conditionMap.put(GT, " > ");
conditionMap.put(GE, " >= ");
conditionMap.put(LT, " < ");
conditionMap.put(LE, " <= ");
conditionMap.put(NOTEQ, " != ");
}
/**
* 传入页面发送来的请求查询的参数集合,返回sql语句中的where语句。
*/
public static String paramsToWhere(Map map){
StringBuilder sb = new StringBuilder();
//定义排序
String orderBy = "";
sb.append(WHERE);
if(map == null){
return "";
}
//遍历表单查询条件集合
for (Map.Entry entry : map.entrySet()) {
//ORDER_id =>desc
String key = entry.getKey(); //EQ@yyyy-MM-dd_createDate
Object value = entry.getValue();//2017-11-11
//用来存放@后的字段类型的格式(日期格式yyyy-MM-dd)
String typePattern = null;
if(value !=null && value!=""){
//因为接受的都是字符串,这里 将字符串类型的值去除两边空格
if(value instanceof String){
value = ((String)value).trim();
}
//根据下划线分割成 关键字和字段名
String fieldName = key.substring(key.indexOf(SEP)+1); //字段名
// EQ@yyyy-MM-dd 或 ORDER
String keyWord = key.substring(0,key.indexOf(SEP));// 关键字
//对字段名统一处理,将驼峰命名格式转为下划线连接
fieldName = parseHump2Underline(fieldName);
//判断是不是排序关键字,组装排序语句
if(keyWord.contains(ORDER)){
//判断是不是第一个排序条件
if(!orderBy.contains(" order by ")){
orderBy = " order by "+fieldName+" "+value;
}else{
orderBy += " , "+fieldName + " " + value;
}
continue;
}
int index = keyWord.indexOf("@"); //算出@的索引
if(index !=-1){ //当有@时,特殊处理
typePattern= keyWord.substring(index+1);
keyWord = keyWord.substring(0, index);//EQ
}
//遍历查询类型
for (Map.Entry conEntry : conditionMap.entrySet()) {
String key2 = conEntry.getKey(); //EQ
String value2 = conEntry.getValue(); // =
//判断是什么条件
if(key2.equalsIgnoreCase(keyWord)){
String newVal; //新的值
//看条件是比较大小还是like关键字
if(key2.equals(LIKE)){
newVal = value2+"'%"+value+"%'";
}else{
newVal = value2 +value; // = '2017-11-11'
}
//to_char(create_date,'yyyy-mm-dd')
if(typePattern != null){ //说明是特殊类型(日期)
fieldName = "to_char("+fieldName+",'"+typePattern+"')";
newVal = value2 +"'"+value+"'";
}
sb.append(fieldName).append(newVal).append(AND);
break;
}
}
}
}
String st = sb.toString();
//如果sb中没有添加任何条件语句,则返回空
if(WHERE.equals(st+orderBy) ){
return "";
}
int andIndex = st.lastIndexOf(AND);
if(andIndex !=-1){
st = st.substring(0, andIndex)+orderBy;
}else{ //说明没有and条件,还得去掉where
st = (st+orderBy).replace(WHERE, "");
}
return st;
}
/**该方法是用来处理条件查询中的jsp页面上一页下一页跳转里连接用的
* 将map集合中的数据转为链接中的参数形式
* pre:给集合的键加上前缀
* 返回的是查询的条件字符串,拼接在链接后面
*/
public static String mapToString(Map map,String pre){
if(map ==null){
return null;
}
StringBuilder sb = new StringBuilder();
for (Map.Entry entry : map.entrySet()) {
String key = entry.getKey();
Object value = entry.getValue();
if(value !=null && value!=""){
sb.append(pre+key+"="+value+"&");
}
}
String string = sb.toString();
int index = string.lastIndexOf("&");
if(string.lastIndexOf("&")!= -1){
string = string.substring(0,index);
}
return string;
}
/**
* 将 驼峰命名格式 字符串转为 下划线方式
* 一个单词组成的返回原样,多个单词返回下划线连接的方式
*/
public static String parseHump2Underline(String oldStr){
Pattern pattern = Pattern.compile("[A-Z]");
for(int i =0;i
String s = oldStr.charAt(i)+"";
if(pattern.matcher(s).matches() ){
oldStr = oldStr.replace(s, SEP+s.toLowerCase());
}
}
return oldStr;
}
}
“`