Spring Data JPA find by nested object id (nested twice)

Question

Is it possible?

I'd like to get all AAA objects with a specific CCC.incidentAssessmentResultId id using JPARepository. Is it possible?

   @Entity
    @Table(schema = "aaa", name = "table")
    public class AAA {

        @Column(name = "kryterium")
        private String criterion;

        @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
        @JoinColumn(name = "id_kryterium_naruszen")
        private List<BBB> violationFactors;

    }

    public class BBB {

        @Column(name = "czynnik")
        private String factor;

        @Column(name = "stopien")
        private float degree;

        @JsonManagedReference
        @OneToOne(mappedBy = "violationFactor")
        private CCC incidentAssessmentFactor;
}

public class CCC {

    @Column(name="komentarz")
    private String comment;

    @Column(name="ocena_naruszenia_wynik_id", updatable=false, insertable=false)
    private Long incidentAssessmentResultId; //-> I'd like to find AAA objects with a specific incidentAssessmentResultId ID

    @Column(name="czynnik_wybrany")
    private Boolean factorIsSelected;

    @Column(name = "wartosc_wybrana")
    private float value;


    @Repository
    public interface ViolationCriterionRepository extends JpaRepository<AAA, Long> {

    //  @Query("select vc from AAA vc left join vc.violationFactors vf left join vf.incidentAssessmentFactor iaf where iaf.incidentAssessmentResultId = ?1")
        List<AAA> findByViolationFactors_IncidentAssessmentFactor_IncidentAssessmentResultId(Long incidentId);
    }

Now, when I call ViolationCriterionRepository .findAll() I get all data but I want to get all data but with certain CCC objects. I've tried with the method below in my Repository but I get 0 results.

UPDATE

My repo:

@Repository
public interface ViolationCriterionRepository extends JpaRepository<ViolationCriterion, Long> {

    @Query("select vc from AAA vc join vc.violationFactors vf join vf.incidentAssessmentFactor iaf where iaf.incidentAssessmentResultId = ?1")
    List<AAA> findByIncidentAssessmentResultId(Long incidentId);
}

Uncomment your query, and choose a readable name for your method. That should be sufficient (assuming ViolationCriterion and AAA are the same thing). I would use inner joins rather than left joins though. — JB Nizet
– JB Nizet, Commented Jun 2, 2019 at 19:15
Unfortunately error: Caused by: org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.IdentNode \-[IDENT] IdentNode: 'cv' {originalText=cv} — Matexon
– Matexon, Commented Jun 2, 2019 at 19:21
You're using select cv, and then from ViolationCriterion vc. Be consistent. It's either vc, or cv. — JB Nizet
– JB Nizet, Commented Jun 2, 2019 at 19:23
That simply means that no row exists in your database that matches your criteria. — JB Nizet
– JB Nizet, Commented Jun 2, 2019 at 19:28

PeMa · Accepted Answer · 2019-06-02 20:56:35Z

6

In the AAAJpaRepository:

List<AAA> aaaList = findByViolationFactorsIncidentAssessmentFactorIncidentAssessmentResultId( long incidentAssessmentResultId);

answered Jun 2, 2019 at 20:56

PeMa

1,75622 silver badges51 bronze badges

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

3 Comments

Matexon Over a year ago

Works too! But how to add groupBy clause in this case? With query the solution is @Query("select vc from AAA vc join vc.violationFactors vf join vf.incidentAssessmentFactor iaf join iaf.incidentAssessment ia where ia.incidentAssessmentId = ?1 group by vc ")

PeMa Over a year ago

You can at grouping at the end of the query string. See the spring jpa reference for examples

PeMa Over a year ago

Yeah me neither .. sorry for that. I think I confused it with order by. However, you can do the grouping in memory. I would anyways recommend changing the return value to a stream. Than (or after streaming the list) you can use java streams groupingBy. Note that you have to close the stream manually or better use it inside a try with block.

Matexon · Accepted Answer · 2019-06-02 20:48:34Z

2

And the answer is (@JB Nizet - many thanks!):

@Repository
public interface ViolationCriterionRepository extends JpaRepository<ViolationCriterion, Long> {

    @Query("select vc from AAA vc join vc.violationFactors vf join vf.incidentAssessmentFactor iaf join iaf.incidentAssessment ia where ia.incidentAssessmentId = ?1 group by vc ")
    List<ViolationCriterion> findIncidentAssessmentByIncidentAssessmentId(Long incidentId);
}

answered Jun 2, 2019 at 20:48

Matexon

2,2868 gold badges47 silver badges94 bronze badges

Collectives™ on Stack Overflow

Spring Data JPA find by nested object id (nested twice)

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related