EasyUI+SpringMVC+Hibernate 日期区间查询统计

最新推荐文章于 2022-10-25 13:18:35 发布

踩沙滩

最新推荐文章于 2022-10-25 13:18:35 发布

阅读量3k

点赞数 3

分类专栏： JavaWeb easyUI 文章标签： easyui datebox mysql sql springmvc

本文链接：https://blog.csdn.net/yang362046076/article/details/45077013

版权

JavaWeb 同时被 2 个专栏收录

15 篇文章 0 订阅

订阅专栏

easyUI

10 篇文章 0 订阅

订阅专栏

图书借阅管理系统，统计书籍借阅情况，可查询历史借阅排行和分类排行，默认返回前30名。查询时用sql

jsp，这里用到了easyui-datebox，用formatter和parser对日期格式进行转换，转换成YYYY-MM-DD 的格式，后台直接是String接受

<%@page import="com.yang.bishe.util.StringUtil"%>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
	<%@ include file="/common/base.jsp"%> 
	<script type="text/javascript">
		//分类排行
		function doSearch(){
			if($('#bookTypeId').combobox('getValue')==''){
				$.messager.alert({	
					title: 'Error',
					msg: '请先选择书类型'
				});
			}else{
				$('#dg').datagrid({
	                url: 'borrowInfo/statistics', 
	                queryParams:{bookTypeId: $('#bookTypeId').combobox('getValue'),startDate:$('#start').datebox('getValue'),endDate:$('#end').datebox('getValue')}
	        	});
			}
		}
		//总排行
		function doSearchOverall(){
			$('#dg').datagrid({
                url: 'borrowInfo/statistics', 
                queryParams:{overall:'overall',startDate:$('#start').datebox('getValue'),endDate:$('#end').datebox('getValue')}
        	});
		}
		//日期区间设置格式
		function mydateformatter(date){
				var y = date.getFullYear();
				var m = date.getMonth()+1;
				var d = date.getDate();
				return y+'-'+(m<10?('0'+m):m)+'-'+(d<10?('0'+d):d);
			}
		function mydateparser(s){
			if (!s) return new Date();
			var ss = (s.split('-'));
			var y = parseInt(ss[0],10);
			var m = parseInt(ss[1],10);
			var d = parseInt(ss[2],10);
			if (!isNaN(y) && !isNaN(m) && !isNaN(d)){
				return new Date(y,m-1,d);
			} else {
				return new Date();
			}
		}
		$.extend($.fn.validatebox.defaults.rules,{  
			 dateValid : {  
			        validator : function(value,param) { //参数value为当前文本框的值，也就是endDate  
			            startTime = $(param[0]).datetimebox('getValue');//获取起始时间的值  
			           // var start = $.fn.datebox.defaults.parser(startTime);  
			          //  var end = $.fn.datebox.defaults.parser(value);  
			            varify = value > startTime;
			            return varify;  
			        },  
			        message : '结束时间要大于开始时间!'  
			    }  
		});  
	</script>
</head>
<body class="easyui-layout" data-options="fit:true,border:false">
	<table id="dg" title="借阅排行" class="easyui-datagrid" style="width:100%;height:100%;"
			toolbar="#toolbar" pagination="false" 
			rownumbers="true" fitColumns="true" singleSelect="true">
		<thead>
			<tr>
				<th field="borrowNum" width="60">借阅次数</th>
				<th field="bookName" width="60">书名</th>
				<th field="author" width="55">作者</th>
				<th field="iSBN" width="45">ISNB</th>
				<th field="publisher" width="60">出版社</th>
				<th field="publishYear" width="60">出版年</th>
				<th field="CallNumber" width="50">索书号</th>
			</tr>
		</thead>
	</table>
	<div id="toolbar" style="height:70px;">
		<div>
			<table>
				<tr>
					<td>    类别排行:</td>
					<td>    书类别:</td>
					<td><select id="bookTypeId" class="easyui-combobox" data-options="editable:false,valueField:'bookTypeId',textField:'bookTypeName',url:'bookType/getBookType',panelHeight:'auto'" style="width:180px;"></select></td>
					<td><a href="javascript:void(0);" class="easyui-linkbutton" iconCls="icon-search"  οnclick="doSearch()">Search</a></td>
				</tr>
				<tr>
					<td>    总排行：</td>
					<td>    <a href="javascript:void(0);" class="easyui-linkbutton" iconCls="icon-search"  οnclick="doSearchOverall()">查询</a></td>
					<td>    查询日期区间(可选)</td>
				 	<td><input id="start" class="easyui-datebox" data-options="formatter:mydateformatter,parser:mydateparser,editable:false"></input>——</td>
				 	<td><input id="end" class="easyui-datebox" data-options="formatter:mydateformatter,parser:mydateparser,editable:false" validType="dateValid['#start']"></input></td>
					<td><a href="javascript:void(0);" class="easyui-linkbutton" data-options="iconCls:'ext-icon-zoom_out',plain:true" οnclick="$('#start').datebox('setValue','');$('#end').datebox('setValue','');$('#dg').datagrid('load',{});">重置过滤</a></td>
				</tr>
			</table>
		</div>
	</div>
