DAO How to map a field returned by SQL function?

Viewed 237

I am using dropwizard framework for my APIs. I have a named query as below :

@NamedQuery(name = "com.myapp.entity.MyEntity.findByMatchId", query = "SELECT test.name, count(test) as count FROM MyEntity as test,Entity1 as d where test.drug=d.id and test.drug.id= :drugId group by test.name") })

I am returning the result of count(test) function as name count. Below is my Entity class :

@NamedQuery(name = "com.myapp.entity.MyEntity.findByMatchId", query = "SELECT test.name, count(test) as count FROM MyEntity as test,Entity1 as d where test.drug=d.id and test.drug.id= :drugId group by test.name") })

@Entity
@Table(name = "my_entity")
@NamedQueries({
        @NamedQuery(name = "com.myapp.entity.MyEntity.findByMatchId", query = "SELECT test.name, count(test) as count FROM MyEntity as test,Entity1 as d where test.drug=d.id and test.drug.id= :drugId group by test.name") }))
public class MyEntity {

        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;

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

        @JsonBackReference("drug_id")
        @ManyToOne(fetch = FetchType.LAZY)
        @JoinColumn(name = "drug_id", nullable = false)
        private Drug drug;

        @Column(name = "timestamp", nullable = false)
        private Date timestamp;

        // This value is not getting mapped 
        private Long count;

        public Long getId() {
            return id;
        }

        public void setId(Long id) {
            this.id = id;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public Drug getDrug() {
            return drug;
        }

        public void setDrug(Drug drug) {
            this.drug = drug;
        }

        public Date getTimestamp() {
            return timestamp;
        }

        public void setTimestamp(Date timestamp) {
            this.timestamp = timestamp;
        }

        public SideEffectSeverity getSideEffectSeverity() {
            return sideEffectSeverity;
        }

        public void setSideEffectSeverity(SideEffectSeverity sideEffectSeverity) {
            this.sideEffectSeverity = sideEffectSeverity;
        }

        public Long getCount() {
            return count;
        }

        public void setCount(Long count) {
            this.count = count;
        }

    }

What can I do here to get correct mapping with the value of count function as count.

Why is my mapping not happening correctly, Do I have to give it some annotation?

2 Answers

Have you got any result from above query?

If yes, then please note as return type of count() function will be long. So hope you have tried to map response with DTO(which should contain two fields as name & count).

Are you using OracleSQL or POSTgreSQL?

Count is a reserved word , renaming your column will fix this like this

   @Column(name = "count1")
   private Long count;
Related