Good afternoon, people:

I'm having the following problem with PAGINATION and ran out of options and possibles causes or solutions, hope someone (anyone) could help me solve this.

Problem

Since last week I being trying to create java code to process large results sets in a J2EE application with a web application as front-end.
After more than a week of research and reading a lot of books about design patterns/anti-patterns from Wesley, Wrox, etc., I came out with a solution for our problem involving Stateful Sessions Beans to keep track of the query, position, current page, etc. for each user and using a Stateless Session Bean for executing querys, no caching results (each view can change over time during the user session) and database access (see code at the end of the post) for fetching the next page of data using Hibernate. All tries failed at the second execution of getPage(..., ... ) during our test case to isolate components (EJBs) problems.

What I have done so far

1.- I went throught Hibernate forums posts
2.- Read Gavin and Hibernate developers group blogs.
3.- Read articles in javaworld, oracle otn, etc.
3.- Did a lot of Google searches
4.- Upgrade to the lastest distro of Hibernate


Result = good design patterns, a lots of pros and cons of each pattern, scrollable results,etc. but haven't being able to fix my problem.


Test Data and Conditions of the Test Case


- Using this table InformeMedico for the query
- This table has 10 records (TotalItems)
- We divided the request (getPage) in two pages of 5 items each one.
- Create a test case for issuing in Cactus the following code

test1

results = getPage(1,5)

Hibernate executed:



Succeed - returned 5 records. Test succeded.

test2

results = getPage(2,5);



Fail !!!!! every time

I tought it was SQL server for being poorly designed, Hibernate setFirstResult failing to retrieve the first item of the result set returned by the database the second time i ran the getPage code, Hibernte caching the query (I afterwards read by default this setting is off I did it just to discard this possibility) and finallygave up.


I hope someone can shreed some ligth on this problem

TIA,
Pedro Taborda






