Hibernate Query Language error using 'is null'

Question

I'm using following Hibernate query and getting error. Product has one To Many relationship with tweeter so type for p.tweets is "List" .

Query:

@NamedQuery(
    name="getAllProductsWithNoTweets",
    query="From Product p where p.tweets is null"
)

Error is:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'is'.

JB Nizet · Accepted Answer · 2013-06-28 16:39:31Z

2

The query makes no sense. A OneToMany will never be null. It might be empty, but not null.

If you want to get all the products without any tweet, the query should be something like

select p from Product p where p.tweets is empty

answered Jun 28, 2013 at 16:39

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

digitaljoel · Accepted Answer · 2013-06-28 16:41:16Z

1

Since p.tweets is a collection you might want to try is empty

Another thought is that you probably have to do a left join because if there are no tweets and you are joining on the tweet table then you simply won't get that product back in the results.

select p
from Product p
    left join p.tweets t
having count(t) = 0

answered Jun 28, 2013 at 16:41

digitaljoel

26.6k15 gold badges94 silver badges116 bronze badges

Collectives™ on Stack Overflow

Hibernate Query Language error using 'is null'

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related