SQL MyBatis RowBounds 不限制查询结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24301787/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MyBatis RowBounds doesn't limit query results
提问by odedia
I am developing an stateless API that needs to support pagination.
我正在开发一个需要支持分页的无状态 API。
I use an Oracle database. I use Spring with MyBatis for database access.
我使用 Oracle 数据库。我使用 Spring 和 MyBatis 进行数据库访问。
From the documentation, I understand that I can use the RowBounds class to limit the number of rows returned by the query.
从文档中，我了解到我可以使用 RowBounds 类来限制查询返回的行数。
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:
例如，如果我将 RowBounds 设置为偏移 100 的 50 条记录，我希望查询添加以下内容：
(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.
谢谢。
回答by khampson
Mybatisleaves many things up to the SQL driverthat is being used, and it appears the exact behavior surroundingRowBoundsis one of those.
Mybatis将许多事情留给SQL driver正在使用的东西，并且似乎周围的确切行为RowBounds就是其中之一。
See http://mybatis.github.io/mybatis-3/java-api.html, particularly the section that says:
请参阅http://mybatis.github.io/mybatis-3/java-api.html，特别是以下部分：
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).
在这方面，不同的驱动程序能够实现不同的效率水平。为了获得最佳性能，请使用 SCROLL_SENSITIVE 或 SCROLL_INSENSITIVE 的结果集类型（换句话说：不是 FORWARD_ONLY）。
The default is apparently UNSET, but you could try to use SCROLL_SENSITIVEas the ResultSetTypeattribute in the selecttag and see if that helps. See http://mybatis.github.io/mybatis-3/sqlmap-xml.htmlfor more info on that.
默认显然是UNSET，但您可以尝试将其SCROLL_SENSITIVE用作标签中的ResultSetType属性select，看看是否有帮助。有关更多信息，请参阅http://mybatis.github.io/mybatis-3/sqlmap-xml.html。
If that doesn't work you can always work around the issue by ditching the use of RowBoundsand implement a SettingsBeanclass (or similar) that your selecttag would take as a parameterType, and which contains fields for the offsetand limit(or perhaps rowStartand rowEndmake 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 selectis executed.
如果不工作，你总是可以通过开沟使用的解决问题RowBounds和实现SettingsBean类（或类似），您的select标签将采取的parameterType，并包含字段的offset和limit（或也许rowStart并rowEnd作出更有意义Oracle，然后您可以根据需要在运行时设置它们，并在执行时将它们动态插入到 SQL 中select。
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 Mybatisand Postgresand it has worked well.
虽然代码有点多，但您可以通过纯动态 SQL 完全按照自己的意愿控制行为。我已经使用了这样的方法，Mybatis并且Postgres效果很好。
So you would implement your SettingsBeanclass with those fields and their getters and setters, and your selectstatement might then look something like:
因此，您将使用这些字段及其 getter 和 setter实现您的SettingsBean类，然后您的select语句可能如下所示：
<select
id="selectFoo"
parameterType="com.foo.bar.SettingsBean">
select *
from foo
where rownum >= #{rowStart}
and rownum < #{rowEnd}
</select>
回答by BoatCode
I found a simple work around to this issue. I had followed the Mybatis instructions @khampson recommended and was passing a RowBounds instance to the mapper with no limits being enforced.
我找到了解决此问题的简单方法。我遵循了@khampson 推荐的 Mybatis 说明，并将 RowBounds 实例传递给映射器，而没有强制执行任何限制。
RowBounds rowbounds = new RowBounds(0, resultLimit);
roster = tableMapper.selectAll(rowbounds);
mapper java
映射器
public List<Row> selectAll(RowBounds rowbounds);
mapper xml
映射器 xml
<select id="com.TableMapper.selectAll" resultMap="row" timeout="10">
SELECT * FROM table;
</select>
simply appending "LIMIT #{param1.offset}, #{param1.limit}" to the mapper's xml produced the behavior I wanted!
只需将 "LIMIT #{param1.offset}, #{param1.limit}" 附加到映射器的 xml 即可产生我想要的行为！
<select id="com.TableMapper.selectAll" resultMap="row" timeout="10">
SELECT * FROM table LIMIT #{param1.offset}, #{param1.limit};
</select>
回答by sgrillon
I tested on Postgres databaseand pagination from AngularUI. The first page is N°1, if you call page N°0, the services return all datas.
我在AngularUI 中对Postgres 数据库和分页进行了测试。第一个页面是N°1，如果你调用页面N°0，服务返回所有数据。
java Service:
服务：
public List<Foo> selectAll(int currentPage, int itemsPerPage);
int offset = (currentPage - 1) * itemsPerPage;
RowBounds rowbounds;
if(currentPage == 0){
rowBounds = new RowBounds();
} else {
rowBounds = new RowBounds(currentPage, itemsPerPage);
}
return fooMapper.selectAll(rowBounds);
}
java Mapper:
java映射器：
public List<Foo> selectAll(RowBounds rowbounds);
xml Mapper:
xml映射器：
<select id="selectAll" resultMap="Foo">
SELECT * FROM foo;
</select>
