java - 如何使用 h2 数据库创建和运行存储过程/函数

Question

我正在使用 h2 数据库来运行组件测试用例，而在开发中使用 postgres。现在，我在 postgres 中创建的功能工作正常，业务逻辑也很好，但是在运行组件测试用例时遇到问题。对于运行组件测试用例，我已将 sql 文件放在类路径中，其中我提到

CREATE ALIAS blockBalance FOR "com.piepeople.balance.PaymentsOutTest.blockBalance";

PaymentsOutTest 是我的组件测试类，其中我放置了静态块平衡方法，其中放置了我的函数逻辑

public static String blockBalance(Connection conn, String entityid, BigDecimal amount, String output)
    throws SQLException {
    ResultSet rs = conn.createStatement()
      .executeQuery(" SELECT * FROM balance.balance WHERE entity_id = " + entityid);
    if(rs.next()){
      if(rs.getBigDecimal("actual_balance").compareTo(amount) > 0){
        output = rs.getString("id");
        return output;
      }else{
        output = "INSUFFICIENT BALANCE";
        return output;
      }
    }else{
      output = "BALANCE DATA NOT FOUND";
      return output;
    }
  }

但是当我运行我的测试用例时，我得到了错误

2021-06-24 11:56:29.170 ERROR 54071 --- [ntainer#0-0-C-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : Invalid value "3" for parameter "parameterIndex" [90008-200]
2021-06-24 11:56:29.180 ERROR 54071 --- [ntainer#0-0-C-1] c.p.b.listener.PaymentsOutListener       : E7250: Generic error: 07360f5b-9200-11ea-bf05-1185804a405x:

org.hibernate.exception.DataException: Error preparing registered callable parameter
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:52)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.procedure.internal.ProcedureCallImpl$3.accept(ProcedureCallImpl.java:406)
    at org.hibernate.procedure.internal.ProcedureCallImpl$3.accept(ProcedureCallImpl.java:390)
    at org.hibernate.query.procedure.internal.ProcedureParameterMetadata.visitRegistrations(ProcedureParameterMetadata.java:186)
    at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:389)
    at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:352)
    at org.hibernate.procedure.internal.ProcedureCallImpl.outputs(ProcedureCallImpl.java:632)
    at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputParameterValue(ProcedureCallImpl.java:670)
    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.$Proxy184.getOutputParameterValue(Unknown Source)
    at com.piepeople.balance.service.PaymentsOutService.blockFunds(PaymentsOutService.java:88)
    at com.piepeople.balance.service.PaymentsOutService.blockFunds(PaymentsOutService.java:54)
    at com.piepeople.balance.listener.PaymentsOutListener.blockFunds(PaymentsOutListener.java:45)
    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.messaging.handler.invocation.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:171)
    at org.springframework.messaging.handler.invocation.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:120)
    at org.springframework.kafka.listener.adapter.HandlerAdapter.invoke(HandlerAdapter.java:48)
    at org.springframework.kafka.listener.adapter.MessagingMessageListenerAdapter.invokeHandler(MessagingMessageListenerAdapter.java:330)
    at org.springframework.kafka.listener.adapter.RecordMessagingMessageListenerAdapter.onMessage(RecordMessagingMessageListenerAdapter.java:86)
    at org.springframework.kafka.listener.adapter.RecordMessagingMessageListenerAdapter.onMessage(RecordMessagingMessageListenerAdapter.java:51)
    at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.doInvokeOnMessage(KafkaMessageListenerContainer.java:2069)
    at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.invokeOnMessage(KafkaMessageListenerContainer.java:2051)
    at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.doInvokeRecordListener(KafkaMessageListenerContainer.java:1988)
    at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.doInvokeWithRecords(KafkaMessageListenerContainer.java:1928)
    at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.invokeRecordListener(KafkaMessageListenerContainer.java:1814)
    at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.invokeListener(KafkaMessageListenerContainer.java:1531)
    at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.pollAndInvoke(KafkaMessageListenerContainer.java:1178)
    at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.run(KafkaMessageListenerContainer.java:1075)
    at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.h2.jdbc.JdbcSQLDataException: Invalid value "3" for parameter "parameterIndex" [90008-200]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:590)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
    at org.h2.message.DbException.get(DbException.java:205)
    at org.h2.message.DbException.getInvalidValueException(DbException.java:280)
    at org.h2.jdbc.JdbcCallableStatement.checkIndexBounds(JdbcCallableStatement.java:1642)
    at org.h2.jdbc.JdbcCallableStatement.registerOutParameter(JdbcCallableStatement.java:1655)
    at org.h2.jdbc.JdbcCallableStatement.registerOutParameter(JdbcCallableStatement.java:123)
    at com.zaxxer.hikari.pool.HikariProxyCallableStatement.registerOutParameter(HikariProxyCallableStatement.java)
    at org.hibernate.query.procedure.internal.ProcedureParameterImpl.prepare(ProcedureParameterImpl.java:223)
    at org.hibernate.procedure.internal.ProcedureCallImpl$3.accept(ProcedureCallImpl.java:397)
    ... 36 common frames omitted

而 PaymentsOutService 是我的业务逻辑类，在 jpa 中调用 postgres 函数方式如下所述

query = entityManager.createStoredProcedureQuery(schema + ".blockBalance")
        .registerStoredProcedureParameter("entityid", String.class, ParameterMode.IN)
        .registerStoredProcedureParameter("amount", BigDecimal.class, ParameterMode.IN)
        .registerStoredProcedureParameter("output", String.class, ParameterMode.OUT)
        .setParameter("entityid", entityId)
        .setParameter("amount", amount);

标签： javaspring-bootspring-data-jpah2java-stored-procedures