</body>
</html>

controller;

/**
	 * 统计,分类统计和总排行
	 * @param request
	 * @param response
	 * @throws Exception
	 */
	@RequestMapping("/statistics")
	  public void statistics(HttpServletRequest request,
				HttpServletResponse response) throws Exception {
		String bookTypeId=request.getParameter("bookTypeId");
		String overall=request.getParameter("overall");
		String startDate=request.getParameter("startDate");//service再判断是否有没有选择了日期区间吧
		String endDate=request.getParameter("endDate");
		List<Book>books=null;
		if(overall!=null){
			books=borrowInfoService.getOverall(startDate,endDate);
		}
		if(bookTypeId!=null){
			books=borrowInfoService.getBookTypeOverall(bookTypeId,startDate,endDate);
		}
		writeJson(books,response);
	}

serviceImpl(接口就省略了):

这里要注意判断是否选择了日期区间

还要注意sql语句中日期区间

+" where BorrowDate"
+" between '"+startDate+"' and '"+endDate
+"' group by temp.BookId "

这里面日期是要用单引号

数据库是mysql，子查询重命名时，不能重命名属性，如下面的

as temp

不能弄成as temp（，，）这种重命名的方式，要获取temp的属性，只能是temp.BorrowInfoId （BorrowInfoId是子查询select返回的字段名）这样，即temp的字段是要和select返回的字段名字一样。

@Override
	public List<Book> getOverall(String startDate,String endDate) {
		List<Book> books=new ArrayList();
		String sql=null;
		//注意这的的判断条件，是空字符串而不是null
		if(startDate!=""&&endDate!=""){
			sql="select  temp.BookId as BookId,count(BookId) as borrowNum"
					+" from (select b.BorrowInfoId,b.BooksBarCode,sn.BookId,b.BorrowDate from t_borrowinfo as b "
					+" left join t_booksn as sn on b.BooksBarCode=sn.BooksBarCode) "
					+" as temp "
					+" where BorrowDate"
					+" between '"+startDate+"' and '"+endDate
					+"' group by temp.BookId "
					+" order by borrowNum desc "
					+" limit 30";
			}else{
					sql="select  temp.BookId as BookId,count(BookId) as borrowNum"
									+" from (select b.BorrowInfoId, b.BooksBarCode, sn.BookId from t_borrowinfo as b "
									+" left join t_booksn as sn on b.BooksBarCode=sn.BooksBarCode) "
									+" as temp "
									+" group by temp.BookId "
									+" order by borrowNum desc "
									+" limit 30";
			}
		 List<Map> map=borrowInfoDao.findBySql(sql);
		 for(int i=0;i<map.size();i++){
			 Book book=new Book();
			 book=bookDao.getById(Book.class,(Integer)map.get(i).get("BookId"));
			 book.setBorrowNum(Integer.parseInt(map.get(i).get("borrowNum").toString()));
			 books.add(book);
		 }
		return books;
	}



	@Override
	public List<Book> getBookTypeOverall(String bookTypeId,String startDate,String endDate) {
		List<Book> books=new ArrayList();
		String sql=null;
		//注意这的的判断条件，是空字符串而不是null
		if(startDate!=""&&endDate!=""){
			sql="select temp.BookId,count(temp.BookId) as borrowNum"
					+" from (select b.BorrowDate,b.BooksBarCode,sn.BookId from t_borrowinfo as b" 
					+" left join t_booksn as sn on b.BooksBarCode=sn.BooksBarCode)"
					+" as temp"
					+" left join t_book as book on book.BookId=temp.BookId"
					+" where book.BookTypeId="+bookTypeId 
					+" and BorrowDate between '"+startDate+"' and '"+endDate
					+"' group by book.BookId"
					+" order by borrowNum desc"
					+" limit 30";
		}else{
			sql="select temp.BookId,count(temp.BookId) as borrowNum"
					+" from (select b.BooksBarCode,sn.BookId from t_borrowinfo as b" 
					+" left join t_booksn as sn on b.BooksBarCode=sn.BooksBarCode)"
					+" as temp"
					+" left join t_book as book on book.BookId=temp.BookId"
					+" where book.BookTypeId="+bookTypeId
					+" group by book.BookId"
					+" order by borrowNum desc"
					+" limit 30";
		}
				List<Map> map=borrowInfoDao.findBySql(sql);
				for(int i=0;i<map.size();i++){
					 Book book=new Book();
					 book=bookDao.getById(Book.class,(Integer)map.get(i).get("BookId"));
					 book.setBorrowNum(Integer.parseInt(map.get(i).get("borrowNum").toString()));
					 books.add(book);
				}
				return books;
	}

Dao：因为用了Hibernate，但是用HQL的话有点乏力。。。所以用sql语句查询，利用Hibernate的借口creatSQLQuery，返回的是List<Map>,接受这里数据看上面serviceImpl代码

	@SuppressWarnings("unchecked")
	@Override
	public List<Map> findBySql(String sql) {
		SQLQuery q = getCurrentSession().createSQLQuery(sql);
		return q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}

效果图: