好得很程序员自学网

<tfoot draggable='sEl'></tfoot>

Mybatis分页的4种方式实例

数组分页

查询出全部数据,然后再list中截取需要的部分。

mybatis接口

?

1

List<Student> queryStudentsByArray();

xml配置文件

?

1

2

3

<select id= "queryStudentsByArray"   resultMap= "studentmapper" >

         select * from student

  </select>

service

?

1

2

3

4

5

6

7

8

9

10

11

12

13

接口

List<Student> queryStudentsByArray( int currPage, int pageSize);

实现接口

  @Override

     public List<Student> queryStudentsByArray( int currPage, int pageSize) {

         //查询全部数据

         List<Student> students = studentMapper.queryStudentsByArray();

         //从第几条数据开始

         int firstIndex = (currPage - 1 ) * pageSize;

         //到第几条数据结束

         int lastIndex = currPage * pageSize;

         return students.subList(firstIndex, lastIndex); //直接在list中截取

     }

controller

?

1

2

3

4

5

6

@ResponseBody

     @RequestMapping ( "/student/array/{currPage}/{pageSize}" )

     public List<Student> getStudentByArray( @PathVariable ( "currPage" ) int currPage, @PathVariable ( "pageSize" ) int pageSize) {

         List<Student> student = StuServiceIml.queryStudentsByArray(currPage, pageSize);

         return student;

     }

sql分页

mybatis接口

?

1

List<Student> queryStudentsBySql(Map<String,Object> data);

xml文件

?

1

2

3

<select id= "queryStudentsBySql" parameterType= "map" resultMap= "studentmapper" >

         select * from student limit #{currIndex} , #{pageSize}

</select>

service

?

1

2

3

4

5

6

7

8

9

接口

List<Student> queryStudentsBySql( int currPage, int pageSize);

实现类

public List<Student> queryStudentsBySql( int currPage, int pageSize) {

         Map<String, Object> data = new HashedMap();

         data.put( "currIndex" , (currPage- 1 )*pageSize);

         data.put( "pageSize" , pageSize);

         return studentMapper.queryStudentsBySql(data);

     }

拦截器分页

创建拦截器,拦截mybatis接口方法id以ByPage结束的语句

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

package com.autumn.interceptor;

 

import org.apache.ibatis.executor.Executor;

import org.apache.ibatis.executor.parameter.ParameterHandler;

import org.apache.ibatis.executor.resultset.ResultSetHandler;

import org.apache.ibatis.executor.statement.StatementHandler;

import org.apache.ibatis.mapping.MappedStatement;

import org.apache.ibatis.plugin.*;

import org.apache.ibatis.reflection.MetaObject;

import org.apache.ibatis.reflection.SystemMetaObject;

 

import java.sql.Connection;

import java.util.Map;

import java.util.Properties;

 

/**

  * @Intercepts 说明是一个拦截器

  * @Signature 拦截器的签名

  * type 拦截的类型 四大对象之一( Executor,ResultSetHandler,ParameterHandler,StatementHandler)

  * method 拦截的方法

  * args 参数,高版本需要加个Integer.class参数,不然会报错

  */

@Intercepts ({ @Signature (type = StatementHandler. class , method = "prepare" , args = {Connection. class })})

public class MyPageInterceptor implements Interceptor {

 

     //每页显示的条目数

     private int pageSize;

     //当前现实的页数

     private int currPage;

     //数据库类型

     private String dbType;

 

 

     @Override

