Hi,
I found the following in OJB code:
For Version 1.0.4:
In class SqlSelectStatement in method protected String
buildStatement():
orderByFields = query.getOrderBy();
columnList = ensureColumns(orderByFields, columnList, stmt);
For Version 1.0 RC6 (which we are still using... sorry)
In class SqlSelectStatement in method public String getStatement():
if (orderByFields != null && !orderByFields.isEmpty())
{
orderByColumnNumbers = ensureColumns(orderByFields, columnList,
stmt);
}
The above code raises the question why these columns are added when
these columns are only appearing in the orderby? This is the root of the
problem, that is causing multiple resultsets for one and the same object
to be returned. OJBIterator implementations do not filter out the
duplicates, and I do not think that that is the right way to go, how do
you implement paging for that matter, especially when native SQL will be
used implementing paging?
Is their a specific reason why these columns are added?
Roger Janssen
iBanx
-----Original Message-----
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
Sent: Friday, June 16, 2006 9:11 PM
To: OJB Users List
Subject: Re: Bug : duplicate objects in resultset
hi roger,
the following works for me. i use a set instead of a collection:
public void testOrderByJoined2()
{
Criteria crit = new Criteria();
QueryByCriteria q = QueryFactory.newQuery(Project.class, crit,
true);
q.addOrderByAscending("persons.lastname");
Collection results = broker.getCollectionByQuery(q);
assertNotNull(results);
ManageableHashSet resultsMgmt = (ManageableHashSet)
broker.getCollectionByQuery(RemovalAwareSet.class, q);
assertNotNull(resultsMgmt);
// compare with count
int count = broker.getCount(q);
assertEquals(resultsMgmt.size(), count);
}
hth
jakob
Janssen, Roger schrieb:
> Hi,
>
> Thanx for the confirmation.
>
> Is it necessary to add the orderby column to the returned columns in
> the resultset, when the orderby column is a column of a joined table?
> This is what causes this behaviour. I personnaly do not think so,
> since for instantiating the objects, it is not required to be in the
resultset.
>
> Example:
>
> OJB generated SQL (A1.VALUE is added to columns in resultset):
>
> SELECT DISTINCT A0.ORG_CONTEXT,A0.ENABLED,A0.ENTITY_CLASS,A1.VALUE as
> ojb_col_49
> FROM IBANX_LOG_EVENT A0 INNER JOIN IBANX_LOG_EVENT_ABSTRACTATTRIBUTE
> A1 ON A0.ID=A1.OID WHERE (( A1.NAME = ?) AND (A1.VALUE LIKE ?)) ORDER
> BY 4
>
> What it (could) should look like, and gives the correct results (no
> duplicates in resultset):
>
> SELECT DISTINCT A0.ID, A0.ORG_CONTEXT,A0.ENABLED,A0.ENTITY_CLASS
> FROM IBANX_LOG_EVENT A0 INNER JOIN IBANX_LOG_EVENT_AA A1 ON
> A0.ID=A1.OID
>
> WHERE (( A1.NAME = ?) AND (A1.VALUE LIKE ?)) ORDER BY A1.VALUE
>
> If you agree that this is the problem, is their a quick patch I can
> apply?
>
> Thanx.
>
> Roger Janssen
> iBanx
>
> -----Original Message-----
> From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 15, 2006 10:13 PM
> To: OJB Users List
> Cc: Selders, Bart
> Subject: Re: Bug : duplicate objects in resultset
>
> hi roger,
>
> i can confirm this problem.
> this simple testcase shows the duplicates:
>
> /**
> * test OrderBy joined column
> */
> public void testOrderByJoined2()
> {
> Criteria crit = new Criteria();
> QueryByCriteria q = QueryFactory.newQuery(Project.class,
crit);
> q.addOrderByAscending("persons.lastname");
>
> Collection results = broker.getCollectionByQuery(q);
> assertNotNull(results);
>
> // compare with count
> int count = broker.getCount(q);
> assertEquals(results.size(), count);
>
> Set resultsSet = new HashSet(results);
> assertEquals(resultsSet.size(), results.size());
> }
>
> jakob
>
>
>
> **********************************************************************
> ***T he information contained in this communication is confidential
> and is intended solely for the use of the individual or entity to
> whom it is addressed.You should not copy, disclose or distribute this
> communication without the authority of iBanx bv. iBanx bv is neither
> liable for the proper and complete transmission of the information has
> been maintainedn or that the communication is free of viruses,
> interceptions or interference.
>
> If you are not the intended recipient of this communication please
> returnt he communication to the sender and delete and destroy all
copies.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
