Query result in JSON format (key value pair) on using @Query annotation in Spring Boot, Hibernate

Question

My Controller

@GetMapping(value="/getAllDetails")
public List<PriceListEntity> getAllDetails() {
    return MyRepository.getAllDetails();
}

My Repository

@Repository
public interface MyRepository extends CrudRepository<TestNativeQ, String> {
    @Query( value="SELECT qplt.name price_list_name,  qplab.status_code, qplab.start_date, (SELECT charge_definition_code FROM oalfsaas_repl.QP_CHARGE_DEFINITIONS_B WHERE charge_definition_id=qplab.charge_definition_id  ) chargedefinitioncode "
            + "FROM  PriceListEntity qplab, PriceListLineEntity qplt "
            + " WHERE qplab.price_list_id  =qplt.price_list_id ", nativeQuery = false)
    public List<PriceListEntity> getAllDetails();
}

Actual Result:

[{"ABC", "DEF", "15/05/2018", "XXZ"}]

Expected Result

[{name: "ABC", statuscode: "DEF", startDate: "15/05/2018", chargedefintioncode: "XXZ"}]

The Query has join with more than one table and also subquery at column level.

Answer 1

Your are actually doing a projection with your select which does not return any specific object but a tuple which is an array of objects you select in your query. Whatever way the JSON is made there are no names just values.

You need to create a DTO to hold the values you want to pass by names in your JSON.

A minimal example, having a simple entity like:

@Entity
@Getter
@RequiredArgsConstructor
public class TestClass {
    @Id
    @GeneratedValue
    private Long id;

    @NonNull
    private String a,b,c;
}

and willing to pass -for example - only a & b there might be DTO like:

@RequiredArgsConstructor
public class TupleDto {
    @NonNull
    private String a,b;
}

and in your case some sort of PriceListDetailsDto

the repository might be declared like:

public interface TestClassRepository extends CrudRepository<TestClass, Long> {

    @Query(value="SELECT new org.example.TupleDto(tc.a, tc.b) FROM TestClass tc")
    List<TupleDto> fetchAB();

}

NOTE: in above that there is used operator new and a full path to the entity constructor.

This way Spring repository knows how to assign selected fields and when making a JSON from this DTO will result having fields with names (names in DTO).

The new operator in JPQL is just calling new in java- So any row data a,b,c can be used to construct Java object with that object's class constructor accepting same parameter amount and types (and in the same order) so liie new MyEntityObject(a,b,c).

NOTE ALSO: in this simple case the original entity could have been used as DTO if it was modified to allow null value in c and adding corresponding constructor. In your case where your tuple is constructed from many tables you need to create a DTO to hold those values.