How to write multiple join in JPA?-CodePudding

I am running a spring boot application JPA is behaving very differently depending on the exact circumstances under which it is used. We have created a JPA query when trying to run getting converter type exception issue.

This is what i tried UHG table is not having any foreign key constraint with other tables

@Entity
@Table("uhg")
public class UHG {

    @Id
    @Column(name="uhg_no")
    private Integer uhgNo;
    private String section;
    private String area;
    private String zip;
    private String rwd;
    // getter setter
}

@Entity
@Table("section")
public class Section {

    @Id
    @Column(name="id")
    private Integer id;
    private String name;
    // getter setter
}

@Entity
@Table("area")
public class Area {

    @Id
    @Column(name="id")
    private Integer identifier;

    @ManyToOne
    @JoinColumn(name="section_id",referencedColumnName = "id")
    private Section section;
    private String name;
    //getter setter
}


@Repository
public interface UHGRepo extends JpaRepository<UHG, Integer> {

    @Query("SELECT a.area, b.id FROM UHG a JOIN Section b ON b.name=a.section LEFT JOIN Area c ON c.name=a.area AND c.section.id=b.id WHERE a.section IS NOT NULL AND a.area IS NOT NULL AND c.identifier IS NULL AND a.zip='Ye12Y'")
    List<Area> findAreaSection();
}

When calling findAreaSection() we are getting ConversionFailedException, No converter found capable of converting from Type... to other Type...

If I use List<Object[]> findAreaSection(); the query is returning the result.

I believe there is an issue in writing query may be I have not written the join in proper way.

CodePudding user response：

You are trying to convert the select to another entity that you don´t have. You need to create a DTO (or POJO) for this query and modify your query.

@Repository
public interface UHGRepo extends JpaRepository<UHG, Integer> {

    @Query("SELECT new set.your.package.here.QueryResponse(a.area, b.id) FROM UHG a JOIN Section b ON b.name=a.section LEFT JOIN Area c ON c.name=a.area AND c.section.id=b.id WHERE a.section IS NOT NULL AND a.area IS NOT NULL AND c.identifier IS NULL AND a.zip='Ye12Y'")
    List<QueryResponse> findAreaSection();
}

You will create this object (QueryResponse for my example) and this object should have a constructor. You need to set the parameters with the required data type in the constructor.

More info here: https://www.baeldung.com/jpa-queries-custom-result-with-aggregation-functions

CodePudding user response：

why not just return c ?

@Query("SELECT Distinct c FROM UHG a JOIN Section b ON b.name=a.section LEFT JOIN Area c ON c.name=a.area AND c.section.id=b.id WHERE a.section IS NOT NULL AND a.area IS NOT NULL AND c.identifier IS NULL AND a.zip='Ye12Y'")
    List<Area> findAreaSection();

CodePudding user response：

You must to use @nativeQuery, when you call native query you can write you sql query directly above de method