按条件模糊查询及分页查询

最新推荐文章于 2022-11-16 22:51:14 发布

幸福是什么感觉呢

最新推荐文章于 2022-11-16 22:51:14 发布

阅读量1.7k

点赞数

分类专栏：学习轨迹 javaEE java 文章标签：分页查询

本文链接：https://blog.csdn.net/zd305826520/article/details/51003462

版权

学习轨迹同时被 3 个专栏收录

34 篇文章 0 订阅

订阅专栏

java

33 篇文章 0 订阅

订阅专栏

javaEE

32 篇文章 0 订阅

订阅专栏

1、查询助手

 1 package cn.buaa.core.util;
 2 
 3 import java.util.ArrayList;
 4 import java.util.List;
 5 
 6 
 7 public class QueryHelper {
 8     
 9     //from子句
10     private String fromClause = "";
11     //where子句
12     private String whereClause = "";
13     //orderBy子句
14     private String orderByClause = "";
15     
16     private List<Object> parameters;
17     
18     //排序常量
19     public static String ORDER_BY_DESC = "DESC"; //降序
20     public static String ORDER_BY_ASC = "ASC"; //升序
21     /**
22      * 构造from子句(有别名)
23      * @param clazz 实体类
24      * @param alias 实体类对应的别名
25      */
26     public QueryHelper(Class clazz,String alias){
27         fromClause = "From " + clazz.getSimpleName() + " " +alias ;
28         System.out.println(fromClause);
29     }
30     /**
31      * 构造from子句（无别名）
32      * @param clazz 实体类
33      */
34     public QueryHelper(Class clazz){
35         fromClause = "From " + clazz.getSimpleName() ;
36     }
37     
38     /**
39      * 构造where子句
40      * @param condition 查询条件语句：例如：i.title like ?
41      * @param params 查询条件语句中？对应的查询条件的值： 例如：%标题%
42      */
43     public void  addCondition(String condition , Object... params ){
44         if(whereClause.length()>1){//非第一个查询条件
45             whereClause += " AND " + condition;
46         }else{//第一个查询条件
47             whereClause += " WHERE " + condition;
48         }
49         
50         //设置查询条件值到查询条件集合中
51         if(parameters == null){
52             parameters = new ArrayList<Object>();
53         }
54         if(params != null){
55             for(Object param : params){
56                 parameters.add(param);
57             }
58         }
59         
60     }
61     
62     /**
63      * 构造order by子句
64      * @param property 排序属性：如：i.createTime
65      * @param order 排序顺序，如：DESC 或 ASC
66      */
67     public void addOrderByProperty(String property,String order){
68         if(orderByClause.length()>1){//非第一个排序属性
69             orderByClause += " ," + property + " " + order;
70         }else{//第一个排序属性
71             orderByClause += " ORDER BY " + property + " " + order;
72         }
73     }
74     //查询hql语句
75     public String getQueryListHql(){
76         
77         return  fromClause + whereClause + orderByClause ;
78     }
79     
80     //查询统计数的hql语句
81     public String getQueryCountHql(){
82         
83         return "SELECT COUNT( * )  " +  fromClause + whereClause;
84     }
85     
86     //查询hql语句中？对应的查询条件值集合
87     public List<Object> getParameters(){
88         
89         return parameters;
90     }
91 }

2、dao逻辑处理代码

@Override
    public PageResult getPageResult(QueryHelper queryHelper, int pageNo, int pageSize) {
        Query query = getSession().createQuery(queryHelper.getQueryListHql());
        List<Object> parameters = queryHelper.getParameters();
        if(parameters != null){
            for(int i= 0;i<parameters.size();i++){
                query.setParameter(i, parameters.get(i));
            }
        }
        if(pageNo<1) pageNo =1;
        query.setFirstResult((pageNo-1)*pageSize);
        query.setMaxResults(pageSize);
        List items =  query.list();
        
        //获取总记录数
        Query queryCount = getSession().createQuery(queryHelper.getQueryCountHql());
        if(parameters != null){
            for(int i= 0;i<parameters.size();i++){
                queryCount.setParameter(i, parameters.get(i));
            }
        }
        long totalCount = (long) queryCount.uniqueResult();
        return new PageResult(totalCount, pageNo, pageSize, items);
    }

