JPA query parent with only selected children

user_3961428 0
Viewed 1731

I have these to classes Parent and Child

Parent.java

@Entity
@Table(name = "parents")
public class Parent implements Serializable {

private static final long serialVersionUID = 873222010704108510L;

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;

private String name;

@OneToMany(cascade=CascadeType.ALL)
@JsonManagedReference
private Set<Child> childs;

// getters and setters

Child.java

@Entity
@Table(name = "childs")
public class Child implements Serializable {

private static final long serialVersionUID = -6756632049453970501L;

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;

private String name;

@ManyToOne(fetch = FetchType.LAZY)
@JsonBackReference
private Parent parent;

// getters and setters

Now my SQL DB has 2 children, child1 and child2 and they both have the same parent.

Now I want to get the parent and only child1 so in my ParentRepository.java I have the following

@Query("select p from Parent p LEFT JOIN p.childs c where c.name = 'child1'")
public List<Parent> getParent();

But when I call this I get back both children, this is because I see hibernate running 2 queries, which are below:

Query 1 (the query I want)

/* select
    p 
from
    Parent p 
LEFT JOIN
    p.childs c 
where
    c.name = 'child1' */ select
        parent0_.id as id1_1_,
        parent0_.name as name2_1_ 
    from
        parents parent0_ 
    left outer join
        parents_childs childs1_ 
            on parent0_.id=childs1_.parent_id 
    left outer join
        childs child2_ 
            on childs1_.childs_id=child2_.id 
    where
        child2_.name='child1'

Then it runs this query - which I dont want cause it is getting all the children

 select
    childs0_.parent_id as parent_i1_2_0_,
    childs0_.childs_id as childs_i2_2_0_,
    child1_.id as id1_0_1_,
    child1_.name as name2_0_1_,
    child1_.parent_id as parent_i3_0_1_ 
from
    parents_childs childs0_ 
inner join
    childs child1_ 
        on childs0_.childs_id=child1_.id 
where
    childs0_.parent_id=?

How can I get it so the 2nd query is not run and I get the parent and only the child I want returned, I also want to return the parent even if there is no matching child which is why I have the left outer join

jpa hibernate mysql java
1 Answers

Option 1

Think in the other way, how about search on Child and get their Parent.

Add new constructors into the Parent class

public Parent(Integer id, String name, Child child) {
    this.id = id;
    this.name = name;
    this.childs = new HashSet<>();
    this.childs.add(child);
}

public Parent() {
}

Change JPA query with below script.

@Query("select new Parent(c.parent.id, c.parent.name, c) from Child c where c.name = 'child1'")
List<Parent> getParent();

Hibernate generates 3 SQLs for two Child record (More one query of all Child count)

Hibernate: select child0_.parent_id as col_0_0_, parent1_.name as col_1_0_, child0_.id as col_2_0_ from childs child0_ cross join parents parent1_ where child0_.parent_id=parent1_.id and child0_.name='child1'
Hibernate: select child0_.id as id1_0_0_, child0_.name as name2_0_0_, child0_.parent_id as parent_i3_0_0_ from childs child0_ where child0_.id=?
Hibernate: select child0_.id as id1_0_0_, child0_.name as name2_0_0_, child0_.parent_id as parent_i3_0_0_ from childs child0_ where child0_.id=?

If you execute only one SQL you can upgrade Parent class and Jpa query below.

Parent constructor:

public Parent(Integer id, String name, Integer childId, String childName) {
    this.id = id;
    this.name = name;
    this.childs = new HashSet<>();
    Child child = new Child();
    child.setId(childId);
    child.setName(childName);
    this.childs.add(child);
}

Added new arguments into the jpa query

@Query("select new Parent(c.parent.id, c.parent.name, c.id, c.name) from Child c where c.name = 'child1'")
List<Parent> getParent();

Hibernate generates a single query.

Hibernate: select child0_.parent_id as col_0_0_, parent1_.name as col_1_0_, child0_.id as col_2_0_, child0_.name as col_3_0_ from childs child0_ cross join parents parent1_ where child0_.parent_id=parent1_.id and child0_.name='child1'

Test

@SpringBootTest
class DemoApplicationTests {

    @Autowired
    private ParentRepository parentRepository;
    
