@NamedNativeQuery - How can I bind it to a repository method?

Question

I am using Spring + Hibernate and I have a particular case where I need to obtain (a list of) non-Entity objects as a result of the query.

I decided to use @ConstructorResult in @SqlResultSetMapping and refer to this mapping in @NamedNativeQuery, as mentioned here and here.

However, in all examples using named native queries, they obtain EntityManager instance via @PersistenceContext and call createNativeQuery on it, providing the name of @NamedNativeQuery as parameter to that call, as seen in this answer.

How can I map a method declared in a repository interface to a particular @NamedNativeQuery? My attempt was to use EntityName.MethodNameInRepository or MethodNameInRepository as the name of @NamedNativeQuery, but no luck.

Here is my simplified code:

@Entity(name = "AdDailyData")
@SqlResultSetMapping(
        name="RevenueByAppAndDayMapping",
        classes=@ConstructorResult(
                targetClass=RevenueByAppAndDay.class,
                columns={@ColumnResult(name="country_code"),
                        @ColumnResult(name="revenue", type=Double.class),
                        @ColumnResult(name="currency")}))
@NamedNativeQuery(
        name="AdDailyData.aggregateRevenue",
        query="SELECT country_code, sum(earnings) as revenue, currency "
                + "FROM ad_daily_data, pseudo_app, app "
                + "WHERE ad_daily_data.pseudo_app_id=pseudo_app.id AND pseudo_app.app_id=app.id AND app.id=:appId and ad_daily_data.day = :day "
                + "GROUP BY country_code, currency "
                + "ORDER BY country_code ASC",
        resultSetMapping="RevenueByAppAndDayMapping")
public class AdDailyDataEntity {

    // fields, getters, setters etc.

    public static interface Repository extends JpaRepository<AdDailyDataEntity, Long> {

        public List<RevenueByAppAndDay> aggregateRevenue(@Param("appId") long appId, @Param("day") LocalDate day);

    }

}

Here is my non-Entity class.

public class RevenueByAppAndDay {

    private String countryCode;
    private Double earnings;
    private String currency;

    public RevenueByAppAndDay(String countryCode, Double earnings, String currency) {
        this.countryCode = countryCode;
        this.earnings = earnings;
        this.currency = currency;
    }

    public String getCountryCode() {
        return countryCode;
    }

    public Double getEarnings() {
        return earnings;
    }

    public String getCurrency() {
        return currency;
    }

}

Any kind of help is highly appreciated.

EDIT: The end of the stack trace is as follows:

Caused by: org.springframework.data.mapping.PropertyReferenceException: No property aggregateRevenue found for type AdDailyDataEntity!

How would aggregateRevenue be called from a service? I've tried autowiring AdDailyDataEntity and calling adDailyDataEntity.aggregateRevenue but I get an error that the method is not defined. — Micho Rizo, Mar 11 '18 at 06:42
Sorry, i meant how do I call from repository. Trying to extend crudrepository with method List aggregateRevenue(...) but get no property found. I'll start up another question — Micho Rizo, Mar 11 '18 at 16:05
@ram. Thank you for your detailed question. It helped me to learn and write JPA named query with ease. — Mahesh Vemula, Mar 24 '21 at 11:11

score 18 · Accepted Answer · edited Jul 03 '17 at 14:35

18

The name value on the @NamedNativeQuery needs to be set to "AdDailyDataEntity.aggregateRevenue". The first part (before the dot) needs to match the entity class name.

edited Jul 03 '17 at 14:35

ram

1,099
1
7
22

answered Jul 03 '17 at 12:16

Grant Lay

800
7
17

This is not working for me _"Errors in named queries: CashFlowDetail.findCashFlowDetails"_ – Javier Heisecke Jul 26 '19 at 16:53

score -1 · Answer 2 · answered Oct 01 '19 at 15:33

-1

Although the answer is correct, for someone to arrived here for similar cuestions this works for me without used @NamedNativeQuery:

public class Example {

private Long someProperty;
// getters and setters
}

To call a query or stored procedure and populate my object:

@Repository
public interface ExampleRepository extends 
JpaRepository<Example,Long> {

/**
 * Search Example by someProperty
 *
 * @param property
 * @return
 */
@Query( nativeQuery = true, value = "SELECT * FROM public.my_stored_procedure(:property)")
List<Example> findByProperty(@Param("property") Long property);

}

I know this is another approach and the code is decoupled, but we get the same result.

I hope that can be useful for someone. Regards.

answered Oct 01 '19 at 15:33

hizmarck

686
9
19

This approach won't work unless `Example` is a managed entity which it's not (according to the question). – io-german Oct 14 '19 at 06:39
1

Hi, io-german, I've mentioned this approach because I've used it. My entity is not managed. In the beginning, I thought using a SQL View, but my requirement didn't let me, then I've created a store procedure and then populate a DTO object. Have you had the opportunity to try it? With much respect, I have put that it is an alternative approach, I have mentioned that there is already a correct answer. Regards. – hizmarck Oct 14 '19 at 12:33
@io-german, could you prove your point? My intention is for learning, may make a mistake. – hizmarck Oct 16 '19 at 17:05
sorry for the late response. I have found that for some reason skipped the stored procedure creation and tried to call a regular query via NamedNativeQuery API. I'll remove both the downvote and both my comments. Sorry for confusion – io-german Oct 21 '19 at 08:50

@NamedNativeQuery - How can I bind it to a repository method?

2 Answers2

Linked