3、action调用处理代码

 1         protected PageResult pageResult;
 2     private int pageNo;
 3     private int pageSize;
 4         。。。。
 5 
 6 // 列表页面
 7     public String listUI() throws Exception {
 8         // 加载分类集合
 9         ActionContext.getContext().getContextMap().put("infoTypeMap", Info.INFO_TYPE_MAP);
10         QueryHelper queryHelper = new QueryHelper(Info.class,"i");
11         try {
12             if(info != null){
13                 if(StringUtils.isNotBlank(info.getTitle())){
14                     //搜索框乱码问题解决
15                     info.setTitle(URLDecoder.decode(info.getTitle(),"utf-8"));
16                     queryHelper.addCondition("i.title like ? ", "%" + info.getTitle() + "%");
17                 }
18                 queryHelper.addCondition("i.state = ?", "1");
19             }
20             //根据创建时间降序排序
21             queryHelper.addOrderByProperty(" i.createTime ", QueryHelper.ORDER_BY_DESC);
22             //infoList = infoService.findObjects(queryHelper);
23             pageResult = infoService.getPageResult(queryHelper,getPageNo(),getPageSize());
24         } catch (Exception e) {
25             throw new ActionException("action出现异常：" + e.getMessage());
26         }
27         return "listUI";
28     }

4、struts配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC
    "-//Apache Software Foundation//DTD Struts Configuration 2.3//EN"
    "http://struts.apache.org/dtds/struts-2.3.dtd">

<struts>
    <package name="info-action" namespace="/nsfw" extends="base-default">
        <action name="info_*" class="cn.buaa.nsfw.info.action.InfoAction"
            method="{1}">
            <result name="{1}">/WEB-INF/jsp/nsfw/info/{1}.jsp</result>
            <result name="list" type="redirectAction">
                <param name="actionName">info_listUI</param>
                <!-- 搜索框搜索值回显 -->
                <param name="info.title">${strTitle}</param>
                <param name="pageNo">${pageNo}</param>
                <!-- 解决搜索框框乱码 -->
                <param name="encode">true</param>
            </result>
        </action>
    </package>
</struts>

5、jsp显示页面

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <%@include file="/common/header.jsp"%>
    <title>信息发布管理</title>
    <script type="text/javascript">
  //全选、全反选
    function doSelectAll() {
        // jquery 1.6 前
        //$("input[name=selectedRow]").attr("checked", $("#selAll").is(":checked"));
        //prop jquery 1.6+建议使用
        $("input[name=selectedRow]").prop("checked",
                $("#selAll").is(":checked"));
    }
    
    //添加
      function doAdd(){
          document.forms[0].action="${basePath}nsfw/info_addUI.action";
          document.forms[0].submit();
      }
      //编辑
      function doEdit(id){
          document.forms[0].action="${basePath}nsfw/info_editUI.action?info.infoId=" + id;
          document.forms[0].submit();
      }
    //删除
      function doDelete(id){
          document.forms[0].action="${basePath}nsfw/info_delete.action?info.infoId=" + id;
          document.forms[0].submit();
      }
  //多选删除
      function doDeleteAll(){
          document.forms[0].action="${basePath}nsfw/info_deleteSelected.action";
          document.forms[0].submit();
      }
  //异步发布信息，信息的id及将要改成的信息状态
  function doPublic(infoId,state){
      //1、更新信息状态
      $.ajax({
          url:"${basePath}nsfw/info_publicInfo.action",
            data:{"info.infoId":infoId,"info.state":state},
            type:"post",
            success:function(msg){
                //2、更新状态栏，操作栏的显示值
                if("更新状态成功" == msg){
                    if(state == 1){//说明信息状态已被改成发布，状态栏显示发布，操作栏显示停用
                        $("#show_"+infoId).html("发布");
                        $("#oper_"+infoId).html('<a href="javascript:doPublic(\''+infoId+'\',0)">停用</a>');
                    }else{
                        $("#show_"+infoId).html("停用");
                        $("#oper_"+infoId).html('<a href="javascript:doPublic(\''+infoId+'\',1)">发布</a>');
                    }
                }else{
                  alert("更新信息状态失败！");
                }
            },
            error:function(){
                alert("更新信息状态失败！");
            }
      });
  }
  
  var list_url = "${basePath}nsfw/info_listUI.action";
  function doSearch(){
          //重置页号
          $("#pageNo").val(1);
          document.forms[0].action= list_url ;
        document.forms[0].submit();
  }
    </script>
