ejb-ql relations/mapping problem

[ppogoda]

Hi!

Does someone know some solution for one of this problems:

- specify LEFT JOIN in EJB-QL on entity bean not being marked by any 'relationship' annotation

- mapping nativeQuery using @SqlResultSetMapping - set some @FieldResult being marked in bean as @Transient)

Generaly I need to achive in EJB-QL something similar to this kind of SQL:

SELECT t1.col1, t1.col2, ..., t2.col1, t2.col2...
FROM tab1 t1 LEFT JOIN tab2.t2 ON (t1.t2_id =t2.id AND t2.coln = :param1 AND t2.coln+1 = :param2)

The problem with relation specific annotations in entity is that they can't be 'parametrized' and join have to had the same number of column on both sides (or maybe I don't know something) :)

More details:
I'v got two tables (in simplify)
DOCUMENTS (doc_id, dict1_id, dict2_id, ...)
DICTIONARIES (dict_id, dict_version_id, dict_status, dict_locale)

I want to join this two tables like this:
SELECT ...
FROM DOCUMENTS d
LEFT JOIN DICTIONARIES d1 ON (d.dict1_id = d1.dict_id AND d1.dict_status = 1 AND d1.dict_locale = :param)
(the same for dict_2)

How to achieve this in EJB-QL, most important for me is to fill dictionary attributes in documents entity and of course it has to be done in one query :/

[ojacobson]

SELECT ...
FROM DOCUMENTS d
LEFT JOIN DICTIONARIES d1 ON (d.dict1_id = d1.dict_id)
WHERE d1.dict_status = 1 AND d1.dict_locale = :param

should be morally equivalent. It's usually a bad idea to put filtering predicates in join predicates in SQL; I can't imagine it being any better for EJBQL.

[ppogoda]

But my problem is that i want to have OneToMany relation between
Documents and Dictionaries

@Entity
class Document {
...
@OneToMany (not ManyToMany)
private Collection dict1
...
}

With many to many i would have to create additional join table...

in Document bean I have only dict_id, without dict_status, dict_locale and using only dict_id in relation annotation give me ManyToMany no matter that I give additional condition in EJB-QL (dict_status, dict_locale)

The best would be to have some kind of parametrized relation with dict_status and dict_locale, that would give me OneToMany.

[fhh]

You probably want a @ManyToOne relationship since you have a collection on the Document side.

Regards

Felix

[ojacobson]

If the dict_status and dict_locale fields are not part of Dictionary's entity key then you can't (portably) use them in a container-managed relationship. There is a hibernate extension that works on jboss when using the hibernate entity manager: have a look at

http://www.hibernate.org/hib_docs/annotations/reference/en/html/entity.html#entity-hibspec-collection-enhance

(2.4.6.1. Enhance collection settings)