Spring-Data-JPA parameterised NativeQuery argument

2019-07-11 18:19发布

I have this JPA entity in a spring-boot v1.5.1 application

@Entity
@Table(name = "score")
public class Score implements Serializable {

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

    @Column(name = "user_id",nullable = false)
    private Long user;

    @Column(name = "message_id",nullable = false)
    private Long message;

    @Column(nullable = false)
    private Long value;

    @Column(nullable = false)
    private Date date;

which records a bunch of user scores by date. I have a simple SQL query which returns a grouped report

SELECT (SELECT fullname from user where id=s.user_id) as name,SUM(s.value) as total FROM score s
WHERE date >= '2017/03/01''
GROUP BY user_id ORDER BY total desc

To recreate this using the spring-data-jpa interface, I've defined this ScoreRepository which is using a Projection interface as a return type.

public interface ScoreRepository extends JpaRepository<Score, Long> {

    @Query(value = "SELECT new c.b.a.service.datastore.model.LeaderScore(" +
            "(SELECT fullname from beuser where id=s.user_id) as name,SUM(s.value) as total) FROM score s " +
            "WHERE date >= '?1' GROUP BY user_id ORDER BY total desc",
            nativeQuery = true)
    List<LeaderScore> getLeaderBoard(Date from); 

My nativeQuery returns the exact field names as per my LeaderScore projection interface

public interface LeaderScore {
    String getName();
    Long getTotal();
}

I get this error, which seems to indicate a Spel mapping error

java.lang.IllegalArgumentException: Parameter with that position [1] did not exist
    at org.hibernate.jpa.spi.BaseQueryImpl.findParameterRegistration(BaseQueryImpl.java:502) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.jpa.spi.BaseQueryImpl.setParameter(BaseQueryImpl.java:692) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.jpa.spi.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:181) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.jpa.spi.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:32) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
    at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:140) ~[spring-data-jpa-1.11.0.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.StringQueryParameterBinder.bind(StringQueryParameterBinder.java:61) ~[spring-data-jpa-1.11.0.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:100) ~[spring-data-jpa-1.11.0.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.SpelExpressionStringQueryParameterBinder.bind(SpelExpressionStringQueryParameterBinder.java:69) ~[spring-data-jpa-1.11.0.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.ParameterBinder.bindAndPrepare(ParameterBinder.java:160) ~[spring-data-jpa-1.11.0.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.ParameterBinder.bindAndPrepare(ParameterBinder.java:151) ~[spring-data-jpa-1.11.0.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.AbstractStringBasedJpaQuery.doCreateQuery(AbstractStringBasedJpaQuery.java:81) ~[spring-data-jpa-1.11.0.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.createQuery(AbstractJpaQuery.java:190) ~[spring-data-jpa-1.11.0.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:121) ~[spring-data-jpa-1.11.0.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:85) ~[spring-data-jpa-1.11.0.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:116) ~[spring-data-jpa-1.11.0.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106) ~[spring-data-jpa-1.11.0.RELEASE.jar:na]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:483) ~[spring-data-commons-1.13.0.RELEASE.jar:na]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:461) ~[spring-data-commons-1.13.0.RELEASE.jar:na]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.6.RELEASE.jar:4.3.6.RELEASE]

See this similar issue :: Modifying native query cannot have named parameter bindings?

0条回答
登录 后发表回答