Dynamic SQL for paging queries

1. conditions for creating entity class objects that need to be queried com.rl.ecps.model.QueryCondition

Private Long Brandid;
Private short auditstatus;
Private short showstatus;
Private String ItemName;
Private Integer PageNo;
Private Integer Startnum;
Private Integer Endnum;

2. Query statements

Number of query bars:

<select id= "Selectitembyconditioncount" parametertype= "com.rl.ecps.model.QueryCondition" resulttype= "int" >
Select COUNT (*) from Eb_item t
<where>
<if test= "Brandid! = null" >
t.brand_id = #{brandid}
</if>
<if test= "Auditstatus! = null" >
and t.audit_status = #{auditstatus}
</if>
<if test= "Showstatus! = null" >
and t.show_status = #{showstatus}
</if>
<if test= "ItemName! = null and ItemName! =" ">
and t.item_name like '%${itemname}% '
</if>
</where>
</select>

Paging query:

<select id= "selectitembycondition" parametertype= "com.rl.ecps.model.QueryCondition" resultmap= "Baseresultmap" >
SELECT *
From (select RowNum rw, a.*
From (
SELECT * FROM Eb_item t
<where>
<if test= "Brandid! = null" >
t.brand_id = #{brandid}
</if>
<if test= "Auditstatus! = null" >
and t.audit_status = #{auditstatus}
</if>
<if test= "Showstatus! = null" >
and t.show_status = #{showstatus}
</if>
<if test= "ItemName! = null and ItemName! =" ">
and t.item_name like '%${itemname}% '
</if>
</where>
ORDER BY t.item_id Desc
<! [cdata[
) A
where RowNum < #{endnum}) b
where B.RW > #{startnum}
]]>
</select>

3. pagination entity class

public class Page {

/**
* Current page number (known)
*/
private int pageno = 1;

/**
* Number of records per page (known)
*/
private int pageSize = 5;

/**
* The total number of records under the specified query condition
*/
private int totalcount = 0;

/**
* The total number of pages under the specified query condition
*/
private int totalpage = 1;

/**
* The start line number of the query
*/
private int startnum = 0;

/**
* The end line number of the query
*/
private int endnum = 0;

/**
* Query Result set
*/
Private list<?> List;

4.service and control

Service implementations:

Public Page SELECTITEMBYQC (Querycondition QC) {
Query the total number of records under the current query condition
int totalcount = Itemdao.selectitembyconditioncount (QC);
Create a Page object
Page page = new page ();
Page.setpageno (Qc.getpageno ());
Page.settotalcount (TotalCount);
Calculate Startnum and Endnum
Integer startnum = Page.getstartnum ();
Integer endnum = Page.getendnum ();
Query object that sets the value to SQL
Qc.setstartnum (Startnum);
Qc.setendnum (Endnum);
Query result set
list<ebitem> itemList = itemdao.selectitembycondition (QC);
Page.setlist (itemList);
return page;
}

public void SaveItem (Ebitem item, Ebitemclob Itemclob,
List<ebparavalue> paralist, list<ebsku> skulist) {
Itemdao.saveitem (item);
Itemclobdao.saveitemclob (Itemclob, Item.getitemid ());
Paradao.saveparavalue (Paralist, Item.getitemid ());
Skudao.savesku (Skulist, Item.getitemid ());
}

Controller class:

@RequestMapping ("/listitem.do")
Public String ListItem (Querycondition QC, model model) {
list<ebbrand> blist = Brandservice.selectbrandall ();
Model.addattribute ("Blist", blist);
if (Qc.getpageno () = = null) {
Qc.setpageno (1);
}
Page page = ITEMSERVICE.SELECTITEMBYQC (QC);
Model.addattribute ("page", page);
Write the QC back, the purpose is to echo
Model.addattribute ("QC", QC);
return "Item/list";
}

Dynamic SQL for paging queries