Controller
/** * 多数据表联合查询、搜索、排序、分页示例 */ @RequestMapping("/list4") public String list4(HttpServletRequest request,Model model){ Sql sql = new Sql(request); //因多表查询,这里设定上form搜索的是哪个数据表的字段,这里是log表 sql.setSearchTable("log"); //增加添加搜索字段。这里的搜索字段跟log表的字段名对应 sql.setSearchColumn(new String[]{"type=","value","userid="}); //查询log数据表的记录总条数 int count = globalService.count("log", sql.getWhere()); //每页显示15条 Page page = new Page(count, 15, request); //创建查询语句,只有SELECT、FROM,原生sql查询。其他的where、limit等会自动拼接 sql.setSelectFromAndPage("SELECT log.*,(SELECT nickname FROM user WHERE user.id = log.userid) AS nickname FROM log", page); //以上sql.setSelectFromAndPage也可以用以下两句来代替,查询结果一样。 // sql.setSelectFromAndPage("SELECT log.*, user.nickname FROM log,user", page); // sql.appendWhere("user.id = log.userid"); //再原本WHERE的末尾附加查询条件。若原本WHERE条件不存在,则现在创建新的WHERE条件 //当用户没有选择排序方式时,系统默认排序。 sql.setDefaultOrderBy("log.id DESC"); //因联合查询,结果集是没有实体类与其对应,故而用List<Map>接收 List<Map<String, Object>> list = globalService.findMapBySql(sql); //将数据记录传到页面以供显示 model.addAttribute("list", list); //将分页信息传到页面以供显示 model.addAttribute("page", page); return "demo/list/list4"; }
View
增加demo/list/list4.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <%@ taglib uri="http://www.xnx3.com/java_xnx3/xnx3_tld" prefix="x" %> <!DOCTYPE html> <html lang="en"> <head> <jsp:include page="../../publicPage/adminCommon/head.jsp"> <jsp:param name="title" value="日志列表DEMO"/> </jsp:include> </head> <body> <div> <!-- 表单搜索 --> <form method="get" style="margin-top:20px;"> <input type="hidden" name="orderBy" value="<%=request.getParameter("orderBy") %>" /> <span style="float:left;line-height:34px;margin-left:10px;">用户ID:</span> <div class="input-group m-bot15 " style="width: 15%;float: left;"> <input type="text" name="userid" class="form-control" value="<%=request.getParameter("userid")==null? "":request.getParameter("userid") %>"> </div> <span style="float:left;line-height:34px;margin-left:10px;">动作类型:</span> <div class="input-group m-bot15 " style="width: 15%;float: left;"> <input type="text" name="type" class="form-control" value="<%=request.getParameter("type")==null? "":request.getParameter("type") %>"> </div> <span style="float:left;line-height:34px;margin-left:10px;">描述:</span> <div class="input-group m-bot15 " style="width: 15%;float: left;"> <input type="text" name="value" class="form-control" value="<%=request.getParameter("value")==null? "":request.getParameter("value") %>"> </div> <div class="input-group m-bot15 " style="width: 100px; float: left;"> <span class="input-group-btn"> <input class="btn btn-success" type="submit" value="搜索"> <i class="fa fa-search"></i> </span> </div> </form> <!-- 用户自定义排序规则 --> <div style="float:right;"> <script type="text/javascript"> orderBy('id_DESC=编号由大到小,id_ASC=编号由小到大,userid_ASC=用户ID由小到大'); </script> </div> </div> <!-- 列表数据 --> <table class="table table-bordered table-striped table-condensed"> <thead> <tr> <th>编号</th> <th>用户昵称</th> <th class="numeric">动作编号</th> <th class="numeric">记录内容</th> <th class="numeric">操作时间</th> </tr> </thead> <tbody> <c:forEach items="${list}" var="log"> <tr> <td>${log['id'] }</td> <td>${log['nickname'] } (ID:${log['userid'] })</td> <td>${log['type'] }</td> <td>${log['value'] }</td> <td><x:time linuxTime="${log['addtime'] }"></x:time></td> </tr> </c:forEach> </tbody> </table> <!-- 通用分页跳转 --> <jsp:include page="../../publicPage/adminCommon/page.jsp"> <jsp:param name="page" value="${page }"/> </jsp:include> <jsp:include page="../../publicPage/adminCommon/footImport.jsp"></jsp:include> </body> </html>