1

I've a simple Person entity with two fields:

@Entity
@Table(name = "person")
public class Person implements Serializable {

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

    @Column(name = "name", length = 50)
    private String name;

    //setter/getters
}

A JpaRepository where I override the findAll method to restrict the records based on user role:

public interface PersonRepository extends JpaRepository<Person,Long> {

    @Query("select p from Person p where p.name like ?#{hasRole('ROLE_ADMIN') ? '%' : principal.username}")
    @Override
    List<Person> findAll();
}

The above setup is working fine. But when I update the query to be the following, it throws me exception as below. It looks it failed to parse the SpEL expression.

Problematic query:

@Query("select p from Person p where p.id like ?#{hasRole('ROLE_ADMIN') ? '%' : principal.userId}")

Exception:

Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: Not all named parameters have been set: [1] [select p from Person p where p.id like ?1]
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1750)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:458)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:114)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:78)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:100)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:91)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:462)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:440)
    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:281)
    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)
    ... 142 common frames omitted
Caused by: org.hibernate.QueryException: Not all named parameters have been set: [1] [select p from Person p where p.id like ?1]
    at org.hibernate.internal.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:401)
    at org.hibernate.internal.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:385)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:99)
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449)
    ... 156 common frames omitted

I played around it and tried the following options, but got the same exception:

1) replacing LIKE with =

@Query("select p from Person p where p.id = ?#{hasRole('ROLE_ADMIN') ? '1' : principal.userId}")

2) Setting hard coded ids :

@Query("select p from Person p where p.id = ?#{hasRole('ROLE_ADMIN') ? '6' : '5'}")

Any suggestions?

FYI :

  • I've spring boot 1.3.6.RELEASE, hibernate 4.3.11.Final in my pom.xml.
  • I've correctly configured/overridden the org.springframework.security.core.userdetails.User object to pass the userId.

1 Answer 1

2

I am not sure if there was a conflict between SpEL parser and Spring Data JPA. But I found an workaround it:

Solution: The following change on @Query worked just fine:

@Query("select p from Person p where p.id = ?#{principal.customerId} or 1=?#{hasRole('ROLE_ADMIN') ? 1 : 0}")

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.