CODEWITHSUNDEEP
springspring-bootjpa
blue-sky
blue-sky

Reputation: 53816

How to define this native query return type?

In order to count the number of items added to a DB table names url_store I use a native query with Spring JPA :

@Query(value="SELECT count(dateadded), dateadded from url_store WHERE dateadded >= ? and dateadded <= ? group by dateadded",
    nativeQuery=true)
    List<CountByDay> getAddedCountByDay(Date fromDate, Date toDate);

The CountByDay DTO object, which should be contained in the returned List:

import java.util.Date;

public class CountByDay {

    public CountByDay(Integer count, Date dateAdded){
        this.count = count;
        this.dateAdded = dateAdded;
    }

    public Integer getCount() {
        return count;
    }

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

    private Integer count;

    public Date getDateAdded() {
        return dateAdded;
    }

    public void setDateAdded(Date dateAdded) {
        this.dateAdded = dateAdded;
    }

    private Date dateAdded;

}

When I invoke the getAddedCountByDay method I receive error:

org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [CountByDay]
    at org.springframework.core.convert.support.GenericConversionService.handleConverterNotFound(GenericConversionService.java:321) ~[spring-core-5.2.8.RELEASE.jar:5.2.8.RELEASE]

It seems I have not defined the dto CountByDay correctly? How to correctly define the dto in order to return a List of CountByDay objects ?

Upvotes: 0

Views: 797

Answers (2)

blue-sky
blue-sky

Reputation: 53816

I got this working by:

1.Redefine CountByDay

import java.util.Date;

public interface CountByDay {
Date getDateAdded();
int getCnt();
}

2.Update the query

@Query(value="SELECT count(dateadded) as cnt, dateadded as dateadded from url_store WHERE dateadded >= ? and dateadded <= ? group by dateadded",
    nativeQuery=true)
    List<CountByDay> getAddedCountByDay(Date fromDate, Date toDate);

This helped: No converter found capable of converting from type to type

Upvotes: 0

Alexander Makarov
Alexander Makarov

Reputation: 886

I would suggest you to try to use constructor expression JPQL as such:

SELECT new CountByDay(count(dateadded), dateadded) from url_store WHERE dateadded >= ? and dateadded <= ? group by dateadded

Upvotes: 1

Related Questions