SpringMvc+Mybatis+Pagehelper paging details
- 2020-05-27 04:48:14
- OfStack
Recently, the company needs to make an alarm page, which needs pagination. After checking a lot of information, we found that PageHelper is more suitable
So write a tutorial on using PageHelper from scratch and keep track of what you've been working on for a day
1. First of all, we need to add the dependency of PageHelper in the project. Here I use Maven to add
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.1.6</version>
</dependency>
2. Add the pagehelper configuration to the mybatis configuration file
<configuration>
<plugins>
<!-- com.github.pagehelper for PageHelper The package name of the class -->
<plugin interceptor="com.github.pagehelper.PageHelper">
<!-- 4.0.0 Later versions may not set this parameter -->
<property name="dialect" value="mysql"/>
<!-- This parameter defaults to false -->
<!-- Set to true When will be RowBounds The first 1 A parameter offset As a pageNum The page number to use -->
<!-- and startPage In the pageNum The effect 1 sample -->
<property name="offsetAsPageNum" value="true"/>
<!-- This parameter defaults to false -->
<!-- Set to true When using RowBounds The paging will happen count The query -->
<property name="rowBoundsWithCount" value="true"/>
<!-- Set to true If the pageSize=0 or RowBounds.limit = 0 You'll get all the results -->
<!-- Equivalent to not executing a paging query, but still returning the same result Page Type) -->
<property name="pageSizeZero" value="true"/>
<!-- 3.3.0 Version available - Paging parameter rationalization, default false disable -->
<!-- Enable rationalization if pageNum<1 Can query the first 1 Page, if pageNum>pages At the end of the query 1 page -->
<!-- Disable rationalization when if pageNum<1 or pageNum>pages Empty data will be returned -->
<property name="reasonable" value="true"/>
<!-- 3.5.0 Version available - In order to support startPage(Object params) methods -->
<!-- increased 1 a `params` Parameter to configure the parameter mapping from Map or ServletRequest The value of -->
<!-- You can configure the pageNum,pageSize,count,pageSizeZero,reasonable,orderBy, Default values are not configured for mappings -->
<!-- Do not copy the configuration without understanding the meaning -->
<!-- <property name="params" value="pageNum=start;pageSize=limit;"/> -->
<!-- Supported by Mapper Interface parameters to pass the paging parameters -->
<property name="supportMethodsArguments" value="true"/>
<!-- always Always returns PageInfo type ,check Check if the return type is PageInfo,none return Page -->
<property name="returnPageInfo" value="check"/>
</plugin>
</plugins>
</configuration>
3. Add an PageBean class to store paging information
public class PageBean<T> implements Serializable {
private static final long serialVersionUID = 1L;
private long total; // The total number of records
private List<T> list; // The result set
private int pageNum; // What page
private int pageSize; // Number of records per page
private int pages; // Total number of pages
private int size; // The number of current pages <=pageSize
public PageBean(List<T> list){
if (list instanceof Page){
Page<T> page = (Page<T>) list;
this.pageNum = page.getPageNum();
this.pageSize = page.getPageSize();
this.total = page.getTotal();
this.pages = page.getPages();
this.list = page;
this.size = page.size();
}
}
public long getTotal() {
return total;
}
public void setTotal(long total) {
this.total = total;
}
public List<T> getList() {
return list;
}
public void setList(List<T> list) {
this.list = list;
}
public int getSize() {
return size;
}
public void setSize(int size) {
this.size = size;
}
public int getPageNum() {
return pageNum;
}
public void setPageNum(int pageNum) {
this.pageNum = pageNum;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getPages() {
return pages;
}
public void setPages(int pages) {
this.pages = pages;
}
}
Here is the code for the business logic
4. Start from the mapper.xml file and operate the sql of the database to find out the data we need
<select id="selectallList" parameterType="com.alarm.bean.AlarmParamModel"
resultMap="AlarmMap">
select message_id, seqnum, message_type, process_status,
distribute_status, processor, occur_time, close_time,
system_id, group_id, warn_level, message_content
from td_alarm_info
</select>
5. Interface method of mapper
public List<AlarmParamModel> selectallList(AlarmParamModel model);
6. Interface method of service
Datagrid selectallList(AlarmParamModel model,int pageNum, int pageSize);
7. Implementation class of service
This is the main logic for paging. pageNum is the page number, pageSize is the number of pages per page, startPag is the initial page, orderBy is to sort the data by a certain field, here I'm using the descending order of occr_time (desc)
public Datagrid selectallList(AlarmParamModel model,int pageNum, int pageSize){
PageHelper.startPage(pageNum, pageSize);
PageHelper.orderBy("occur_time desc");
List<AlarmParamModel> list = this.alarmMgrMapper.selectallList(model);
PageInfo<AlarmParamModel> pageInfo = new PageInfo<AlarmParamModel>(list);
Datagrid datagrid = new Datagrid(pageInfo.getTotal(),pageInfo.getList());
return datagrid;
}
8. Notice that I used a class Datagrid for data transfer to the front desk, including total (total number) and rows (data).
public class Datagrid {
private long total;
private List rows = new ArrayList<>();
public Datagrid() {
super();
}
public Datagrid(long total, List rows) {
super();
this.total = total;
this.rows = rows;
}
public long getTotal() {
return total;
}
public void setTotal(long total) {
this.total = total;
}
public List getRows() {
return rows;
}
public void setRows(List rows) {
this.rows = rows;
}
}
9. Start writing the controller layer and call the method you wrote before
What needs to be noted here is that offset and limit are the page Numbers sent from the front desk and the number displayed on each page. They are different from offset and limit of bootstraptable. The offset represents the offset, that is, if 10 pieces of data are displayed on each page, offset on page 2 of bootstrap is 10, while offset on page 1 and page 3 are 0 and 20, respectively. And I'm referring to offset as pageNum.
@RequestMapping(value = "/AlarmInfo/list", method = {RequestMethod.GET,RequestMethod.POST})
@ResponseBody
public Datagrid alarmInfo(AlarmParamModel model,@RequestParam(value="offset",defaultValue="0",required=false)Integer pageNum,
@RequestParam(value="limit",defaultValue="10",required=false)Integer pageSize)
{
Datagrid datagrid = this.alarmMgrService.selectallList(model,pageNum, pageSize);
return datagrid;
}
10. Up to now, the request of the foreground can get the background data and page it, so I will paste the configuration of my foreground bootstrap table again
$('#tb_departments').bootstrapTable({
url: 'http://10.1.234.134:8088/api/AlarmInfo/list', // Request background URL ( * )
method: 'get', // Request mode ( * )
striped: false, // Whether to display line spacing color
cache: false, // Whether to use caching, the default is true , so 1 Usually you need to set it 1 Under this property ( * )
pagination: true, // Whether to display paging ( * )
onlyInfoPagination:true, // Set to true Displays only the total data, not the paging button. Need to be pagination='True'
sortable: true, // Whether to enable sorting or not
sortOrder: "asc", // The sorting way
queryParams: oTableInit.queryParams,// Pass parameter ( * )
sidePagination: "server", // Paging mode: client Client page, server Server side paging ( * )
pageNumber:1, // Initializes the load control 1 Page, default no 1 page
pageSize: 10, // Number of rows per page ( * )
pageList: [10, 25, 50, 100], // Number of lines per page ( * )
search: false, // Whether to display the table search, this search is a client search, not into the server side, so the personal feeling is not significant
strictSearch: true,
showColumns: false, // Whether to display all columns
showRefresh: false, // Whether to display the refresh button
minimumCountColumns: 2, // The minimum number of columns allowed
clickToSelect: true, // Whether to enable click select row
checkboxHeader:true, //add
height: 500, // Line height if not set height Attribute, the table automatically determines the height of the table based on the number of records
uniqueId: "id", // every 1 The only 1 Logo, 1 General primary key column
showToggle:false, // Toggle button to display detail view and list view
cardView: false, // Whether to display the detail view
detailView: true,
detailFormatter:detailFormatter ,
paginationHAlign:"left",
paginationDetailHAlign:"right",
I didn't use the paging buttons that come with bootstrap, but I wrote my own set of buttons using jq. In the next article, I will post the button codes, so that the degree of customization will be higher.