Spring JdbcTemplate + Easyui Physical Paging

The article says, the new project, uses is SPRINGMVC + JdbcTemplate, the foreground is the Easyui, discovers the colleague formerly encapsulates the page is the logical paging, therefore, has started to encapsulate the physical paging.

This is the core paging entity:

Import java.io.Serializable;
Import java.util.List;

Import Java.util.Map;
Import Org.apache.commons.lang.StringUtils;


Import Org.springframework.jdbc.core.JdbcTemplate; /** * @author LYH * @version 2013-10-10 * @see pagination * @since/public class pagination implements-Seri

    alizable {/** * serial number <br>/private static final long serialversionuid = -2554565760258955645l;

    /** * The number of records displayed per page/private int numperpage;

    /** * The total number of records (name must be the sum corresponding to Easyui page) * * Private int.

    /** * Total pages */private int totalpages;

    /** * Current page number/private int currentpage;

    /** * Record number of start line * * private int startIndex;

    /** * Record end line number/private int lastindex;


    /** * Result set store List (name must be rows corresponding to Easyui pagination)/private list<map<string,object>> rows; /** * constructor * @param SQL SQL statement * @param currentpage Current page number * @param numperpage per page displayNumber of records * @param jdbctemplate jdbctemplate Instance */public pagination (String sql, int currentpage, int numperpage, J Dbctemplate jdbctemplate) {if (JdbcTemplate = null) {throw new illegalargumentexception ("JdbcTemplate is NULL, pls initialize ...
        "); else if (Stringutils.isblank (SQL)) {throw new IllegalArgumentException ("SQL is blank, pls Initialize ...
        ");

        //Set the number of records to display per page setnumperpage (numperpage);

        Sets the current number of pages setcurrentpage (currentpage);
        Calculates the total number of records SQL StringBuffer totalsql = new StringBuffer ("SELECT count (1) from (");
        Totalsql.append (SQL);

        Totalsql.append (")");

        Total record number settotal (Jdbctemplate.queryforint (totalsql.tostring ()));

        Calculate the total number of pages settotalpages ();

        Calculates the starting row number Setstartindex ();

        Calculates the end row number Setlastindex (); Assemble Oracle's paging statements (other DB modifies the paging keyword here) STringbuffer paginationsql = new StringBuffer ("SELECT * from");
        Paginationsql.append ("Select Row_limit.*,rownum rownum_ from");
        Paginationsql.append (SQL);
        Paginationsql.append (") Row_limit where rownum <=" + lastindex);

        Paginationsql.append (") where rownum_ >" + startIndex); Mount result set (key to lowercase) setrows (convertmapkey.listkeytolower (jdbctemplate.queryforlist Paginationsql.tostrin
    g ())); /** * Calculates the total number of pages based on Total records and number of records per page * * @see/private void Settotalpages () {if (t
        Otal% Numperpage = = 0) {this.totalpages = Total/numperpage;
        else {this.totalpages = (total/numperpage) + 1;
        }/** * Calculates the number of record start lines based on the current page and per-page display * * @see/private void Setstartindex () {
    This.startindex = (currentPage-1) * numperpage; /** * Calculate the number of record end lines * * @see
     * * private void Setlastindex () {if (Total < numperpage) {This.lastindex = t
        Otal; else if (total% numperpage = 0) | |
        (total% numperpage!= 0 && currentpage < totalpages))
        {This.lastindex = currentpage * numperpage; else if (total% numperpage!= 0 && currentpage = = totalpages) {This.lastindex = to
        Tal
    }//setter and Getter public int getcurrentpage () {return currentpage;
    The public void setcurrentpage (int currentpage) {this.currentpage = CurrentPage;
    public int getnumperpage () {return numperpage;
    The public void setnumperpage (int numperpage) {this.numperpage = Numperpage;
    Public list<map<string,object>> GetRows () {return rows; } public void Setrows (LIST&LT;MAP&LT;STRING,OBJECT&GT;&GT
    rows) {this.rows = rows;
    public int gettotalpages () {return totalpages;
    public int gettotal () {return total;
    public void settotal (int total) {this.total = total;
    public int Getstartindex () {return startIndex;
    public int Getlastindex () {return lastindex;
 }

}

(Update 2014.09.15 today found a mistake before, the original JdbcTemplate query the map key is ignored case, the principle is that spring rewrite a linkedhashmap, see: http:// www.verydemo.com/demo_c143_i1696.html)

Map Transformation Tool Class:

public class Convertmapkey {

	/**
	 * Convert the key of the map object to lowercase
	 * @param map
	 * @return
	/public static Map<string, object> keytolower (map<string, object> Map) {
		map<string, object> r = new HashMap< String, object> ();
		if (map = = NULL | | map.size () = = 0) return
			R;
		For (map.entry<string, object> entry:map.entrySet ()) {
			r.put (Entry.getkey (). toLowerCase (), Entry.getvalue ());
        }
		return r;
	}
	
	/**
	 * Convert the key of Map object in list map to lowercase
	 * @param listmap
	 * @return/public
	static List<map <string, object>> listkeytolower (list<map<string, object>> listmap) {
		List<Map< String, object>> r = new arraylist<map<string, object>> ();
		if (Listmap = null | | listmap.size () = = 0) return
			R;
		For (map<string, object> map:listmap) {
			r.add (Keytolower (MAP));
		}
		return r;
	}
}

DAO Layer \ Service layer:

Return to new pagination (sqlfetchrows, page, rows, jdbctemplate);

Controller layer:

  @RequestMapping (value = "/list_school")
	@ResponseBody public
	pagination Listschool (httpservletrequest Request) {
	    
	    pagination p = null;
		try {
			
			int page = Integer.parseint (request.getparameter ("page"));
			int rows = Integer.parseint (Request.getparameter ("Rows"));
			
			String School_code = Request.getparameter ("S_school_code");
			String school_name = Request.getparameter ("S_school_name");
			
			Page: Current page   rows: Displaying the number of record bars per page  difference result set store list-rows
			p  = schoolservice.pagedata (page, rows, school_id, School_code, school_name);			
			
		catch (Exception e) {
			logger.error (e);
		}
		return p;
	}

Note: The pagination entity returned by Controller is converted to JSON with Jackson (so the entity must not contain JdbcTemplate fields, JdbcTemplate can only be injected by method), and then displayed in Easyui.

<[org.springframework.web.servlet.mvc.method.annotation.requestresponsebodymethodprocessor]-Written [{ Twokey=twovalue, Onekey=onevalue}] as "Application/json;charset=utf-8" using [ Org.springframework.http.converter.json.mappingjacksonhttpmessageconverter@8f3da5]>

Firefox allows you to discover the JSON of the paging entity that is returned above.

JS section:

      Query
	$ (' #btn_02010100 '). Click (function () {
		$ (' #basedg '). DataGrid (' Load ');
	});

	$ (' #basedg '). DataGrid ({animate:true,//whether to animate the collapsed checkbox:true,//Select all check boxes Checkonselect:t Rue,///Select the check box while selecting the row selectoncheck:true,///Select the check box Singleselect:false,//whether to select a radio Collapsible:tru E,/whether can be folded height: ' auto ',//automatic height iconcls: ' icon-save ',//Style icon IDfield: ' school_id ',//main index, Unique identity field loadmsg: ' Data loading, please wait ... ', maximizable:true, nowrap:true,//Truncate text PAGINATION:TR UE,//Paging pagelist: [10,15,20,25,30], rownumbers:true,//whether to display the number of columns, Url:basepath + '/school/list_scho Ol ', frozencolumns: [[{field: ' CK ', checkbox:true}, {field: ' school_id ', title: ' Unique ID ', Hidden:tru
   	    e}, {field: ' School_code ', title: ' School Code ', width:80}, {field: ' School_name ', title: ' School Name ', width:200} ]], columns: [[{field: ' Schooling_length ', Title: ' Educational system ', width:100}, {field: ' Telephone ', title: ' Tel ', width:120}, {Field: ' School_address ', Title: ' School Address ', width:300}, {field: ' Establishment_date ', title: ' Build School ', width:80, Formatt Er:function (value) {return value = = null?
			    ': (New Date (value). Format (' Yyyy-mm-dd '); }], onbeforeload:function (param) {Param.s_school_code = $ (' #s_school_code '). SearchBox (' GetValue '
	    	);
        Param.s_school_name = $ (' #s_school_name '). SearchBox (' GetValue ');
}
	}); }

JSP page:

<table id= "Basedg" toolbar= "#toolbar" pagination= "true" ></table>
<div id= "Toolbar" >
	    < Div id= "Searchbar" >
	    	<table cellspacing= "0" cellpadding= "0" >
				<tr>
					<td> query conditions: </ td>
					<td> <input id= "S_school_code" class= "Easyui-searchbox" data-options= "prompt
						: ' School Code ' > </input>
					</td>
					<td>
						<input id= "S_school_name" class= "Easyui-searchbox" data-options= "prompt: ' School name '" ></input>
					</td>
				</tr>
		</table>
	    < /div>
	    <a href= "javascript:void (0)" id= "btn_02010100" class= "Easyui-linkbutton" iconcls= "Icon-search" Plain= "true" > Query </a>
</div>

Final effect:

The triggering SQL for each query is as follows:

log begining method:com.ruhuiyun.studentmanager.service.SchoolService.pageData 2013-10-11 10:40:18,345 [qtp14565508-22] DEBUG [org.springframework.jdbc.core.JdbcTemplate]-Executing SQL query [select COUNT (1) From (SELECT school_id, School_code, School_name, Schooling_length, school_address, telephone, establishment_date from T _school WHERE 1 = 1 and InStr (School_name, ' Wuxi ') > 0)] 2013-10-11 10:40:18,349 [qtp14565508-22] DEBUG [Org.springfram Ework.jdbc.core.JdbcTemplate]-Executing SQL query [select * FROM (select Row_limit.*,rownum rownum_ from (select Sch  ool_id, School_code, School_name, Schooling_length, school_address, telephone, establishment_date from T_SCHOOL where 1 = 1 and InStr (School_name, ' Wuxi ') > 0) row_limit where rownum <= 2) where rownum_ > 0] 2013-10-11 10:40:18,353 [q TP14565508-22] INFO [Com.ruhuiyun.studentmanager.aop.LogAdvice]-Log ending method: Com.ruhuiyun.studentmanager.service.SchoolService.pageData 

The end of a song ~ ~