CODEWITHSUNDEEP
hibernatejpaspring-boot
mariosk89
mariosk89

Reputation: 954

Custom SQL query in JPA repositories

For the following query:

@Query("SELECT fd FROM DPPFile fd WHERE fd.ID= ':ID'")
List<DPPFile> findByID(@Param("ID")String ID/);

I'm getting the following weird exception:

Caused by: java.lang.IllegalArgumentException: Parameter with that position [1] did not exist
    at org.hibernate.jpa.spi.BaseQueryImpl.findParameterRegistration(BaseQueryImpl.java:502)
    at org.hibernate.jpa.spi.BaseQueryImpl.setParameter(BaseQueryImpl.java:692)
    at org.hibernate.jpa.spi.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:181)
    at org.hibernate.jpa.spi.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:32)
    at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:140)
    at org.springframework.data.jpa.repository.query.StringQueryParameterBinder.bind(StringQueryParameterBinder.java:61)
    at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:100)
    at org.springframework.data.jpa.repository.query.SpelExpressionStringQueryParameterBinder.bind(SpelExpressionStringQueryParameterBinder.java:69)
    at org.springframework.data.jpa.repository.query.ParameterBinder.bindAndPrepare(ParameterBinder.java:160)
    at org.springframework.data.jpa.repository.query.ParameterBinder.bindAndPrepare(ParameterBinder.java:151)
    at org.springframework.data.jpa.repository.query.AbstractStringBasedJpaQuery.doCreateQuery(AbstractStringBasedJpaQuery.java:81)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.createQuery(AbstractJpaQuery.java:190)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:121)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:85)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:116)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:483)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:461)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)

I'd expect this exception if I was using ?1 instead of the named parameter approach. Using the ?1 fails as well so I'm not sure what's going wrong here. The DPPFile class has a member called ID as well as getter/setter.

(The reason I'm not using the default "findByID" with no query on top of it function is because I want to extend the query with additional parameters)

Upvotes: 1

Views: 1057

Answers (2)

lrv
lrv

Reputation: 290

As an alternative solution, you can skip the whole @Query annotation by using spring-data findOne assuming ID is your unique identifier in the entity. Otherwise, you can rely on spring-data to generate the query from the name of the method. Use something like this:

@Entity
public class DPPFile {

  @Id
  private Integer identifier;

  private Integer ID;
}

public interface DPPFileRepository extends Repository< DPPFile, Integer> {

   List<DPPFile> findByID(String ID);
}

see https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.query-methods.query-creation

Upvotes: 0

Neil Stockton
Neil Stockton

Reputation: 11531

Don't put the parameter in single quotes otherwise it is resolved as a literal. Hence the query will not be registered.

Upvotes: 2

Related Questions