MySQL 如何用mybatis做分页？

Question

提问by Shailesh Pratapwar

I am currently working on a ecommerce application where I have to show a list of available products using search functionality.

我目前正在开发一个电子商务应用程序，我必须使用搜索功能显示可用产品列表。

As with every search, I have to implement Pagination here.

与每次搜索一样，我必须在这里实现分页。

I am using mybatis as my ORM tool and mysql as an underlying database.

我使用 mybatis 作为我的 ORM 工具，使用 mysql 作为底层数据库。

Googling around I found following ways to accomplish this task :

谷歌搜索我发现以下方法来完成这项任务：

Client Side paging: Here I will have to fetch all the results from the database matching the search criteria in one stroke and handle the pagination at my code level (Possibly frond end code ).
Server Side Paging: With mysql I can use the Limit and the offset of the resultset to construct a query like : SELECT * FROM sampletable WHERE condition1>1 AND condition2>2 LIMIT 0,20

客户端分页：在这里，我必须一次性从数据库中获取与搜索条件匹配的所有结果，并在我的代码级别（可能是前端代码）处理分页。
服务器端分页：使用 mysql，我可以使用结果集的限制和偏移量来构造如下查询： SELECT * FROM sampletable WHERE condition1>1 AND condition2>2 LIMIT 0,20

Here, I have to pass the offset and limit count everytime the user selects a new page while navigating in search results.

在这里，每次用户在搜索结果中导航时选择新页面时，我都必须传递偏移量和限制计数。

Can anyone tell,

谁能告诉，

which will be better way to implement paging ?
Do mybatis supports a better way to implement paging than just relying on above SQL queries ( like the hibernate criteria APIs).

这将是实现分页的更好方法？
mybatis 是否支持更好的方式来实现分页，而不仅仅是依赖上面的 SQL 查询（如 hibernate 标准 API）。

Any inputs is highly appreaciated. Thanks .

任何输入都受到高度赞赏。谢谢。

Answer 1

采纳答案by Volodymyr Levytskyi

I myself use your second opion with LIMIT in sql query.

我自己在 sql 查询中使用你的第二个选项和 LIMIT。

But there is range of methods that support pagination using RowBounds class. This is well described in mybatis documentation here

但是有一系列方法支持使用 RowBounds 类进行分页。这在mybatis文档here中有很好的描述

Pay attention to correct result set type to use.

注意使用正确的结果集类型。

Answer 2

回答by Tydaeus

If you're using Mappers (much easier than using raw SqlSessions), the easiest way to apply a limit is by adding a RowBounds parameter to the mapping function's argument list, e.g:

如果您使用 Mappers（比使用原始 SqlSessions 容易得多），应用限制的最简单方法是将 RowBounds 参数添加到映射函数的参数列表中，例如：

// without limit
List<Foo> selectFooByExample(FooExample ex);

// with limit
List<Foo> selectFooByExample(FooExample ex, RowBounds rb);

This is mentioned almost as an afterthought in the link Volodymyr posted, under the Using Mappers heading, and could use some more emphasis:

这几乎是在Volodymyr 发布的链接中的事后提及，在使用映射器标题下，并且可以更加强调：

You can also pass a RowBounds instance to the method to limit query results.

您还可以将 RowBounds 实例传递给该方法以限制查询结果。

Note that support for RowBounds may vary by database. The Mybatis documentation implies that Mybatis will take care of using the appropriate query. However, for Oracle at least, this gets handled by very inefficient repeat calls to the database.

请注意，对 RowBounds 的支持可能因数据库而异。Mybatis 文档暗示 Mybatis 将负责使用适当的查询。然而，至少对于 Oracle 来说，这通过对数据库的非常低效的重复调用来处理。

Answer 3

回答by Junchen Liu

pagination has two types, physical and logical

分页有两种类型，物理的和逻辑的

logical means to retrieve all the data first then sort them in memory
physical means database level subset select

首先检索所有数据然后在内存中对它们进行排序的逻辑方法
物理意味着数据库级子集选择

the default mybatis pagination is logical... thus when you select a massive database e.g 100GB of blobs, the rowbound method will still be very slow

默认的 mybatis 分页是合乎逻辑的...因此，当您选择一个大型数据库（例如 100GB 的 blob）时，行绑定方法仍然会很慢

the solution is to use the physical pagination

解决方案是使用物理分页

you can do your own way through the mybatis interceptor
or using pluginspre made by someone else

你可以通过mybatis拦截器做自己的事情
或使用其他人预先制作的插件

Answer 4

回答by bobmarksie

If you are using Spring MyBatis, you can achieve pagination manually using 2 MyBatis queries and the useful Spring Pageand Pageableinterfaces.

如果您使用的是 Spring MyBatis，您可以使用 2 个 MyBatis 查询和有用的 SpringPage和Pageable接口手动实现分页。

You create a higher level DAOinterface e.g. UploadDao

您创建了一个更高级别的DAO界面，例如UploadDao

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;

public interface UploadDao {

    Page<Upload> search(UploadSearch uploadSearch, Pageable pageable);

}

... where Uploadmaps to an uploadtable and UploadSearchis a parameter POJO e.g.

... 其中Upload映射到upload表并且UploadSearch是参数 POJO 例如

@Data  // lombok
public class UploadSearch {

    private Long userId;
    private Long projectId;
    ... 

}

An implementation of UploadDao(which injects a MyBatis UploadMappermapper) is as follows:

