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%)"