MyBatis RowBounds不限制查询结果 [英] MyBatis RowBounds doesn't limit query results

问题描述

我正在开发需要支持分页的无状态API.

我使用Oracle数据库. 我使用Spring和MyBatis进行数据库访问.

从文档中了解，我可以使用RowBounds类限制查询返回的行数.

但是，似乎没有对查询进行特殊优化以支持分页.

例如，如果我将RowBounds设置为具有50条记录的偏移量100，我希望查询中添加以下内容:

(original query with the where clause...)
and ROWNUM < 150
and ROWNUM >= 100

但是这里什么也没有，只是我手动定义的查询.

这对性能很糟糕，因为我可能会获得数千个结果.

我在做什么错了?

谢谢.

解决方案

Mybatis将许多事情留给正在使用的SQL driver，看来围绕RowBounds的确切行为就是其中之一. /p>

请参见 http://mybatis.github.io/mybatis-3/java -api.html ，尤其是显示以下内容的部分:

不同的驱动程序能够实现不同级别的效率 在这方面.为了获得最佳性能，请使用以下结果集类型 SCROLL_SENSITIVE或SCROLL_INSENSITIVE(换句话说:不是 FORWARD_ONLY).

默认值显然是UNSET，但是您可以尝试使用SCROLL_SENSITIVE作为select标记中的ResultSetType属性，看看是否有帮助.请参见 http://mybatis.github.io/mybatis-3/sqlmap-xml. html 以获得更多信息.

如果这不起作用，您可以通过放弃使用RowBounds并实施 SettingsBean 类(或类似的类)来解决此问题，您的select标记将其视为parameterType，其中包含offset和limit的字段(或者rowStart和rowEnd对于Oracle更有意义，然后您可以根据需要在运行时设置它们并将其动态内插到select执行时的SQL.

虽然有更多代码，但是您可以通过纯动态SQL完全根据需要控制行为.我对Mybatis和Postgres使用了类似的方法，并且效果很好.

因此，您将使用这些字段以及它们的getter和setter来实现 SettingsBean 类，然后您的select语句可能类似于:

<select
  id="selectFoo"
  parameterType="com.foo.bar.SettingsBean">

select *
from foo
where rownum >= #{rowStart}
  and rownum < #{rowEnd}
</select>

I am developing an stateless API that needs to support pagination.

I use an Oracle database. I use Spring with MyBatis for database access.

From the documentation, I understand that I can use the RowBounds class to limit the number of rows returned by the query.

However, it seems that there's no special optimization done to the query to support pagination.

For example, if I set RowBounds to offset 100 with 50 records, I'd expect the query to have the following added:

(original query with the where clause...)
and ROWNUM < 150
and ROWNUM >= 100

But there's nothing there, it's just the query that I defined manually.

This is terrible for performance, since I might have several thousand results.

What am I doing wrong?

Thanks.

解决方案

Mybatis leaves many things up to the SQL driver that is being used, and it appears the exact behavior surroundingRowBounds is one of those.

See http://mybatis.github.io/mybatis-3/java-api.html, particularly the section that says:

Different drivers are able to achieve different levels of efficiency in this regard. For the best performance, use result set types of SCROLL_SENSITIVE or SCROLL_INSENSITIVE (in other words: not FORWARD_ONLY).

The default is apparently UNSET, but you could try to use SCROLL_SENSITIVE as the ResultSetType attribute in the select tag and see if that helps. See http://mybatis.github.io/mybatis-3/sqlmap-xml.html for more info on that.

If that doesn't work you can always work around the issue by ditching the use of RowBounds and implement a SettingsBean class (or similar) that your select tag would take as a parameterType, and which contains fields for the offset and limit (or perhaps rowStart and rowEnd make more sense for Oracle, and then you can set those at runtime as needed and interpolate them dynamically into the SQL at the time the select is executed.

While a bit more code, you get to control the behavior exactly as you want through pure dynamic SQL. I have used an approach like this with Mybatis and Postgres and it has worked well.

So you would implement your SettingsBean class with those fields and their getters and setters, and your select statement might then look something like:

<select
  id="selectFoo"
  parameterType="com.foo.bar.SettingsBean">

select *
from foo
where rownum >= #{rowStart}
  and rownum < #{rowEnd}
</select>

这篇关于MyBatis RowBounds不限制查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！