CODEWITHSUNDEEP
springjpajpql
vikram
vikram

Reputation: 135

JPQL Named query parameter throwing exception with LEFT JOIN queries

I am new to spring data. I have a LEFT Join query using JPQL and am using named query params for the left table, but i get an exception "Parameter with that position [1] did not exist.

I have 2 entities Post and Comments with Many2One relation. I want to get all Post's based on named query params and the latest Comment associated with that Post. Below are the Entities and the JPQL

@Entity
public class Post{
  @Id
  @GeneratedValue
  private long id;
  private String authorId;
}

@Entity
public class Comment {
  @Id
  @GeneratedValue
  private long id;
  private long creationTime;
  @ManyToOne
  @JoinColumn(name="post_id")
  private Post post;
}

I have scala code for my repository:

@Repository
trait PostRepository extends PagingAndSortingRepository[Post,Long] {

  @Query("SELECT P, COM FROM Post P "
    + "LEFT JOIN FETCH  Comment COM on COM.creationTime = "
    + "(SELECT MAX(COM2.creationTime) from Comment COM2 "
    + "WHERE P.id = COM2.post_id GROUP BY COM2.post_id) "
    + "WHERE P.authorId = :authorId"
  )
  def getPosts(@Param("authorId") authorId:String) : List[Array[Object]]
}

If I do not use the named query param, then there is no exception, but as soon I add the "WHERE P.authorId = :authorId", I get the exception related to the named param. I am unable to figure out the problem.

If JPQL is not the way to go for the query I want, can you please suggest/provide an example of how I can do this using spring data (perhaps Specifications?). I am new to this and so any sample code is greatly appreciated.

Caused by: 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.12.Final.jar!/:5.0.12.Final]

            at org.hibernate.jpa.spi.BaseQueryImpl.setParameter(BaseQueryImpl.java:692) ~[hibernate-entitymanager-5.0.12.Final.jar!/:5.0.12.Final]

            at org.hibernate.jpa.spi.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:181) ~[hibernate-entitymanager-5.0.12.Final.jar!/:5.0.12.Final]

            at org.hibernate.jpa.spi.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:32) ~[hibernate-entitymanager-5.0.12.Final.jar!/:5.0.12.Final]

            at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_162]

            at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_162]

            at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_162]

            at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_162]

            at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:372) ~[spring-orm-4.3.14.RELEASE.jar!/:4.3.14.RELEASE]

            at com.sun.proxy.$Proxy204.setParameter(Unknown Source) ~[na:na]

            at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:141) ~[spring-data-jpa-1.11.10.RELEASE.jar!/:na]

            at org.springframework.data.jpa.repository.query.StringQueryParameterBinder.bind(StringQueryParameterBinder.java:61) ~[spring-data-jpa-1.11.10.RELEASE.jar!/:na]

            at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:101) ~[spring-data-jpa-1.11.10.RELEASE.jar!/:na]

            at org.springframework.data.jpa.repository.query.SpelExpressionStringQueryParameterBinder.bind(SpelExpressionStringQueryParameterBinder.java:76) ~[spring-data-jpa-1.11.10.RELEASE.jar!/:na]

            at org.springframework.data.jpa.repository.query.ParameterBinder.bindAndPrepare(ParameterBinder.java:161) ~[spring-data-jpa-1.11.10.RELEASE.jar!/:na]

            at org.springframework.data.jpa.repository.query.ParameterBinder.bindAndPrepare(ParameterBinder.java:152) ~[spring-data-jpa-1.11.10.RELEASE.jar!/:na]

            at org.springframework.data.jpa.repository.query.AbstractStringBasedJpaQuery.doCreateQuery(AbstractStringBasedJpaQuery.java:81) ~[spring-data-jpa-1.11.10.RELEASE.jar!/:na]

            at org.springframework.data.jpa.repository.query.AbstractJpaQuery.createQuery(AbstractJpaQuery.java:190) ~[spring-data-jpa-1.11.10.RELEASE.jar!/:na]

            at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:123) ~[spring-data-jpa-1.11.10.RELEASE.jar!/:na]

            at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:87) ~[spring-data-jpa-1.11.10.RELEASE.jar!/:na]

            at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:116) ~[spring-data-jpa-1.11.10.RELEASE.jar!/:na]

            at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106) ~[spring-data-jpa-1.11.10.RELEASE.jar!/:na]

            at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:492) ~[spring-data-commons-1.13.10.RELEASE.jar!/:na]

            at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:475) ~[spring-data-commons-1.13.10.RELEASE.jar!/:na]

            at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.14.RELEASE.jar!/:4.3.14.RELEASE]

            at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:56) ~[spring-data-commons-1.13.10.RELEASE.jar!/:na]

            at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.14.RELEASE.jar!/:4.3.14.RELEASE]

            at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) ~[spring-tx-4.3.14.RELEASE.jar!/:4.3.14.RELEASE]

            at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282) ~[spring-tx-4.3.14.RELEASE.jar!/:4.3.14.RELEASE]

            at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.3.14.RELEASE.jar!/:4.3.14.RELEASE]

            at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.14.RELEASE.jar!/:4.3.14.RELEASE]

            at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ~[spring-tx-4.3.14.RELEASE.jar!/:4.3.14.RELEASE]

            ... 152 common frames omitted

Upvotes: 1

Views: 678

Answers (1)

Sorin Penteleiciuc
Sorin Penteleiciuc

Reputation: 865

You can set the hibernate generation of query log level to FINEST and see the generated SQL. I think there is a problem in the sql that is being generated.
I ran the code in my project and the output query is like this :

select id, authorId, creationTime from post // creationTime comes from comment but is still generated as belonging to post

Maybe this can help you Hibernate show real SQL.

The problem is that you want to retrieve 2 object entities as a response in an object while the good way would be, to retrieve a tuple/ mapEntry or something similar . 

SELECT new java.util.AbstractMap.SimpleEntry(post, comment )

Upvotes: 0

Related Questions