Getting only values instead of keys and values in JSON response in spring boot

Question

I am trying to retrieve data from Repository using an SQL query but I am getting only values instead of keys and values in JSON response when I am trying to retrieve it.

I have the following classes

Repository

@Repository
public interface ReportingDataRepository extends CrudRepository<Task, String> {
  @Query(name = "task.getReportingData")
  List<Task> getReportingData(String startDate, String endDate);
}

Controller

@RequestMapping(path = "/api/{startdate}/{enddate}")
public TaskScores getScoresDataByDate(@PathVariable("startdate") String startDate,
                                    @PathVariable("enddate") String endDate) {
  return new TaskScores((reportingDataRepository.getReportingData(startDate, endDate)));

}

Wrapper class within the controller

@Getter
@Setter
@ToString
@AllArgsConstructor
@NoArgsConstructor
@Data
public class TaskScores {
  List<Task> tasks;
}

Entity

@Getter
@Setter
@ToString
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Data

@NamedNativeQuery(
    name = "task.getReportingData",
    query = "SELECT sys.id as id, sys.name as sys, sys.eventtype, workflow_agg.subprocess_id "
            + "FROM daily_agg_counts_system_score as sys_agg "
            + "join system_eventtype as sys on sys_agg.system_id = sys.id "
            + "join system_subprocess_mapping sm on sys_agg.system_id = sm.system_id "
            + "join daily_agg_workflow_completion_score workflow_agg on sm.subprocess_id = workflow_agg.subprocess_id "
            + "join sub_process sp on sp.id = sm.subprocess_id "
            + "join process as p on p.id = sys.process_id "
            + "join flow on flow.id = p.flow_id "
            + "join program on program.id = flow.program_id "
            + "where sys_agg.datepst BETWEEN ?1 AND ?2 "
            + "order by program.id asc, p.flow_id asc, sp.id asc, sys_agg.system_id asc, sys_agg.datepst asc "
            + "limit 1"
    )

    public class Task implements Serializable{
      @Column(name = "id")
      @Id
      private String id;

      @Column(name = "system")
      private String sys;

      @Column(name = "event_type")
      private String eventType;

      @ManyToOne(fetch = FetchType.LAZY)
      private SubProcess subprocessId;

      @Embeddable
      class Score implements Serializable {
      @JsonIgnore
      String score;
    }
 }

I am using Spring Data JPA. When I am running this code I am getting the following JSON back

{ "tasks": [ [ 1, "oms", "BE", 1 ] ] }

but I want it to be like actual json { "tasks": [ [ "id" : 1, "sys" : "oms", "eventType" : "BE", "subprocessId" : 1 ] ] }

Any idea where I am going wrong?

dxjuv · Accepted Answer · 2020-03-13 04:44:23Z

Yes, you are using a native query and you are trying to store the result in an object by mapping the result of the query with your object. Unfortunately, it does not work that way.

If you try to use result of a native query in an Object, you will only get values instead of keys and values. To get both keys and values you have two options:

One way to do it is SQLResultsetMapping. See SQLResultsetMapping to understand more.

Another, easy way is to use a Projection interface.

First change this line in your query:

SELECT sys.id as id, sys.name as sys, sys.eventtype, workflow_agg.subprocess_id

to

SELECT sys.id as id, sys.name as sys, sys.eventtype as eventtype, workflow_agg.subprocess_id as subprocessid

i.e. give alias to all of your columns you retrieved.

And then you create an interface like this (This is called a projection interface):

interface getSysProperties{
    getid();
    getsys();
    geteventtype();
    getsubprocessid();
}

And then instead of retrieving your SQL result in an object, retrieve it in your interface projection like:

  @Query(name = "task.getReportingData")
  List<getSysProperties> getReportingData(String startDate, String endDate);
        // ^Projection interface instead of Entity

This method will work if you only want to retrieve your SQL data and then not modify it in your code afterwards.

Ulug'bek · Accepted Answer · 2020-03-13 05:23:40Z

1

In your case I would create a TaskDto class:

package your.package.dto;
@Data
@AllArgsConstructor
public class TaskDto implements Serializable { 
    // all fields 
}

than, I would use query like below

SELECT new your.package.dto.TaskDto (sys.id, sys.name, sys.eventtype, workflow_agg.subprocess_id)
FROM ...

Note that:

Inside the query is not used as;
Query must not by native;

answered Mar 13, 2020 at 5:23

Ulug'bek

2,8426 gold badges35 silver badges62 bronze badges

Collectives™ on Stack Overflow

Getting only values instead of keys and values in JSON response in spring boot

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related