</head>
<body class="rightBody">
<form name="form1" action="" method="post">
    <div class="p_d_1">
        <div class="p_d_1_1">
            <div class="content_info">
                <div class="c_crumbs"><div><b></b><strong>信息发布管理</strong></div> </div>
                <div class="search_art">
                    <li>
                        信息标题：<s:textfield name="info.title" cssClass="s_text" id="infoTitle"  cssStyle="width:160px;"/>
                    </li>
                    <li><input type="button" class="s_button" value="搜 索" onclick="doSearch()"/></li>
                    <li style="float:right;">
                        <input type="button" value="新增" class="s_button" onclick="doAdd()"/>&nbsp;
                        <input type="button" value="删除" class="s_button" onclick="doDeleteAll()"/>&nbsp;
                    </li>
                </div>

                <div class="t_list" style="margin:0px; border:0px none;">
                    <table width="100%" border="0">
                        <tr class="t_tit">
                            <td width="30" align="center"><input type="checkbox" id="selAll" onclick="doSelectAll()" /></td>
                            <td align="center">信息标题</td>
                            <td width="120" align="center">信息分类</td>
                            <td width="120" align="center">创建人</td>
                            <td width="140" align="center">创建时间</td>
                            <td width="80" align="center">状态</td>
                            <td width="120" align="center">操作</td>
                        </tr>
                        <s:iterator value="pageResult.items" status="st">
                            <tr <s:if test="#st.odd"> bgcolor="f8f8f8" </s:if> >
                                <td align="center"><input type="checkbox" name="selectedRow" value="<s:property value='infoId'/>"/></td>
                                <td align="center"><s:property value="title"/></td>
                                <td align="center">
                                    <s:property value="#infoTypeMap[type]"/>    
                                </td>
                                <td align="center"><s:property value="creator"/></td>
                                <td align="center"><s:date name="createTime" format="yyyy-MM-dd HH:mm"/></td>
                                <td id="show_<s:property value='infoId'/>" align="center"><s:property value="state==1?'发布':'停用'"/></td>
                                <td align="center">
                                    <span id="oper_<s:property value='infoId'/>">
                                    <s:if test="state==1">
                                        <a href="javascript:doPublic('<s:property value='infoId'/>',0)">停用</a>
                                    </s:if>
                                    <s:else>
                                        <a href="javascript:doPublic('<s:property value='infoId'/>',1)">发布</a>
                                    </s:else>
                                        
                                    
                                    </span>
                                    <a href="javascript:doEdit('<s:property value='infoId'/>')">编辑</a>
                                    <a href="javascript:doDelete('<s:property value='infoId'/>')">删除</a>
                                </td>
                            </tr>
                        </s:iterator>
                    </table>
                </div>
            </div>
        <jsp:include page="/common/pageNavigator.jsp"/>
    </div>
</form>

</body>
</html>

6、分页代码jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="s"  uri="/struts-tags"%>
<div class="c_pate" style="margin-top: 5px;">
        <s:if test="pageResult.totalCount > 0">
        <table width="100%" class="pageDown" border="0" cellspacing="0"
            cellpadding="0">
            <tr>
                <td align="right">
                     总共<s:property value="pageResult.totalCount"/>条记录，当前第<s:property value="pageResult.pageNo"/>页，
                     共 <s:property value="pageResult.totalPageCount"/> 页 
                    <s:if test="pageResult.pageNo > 1">
                                 &nbsp;&nbsp;  <a href="javascript:doGoPage(<s:property value="pageResult.pageNo-1"/>)">上一页</a>
                    </s:if>                 
                    <s:if test="pageResult.pageNo < pageResult.totalPageCount">
                                 &nbsp;&nbsp;<a href="javascript:doGoPage(<s:property value="pageResult.pageNo+1"/>)">下一页</a>
                       </s:if>              
                    到&nbsp;<input id="pageNo" name="pageNo" type="text" style="width: 30px;" onkeypress="if(event.keyCode == 13){doGoPage(this.value);}" min="1"
                    max="" value="<s:property value="pageResult.pageNo"/>" /> &nbsp;&nbsp;
                </td>
            </tr>
        </table>    
        </s:if><s:else>
            暂无数据！
        </s:else>
        </div>
        <script type="text/javascript">
            //翻页
            function doGoPage(pageNo){
                document.getElementById("pageNo").value = pageNo;
                document.forms[0].action =list_url;
                document.forms[0].submit();
            }
        </script>
        </div>