UploadDao（注入 MyBatisUploadMapper映射器）的实现如下：

public class DefaultUploadDao implements UploadDao {

    @Autowired
    private UploadMapper uploadMapper;

    public Page<Upload> searchUploads(UploadSearch uploadSearch, Pageable pageable) {
        List<Upload> content = uploadMapper.searchUploads(uploadSearch, pageable);
        Long total = uploadMapper.countUploads(uploadSearch);
        return new PageImpl<>(content, pageable, total);
    }

}

The DAO implementation calls 2 methods of UploadMapper. These are:

DAO 实现调用UploadMapper. 这些是：

UploadMapper.searchUploads- returns a page of results based on search param (UploadSearch) and Pageableparam (contains offset / limit etc).
UploadMapper.countUploads- returns total count, again based on search param UploadSearch. NOTE - Pageableparam is not required here as we're simply determining the total rows the search parameter filters to and don't care about page number / offset etc.

UploadMapper.searchUploads- 根据搜索参数 ( UploadSearch) 和Pageable参数（包含偏移/限制等）返回结果页面。
UploadMapper.countUploads- 返回总计数，再次基于搜索参数UploadSearch。注意 -Pageable这里不需要参数，因为我们只是确定搜索参数过滤到的总行数，而不关心页码/偏移量等。

The injected UploadMapperinterface looks like ...

注入的UploadMapper接口看起来像......

@Mapper
public interface UploadMapper {

    List<Upload> searchUploads(
        @Param("search") UploadSearch search,
        @Param("pageable") Pageable pageable);

    long countUploads(
        @Param("search") UploadSearch search);

}

... and the mapper XML file containing the dynamic SQL e.g. upload_mapper.xmlcontains ...

...以及包含动态 SQL 的映射器 XML 文件，例如upload_mapper.xml包含 ...

<mapper namespace="com.yourproduct.UploadMapper">

    <select id="searchUploads" resultType="com.yourproduct.Upload">
        select u.*
          from upload u
         <include refid="queryAndCountWhereStatement"/>
         <if test="pageable.sort.sorted">
             <trim prefix="order by">
                 <foreach item="order" index="i" collection="pageable.sort" separator=", ">
                     <if test="order.property == 'id'">id ${order.direction}</if>
                     <if test="order.property == 'projectId'">project_id ${order.direction}</if>
                 </foreach>
             </trim>
         </if>
        <if test="pageable.paged">
            limit #{pageable.offset}, #{pageable.pageSize}  
        </if>
        <!-- NOTE: PostgreSQL has a slightly different syntax to MySQL i.e. 
             limit #{pageable.pageSize} offset #{pageable.offset} 
        -->
    </select>

    <select id="countUploads" resultType="long">
        select count(1)
         from upload u
        <include refid="queryAndCountWhereStatement"/>
    </select>

    <sql id="queryAndCountWhereStatement">
        <where>
            <if test="search != null">
                <if test="search.userId != null"> and u.user_id = #{search.userId}</if>
                <if test="search.productId != null"> and u.product_id = #{search.productId}</if>
                ...
            </if>
        </where>
    </sql>
</mapper>

NOTE - <sql>blocks (along with <include refid=" ... " >) are very useful here to ensure your countand selectqueries are aligned. Also, when sorting we are using conditions e.g. <if test="order.property == 'projectId'">project_id ${order.direction}</if>to map to a column (and stop SQL injection). The ${order.direction}is safe as the Spring Directionclass is an enum.

注意 -<sql>块（连同<include refid=" ... " >）在此处非常有用，可确保您的查询count和select查询保持一致。此外，在排序时我们使用条件，例如<if test="order.property == 'projectId'">project_id ${order.direction}</if>映射到列（并停止 SQL 注入）。在${order.direction}为春天是安全Direction类是一个enum。

The UploadDaocould then be injected and used from e.g. a Spring controller:

在UploadDao随后将被注入，并从例如弹簧控制器使用：

@RestController("/upload")
public UploadController {

    @Autowired
    private UploadDao uploadDao;  // Likely you'll have a service instead (which injects DAO) - here for brevity

    @GetMapping
    public Page<Upload>search (@RequestBody UploadSearch search, Pageable pageable) {
        return uploadDao.search(search, pageable);
    }

}

Answer 5

回答by Ither

If you are using the MyBatis Generator, you may want to try the Row Bounds plugin from the official site: org.mybatis.generator.plugins.RowBoundsPlugin. This plugin will add a new version of the selectByExamplemethod that accepts a RowBounds parameter.

如果您使用的是 MyBatis Generator，您可能想尝试从官方站点获取 Row Bounds 插件：org.mybatis.generator.plugins.RowBoundsPlugin。此插件将添加selectByExample接受 RowBounds 参数的方法的新版本。

MySQL 如何用mybatis做分页？

提问by Shailesh Pratapwar

采纳答案by Volodymyr Levytskyi

回答by Tydaeus

回答by Junchen Liu

回答by bobmarksie

回答by Ither

相关推荐

最近更新

标签

MySQL 如何用mybatis做分页？

提问by Shailesh Pratapwar

采纳答案by Volodymyr Levytskyi

回答by Tydaeus

回答by Junchen Liu

回答by bobmarksie

回答by Ither

相关推荐

MySQL 查询执行被中断，错误 #1317

MySQL 我们如何在存储过程中使用 mysql_affected_rows()

MySQL 存储工作时间和高效查询的最佳方式

如何在 MySQL 中存储数组？

相关推荐

最近更新

标签