Return Data of two tables using mysql join as one java Object in Spring boot

Question

CPT Table

@Entity
@Table(name = "CPT")
public class CPTCodeset {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "Id")
    private int id;

    @Column(name = "Code")
    private String code;

    @Column(name = "Description")
    private String desc;
//getters and setters
}

CPT Procedures Table

@Entity
@Table(name = "cptprocedures")
public class CPTProcedures {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "Id")
    private int Id;

    @OneToOne(fetch=FetchType.EAGER)
    @JoinColumn(name = "ProcedureId")
    private Procedure procedure;
    @Transient
    private int procedureId;

    @OneToOne(fetch=FetchType.EAGER)
    @JoinColumn(name = "CPTCodeSet")
    private CPTCodeset cpt;
    @Transient
    private int cptCodeSet;
//getters and setters
}

CPTRepository

@Query(nativeQuery = true, value = "SELECT cp.ProcedureId,c.Code,c.Description FROM cptprocedures cp JOIN CPT c ON cp.CPTCodeSet = c.Id where c.Description like \"%ane\";")
List<CPTCodeset> findBySearch(@Param("searchString") String searchString);

This Query Returning Data is what I want. But how can I map this data into a single object where as it includes 2 tables.

How can I do it by using @Entity without creating a table. Any suggestions?

Could you show what you have attempted so far? Also it would be a good idea to show the Entity you are trying to map to — Brendon Randall
– Brendon Randall, Commented Feb 19, 2020 at 7:52

Youcef LAIDANI · Accepted Answer · 2020-02-19 08:10:34Z

6

@Query("SELECT new com.dto.ProduceCPT(cp.ProcedureId, c.Code, c.Description) FROM cptprocedures cp JOIN CPT c ON cp.CPTCodeSet = c.Id where c.Description like \"%ane\" ")
List< ProduceCPT> fetchProduceCPT();

edited Feb 19, 2020 at 8:10

Youcef LAIDANI

60.3k21 gold badges111 silver badges178 bronze badges

answered Feb 19, 2020 at 8:05

Fereydoun Gholami

1246 bronze badges

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

6 Comments

RaviChandra T Over a year ago

So this ProduceCPT is acts like a Wrapper class right

Fereydoun Gholami Over a year ago

ProduceCPT is a wrapper class

Fereydoun Gholami Over a year ago

be sure of your constructor plz refer to this link: smarterco.de/spring-data-jpa-query-result-to-dto

RaviChandra T Over a year ago

The Description is TEXT type in mySql Data Table

RaviChandra T Over a year ago

I have created Parameterized constructor but then also it is saying same cptprocedures not mapped

|

Adrian Lagartera · Accepted Answer · 2020-02-19 08:05:08Z

0

You're doing it wrong if you are using JPA and still thinking in terms of tables first. The whole point of JPA is to have SQL generated from objects so you can pretend that the relational model doesn't exist

For this example you can use a projection interface

That can solve your problem, you doing your projection, and later set the information into a WrapperObject. But you cant do a Wrapper in your JPARepository, if that wrapper doesn't exists in your Database.

answered Feb 19, 2020 at 8:05

Adrian Lagartera

5061 gold badge7 silver badges21 bronze badges

1 Comment

RaviChandra T Over a year ago

Yeah Thats what i'm thinking but i didn't getting idea about how to do it

Oleksii Valuiskyi · Accepted Answer · 2020-02-19 11:39:24Z

0

You should replace desc field name. It’s better not to use JPA reserved words

@Entity
@Table(name = "CPT")
public class CPTCodeset {
    //fields

    @Column(name = "Description")
    private String description; 

    //getters and setters
}

To get query result as List<Object[]>

@Query("select cp.procedure.id, c.code, c.description "
         + "from CPTProcedures cp join cp.cpt c "
         + "where c.description like :searchString")
List<Object[]> findBySearch(@Param("searchString") String searchString);

or create DTO class with appropriate constructor SearchResultDto(int, String, String) and use this

@Query("select new yourproject.dto.SearchResultDto(cp.procedure.id, c.code, c.description) "
         + "from CPTProcedures cp join cp.cpt c "
         + "where c.description like :searchString")
List<SearchResultDto> findBySearch(@Param("searchString") String searchString);

Repository usage:

// description contains searchString  
List<SearchResultDto> dtos1 = repository.findBySearch("%" + searchString + "%");

// description start with searchString
List<SearchResultDto> dtos2 = repository.findBySearch("%" + searchString); 

// description end with searchString
List<SearchResultDto> dtos3 = repository.findBySearch(searchString + "%");

edited Feb 19, 2020 at 11:39

answered Feb 19, 2020 at 11:22

Oleksii Valuiskyi

2,8711 gold badge10 silver badges24 bronze badges

1 Comment

RaviChandra T Over a year ago

I tried with DTO but is showing error like cptprocedures is not mapped

Collectives™ on Stack Overflow

Return Data of two tables using mysql join as one java Object in Spring boot

3 Answers 3

6 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related