JPA Repository : inner join with null value

Question

I am using JPA repository to access Mysql. I have this query :

@Query("Select s.productName, s.stock from Sell s");

I am getting an execution error because stock is sometime null.
The generated sql translation of this query is :

select sell0_.productName, sell0_.stock_id from T_SELL sell0_ inner join T_STOCK stock0_ on sell0_.stock_id=stock0_.id

Is there a way to make this JPA query work even if stock_id is null?

How have you defined Sell - Stock relation? – Ricardo Vila Jan 12 '16 at 15:17 — Ricardo Vila, Jan 12 '16 at 15:17
in Sell : @OneToOne private Stock stock; – user1260928 Jan 13 '16 at 08:42 — user1260928, Jan 13 '16 at 08:42

score 3 · Answer 1 · answered Jan 13 '16 at 08:39

3

Should be able to coalesce

@Query("Select s.productName, coalesce(s.stock,'xxx') from Sell s");

Full man is https://docs.jboss.org/hibernate/orm/4.3/devguide/en-US/html/ch11.html

Hope this helps.

answered Jan 13 '16 at 08:39

Essex Boy

7,565
2
21
24

score 1 · Answer 2 · edited May 23 '17 at 12:04

1

Try to change relation definition, using optional = true:

@OneToOne(optional = true)
private Stock stock;

You can even change how this data should be fetched, using a join, a subselect or with a different select. More info here: JPA eager fetch does not join

edited May 23 '17 at 12:04

Community

1
1

answered Jan 13 '16 at 09:09

Ricardo Vila

1,626
1
18
34

JPA Repository : inner join with null value

2 Answers2