YouYou YouYou - 1 year ago 40
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


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

select ov from OrganizationVideo ov where ( is null OR

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

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

Generated SQL statment :

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

Answer Source

If you want to refer to 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 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.