     public Object intercept(Invocation invocation) throws Throwable {

         //获取StatementHandler,默认是RoutingStatementHandler

         StatementHandler statementHandler = (StatementHandler) invocation.getTarget();

         //获取statementHandler包装类

         MetaObject MetaObjectHandler = SystemMetaObject.forObject(statementHandler);

 

         //分离代理对象链

         while (MetaObjectHandler.hasGetter( "h" )) {

             Object obj = MetaObjectHandler.getValue( "h" );

             MetaObjectHandler = SystemMetaObject.forObject(obj);

         }

 

         while (MetaObjectHandler.hasGetter( "target" )) {

             Object obj = MetaObjectHandler.getValue( "target" );

             MetaObjectHandler = SystemMetaObject.forObject(obj);

         }

 

         //获取连接对象

         //Connection connection = (Connection) invocation.getArgs()[0];

 

 

         //object.getValue("delegate");  获取StatementHandler的实现类

 

         //获取查询接口映射的相关信息

         MappedStatement mappedStatement = (MappedStatement) MetaObjectHandler.getValue( "delegate.mappedStatement" );

         String mapId = mappedStatement.getId();

 

         //statementHandler.getBoundSql().getParameterObject();

 

         //拦截以.ByPage结尾的请求,分页功能的统一实现

         if (mapId.matches( ".+ByPage$" )) {

             //获取进行数据库操作时管理参数的handler

             ParameterHandler parameterHandler = (ParameterHandler) MetaObjectHandler.getValue( "delegate.parameterHandler" );

             //获取请求时的参数

             Map<String, Object> paraObject = (Map<String, Object>) parameterHandler.getParameterObject();

             //也可以这样获取

             //paraObject = (Map<String, Object>) statementHandler.getBoundSql().getParameterObject();

 

             //参数名称和在service中设置到map中的名称一致

             currPage = ( int ) paraObject.get( "currPage" );

             pageSize = ( int ) paraObject.get( "pageSize" );

 

             String sql = (String) MetaObjectHandler.getValue( "delegate.boundSql.sql" );

             //也可以通过statementHandler直接获取

             //sql = statementHandler.getBoundSql().getSql();

 

             //构建分页功能的sql语句

             String limitSql;

             sql = sql.trim();

             limitSql = sql + " limit " + (currPage - 1 ) * pageSize + "," + pageSize;

 

             //将构建完成的分页sql语句赋值个体'delegate.boundSql.sql',偷天换日

             MetaObjectHandler.setValue( "delegate.boundSql.sql" , limitSql);

         }

         //调用原对象的方法,进入责任链的下一级

         return invocation.proceed();

     }

 

 

     //获取代理对象

     @Override

     public Object plugin(Object o) {

         //生成object对象的动态代理对象

         return Plugin.wrap(o, this );

     }

 

     //设置代理对象的参数

     @Override

     public void setProperties(Properties properties) {

         //如果项目中分页的pageSize是统一的,也可以在这里统一配置和获取,这样就不用每次请求都传递pageSize参数了。参数是在配置拦截器时配置的。

         String limit1 = properties.getProperty( "limit" , "10" );

         this .pageSize = Integer.valueOf(limit1);

         this .dbType = properties.getProperty( "dbType" , "mysql" );

     }

}

 配置文件SqlMapConfig.xml

?

1

2

3

4

5

6

7

8

9

10

<configuration>

 

     <plugins>

         <plugin interceptor= "com.autumn.interceptor.MyPageInterceptor" >

             <property name= "limit" value= "10" />

             <property name= "dbType" value= "mysql" />

         </plugin>

     </plugins>

 

</configuration>

mybatis配置

?

1

2

3

4

5

6

7

8

9

10

11

<!--接口-->

List<AccountExt> getAllBookByPage( @Param ( "currPage" )Integer pageNo, @Param ( "pageSize" )Integer pageSize);

<!--xml配置文件-->

   <sql id= "getAllBooksql" >

     acc.id, acc.cateCode, cate_name, user_id,u.name as user_name, money, remark, time

   </sql>

   <select id= "getAllBook" resultType= "com.autumn.pojo.AccountExt" >

     select

     <include refid= "getAllBooksql" />

     from account as acc

   </select>

service

?

1

2

3

public List<AccountExt> getAllBookByPage(String pageNo,String pageSize) {

         return accountMapper.getAllBookByPage(Integer.parseInt(pageNo),Integer.parseInt(pageSize));

     }

controller

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

@RequestMapping ( "/getAllBook" )

     @ResponseBody

     public Page getAllBook(String pageNo,String pageSize,HttpServletRequest request,HttpServletResponse response){

         pageNo=pageNo== null ? "1" :pageNo;   //当前页码

         pageSize=pageSize== null ? "5" :pageSize;   //页面大小

         //获取当前页数据

         List<AccountExt> list = bookService.getAllBookByPage(pageNo,pageSize);

         //获取总数据大小

         int totals = bookService.getAllBook();

         //封装返回结果

         Page page = new Page();

         page.setTotal(totals+ "" );

         page.setRows(list);

         return page;

     }

