how can I apply 2 conditions in join in HQL

Question

How can I apply 2 conditions in join in HQL? I want to write HQL for below query:

Pervious HQL:

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");
        sb.append(" where h.konto.sisnetConfigSwitch = 1");
        sb.append(" and h.technischeNr is not null");
        sb.append(" and upper(c3.hwCategory3) like :cat3");

Previous SQL:

SELECT  *
FROM     HARDWAREKOMPONENTE h 
         INNER JOIN V_MD_HARDWARE_CAT4 c4 ON h.Hw_Kategorie4_Id=c4.Hardware_Cat4_Id 
         INNER JOIN V_MD_HARDWARE_CAT3 c3 ON c4.Hardware_Cat3_Id=c3.Hardware_Cat3_Id 
         LEFT OUTER JOIN V_MD_ACCOUNT k ON h.Konto_Id=k.Account_Id  
WHERE    (h.Technische_Nr is not null) and (upper(c3.Hardware_Cat3) like '%SWITCH%') and k.Sisnet_Config_Switch = 1;

But now due to some changes, I want to move 2 conditions along with join to get some output which the previous query was not giving me:

New HQL(which I am trying for, but its failing for with operator):

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

New SQL:

SELECT  *
FROM     HARDWAREKOMPONENTE h 
         INNER JOIN V_MD_HARDWARE_CAT4 c4 ON h.Hw_Kategorie4_Id=c4.Hardware_Cat4_Id 
         INNER JOIN V_MD_HARDWARE_CAT3 c3 ON c4.Hardware_Cat3_Id=c3.Hardware_Cat3_Id AND (upper(c3.Hardware_Cat3) like '%SWITCH%')
         LEFT OUTER JOIN V_MD_ACCOUNT k ON h.Konto_Id=k.Account_Id AND k.Sisnet_Config_Switch = 1
WHERE    (h.Technische_Nr is not null);

So, my question was how can I add or tell Hibernate to add conditions along with join?

Answer 1

In the first with statement you are referring to an alias which is not yet defined (c3).

I would structure the query as follows:

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