title: Hibernate can't define null parameters in jpql with Postgres
body: <p>I made some method in a Spring Data repository. The idea is to make each parameter optional (or nullable).</p>
  <pre><code> @Query(&quot;SELECT lp FROM LicensePlatformEntity lp WHERE &quot; +
          &quot;(:ownerId is null or lp.ownerCompanyId = :ownerId) AND &quot; +
          &quot;(:assignedCompanyId is null or lp.assignedCompanyId = :assignedCompanyId) AND &quot; +
          &quot;(:assignedUserId is null or lp.assignedUser.id = :assignedUserId) AND &quot; +
          &quot;(:isAssigned is null or lp.assignedCompanyAcceptedLicense = :isAssigned)&quot;)
  List&lt;LicensePlatformEntity&gt; findAllByFollowing(UUID ownerId, UUID assignedCompanyId, UUID assignedUserId, Boolean isAssigned);
  </code></pre>
  <p>It works perfectly well on the H2 in memory database, but PostgreSQL throws an exception, and claims that it can't define parameter %1 or any other which passed with null value. The next step is to apply pagination, so currently it works with <code>CriteriaBuilder</code> but it has no good pagination solutions, not based on offset limit.</p>
  <p>How to make optional parameter search suitable for PostgreSQL with out of the box pagination solution, which is not verbose extendable and Spring stack based? Appreciate any help!</p>
  <p>There was some attempt of explicit casting of parameters to hibernate types, with full type references, it looks horrible. And also spoils Intellij query processing, which is not useless thing.
  <a href="https://i.stack.imgur.com/VRFBy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VRFBy.png" alt="enter image description here" /></a></p>
  <p>This <a href="https://stackoverflow.com/questions/28554798/jpa-query-to-handle-null-parameter-value">JPA Query to handle NULL parameter value</a>
  does not have any useful information. Please think before claiming a duplicate.</p>
  <pre><code>Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
  21-April-01 13:16:14:243  ERROR restartedMain o.s.b.SpringApplication:856 - Application run failed
  org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'companiesApiImpl': Invocation of init method failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
      at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:160)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyBeanPostProcessorsBeforeInitialization(AbstractAutowireCapableBeanFactory.java:429)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1780)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:609)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:531)
      at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335)
      at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
      at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333)
      at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)
      at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:944)
      at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:923)
      at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:588)
      at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:144)
      at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:767)
      at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:759)
      at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:426)
      at org.springframework.boot.SpringApplication.run(SpringApplication.java:326)
      at org.springframework.boot.SpringApplication.run(SpringApplication.java:1311)
      at org.springframework.boot.SpringApplication.run(SpringApplication.java:1300)
      at it.agroadvisor.licensing.Application.main(Application.java:16)
      at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.base/java.lang.reflect.Method.invoke(Method.java:566)
      at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)
  Caused by: org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
      at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259)
      at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
      at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)
      at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
      at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
      at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
      at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
      at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
      at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
      at com.sun.proxy.$Proxy107.findAllByFollowing(Unknown Source)
      at it.agroadvisor.licensing.web.input.CompaniesApiImpl.ass(CompaniesApiImpl.java:93)
      at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.base/java.lang.reflect.Method.invoke(Method.java:566)
      at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleElement.invoke(InitDestroyAnnotationBeanPostProcessor.java:389)
      at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:333)
      at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:157)
      ... 24 common frames omitted
  Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
      at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103)
      at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
      at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
      at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
      at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67)
      at org.hibernate.loader.Loader.getResultSet(Loader.java:2304)
      at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2057)
      at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2019)
      at org.hibernate.loader.Loader.doQuery(Loader.java:948)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
      at org.hibernate.loader.Loader.doList(Loader.java:2850)
      at org.hibernate.loader.Loader.doList(Loader.java:2832)
      at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2664)
      at org.hibernate.loader.Loader.list(Loader.java:2659)
      at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506)
      at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400)
      at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
      at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1414)
      at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1625)
      at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1593)
      at org.hibernate.query.Query.getResultList(Query.java:165)
      at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.base/java.lang.reflect.Method.invoke(Method.java:566)
      at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:406)
      at com.sun.proxy.$Proxy114.getResultList(Unknown Source)
      at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:126)
      at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88)
      at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155)
      at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143)
      at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)
      at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)
      at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:152)
      at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:131)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
      at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
      at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
      at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
      at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
      ... 39 common frames omitted
  Caused by: org.postgresql.util.PSQLException: ОШИБКА: не удалось определить тип данных параметра $1
      at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2497)
      at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2233)
      at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310)
      at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
      at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
      at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
      at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:108)
      at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
      at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
      at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
      ... 75 common frames omitted
  </code></pre>