Heya,
I found a way to inject that new feature in OJB, however it required a bit of hacking using reflection to remove access restriction to a private method in
SqlQueryStatement class :(
I'll probably do a custom build of OJB with that one method set to 'protected'
instead of 'private', to avoid problems with security managers.
With this enhancement now you can force the addition of joins for tables not
related by foreign keys (or by reference/collection descriptors in the
repository.xml)
The syntax for adding extra joins would be:
ExtraJoinsCriteria c1 = new ExtraJoinsCriteria();
// Add the information for the extra join
c1.addExtraJoinInfo(
new ExtraJoinsCriteria.JoinInfo( "myJoinRefName", "leftAttr",
"myJoinRefName.rightAttr", RightJoinTable.class, false )
);
// Add the clauses using the extra-join
c1.addEqualTo( "myJoinRefName.attr2", X );
c1.addEqualTo( "myJoinRefName.attr3", Y );
Note that ExtraJoinsCriteria extends Criteria, JoinInfo constructor is defined
as:
/** Create join information for adding extra join criteria to the final query
* @param name String the name of the join, this name can then be used when
adding clauses to the criteria
* @param attrLeft The name of the attribute on the left side of the join
* @param attrRight The name of the attribute on the right side of the join
* @param attrRightClass The Class of the right side entities
* @param isOuterJoin true will use a left outer join, false an inner join
*/
public JoinInfo( String name, String attrLeft, String attrRight, Class
attrRightClass, boolean isOuterJoin )
This will add something like:(...) LEFT_JOIN_TABLE A0 INNER JOIN
RIGHT_JOIN_TABLE J0 ON A0.LEFT_ATTR = J0.RIGHT_ATTR WHERE J0.ATTR2 = X AND
J0.ATTR3 = Y
It should be possible to add this functionality as a default feature to the
Criteria class
If you want I can upload/send my code somewhere, currently I plugged in a custom SqlQueryGenerator implementation that will use a custom SqlSelectStatement
extension with this extra feature.
The code could be extracted and integrated in the existing classes and executed in a more efficient way than now, however I'll do some more testing with this
code first.
Another question is: why are so many methods in SqlQueryStatement private? It is a real pain to add extra functionalites to the SQL generation routines with all
these private methods.
let me know what you think about it, cheers
Danilo
Charles Anthony wrote:
Hi,
I'm afraid the answer is simply "no". Joins in OJB queries can only be done
by using the references defined in the metadata.
My suggestion would be to dynamically add/remove the class metadata for
A/B/C at runtime (or indeed permanently) with references to TEMP. The TEMP
entity should not be materialised if it is simply used for a join and if you
add auto-load="false" to the reference.
I suspect you know this, but for clarities sake - there is no requirement
for OJB references to be backed by foreign-key constraints on the database.
HTH.
Cheers,
Charles.
-----Original Message-----
From: Danilo Tommasina [mailto:[EMAIL PROTECTED]
Sent: 21 June 2006 08:53
To: OJB Users List
Subject: Is It Possible Add Joins To QueryByCriteria?
Hello,
We are having following problem:
Table TEMP {
attr1, attr2, attr3
}
Table A {
attr1
(...)
}
Table B {
attr1
(...)
}
Table C {
attr1
(...)
}
There are no foreign key contraints between TEMP and the A,B,C tables
however 'attr1' in all tables refer to the same 'logical object'
We have QueryByCriteria objects for returning entities of type A, B or C
however we need to do some filtering according to the entries in table TEMP.
Currently we are using a IN-criteria with sub-selects, ex.
SELECT (...) FROM A where (...) AND A.attr1 IN ( SELECT attr1 FROM TEMP
WHERE attr2 = X AND attr3 = Y )
The above query is not optimal, a better way would be to use a join, in SQL
we could do an inner join between A and TEMP tables, ex.
SELECT (...) FROM A INNER JOIN TEMP ON A.attr1 = TEMP.attr1 WHERE (...) AND
TEMP.attr2 = X AND TEMP.attr3 = Y
Since there is no foreign key constraint and no references in the
repository.xml we cannot use the standard Criteria objects, since there is
no known
relationship from tables A,B,C to table TEMP in OJB.
I know there is a QueryBySql, however the (...) part in the WHERE clause is
very complex and completely dynamically generated, so a QueryBySql is a big
no-no
for us.
My question is: Is it somehow possible to force the inclusion of a join
between tables in OJB using a QueryByCriteria ?
Any ideas?
Adding the references in the repository.xml or in real-time may also be
problematic since we do not want to materialise TEMP entities within A,B or
C entities.
thanks for support
Danilo
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
___________________________________________________________
HPD Software Ltd. - Helping Business Finance Business
Email terms and conditions: www.hpdsoftware.com/disclaimer
---------------------------------------------------------------------
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]
