Andrunevchyn

Andrunevchyn


November 2016
M T W T F S S
 123456
78910111213
14151617181920
21222324252627
282930  

Categories


JPQL: self join

Andriy AndrunevchynAndriy Andrunevchyn

Assume we have following classes

class Storage{
@OneToMany(mappedBy = "storage", cascade = CascadeType.ALL, targetEntity = Fruit.class, fetch = FetchType.LAZY)

List fruits;

}

class Fruit{

String name;

String owner;
@ManyToOne
@JoinColumn(name = "STORAGE_ID")

Storage storage;

}

What you have to do if you would like find all storages with apples which belongs to owners with name “Jhon”

In sql it looks like

SELECT c.*
FROM Fruit as f1
join Fruit as f2 on f1.STORAGE_ID=f2.STORAGE_ID
where f1.NAME ='apple'
and f2.owner LIKE'%Jhon%'

In jpql

select f1.storage FROM Fruit as f1, Fruit as f2 where f1.storage.id=f2.storage.id and f1.name=':name' AND f2.owner like concat('%',:owner,'%')

Pay attention on trick with function CONCAT

Comments 0
There are currently no comments.