Search code examples
jpaspring-data-jpa

JPA Native Query across multiple tables

I have the following defined as a native query in a repository (dispenseRepository):

@Query(
    value = "SELECT p.*, c.*, s.*, d.* from patient p, consult c ,script s,dispense d "
            + " where p.patient_id=c.patient_id "
            + " and c.consult_id = d.consult_id " 
            + " and c.fk_script_id =s.script_id"
            + " and c.consult_id=?1 ", 
    nativeQuery = true
)
List<Dispense> findInvoiceByConsultId(Long consultId);

The Rest Controller has:

@RequestMapping(value = "/api/invoice/{consultId}",method = {RequestMethod.GET}) 
public List<Dispense> invoice(@PathVariable(value="consultId")Long consultId){
    return dispenseRepository.findInvoiceByConsultId(consultId);
}

When I hit the api, I only get dispense details:

[
    {
        "dispenseId": 1,
        "icd10": "J.10",
        "tariffCode": "10010",
        "dispenseItem": "Lenses",
        "price": 400.0
    },
    {
        "dispenseId": 3,
        "icd10": "J.10",
        "tariffCode": "111000",
        "dispenseItem": "Other",
        "price": 1500.0
    },
    {
        "dispenseId": 4,
        "icd10": "K.100",
        "tariffCode": "10010",
        "dispenseItem": "Eye Test",
        "price": 550.0
    }
]

I'd like all the data as per query which will be used for Jasper report.

ERD

patient-consult 1-M

consult-script 1-1

consult-dispense 1-M
Solution

  • Since in your query you return all fields from all tables: SELECT p.*, c.*, s.*, d.* from patient p, consult c ,script s,dispense d creating projections/DTOs for so many objects and fields is very cumbersome. There are 2 ways to proceed. Either specify exactly the fields you want from each table in your query and create a DTO to hold those fields. e.g.

    Approach 1:

    I chose only one field from each table to make it as example. Please not that you have to convert your query from native to jpa one!

    @Query("SELECT new com.example.demo.ResultDTO(p.patientName, c.reservationNumber, s.addition, d.dispenseItem) from Patient p, Consult c, Script s, Dispense d ...")
List<ResultDTO> findInvoiceByConsultId(Long consultId);

    and ResultDTO class can be:

    package com.example.demo;

public class ResultDTO {

    private String patientName;
    private String reservationNumber;
    private String addition;
    private String dispenseItem;

    public ResultDTO(String patientName, String reservationNumber, String addition, String dispenseItem) {
        this.patientName = patientName;
        this.reservationNumber = reservationNumber;
        this.addition = addition;
        this.dispenseItem = dispenseItem;
    }

    public String getPatientName() {
        return patientName;
    }

    public void setPatientName(String patientName) {
        this.patientName = patientName;
    }

    public String getReservationNumber() {
        return reservationNumber;
    }

    public void setReservationNumber(String reservationNumber) {
        this.reservationNumber = reservationNumber;
    }

    public String getAddition() {
        return addition;
    }

    public void setAddition(String addition) {
        this.addition = addition;
    }

    public String getDispenseItem() {
        return dispenseItem;
    }

    public void setDispenseItem(String dispenseItem) {
        this.dispenseItem = dispenseItem;
    }
}

    UPDATE Approach 1 won't work with a nativeQuery, you have to convert it to jpa one so unless you convert your query to jpql, the above code wont work.

    OR the much easier but bulkier, keep the query as is and place the result on a List of Maps.

    Approach 2:

    @Query(
        value = "SELECT p.*, c.*, s.*, d.* from patient p, consult c ,script s,dispense d "
                + " where p.patient_id=c.patient_id "
                + " and c.consult_id = d.consult_id " 
                + " and c.fk_script_id =s.script_id"
                + " and c.consult_id=?1 ", 
        nativeQuery = true
)
List<Map<String, Object>>  findInvoiceByConsultId(Long consultId);