Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I am new to spring boot and spring data jpa. I am trying to use native queries for executing search based on search attributes received from UI.

The records that are obtained based on the searchParam should search if the searchParam is contained in any of the specified columns (as mentioned in the native query)

I have written the following code but I end up receiving the error as mentioned in the title. I have tried looking up for response in stackoverflow. But i believe i have followed the suggestions as mentioned in many of the threads.

Any help in this regard would be highly appreciated.

Code snippet below

EpicController.java

@CrossOrigin
    @RequestMapping(value="/search", method = RequestMethod.GET)
    public Page<Epic> searchEpicsByProjectIdAndSearchParam(@RequestParam String searchParam, @RequestParam String projectId, Pageable pageable) throws Exception {
        logger.info("Inside searchEpicsByAttributes() based on searchQuery API");
        Page<Epic> results = null;
        try {
            results = epicService.searchEpicsByProjectIdAndSearchParam(searchParam, projectId, pageable);
        catch(Exception ex) {
            ex.printStackTrace();
            throw new Exception("Exception occurred :: " + ex.getStackTrace());
        return results;
EpicService.java (Interface)
public interface EpicService {
   Page<Epic> searchEpicsByProjectIdAndSearchParam(String searchParam, String projectId, Pageable pageable);
EpicServiceImpl.java
@Override
    public Page<Epic> searchEpicsByProjectIdAndSearchParam(String searchParam, String projectId, Pageable pageable) {
        logger.info(" Inside searchEpicsByProjectIdAndSearchParam() API in EpicServiceImpl");
        return epicRepository.findBySearchParamsAndProjectId(searchParam,projectId, pageable);
EpicRepository.java
@Repository
public interface EpicRepository extends JpaRepository<Issue, String> {
@Query(value = 
            "select i.* from issue i where ("
            + "upper(i.name) like upper('%?1%'))"
            +  "and upper(i.project_id) = upper('%?2%')"
            + "ORDER BY i.name DESC \n-- #pageable\n",
           countQuery = 
            "select count(i.*) from issue i where ("
            + "upper(i.name) like upper('%?1%'))"
            +  "and upper(i.project_id) = upper('%?2%')",
            nativeQuery = true)
    Page<Epic> findBySearchParamsAndProjectId(String name, String projectId, Pageable pageable);
Exception:
2019-02-08 23:25:21.199  INFO 12556 --- [nio-8080-exec-1] c.a.m.A.controller.ProjectController     :  Inside searchEpicsByProjectIdAndSearchParam() API in EpicServiceImpl
org.springframework.dao.InvalidDataAccessApiUsageException: Parameter with that position [1] did not exist; nested exception is java.lang.IllegalArgumentException: Parameter with that position [1] did not exist
        at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:384)
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:246)
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:525)
        at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
        at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:209)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
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: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:202)
        at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:188)
Skip Single Quotations "'" around params i.e. ?1 and ?2. Working query will be like:
"select i.* from issue i where ("
        + "upper(i.name) like upper(%?1%))"
        +  "and upper(i.project_id) = upper(%?2%)"
        + "ORDER BY i.name DESC \n-- #pageable\n",
       countQuery = 
        "select count(i.*) from issue i where ("
        + "upper(i.name) like upper(%?1%))"
        +  "and upper(i.project_id) = upper(%?2%)"
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.