    @Test
    void contextLoads() {
        Parent parent = new Parent();
        parent.setName("p1");
        parent.setChilds(new HashSet<>());
        parent.getChilds().add(new Child("child1", parent));
        parent.getChilds().add(new Child("child2", parent));

        parentRepository.save(parent);

        parent = new Parent();
        parent.setName("p2");
        parent.setChilds(new HashSet<>());
        parent.getChilds().add(new Child("child1", parent));

        parentRepository.save(parent);

        List<Parent> parents = parentRepository.getParent();
        Assertions.assertEquals(2, parents.size());

        for (Parent parent1 : parents) {
            System.out.println(parent1);
        }
    }
}

Output

Parent(id=1, name=p1, childs=[Child(id=3, name=child1)])
Parent(id=4, name=p2, childs=[Child(id=5, name=child1)])

Option 2

Fetch all children and manage Parent on java side.

public interface ChildRepository extends JpaRepository<Child, Integer> {
    @Query("select c from Child c left join fetch c.parent where c.name = 'child1'")
    List<Child> search();
}

Hibernate generates single SQL.

Hibernate: select child0_.id as id1_0_0_, parent1_.id as id1_1_1_, child0_.name as name2_0_0_, child0_.parent_id as parent_i3_0_0_, parent1_.name as name2_1_1_ from childs child0_ left outer join parents parent1_ on child0_.parent_id=parent1_.id where child0_.name='child1'

Test

@SpringBootTest
class DemoApplicationTests2 {

    @Autowired
    private ParentRepository parentRepository;

    @Autowired
    private ChildRepository childRepository;

    @Test
    void contextLoads() {
        Parent parent = new Parent();
        parent.setName("p1");
        parent.setChilds(new HashSet<>());
        parent.getChilds().add(new Child("child1", parent));
        parent.getChilds().add(new Child("child2", parent));

        parentRepository.save(parent);

        parent = new Parent();
        parent.setName("p2");
        parent.setChilds(new HashSet<>());
        parent.getChilds().add(new Child("child1", parent));

        parentRepository.save(parent);

        // search children
        List<Child> children = childRepository.search();

        // create parent hash map
        Map<Integer, Parent> parentMap = children.stream()
                .map(Child::getParent)
                .distinct()
                .peek(p -> {
                    p.setChilds(new HashSet<>());
                })
                .collect(Collectors.toMap(Parent::getId, p -> p));

        // add children manually
        for (Child child : children) {
            parentMap.get(child.getParent().getId()).getChilds().add(child);
        }

        // get parents
        Collection<Parent> parents = parentMap.values();

        Assertions.assertEquals(2, parents.size());

        for (Parent parent1 : parents) {
            System.out.println(parent1);
        }
    }
}

Output

Parent(id=1, name=p1, childs=[Child(id=3, name=child1)])
Parent(id=4, name=p2, childs=[Child(id=5, name=child1)])

Extra

Fetch all Parent and fill matched Child records. We need some changes on Parent constructor pre-defined and a new JPA query that is where is replaced with and keyword.

Parent Constructor: There is an extra childId control to avoid inserting a null child record.

public Parent(Integer id, String name, Integer childId, String childName) {
    this.id = id;
    this.name = name;
    this.childs = new HashSet<>();
    if (childId != null) {
        Child child = new Child();
        child.setId(childId);
        child.setName(childName);
        this.childs.add(child);
    }
}

ParentRepository: There is no where key, because it restricts our result, we need just and instead of it.

@Query("select new Parent(p.id, p.name, c.id, c.name) from Parent p left outer join Child c on c.parent = p and c.name = 'child2'")
List<Parent> getParent();

Hibernate Query: There is a single query just executed.

Hibernate: select parent0_.id as col_0_0_, parent0_.name as col_1_0_, child1_.id as col_2_0_, child1_.name as col_3_0_ from parents parent0_ left outer join childs child1_ on (child1_.parent_id=parent0_.id and child1_.name='child2')

Test


List<Parent> parents = parentRepository.getParent();
Assertions.assertEquals(2, parents.size());

for (Parent parent1 : parents) {
    System.out.println(parent1);
}

Output: We are searching child2 and one record has this one, other is empty.

Parent(id=1, name=p1, childs=[Child(id=2, name=child2)])
Parent(id=4, name=p2, childs=[])
user_131064950
Related