Page实体类

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

package com.autumn.pojo;

 

import java.util.List;

 

/**

  * Created by Autumn on 2018/6/21.

  */

public class Page {

     private String pageNo = null ;

     private String pageSize = null ;

     private String total = null ;

     private List rows = null ;

 

     public String getTotal() {

         return total;

     }

 

     public void setTotal(String total) {

         this .total = total;

     }

 

     public List getRows() {

         return rows;

     }

 

     public void setRows(List rows) {

         this .rows = rows;

     }

 

     public String getPageNo() {

         return pageNo;

     }

 

     public void setPageNo(String pageNo) {

         this .pageNo = pageNo;

     }

 

     public String getPageSize() {

         return pageSize;

     }

 

     public void setPageSize(String pageSize) {

         this .pageSize = pageSize;

     }

 

}

前端

bootstrap-table接受数据格式

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

{

   "total" : 3 ,

   "rows" : [

     {

       "id" : 0 ,

       "name" : "Item 0" ,

       "price" : "$0"

     },

     {

       "id" : 1 ,

       "name" : "Item 1" ,

       "price" : "$1"

     }

   ]

}

boostrap-table用法

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

var $table = $( '#table' );

         $table.bootstrapTable({

         url: "/${appName}/manager/bookController/getAllBook" ,

         method: 'post' ,

         contentType: "application/x-www-form-urlencoded" ,

         dataType: "json" ,

         pagination: true , //分页

         sidePagination: "server" , //服务端处理分页

         pageList: [ 5 , 10 , 25 ],

         pageSize: 5 ,

         pageNumber: 1 ,

         //toolbar:"#tb",

         singleSelect: false ,

         queryParamsType : "limit" ,

         queryParams: function queryParams(params) {   //设置查询参数

           var param = {

             pageNo: params.offset/params.limit+ 1 ,  //offset为数据开始索引,转换为显示当前页

             pageSize: params.limit  //页面大小

           };

           console.info(params);   //查看参数是什么

           console.info(param);   //查看自定义的参数

           return param;

         },

         cache: false ,

         //data-locale: "zh-CN", //表格汉化

         //search: true, //显示搜索框

         columns: [

                 {

                     checkbox: true

                 },

                 {

                     title: '消费类型' ,

                     field: 'cate_name' ,

                     valign: 'middle'

                 },

                 {

                     title: '消费金额' ,

                     field: 'money' ,

                     valign: 'middle' ,

                     formatter:function(value,row,index){

                         if (!isNaN(value)){   //是数字

                             return value/ 100 ;

                         }

                     }

                 },

                 {

                     title: '备注' ,

                     field: 'remark' ,

                     valign: 'middle'

                 },

                 {

                     title: '消费时间' ,

                     field: 'time' ,

                     valign: 'middle'

                 },

                 {

                     title: '操作' ,

                     field: '' ,

                     formatter:function(value,row,index){

                         var f = '<a href="#" rel="external nofollow"  class="btn btn-gmtx-define1" onclick="delBook(\'' + row.id + '\')">删除</a> ' ;

                         return f;

                        }

                 }

             ]

           });

       });

RowBounds分页

数据量小时,RowBounds不失为一种好办法。但是数据量大时,实现拦截器就很有必要了。

mybatis接口加入RowBounds参数

?

1

public List<UserBean> queryUsersByPage(String userName, RowBounds rowBounds);

service

?

1

2

3

4

5

@Override

     @Transactional (isolation = Isolation.READ_COMMITTED, propagation = Propagation.SUPPORTS)

     public List<RoleBean> queryRolesByPage(String roleName, int start, int limit) {

         return roleDao.queryRolesByPage(roleName, new RowBounds(start, limit));

     }

更多关于Mybatis分页的方式实例请查看下面的相关链接

原文链接:https://HdhCmsTestcnblogs测试数据/aeolian/p/9229149.html

查看更多关于Mybatis分页的4种方式实例的详细内容...

  阅读:14次