YouYou YouYou - 4 months ago 9
Java Question

Hibernate selecting list with condition (true or something causes NPE) returns no results

I have an entity called 'OrganizationVideo' that has one-to-one entity called 'Video' that has parent entity called 'OrganizationIssue' that has boolean field called

published


A query is built to select any
OrganizationVideo
that has
video having OrganizationIssue.published=true
OR doesn't have any
OrganziationIssues
organizationIssue is null


select ov from OrganizationVideo ov where (ov.video.organizationIssue is null OR ov.video.organizationIssue.published=true)


So question is why this query returns nothing when second OR doesn't match that means that
ov.video.organizationIssue is null


If hibernate works as Java that could be valid since second operand will fire
NullPointerException


Generated SQL statment :

select organizati0_.id as organizati0_.organization from organization_video organizati0_ left outer join video video1_ on organizati0_.id=video1_.id cross join organization_issue organizati2_ where video1_.organization_issue=organizati2_.id and organizati0_.organization=? and (video1_.organization_issue is null or organizati2_.published=1) order by organizati0_.id desc

Answer

If you want to refer to ov.video in multiple places you need to do an explicit JOIN so then it uses this join for both parts of the WHERE (rather than doing a separate INNER JOIN for each). You likely would have worked this out by looking at the SQL generated from your query.

Using explicit joins you get more like

SELECT ov FROM OrganizationVideo ov LEFT OUTER JOIN ov.video v LEFT OUTER JOIN OrganisationIssues v.organisationIssues oi
WHERE (oi is null OR oi.published=true)

You could use (INNER) JOIN there depending on the precise requirement, but the point is the same ... by explicitly specifying joins you guarantee what happens.