使用JDBC驱动程序在SQL Server中设置默认行预取 [英] Set a default row prefetch in SQL Server using JDBC driver
问题描述
我有一个应用程序,我想为Oracle和SQL Server驱动程序定义要为连接预取的默认行数。 Oracle驱动程序有一个 OracleConnection
接口,它提供了 setDefaultRowPrefetch
方法来完成它,但是我找不到任何与SQL Server驱动程序等效的东西。
I have an application where I want to define the default number of rows to prefetch for a connection, using for both Oracle and SQL Server drivers. The Oracle driver has a the OracleConnection
interface, which provides the setDefaultRowPrefetch
method to do it, but I didn't find anything equivalent for the SQL Server driver.
有一种方法可以使用SQL Server JDBC驱动程序为连接定义默认行预取吗?
There is a method to define the default row prefetch for a connection using the SQL Server JDBC driver?
推荐答案
设置行提取大小的常用方法是:
The usual ways to set row fetch size are:
- 通过
java.sql.Connection
供应商实现类自定义方法(例如OracleConnection.setDefaultRowPrefetch
) - 通过
java.sql.Statement.setFetchSize(int)
:向驱动程序提供有关从此语句
获取的所有ResultSets
的行提取大小的提示。此方法由PreparedStatement
和CallableStatement
继承。大多数JDBC驱动程序都支持它。 - 通过
java.sql.ResultSet.setFetchSize(int)
:给出向驱动程序提示所有ResultSet
的行提取大小。
- Via
java.sql.Connection
vendor implementation class custom method (e.g.OracleConnection.setDefaultRowPrefetch
) - Via
java.sql.Statement.setFetchSize(int)
: gives a hint to the driver as to the row fetch size for allResultSets
obtained from thisStatement
. This method is inherited byPreparedStatement
andCallableStatement
. Most JDBC drivers support it. - Via
java.sql.ResultSet.setFetchSize(int)
: gives a hint to the driver as to the row fetch size for all thisResultSet
.
MS SQL Server JDBC驱动程序不支持以下任何方式:
MS SQL Server JDBC driver does not support any of these ways:
- MSSQL驱动程序没有这样的方法。
- 不幸的是,虽然大多数驱动程序都尊重提示,但MSSQL驱动程序却没有。所以对你没用。请参阅 Statement.setFetchSize(nSize)方法在SQL Server JDBC驱动程序中的作用是什么?
- 与
声明相同的问题
。
- MSSQL driver has no such a method.
- Unfortunately, while most drivers respect the hint, the MSSQL driver does not. So not useful for you. See What does Statement.setFetchSize(nSize) method really do in SQL Server JDBC driver?
- Same problem as
Statement
.
默认情况下,除非在JDBC驱动程序中指定游标类型,否则它将从数据库中检索所有行。 MSSQL驱动程序无法使用常用方法直接控制提取大小。
By default it retrieves all the rows from database unless you specify cursor type in the JDBC driver. MSSQL driver can't directly control the fetch size using the usual methods.
解决方案:
- 将
语句
转换为SQLServerStatement
并使用方法setMaxRows(int)的
。为什么他们没有在标准方法中实现这一点史蒂夫鲍尔默只知道; ^) - 创建你的驱动程序光标类型。游标的默认提取大小为1.设置
连接
字符串属性selectMethod = cursor
。或者,您可以使用com.microsoft.sqlserver.jdbc.SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY
创建语句
,以便向前滚动,只读访问,然后使用setFetchSize
方法来调整性能。 http://technet.microsoft.com/en- us / library / aa342344%28SQL.90%29.aspx - 使用(专有)SQL限制返回的行数(与设置不同)获取大小):
SET ROWCOUNT
或SELECT TOP N
- 切换到开源 jTDS 驱动程序,专门用于克服SQL Server驱动程序的问题。这是一个优秀的司机。
- Cast your
Statement
toSQLServerStatement
and use the methodsetMaxRows(int)
. Why they didn't implement this within the standard method Steve Ballmer only knows ;^) - Create your driver with a cursor type. The default fetch size for a cursor is 1. Set the
Connection
string propertyselectMethod=cursor
. Alternatively, you can create theStatement
withcom.microsoft.sqlserver.jdbc.SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY
scrollability for forward-only, read-only access, and then use thesetFetchSize
method to tune performance. http://technet.microsoft.com/en-us/library/aa342344%28SQL.90%29.aspx - Use (proprietary) SQL to limit the number of rows returned (not the same thing as setting the fetch size):
SET ROWCOUNT
orSELECT TOP N
- Switch to open source jTDS driver, specially made to overcome the problems of the SQL Server driver. It's a superior driver.
这篇关于使用JDBC驱动程序在SQL Server中设置默认行预取的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!