CODEWITHSUNDEEP
hibernatejpajpa-2.0criteria-api
Victor Bello
Victor Bello

Reputation: 493

Criteria JPA 2, Hibernate - multiples joins

I've been trying make the following query, I tried start but I don't know from where. Considering that I have an entity for each table, how could I create this query using Criteria Builder?

SELECT   *
        FROM   TCTIPort as CTIPort        
        INNER JOIN TCTIBoardType AS BoardType                    
        ON CTIPort.BoardType = BoardType.BoardType  

        LEFT JOIN TSysPort AS Port            
        ON CTIPort.Switch = Port.Switch AND        
           CTIPort.Machine = Port.Machine AND        
           CTIPort.Port =  Port.Channel

        LEFT JOIN TSysMachineId AS Mach        
        ON Mach.IdApp = Port.IdApp AND        
           Mach.IdTypeApp = Port.IdTypeApp        

        LEFT JOIN TUnPbxGenericValue AS Gen         
        ON CTIPort.Machine = Gen.Machine AND         
           CTIPort.Port = Gen.Port        

        LEFT JOIN TUnPbxGenericName AS GName         
        ON GName.IdGenericName = Gen.IdGenericName        

        LEFT JOIN TSysTypeOfApp STA        
        ON STA.IdTypeOfApp = Port.IdTypeApp  

        LEFT JOIN TSysApplication AS SysApp 
        ON SysApp.IdApp = Port.IdApp 
        AND SysApp.idTypeOfApp = Port.idTypeApp  

        WHERE        
            LTRIM(RTRIM(BoardClass)) IN ('MSI', 'TAP') AND        
            CTIPort.Switch = @Switch AND        
            CTIPort.Machine = @Machine AND        
            (CTIPort.Port = @Port OR @Port IS NULL) AND        
            (Mach.Machine LIKE @NameMachine OR      
            Gen.GenericValue LIKE @NameMachine OR       
            @NameMachine IS NULL)      
        ORDER BY     
            CTIPort.Port

Should I use the Interface "JOIN" for each column? How I add the condition "AND" to the joins?

I searched some examples, but they show just simple queries (withou "and" and without more tables, max 2).

Upvotes: 1

Views: 114

Answers (1)

uaiHebert
uaiHebert

Reputation: 1912

JPA 2.0 has no support to "JOIN + ON".

You will need to do something like:

select p
from Person p
left join p.dogs d
where d.age = 15 and d.sex = 'MALE'

If you want to add criteria in the join clause you will need to use JPA 2.1

Upvotes: 2

Related Questions