How to do the mappings for joins with OR conditions using Hibernate

Question

I am trying to do the mappings and trying to write a non-native HQL query by joining the 4 tables. The logic is written in stored procedure, but we want to migrate it to hibernate/JPA. I am unable to do proper mappings and write a query to create same logic

FROM
[dbo].[vwInstitutionUser]AS IU
INNER JOIN [dbo].[vwCommonCourse] AS C
ON C.CustomerID=CAST(IU.InstitutionID AS VARCHAR(10))
INNER JOIN [dbo].[vwCommonCourses] AS CM
ON C.CourseID = CM.CourseID
INNER JOIN dbo.vwProductMaster AS PM
ON CM.LearningActivityID = PM.ResourceID
OR CM.AssessmentID = PM.AssessmentID
WHERE
IU.UserID = @UserID 
AND PM.IsCert = CASE WHEN @SubReportID='MACS' THEN PM.IsCert ELSE 
@IsCert END
ORDER BY PM.IsCert, PM.ResourceName

Please let me know if anybody has same use case, Thanks!

Answer 1

I m not sure if I undrestand you correctly, but i would recommend you the following actions:

• Each inner join can be mapped as a relation within JPA (@OneToMany, @OneToOne, @ManyToOne,@ManyToMany)
• Ordering can be implemented with Comparable interface on entity level
• For the computed values i would suggest to use sql computed value or service layer within your business logic which will provide you those values (@Transient field)
• You can also write NativeQuery for you model

Hope that i answered at least some of your questions :)