如何在HQL的JOIN中添加两个关联条件？附代码示例

Let's fix your HQL issue step by step. The problem with your initial attempt is that you tried to reference c3 in the WITH clause of the c4 join, but c3 hadn't been associated yet—Hibernate can't resolve an entity that hasn't been joined into the query context.

Here's the correct approach to move your filter conditions into the respective JOIN clauses, matching your target SQL:

Correct HQL Implementation #

StringBuffer sb = new StringBuffer();
sb.append("from Hardwarekomponente h ");
sb.append("left join fetch h.hwCategory4 c4 ");
sb.append("left join fetch c4.hwCategory3 c3 with upper(c3.hwCategory3) like :cat3 ");
sb.append("left join h.konto k with k.sisnetConfigSwitch = 1 ");
sb.append("where h.technischeNr is not null ");

Key Explanations: #

• Condition for c3: We add the WITH upper(c3.hwCategory3) like :cat3 clause when joining c3 itself (not when joining c4). At this point, c3 is part of the query context, so Hibernate can resolve the property reference correctly.
• Condition for konto: Since your original query implicitly joined h.konto via the WHERE clause, we now explicitly add a left join h.konto k with the WITH k.sisnetConfigSwitch = 1 condition. This ensures the filter is applied at the JOIN level instead of filtering after the join (which would eliminate rows where konto doesn't match, defeating the left join purpose).
• Fetch vs Regular Join: We keep left join fetch for c4 and c3 to maintain eager loading of those associations, while using a regular left join for konto (since we don't need to fetch its data into the Hardwarekomponente entity, just apply the join filter).

This HQL will generate exactly the target SQL you want:

• The c3 filter is added as an AND condition on the INNER JOIN V_MD_HARDWARE_CAT3 clause
• The konto filter is added as an AND condition on the LEFT OUTER JOIN V_MD_ACCOUNT clause
• Only h.technischeNr is not null remains in the WHERE clause

内容的提问来源于stack exchange，提问作者NobesInd