Using annotations to implement a static join in hibernate

Hi, I'm relatively new to hibernate and was wondering if someone could help me out. While I have no issues implementing a normal join on multiple columns in hibernate using the

@JoinColumns

tag, I'm stumped when trying to implement the following query in annotations:

SELECT A.* FROM TABLEA A
LEFT OUTER JOIN TABLEB B
ON A.UID = B.ID
AND B.NAME = 'JAY'

As you can see the join is also based on a value ('JAY') which is not a column. I don't know how to proceed with such a mapping in annotations.

Can someone help?

Thanks, Jay

From stackoverflow

• you don't need to specify b.name as a join condition. instead just specify it as part of the where clause.

  SELECT A.* 
  FROM TABLEA A 
  LEFT OUTER 
  JOIN TABLEB B ON A.UID = B.ID 
  WHERE B.NAME is null or B.NAME = 'JAY'
  

  Justice : This is not the same query. It *would* be the same query were the required join an inner join, rather than a left outer join.

  Nathan Feger : sorry you are right, -1 sql foo points.

  ChssPly76 : That's still incorrect unless b.name is constrained as not nullable because it will return rows from B where name is null; original query would not.

  Nathan Feger : I don't understand. If its an inner join then nullable values from table b won't be returned... but if it is a left outer join then nullable results from table 2 will be returned. You seem to be intimating some third option. Which is neither an inner or outer join.

  ChssPly76 : No, I'm "intimating" that there's a difference between specifying conditions in "LEFT JOIN ... ON ..." and "WHERE ..." clauses; adding "OR ... IS NULL" to "WHERE" does not help. Not that any of this matters - OP has specified the SQL query in the question; I'm not quite sure why you're trying to rewrite it in a different form but I don't think that's what he asked for.

• It's unclear from your question whether you're trying to map this as HQL query or an association.

  I'm assuming you're talking about a query and that association between TABLEA and TABLEB is already mapped via A.UID and B.ID columns. In that case you can write your query using with keyword:

  select a.*
    from TableA a
    left join a.tableB b
    with b.name = 'JAY'
  

  If that's not what you're looking for, please clarify your question.

  Jay : Hi Chyss, I'm using the Criteria API as I need a dynamic query. The association between the tables is as defined in the question, a join on the ID column alone isn't sufficient. While its easy enough setting up a single column join in annotations: @JoinColumn(name = "UID", referencedColumnName = "ID") I can't figure how to add the second join constraint.

  ChssPly76 : "Association" in Hibernate context means "association between entities as defined by mappings". You will **not** be able to translate your query into association definition; core Hibernate kind of allows you to do that with formulas (http://docs.jboss.org/hibernate/stable/core/reference/en/html/mapping.html#mapping-column) but it's rather messy; there's no JPA / annotations counterpart for that, though. You will not be able to write the above query using Criteria either because there's no alternative to "with".

  Jay : Thanks much, so my only alternatives are HQL or named queries? (If I chose not to with the formulas i.e.)

  ChssPly76 : The only way to use HQL here is to map `TableA` and `TableB` using `A.UID` and `B.ID` and then use the above query - named or adhoc. You can do named SQL without mapping the association at all. You can use that named sql query to specify custom loader for your collection (http://docs.jboss.org/hibernate/stable/core/reference/en/html/querysql.html#querysql-load) but that will only work for straight-up collection load; not as part of the bigger query.

  Jay : Although I haven't tried it, I think Hibernate Filters might be the solution.