How to do inner join on different db tables in springboot jpa

Question

I want to implement below query using springboot jpa,Is it possible??

    select * from 
    Claims.misuser.CLXMstApplicationRole AR WITH (NOLOCK) INNER JOIN 
    PADB.misuser.clxmstaccesspermission AP WITH (NOLOCK) ON 
    AP.intApplicationRoleId = AR.intApplicationRoleId 
    where AR.txtApplicationRoleCode = 'CRUAPP'

I have written below code in my repository Interface

@Query(value=QueryConstants.InnerJoin_AppRole_AccessPermission)
List<ApplicationRole> findBy_InnerJoin_AppRole_AccessPermission();

QueryConstants.java

 InnerJoin_AppRole_AccessPermission="select AR FROM com.infosys.claims.broadband.springboot.entity.claims.ApplicationRole AR "+
                                                                  "INNER JOIN com.infosys.claims.broadband.springboot.entity.padb.AccessPermission AP"+
                                                                  "ON AR.applicationRole = AP.applicationRoleId" + 
                                                                  "where AR.applicationRoleCode = 'CRUAPP' ";

But this gives me empty list, In mssql, it gives me records.

score 0 · Answer 1 · answered Aug 14 '19 at 10:05

You can use the query as it is.

@Query(value = "select * from 
    Claims.misuser.CLXMstApplicationRole AR WITH (NOLOCK) INNER JOIN 
    PADB.misuser.clxmstaccesspermission AP WITH (NOLOCK) ON 
    AP.intApplicationRoleId = AR.intApplicationRoleId 
    where AR.txtApplicationRoleCode = (?1)", nativeQuery = true)
List<ApplicationRole> findBy_InnerJoin_AppRole_AccessPermission(ApplicationRoleCode crudApp);

Just set the value of variable crudApp as "CRUAPP".

,nativeQuery = true) in the above code makes it very easy if u have faith on your queries!

How to do inner join on different db tables in springboot jpa

1 Answers1