JPA executes part of the request if value is not null

Question

in Java, the following: this is a common idiom:

if( null != obj && obj.getSomeNumber() > 0 ) { ... } 

This will only perform a length check if null! = Obj is true. However, in JPA NamedQuery this does not work:

 @NamedQuery( name = "query" query = "SELECT o FROM SomeObjectList o WHERE o.someObject is not null AND o.someObject.someNumber > 0") 

(This is what I expected, as it also does not work in SQL.)

o.someObject is either "null" or the foreign key to the table where SomeObjects are stored. (Each column of the table corresponds to the SomeObject attribute.)

 ------------------------- ---------------------------- | Table: SomeObjectList | | Table: SomeObject | ------------------------- ---------------------------- | id | someObject | | id | number | name | ------------------------- ---------------------------- | 1 | 4 | | 3 | -4 | foo | ------------------------- ---------------------------- | 2 | null | | 4 | 2 | bar | ------------------------- ---------------------------- 

So, I would like to create a NamedQuery that will return all objects from SomeObjectList that either do not have an object (someObject == null) or where SomeObject.number> 0. Currently I get all the objects and check if any an object.

But is there a way to get this behavior in JPA or do I need to do checks on returned objects?

edit: added graphics and clarified the problem. (Thanks to James.)

Answer 1

For this you need to use an external connection.

Note that your request is incorrect, you need the OR not to be AND, otherwise just delete the null check.

 SELECT o FROM SomeObjectList o left join o.someObject so WHERE o.someObject is null OR so.someNumber > 0 

Answer 2

Not sure what I understand?

You don’t even need to be “not null”, just a length> 0 will not return true for null?

Also your code is incorrect, length is a function, not navigation.

Must be

 SELECT o FROM SomeObject o WHERE LENGTH(o.arry) > 0 

Or is there a collection relationship? In this case, you will need to use SIZE, and the connection will also filter for missing relationships.