Is it possible to use 'LIKE' and '= ?' In the same SQL query?-CodePudding

I am creating a Filter function that sorts a list. I'm using ajax to return and display the results.

But I can't seem to find a way to return results when both the search input and an option have been set.

I keeping getting an error whenever I write my query like this:

@Query(value = "SELECT * FROM colaboradores where name LIKE :search% AND department = ? AND category = ? ORDER BY id DESC", nativeQuery = true)
List <Colaboradores> filterColaboradorBySearch(@Param("search")String search, int department, int category);

ERROR:

    Caused by: org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List com.dexa.hr.repository.ColaboradorRepository.filterColaboradoresAll(java.lang.String,int,int)! Reason: Mixing of ? parameters and other forms like ?1 is not supported!; nested exception is java.lang.IllegalArgumentException: Mixing of ? parameters and other forms like ?1 is not supported!
    at org.springframework.data.repository.query.QueryCreationException.create(QueryCreationException.java:101) ~[spring-data-commons-2.6.1.jar:2.6.1]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:106) ~[spring-data-commons-2.6.1.jar:2.6.1]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lambda$mapMethodsToQuery$1(QueryExecutorMethodInterceptor.java:94) ~[spring-data-commons-2.6.1.jar:2.6.1]
    at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197) ~[na:na]
    at java.base/java.util.Iterator.forEachRemaining(Iterator.java:133) ~[na:na]
    at java.base/java.util.Collections$UnmodifiableCollection$1.forEachRemaining(Collections.java:1061) ~[na:na]
    at java.base/java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1845) ~[na:na]
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509) ~[na:na]
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499) ~[na:na]
    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:921) ~[na:na]
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[na:na]
    at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:682) ~[na:na]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.mapMethodsToQuery(QueryExecutorMethodInterceptor.java:96) ~[spring-data-commons-2.6.1.jar:2.6.1]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lambda$new$0(QueryExecutorMethodInterceptor.java:86) ~[spring-data-commons-2.6.1.jar:2.6.1]
    at java.base/java.util.Optional.map(Optional.java:260) ~[na:na]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.<init>(QueryExecutorMethodInterceptor.java:86) ~[spring-data-commons-2.6.1.jar:2.6.1]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:364) ~[spring-data-commons-2.6.1.jar:2.6.1]
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.lambda$afterPropertiesSet$5(RepositoryFactoryBeanSupport.java:322) ~[spring-data-commons-2.6.1.jar:2.6.1]
    at org.springframework.data.util.Lazy.getNullable(Lazy.java:230) ~[spring-data-commons-2.6.1.jar:2.6.1]
    at org.springframework.data.util.Lazy.get(Lazy.java:114) ~[spring-data-commons-2.6.1.jar:2.6.1]
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:328) ~[spring-data-commons-2.6.1.jar:2.6.1]
    at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:144) ~[spring-data-jpa-2.6.1.jar:2.6.1]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1863) ~[spring-beans-5.3.15.jar:5.3.15]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1800) ~[spring-beans-5.3.15.jar:5.3.15]
    ... 84 common frames omitted
Caused by: java.lang.IllegalArgumentException: Mixing of ? parameters and other forms like ?1 is not supported!
    at org.springframework.data.jpa.repository.query.StringQuery$ParameterBindingParser.parseParameterBindingsOfQueryIntoBindingsAndReturnCleanedQuery(StringQuery.java:281) ~[spring-data-jpa-2.6.1.jar:2.6.1]
    at org.springframework.data.jpa.repository.query.StringQuery$ParameterBindingParser.access$000(StringQuery.java:182) ~[spring-data-jpa-2.6.1.jar:2.6.1]
    at org.springframework.data.jpa.repository.query.StringQuery.<init>(StringQuery.java:73) ~[spring-data-jpa-2.6.1.jar:2.6.1]
    at org.springframework.data.jpa.repository.query.DeclaredQuery.of(DeclaredQuery.java:38) ~[spring-data-jpa-2.6.1.jar:2.6.1]
    at org.springframework.data.jpa.repository.query.JpaQueryMethod.assertParameterNamesInAnnotatedQuery(JpaQueryMethod.java:158) ~[spring-data-jpa-2.6.1.jar:2.6.1]
    at org.springframework.data.jpa.repository.query.JpaQueryMethod.<init>(JpaQueryMethod.java:139) ~[spring-data-jpa-2.6.1.jar:2.6.1]
    at org.springframework.data.jpa.repository.query.DefaultJpaQueryMethodFactory.build(DefaultJpaQueryMethodFactory.java:44) ~[spring-data-jpa-2.6.1.jar:2.6.1]
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:87) ~[spring-data-jpa-2.6.1.jar:2.6.1]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:102) ~[spring-data-commons-2.6.1.jar:2.6.1]
    ... 106 common frames omitted

I would like to know why.

CodePudding user response：

This error has nothing to do with the mixing of = and LIKE.

This is happening because for one value (search) you're using @Param while for the other 2 attributes you're using a question mark as the placeholder.

Change to

@Query(value = "SELECT * FROM colaboradores where name LIKE :search% AND department = :department AND category = :category ORDER BY id DESC", nativeQuery = true)
List<Colaboradores> filterColaboradorBySearch(@Param("search") String search, @Param("department") int department, @Param("category") int category);

or to

@Query(value = "SELECT * FROM colaboradores where name LIKE ?1% AND department = ?2 AND category = ?3 ORDER BY id DESC", nativeQuery = true)
List<Colaboradores> filterColaboradorBySearch(String search, int department, int category);