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?