How to fetch parent and all its children with criteria using Spring Data Jpa?

Question

I have a Parent and Child entities like below -

class Parent { Long id; List<Child> children; } class Child { Long id; String status; // ACTIVE or INACTIVE Parent parent; }

I would like to fetch the parent along with all its children having status=ACTIVE property.

public interface ParentRepository<Parent, Long> { Parent findByIdAndChildStatus(@Param("id") id, @Param("childStatus") status); }

Question is -

What is the easiest way to fetch only ACTIVE children?
Is it possible to set ACTIVE query condition on the Child entity's status property to fetch only active child entities by default?
Will something like above query method work?

Mafor · Accepted Answer · 2020-09-07 17:56:03Z

See below
It's not possible with Spring annotations alone, but you can achieve it with Hibernate's @Where:

@Entity @Getter @Setter public class Parent { @Id @GeneratedValue(strategy = GenerationType.SEQUENCE) private Long id; @Where(clause = "status='ACTIVE'") @OneToMany(mappedBy = "parent", cascade = CascadeType.ALL) private List<Child> children; }

Your query method would not work, Spring Data wouldn't understand the method name. To make it work, the Child entity would need to be in the plural form:

public interface ParentRepository extends CrudRepository<Parent, Long> { Parent findByIdAndChildrenStatus(Long id, String status); }

Unfortunately, the meaning of the method would be a bit different than expected. It would mean: get Parent with the id id and having a Child with the status status. The generated query looks as follows:

SELECT parent0_.id AS id1_1_ FROM parent parent0_ LEFT OUTER JOIN child children1_ ON parent0_.id=children1_.parent_id WHERE parent0_.id=? AND children1_.status=?

Collectives™ on Stack Overflow

How to fetch parent and all its children with criteria using Spring Data Jpa?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related