4 Replies Latest reply on May 31, 2007 5:00 PM by Owen Jacobson

    ejb-ql relations/mapping problem

    Pawe? Pogoda Newbie


      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 ...
      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 :/