0

I have a table form_header with 3 records

enter image description here

There are more fields in the table decided not to add it here in the post since most are irrelevant. I created a class/entity to get the count with distinct for each status in sql.

@Entity()
@Table(name = "Form_Header")
@SqlResultSetMapping(name = "myMapping", 
    entities = {@EntityResult(
            entityClass = FormSummary.class, 
            fields = {@FieldResult(name = "status", column = "status"),
                    @FieldResult(name = "id", column = "header_id")})})
public class FormSummary {

@Id()
private Long id;

private String status;

<getter and setter>

with entity manager

        List<FormSummary> results  = entityManager.createNativeQuery("select DISTINCT(status), COUNT(header_id) as header_id from Form_Header where is_deleted = 0 group by status order by status", "myMapping").getResultList();
        for (FormSummary x : results) {
            System.out.println("ABC " + x.getId());
            System.out.println("ABC " + x.getStatus());
        }

Issue is the sysout is showing this

enter image description here

instead of this

status  header_id
APPROVE 1
DRAFT   1
SUBMITTED   1

Whats even weird is if I add an extra record in the table with the same status

enter image description here

I will get the correct data in my jpa

enter image description here

Am I missing something in my code or a possible bug with SqlResultSetMapping?

Arnold Cristobal
  • 843
  • 2
  • 16
  • 36
  • Are you sure that the query `select DISTINCT(status), COUNT(header_id) as header_id from Form_Header where is_deleted = 0 group by status order by status` is correct? What database do you use? Could you provide also DDL of your table. – SternK Sep 10 '20 at 05:46
  • in oracle it throws the error "not a single-group group function", can you try adding the group by status clause? – JLazar0 Sep 10 '20 at 09:51
  • its sql and yes it works when i ran that query in the db, getting the correct result – Arnold Cristobal Sep 10 '20 at 13:32

0 Answers0