Hibernate version: 3.2.2
Database version: Oracle 9.2.0.6.0 64bit
Generated SQL: select loanimpl0_.as_of_date as as1_2_, loanimpl0_.id_number as id2_2_, loanimpl0_.gl_account_id as gl3_2_ from loans loanimpl0_ where loanimpl0_.as_of_date=?
Mapping file:
<hibernate-mapping package="com.w.eis.domain.impl">

<typedef name="money" class="com.w.eis.dal.usertypes.MoneyUserType" />
<typedef name="date" class="com.w.eis.dal.usertypes.JDateUserType" />

<class name="LoanImpl" table="loans" lazy="false">

<composite-id>
<key-property type="date" name="asOfDate" column="as_of_date" />
<key-property type="int" name="idNumber" column="id_number" />
</composite-id>
<property type="long" name="glAccount" column="gl_account_id" />
</class>

<query name="LoanByAsOfDate" cacheable="false">
<![CDATA[
from LoanImpl l where l.asOfDate = :AsOfDate
]]>
</query>

</hibernate-mapping>

Debug log:
10:39:30,158 DEBUG SessionImpl:220 - opened session at timestamp: 11715647699
10:39:30,517 DEBUG ThreadLocalSessionContext:290 - allowing method [beginTransaction] in non-transacted context
10:39:30,517 DEBUG ThreadLocalSessionContext:300 - allowing proxied method [beginTransaction] to proceed to real session
10:39:30,517 DEBUG JDBCTransaction:54 - begin
10:39:30,517 DEBUG ConnectionManager:419 - opening JDBC connection
10:39:30,517 DEBUG DriverManagerConnectionProvider:93 - total checked-out connections: 0
10:39:30,517 DEBUG DriverManagerConnectionProvider:99 - using pooled JDBC connection, pool size: 0
10:39:30,517 DEBUG JDBCTransaction:59 - current autocommit status: false
10:39:30,517 DEBUG JDBCContext:210 - after transaction begin
10:39:30,673 DEBUG ConfigurationManager:113 - Loading conf/usertype.properties configuration file into memory.
10:39:35,658 DEBUG ThreadLocalSessionContext:300 - allowing proxied method [getNamedQuery] to proceed to real session
10:39:35,689 DEBUG QueryPlanCache:76 - located HQL query plan in cache (

from LoanImpl l where l.asOfDate = :AsOfDate

)
10:39:35,783 DEBUG SessionImpl:1308 - setting cache mode to: IGNORE
10:39:35,783 DEBUG QueryPlanCache:76 - located HQL query plan in cache (

from LoanImpl l where l.asOfDate = :AsOfDate

)
10:39:35,783 DEBUG HQLQueryPlan:235 - iterate:

from LoanImpl l where l.asOfDate = :AsOfDate


10:39:35,845 DEBUG QueryParameters:277 - named parameters: {AsOfDate=JDate[2006,3,31]}
10:39:35,845 DEBUG AbstractBatcher:358 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
10:39:42,392 DEBUG AbstractBatcher:476 - preparing statement

ScrollableResults question

My simple query should return about 7 million rows. Yeah, I know... I'm just using Hibernate to blast a bunch of POJOs across our message bus into our rules engine.

As such I'm attempting to use Query.scroll(ScrollMode.FORWARD_ONLY) to cycle through the results, however after Hibernate calls ps.executeQuery() on line 186 in AbstractBatcher.java - the ResultSet is never returned to Hibernate, even after waiting an hour. Queries which return far less data (several hundred records or less) work just fine.

I've tried the same query, with the same JDBC driver, with the same connection string using regular JDBC calls from a test application. The interesting thing is when I call statement.executeQuery() in my test app, I get results back immediately; I can then begin cycling through the results (all 7 million). I want Hibernate to have the same behavior as my test app, but it seems something is getting set somewhere that changes this behavior - but what?

I've verified that the JDBC prepared statement Hibernate is using is actually a result set type of: TYPE_FORWARD_ONLY and a concurrency type of: CONCUR_READ_ONLY. This is exactly what my little test app is using when it creates a prepared statement.

If anyone has any suggestions on how to get this to work using query.scroll() that would be appreciated.