Easyui+springmvc+hibernate date interval Query statistics

Book Lending management system, Statistics book lending situation, can query the history of borrowing ranking and classification ranking, the default return to the top 30. Use SQL when querying

JSP, here to use the Easyui-datebox, with formatter and parser to the date format conversion, converted to YYYY-MM-DD format, the background is directly string accept

<%@ page language= "java" contenttype= "text/html; Charset=utf-8 "pageencoding=" UTF-8 "%><%@ page import=" com.yang.bishe.entity.User "%><%@ page import=" Com.yang.bishe.entity.Book "%><! DOCTYPE html>
Controller
/** * Statistics, categorical statistics and total ranking * @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 to determine if there is a date range selected. 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 (the interface is omitted):
Here to pay attention to decide whether to choose the date range, my date range of judgment is not perfect, should be judged enddate>startdate, this judgment can be placed in the foreground judgment.
Also pay attention to the date range in the SQL statement
+ "where borrowdate" + "between" "+startdate+" ' and ' "+enddate+" ' GROUP by temp. BookId "
the date is in single quotation marks .
The database is MySQL, and when a subquery is renamed, the property cannot be renamed, as in the following
As Temp
You cannot make the as temp (,) This way of renaming, to get the temp attribute, only temp. Borrowinfoid (Borrowinfoid is the field name returned by the subquery Select) so that the temp field is the same as the field name returned by SELECT.

@Overridepublic list<book> getoverall (String startdate,string endDate) {list<book> books=new ArrayList (); String sql=null;//Note the criteria for this, which is an empty string instead of Nullif (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 joins 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 joins 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;} @Overridepublic list<book> getbooktypeoverall (String booktypeid,string startdate,string endDate) {List<book > Books=new ArrayList (); String sql=null;//Note the criteria for this, which is an empty string instead of Nullif (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 joins T_BOOKSN as SN on B.BOOKSBARCODE=SN. Booksbarcode) "+" as temp "+" left join T_book as book in 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 joins T_BOOKSN as SN on B.BOOKSBARCODE=SN. Booksbarcode) "+" as temp "+" left join T_book asBook on the 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: Because of using Hibernate, but with hql words a little weak ... So use the SQL statement query, use hibernate pretext Creatsqlquery, return is list<map>, accept the data here to see the above Serviceimpl code
@SuppressWarnings ("unchecked") @Overridepublic list<map> findbysql (String sql) {sqlquery q = getcurrentsession () . createsqlquery (SQL); return Q.setresulttransformer (Transformers.alias_to_entity_map). List ();}

:
















 Easyui+springmvc+hibernate date interval query statistics