Hello,
I use eclipselink and it's very nice. Our backend DB is MSSQL. I have to run pagination to millions of records. It seems eclipselink doesn't support setFirstResult/setMaxRows Pagination about MSSQL.
https://urldefense.proofpoint.com/v2/url?u=https-3A__wiki.eclipse.org_EclipseLink_Examples_JPA_Pagination&d=DwICAg&c=RoP1YumCXCgaWHvlZYR8PZh8Bv7qIrMUB65eapI_JnE&r=aeYmvFwRsCWfafng1rj-AcoD-cr8mkJQBEoVD5OszU0&m=H21Cpo0Xb8YWaFTSbpQKftGrjbPGO1lcCEbVN23JJic&s=YgvTjlOUpgVRIsshc_l5SSHl_vyqEDoN074cZcsMErk&e=Using above method, performance wasn't good because MSSQL pagination query(FETCH OFFSET) isn't used.
So, I tried to use CursoredStream as below.
https://urldefense.proofpoint.com/v2/url?u=https-3A__wiki.eclipse.org_Using-5FAdvanced-5FQuery-5FAPI-5F-28ELUG-29-23Handling-5FCursor-5Fand-5FStream-5FQuery-5FResults&d=DwICAg&c=RoP1YumCXCgaWHvlZYR8PZh8Bv7qIrMUB65eapI_JnE&r=aeYmvFwRsCWfafng1rj-AcoD-cr8mkJQBEoVD5OszU0&m=H21Cpo0Xb8YWaFTSbpQKftGrjbPGO1lcCEbVN23JJic&s=tObV-_v6NvZSX7W9NSwH6UqL2PpgT-G4PTrA1cYcZzA&e=
The problem is even if cursor.clear() is called, all record objects remain in UnitOfWork object as DescriptorEvent. I have to update records so "READ_ONLY" query hint can't be used.
===============================================
int max = 1000;
query.setHint(QueryHints.CURSOR, HintValues.TRUE)
.setHint(QueryHints.JDBC_FETCH_SIZE, max);
CursoredStream cursor = (CursoredStream)query.getSingleResult();
while (cursor.hasNext()) {
list = cursor.next(max);
for (Object recordObj : list) {
RecordEntity record = (RecordEntity)recordObj;
doSomething(record)
record.setSomeValue(someValue); // the record has to be updated
entityManager.merge(record);
}
cursor.clear(); //cursor.releasePrevious() doesn't work as well
}
cursor.close();
===============================================
Is it possible paging records by CursoredStream without getting all records in memory? If anyone could tell me, it's really helpful.
Thank you,
Atsuo
_______________________________________________
eclipselink-dev mailing list
eclipselink-dev@xxxxxxxxxxx
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://urldefense.proofpoint.com/v2/url?u=https-3A__www.eclipse.org_mailman_listinfo_eclipselink-2Ddev&d=DwICAg&c=RoP1YumCXCgaWHvlZYR8PZh8Bv7qIrMUB65eapI_JnE&r=aeYmvFwRsCWfafng1rj-AcoD-cr8mkJQBEoVD5OszU0&m=H21Cpo0Xb8YWaFTSbpQKftGrjbPGO1lcCEbVN23JJic&s=qkckS1sgK6lh2wPJzCkJuWLO_MAJSb_jyCi8k_IFp_k&e=