oracle 如何在JDBC中设置Cursor类型?

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/337179/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 17:31:35  来源:igfitidea点击:

How to set Cursor type in JDBC?

javaoraclejdbccursors

提问by Kieveli

I'm running tomcat and have some jsp pages that display a subset of a table. I show 20 rows at a time on a single page. When the table has large amounts of data, the jsp page doesn't render. I'm guessing that the ResultSet is using a client side cursor. I've worked with ASP in the past, and we always used server side forward only cursors, and never had any problems with large amounts of data. Our database is oracle 10g.

我正在运行 tomcat 并且有一些显示表格子集的 jsp 页面。我在一个页面上一次显示 20 行。当表有大量数据时,jsp 页面不呈现。我猜 ResultSet 正在使用客户端游标。我过去使用过 ASP,我们总是使用服务器端前向游标,并且从没有遇到大量数据的任何问题。我们的数据库是oracle 10g。

How can I specify a server-side forward-only cursor in JDBC?

如何在 JDBC 中指定服务器端只进游标?

回答by Kieveli

The oracle driver implements server-side cursors via the FetchSize property.

oracle 驱动程序通过 FetchSize 属性实现服务器端游标。

Unfortunately, JDBC doesn't explicitly allow for setting client vs server-side cursors, so different drivers implement it in different ways. Here are the other links that helped:

不幸的是,JDBC 没有明确允许设置客户端与服务器端游标,因此不同的驱动程序以不同的方式实现它。以下是其他有帮助的链接:

Fetch Size
Cursors
Oracle Driver

获取大小
游标
Oracle 驱动程序

回答by adam

Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY);
ResultSet rs = stmt.executeQuery(sql);

This should set it but apparently somedrivers ignore it.

这应该设置它,但显然有些驱动程序忽略它。

You could always try and set it again at ResultSet level.

您可以随时尝试在 ResultSet 级别再次设置它。

rs.setFetchDirection(ResultSet.TYPE_FORWARD_ONLY);

Hope that helps.

希望有帮助。

回答by Dan Vinton

Not quite answering the question, but have you considered explicitly adding paging to your SELECT query using ROWNUM or ROWNUMBER in your WHERE clause?

没有完全回答这个问题,但是您是否考虑过在 WHERE 子句中使用 ROWNUM 或 ROWNUMBER 向 SELECT 查询显式添加分页?

eg: for the second page of data, 20 element page size:

例如:对于第二页数据,20 个元素的页面大小:

SELECT * 
  FROM MyDataObjects
  WHERE rownum > 20 AND rownum < 41

This would ensure that at most one page of records are returned, removing the large cursor issue.

这将确保最多返回一页记录,从而消除大游标问题。