title: How do I execute an H2 stored procedure with Spring Data JPA & Hibernate?
body: <p>I want to execute a simple H2 database stored procedure using Spring Data JPA with Hibernate.</p>
  
  <p>Stored procedure class:</p>
  
  <pre><code>public class H2StoredProcedures {
      public static String stringIn(final String inValue) {
          log.info("stringIn: '{}'", inValue);
          return inValue + "_result";
      }
  }
  </code></pre>
  
  <p>Stored procedure alias:</p>
  
  <pre><code>DROP ALIAS IF EXISTS STRING_IN;
  CREATE ALIAS STRING_IN FOR "H2StoredProcedures.stringIn";
  </code></pre>
  
  <p>Executing in H2 console works as expected:</p>
  
  <pre><code>CALL STRING_IN('fooIn');
  PUBLIC.STRING_IN('fooIn')  
  fooIn_result
  (1 row, 1 ms)
  </code></pre>
  
  <p>Spring Data JPA Repository Class:</p>
  
  <pre><code>@Procedure("STRING_IN")
  String stringIn(@Param("inValue") final String inValue);
  </code></pre>
  
  <p>Repository Test:</p>
  
  <pre><code>@Test
  public void testStringIn() throws Exception {
      assertEquals("fooIn_result", this.testRepository.stringIn("fooIn"));
  }
  </code></pre>
  
  <p>Results in the following output:</p>
  
  <pre><code>2015-08-31 14:52:38,117 WARN  [main]: HHH000456: Named parameters are used for a callable statement, but database metadata indicates named parameters are not supported. [logger=org.hibernate.procedure.internal.ProcedureCallImpl, mdc={}]
  Hibernate: {call STRING_IN(?,?)}
  2015-08-31 14:52:38,119 INFO  [main]: AtomikosNonXADataSourceBean 'adminDataSource1': getConnection ( null )... [logger=com.atomikos.jdbc.nonxa.AtomikosNonXADataSourceBean, mdc={}]
  2015-08-31 14:52:38,128 WARN  [main]: Error delegating 'prepareCall' call [logger=com.atomikos.jdbc.JdbcConnectionProxyHelper, mdc={}]
  org.h2.jdbc.JdbcSQLException: Method "STRING_IN (H2StoredProcedures, parameter count: 2)" not found; SQL statement:
   call STRING_IN(?,?)  [90087-187]
      at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
      at org.h2.message.DbException.get(DbException.java:179)
      at org.h2.message.DbException.get(DbException.java:155)
      at org.h2.engine.FunctionAlias.findJavaMethod(FunctionAlias.java:272)
      at org.h2.expression.JavaFunction.&lt;init&gt;(JavaFunction.java:32)
      at org.h2.command.Parser.readJavaFunction(Parser.java:2364)
      [...]
      at org.h2.server.TcpServerThread.run(TcpServerThread.java:159)
      at java.lang.Thread.run(Thread.java:745)
  
      at org.h2.engine.SessionRemote.done(SessionRemote.java:622)
      at org.h2.command.CommandRemote.prepare(CommandRemote.java:68)
      at org.h2.command.CommandRemote.&lt;init&gt;(CommandRemote.java:45)
      at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:492)
      at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1189)
      at org.h2.jdbc.JdbcPreparedStatement.&lt;init&gt;(JdbcPreparedStatement.java:72)
      at org.h2.jdbc.JdbcCallableStatement.&lt;init&gt;(JdbcCallableStatement.java:52)
      at org.h2.jdbc.JdbcConnection.prepareCall(JdbcConnection.java:899)
      [...]
      at com.sun.proxy.$Proxy72.prepareCall(Unknown Source)
      at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$1.doPrepare(StatementPreparerImpl.java:103)
      at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186)
      at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:96)
      at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:407)
      at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:378)
      at org.hibernate.jpa.internal.StoredProcedureQueryImpl.outputs(StoredProcedureQueryImpl.java:251)
      at org.hibernate.jpa.internal.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:234)
      at org.springframework.data.jpa.repository.query.JpaQueryExecution$ProcedureExecution.doExecute(JpaQueryExecution.java:299)
      [...]
      at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:122)
      [...]
      at com.sun.proxy.$Proxy71.stringIn(Unknown Source)
      at TestRepositoryTest.testStringIn(TestRepositoryTest.java:227)
      [...]
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
  2015-08-31 14:52:38,131 WARN  [main]: SQL Error: 90087, SQLState: 90087 [logger=org.hibernate.engine.jdbc.spi.SqlExceptionHelper, mdc={}]
  2015-08-31 14:52:38,131 ERROR [main]: Method "STRING_IN (H2StoredProcedures, parameter count: 2)" not found; SQL statement:
   call STRING_IN(?,?)  [90087-187] [logger=org.hibernate.engine.jdbc.spi.SqlExceptionHelper, mdc={}]
  </code></pre>
  
  <p>The same code against an MS SQL Server database with the following stored procedure works as expected:</p>
  
  <pre><code>CREATE PROCEDURE STRING_IN
      @InValue SYSNAME,
      @OutValue NVARCHAR(255) OUTPUT
  AS 
  BEGIN
      SET NOCOUNT ON;
      SET @OutValue = @InValue + '_result';
      RETURN;
  END;
  </code></pre>
  
  <p>It seems like there is a bug in either the org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery#createStoredProcedure() or more likely Hibernates org.hibernate.jpa.internal.StoredProcedureQueryImpl#registerStoredProcedureParameter(String, Class, ParameterMode) which should know about the dialect.</p>
  
  <p>I tried using @NamedStoredProcedureQuery and basically every annotation combination and I get essentially the same error about not finding a procedure with 2 parameters.</p>
  
  <p>I also tried adding a version with an in/out parameter to the H2StoredProcedures:</p>
  
  <pre><code>public static void stringInOut(final String inValue, String outValue) {
      log.info("stringInOut: '{}'", inValue);
      outValue = inValue + "_result";
  }
  </code></pre>
  
  <p>If I set up the in/out repository method with a String return type:</p>
  
  <pre><code>@Procedure(procedureName = "STRING_IN_OUT")
  String stringInOut(@Param("inValue") final String inValue, @Param("outValue") String outValue);
  </code></pre>
  
  <p>I get a third parameter not found:</p>
  
  <pre><code>2015-08-31 15:19:15,517 ERROR [main]: Method "STRING_IN_OUT (H2StoredProcedures, parameter count: 3)" not found; SQL statement:
   call STRING_IN_OUT(?,?,?)  [90087-187] [logger=org.hibernate.engine.jdbc.spi.SqlExceptionHelper, mdc={}]
  </code></pre>
  
  <p>If I set up the in/out repository method with a void return type:</p>
  
  <pre><code>@Procedure(procedureName = "STRING_IN_OUT")
  void stringInOut(@Param("inValue") final String inValue, @Param("outValue") String outValue);
  </code></pre>
  
  <p>I get a error presumably try to map the result to void:</p>
  
  <pre><code>Caused by: org.hibernate.MappingException: No Dialect mapping for JDBC type: 0
      at org.hibernate.dialect.TypeNames.get(TypeNames.java:87)
      at org.hibernate.dialect.TypeNames.get(TypeNames.java:118)
      at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:653)
      at org.hibernate.loader.custom.JdbcResultMetadata.getHibernateType(JdbcResultMetadata.java:93)
      at org.hibernate.loader.custom.ScalarResultColumnProcessor.performDiscovery(ScalarResultColumnProcessor.java:62)
      at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:498)
      at org.hibernate.result.internal.OutputsImpl$CustomLoaderExtension.processResultSet(OutputsImpl.java:297)
      at org.hibernate.result.internal.OutputsImpl.extractResults(OutputsImpl.java:152)
      at org.hibernate.result.internal.OutputsImpl.extractCurrentResults(OutputsImpl.java:143)
      at org.hibernate.result.internal.OutputsImpl.access$100(OutputsImpl.java:52)
      at org.hibernate.result.internal.OutputsImpl$CurrentReturnState.buildOutput(OutputsImpl.java:203)
      at org.hibernate.result.internal.OutputsImpl$CurrentReturnState.getOutput(OutputsImpl.java:187)
      at org.hibernate.result.internal.OutputsImpl.getCurrent(OutputsImpl.java:108)
      at org.hibernate.jpa.internal.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:234)
      at org.springframework.data.jpa.repository.query.JpaQueryExecution$ProcedureExecution.doExecute(JpaQueryExecution.java:299)
      at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:77)
      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:393)
      at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:371)
      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)
      ... 43 more
  </code></pre>