Java Hibernate java.lang.IllegalArgumentException: Unknown parameter position: 1

Question

This is my query method :

@Query(value = "select new com.twognation.hub.dto.GameDTO(game.id, game.name, game.active, game.description, game.coverImage, game.logo, game.backgroundImage, game.featuredImage, game.characterImage, game.smallCoverImage, count(tournament.id) as tournamentCount) from Game game left join Tournament tournament on game.id=tournament.game where lower(game.name) like '%:name%' group by game.id order by tournamentCount desc, game.id",
                countQuery = "select count(1) from Game game left join Tournament tournament on game.id=tournament.game where lower(game.name) like '%:name%' group by game.id order by tournamentCount desc, game.id")
Page<GameDTO> findAllOrderByTournamentCount(@Param("name") String name, Pageable page);

When I call this method i get this error :

Caused by: java.lang.IllegalArgumentException: Unknown parameter position: 1    
    at org.hibernate.query.internal.QueryParameterBindingsImpl.getBinding(QueryParameterBindingsImpl.java:240)  
    at org.hibernate.query.internal.AbstractProducedQuery.setParameter(AbstractProducedQuery.java:503)  
    at org.hibernate.query.internal.AbstractProducedQuery.setParameter(AbstractProducedQuery.java:104)  
    at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:141)     
    at org.springframework.data.jpa.repository.query.StringQueryParameterBinder.bind(StringQueryParameterBinder.java:61)    
    at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:101)     
    at org.springframework.data.jpa.repository.query.SpelExpressionStringQueryParameterBinder.bind(SpelExpressionStringQueryParameterBinder.java:76)    
    at org.springframework.data.jpa.repository.query.ParameterBinder.bindAndPrepare(ParameterBinder.java:161)   
    at org.springframework.data.jpa.repository.query.ParameterBinder.bindAndPrepare(ParameterBinder.java:152)   
    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$PagedExecution.doExecute(JpaQueryExecution.java:186)     
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:87)   
    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:492)     
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:475)   
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)    
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:56)    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)   
    ... 172 common frames omitted

try to use JpaRepository or CrudRepository, in this case you will not need to use @Query and maybe you'll fix the problem — Azzabi Haythem
– Azzabi Haythem, Commented Sep 5, 2018 at 14:50
'%:name%' is a String literal ... do you see why that may be a problem ? — user3973283
– user3973283, Commented Sep 5, 2018 at 15:28

Youcef LAIDANI · Accepted Answer · 2018-09-05 14:50:39Z

2

This :

'%:name%'

In JPA is just a native String, to solve this issue you can use CONCAT operator like so :

CONCAT('%', :name, '%')

This will be converted later to '%some_value%' for example

answered Sep 5, 2018 at 14:50

Youcef LAIDANI

60.3k21 gold badges111 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3364181 Over a year ago

That worked. One more question. In game i have Set of genres. How can i add to query to filter by specific genre id ?

Youcef LAIDANI Over a year ago

@user3364181 maybe you can use IN for example :genre IN g.genres

user3364181 Over a year ago

i got this error when i tried that : Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement

Youcef LAIDANI Over a year ago

@user3364181 It is bad to ask other questions beside the original question, so instead please create a new question where you describe every thing about what you want to do exactly and what you tried so far and I'm sure that we can help you :) like that I can't understand what you do or what you want exactly I'm waiting your new question ok

Mạnh Quyết Nguyễn · Accepted Answer · 2018-09-05 14:53:23Z

2

Look like you have problem with like.

Instead of

'%:name%'

do:

%:name%

answered Sep 5, 2018 at 14:53

Mạnh Quyết Nguyễn

18.3k1 gold badge28 silver badges54 bronze badges

1 Comment

patrickjp93 Over a year ago

This fails for SQL Server 2016. The CONCAT suggestion up above works.

Collectives™ on Stack Overflow

Java Hibernate java.lang.IllegalArgumentException: Unknown parameter position: 1

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related