Technnical Details (sorry for any variables in spanish but it's time consuming translating into english the names)



Hibernate version:
2.1.6

Query:



Mapping documents:

InformeMedicoImpl

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping>
<!--
Created by the Middlegen Hibernate plugin 2.1

http://boss.bekk.no/boss/middlegen/
http://www.hibernate.org/
-->

<class
name="com.procedatos.clinycas.im.impl.InformeMedicoImpl"
table="InformeMedico"
>
<meta attribute="implement-equals" inherit="false">true</meta>

<id
name="idInformeMedico"
type="int"
column="idInformeMedico"
>
<meta attribute="use-in-equals">true</meta>
<generator class="assigned" />
</id>

<property
name="txFirmaDocmto"
type="java.lang.String"
column="txFirmaDocmto"
length="150"
/>
<property
name="feElaboracion"
type="java.sql.Timestamp"
column="feElaboracion"
not-null="true"
length="23"
/>
<property
name="deContenidoInformeMedico"
type="java.lang.String"
column="deContenidoInformeMedico"
not-null="true"
length="2147483647"
/>
<property
name="feFirma"
type="java.sql.Timestamp"
column="feFirma"
length="23"
/>
<property
name="feUltimaModificacion"
type="java.sql.Timestamp"
column="feUltimaModificacion"
length="23"
/>

<!-- Associations -->

<!-- bi-directional many-to-one association to MedicoTratante -->
<many-to-one
name="medicoTratante"
class="com.procedatos.clinycas.im.impl.MedicoTratanteImpl"
not-null="true"
>
<column name="idMedicoTratante" />
</many-to-one>
<!-- bi-directional many-to-one association to OrdenServicio -->
<many-to-one
name="ordenServicio"
class="com.procedatos.clinycas.ods.impl.OrdenServicioImpl"
not-null="true"
>
<column name="idOrdenServicio" />
<column name="idVersion" />
</many-to-one>
<!-- bi-directional one-to-one association to InformeMedicoInterconsulta -->
<one-to-one
name="informeMedicoInterconsulta"
class="com.procedatos.clinycas.im.impl.InformeMedicoInterconsultaImpl"
outer-join="auto"
/>
<!-- bi-directional one-to-one association to InformeMedicoIngreso -->
<one-to-one
name="informeMedicoIngreso"
class="com.procedatos.clinycas.im.impl.InformeMedicoIngresoImpl"
outer-join="auto"
/>
<!-- bi-directional one-to-one association to InformeMedicoEvolutivo -->
<one-to-one
name="informeMedicoEvolutivo"
class="com.procedatos.clinycas.im.impl.InformeMedicoEvolutivoImpl"
outer-join="auto"
/>
<!-- bi-directional one-to-many association to SolicitudInformeInterconsulta -->
<set
name="solicitudesInformeInterconsulta"
lazy="true"
inverse="true"
cascade="none"
>
<key>
<column name="idInformeMedico" />
</key>
<one-to-many
class="com.procedatos.clinycas.im.impl.SolicitudInformeInterconsultaImpl"
/>
</set>
<!-- bi-directional one-to-one association to InformeMedicoEgreso -->
<one-to-one
name="informeMedicoEgreso"
class="com.procedatos.clinycas.im.impl.InformeMedicoEgresoImpl"
outer-join="auto"
/>

</class>
</hibernate-mapping>


InformeMedicoIngresoImpl

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping>
<!--
Created by the Middlegen Hibernate plugin 2.1

http://boss.bekk.no/boss/middlegen/
http://www.hibernate.org/
-->

<class
name="com.procedatos.clinycas.im.impl.InformeMedicoIngresoImpl"
table="InformeMedicoIngreso"
>
<meta attribute="implement-equals" inherit="false">true</meta>

<id
name="idInformeMedicoIngreso"
type="int"
column="idInformeMedicoIngreso"
>
<meta attribute="use-in-equals">true</meta>
<generator class="foreign">
<param name="property">informeMedico</param>
</generator>

</id>

<property
name="txMotivoConsulta"
type="java.lang.String"
column="txMotivoConsulta"
not-null="true"
length="2147483647"
/>
<property
name="txEnfermedadActual"
type="java.lang.String"
column="txEnfermedadActual"
not-null="true"
length="2147483647"
/>
<property
name="txAntecedentePersonal"
type="java.lang.String"
column="txAntecedentePersonal"
not-null="true"
length="2147483647"
/>
<property
name="txAntecedenteFamiliar"
type="java.lang.String"
column="txAntecedenteFamiliar"
not-null="true"
length="2147483647"
/>
<property
name="txRevisionSistemas"
type="java.lang.String"
column="txRevisionSistemas"
not-null="true"
length="2147483647"
/>
<property
name="txExamenFisico"
type="java.lang.String"
column="txExamenFisico"
not-null="true"
length="2147483647"
/>
<property
name="txIdx"
type="java.lang.String"
column="txIDX"
not-null="true"
length="2147483647"
/>
<property
name="txOrdenMedica"
type="java.lang.String"
column="txOrdenMedica"
not-null="true"
length="2147483647"
/>
<property
name="txComentarioAdicional"
type="java.lang.String"
column="txComentarioAdicional"
length="2147483647"
/>
<property
name="inInterconsulta"
type="boolean"
column="inInterconsulta"
not-null="true"
length="1"
/>

<!-- Associations -->

<!-- bi-directional one-to-one association to InformeMedico -->
<one-to-one
name="informeMedico"
class="com.procedatos.clinycas.im.impl.InformeMedicoImpl"
outer-join="auto"
constrained="true"
/>

</class>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():



[code]public List getPage(int currentPage, int pageSize) throws ExcepcionPersistencia {
SesionPersistencia sesion = null;
List results = null;
int totalPaginasIM = 0;
int totalItemsIM = 0;



try
{
sesion = sesionPersistenciaFactory.crearSesionPersistencia();

totalItemsIM = ((Integer)parametros.get("totalItems")).intValue();
totalPaginasIM = ((Integer)parametros.get("totalPages")).intValue();

// V

The Dialect (Hence I assume SQL Server) does not support an offset for the query.

Doing a quick search around the net finds that indeed only 'top' is available to limit the data selected. There are suggested work arounds that involve introducing extra field for rowcount either into temp tables or use a correlated subquery as a rowCount field into the dataset.

Hi David:

First thanks for providing some help. Yes , the Dialet is SQL Server, but can you explain me in more detail your proposed work around don't quiet get it

TIA,
Pedro

Pedro,

This is caused by a bug in jTDS 0.9-rc1. Please download and use jTDS 0.9-rc2. The full 0.9 release will be available within a week so make sure to get that too, as it will fix a lot of other issues.

Sorry for all the trouble.

Alin.

Alin, thankssss, you were rigth, it was the driver version, upgrade to the latest version and it worked like a charm.


Thanks again,
Pedro