时间戳

首页 > 解决方案 > CallableStatement 抛出日期参数未知的 org.postgresql.util.PSQLException

java - CallableStatement 抛出日期参数未知的 org.postgresql.util.PSQLException

问题描述

我正在使用 CallableStatement 类来执行 Postgres 过程。但是它会引发 PSQLException 异常。请参阅下面的 java 代码和日志。

Java代码：

reCalculateBalance(null, account.getId(), DateUtils.localDateToDate(account.getDateOfInitialBalance()));

private void reCalculateBalance(BigInteger transactionId, BigInteger accountId, java.sql.Date startingDate) {
        log.info("start call sp_balance_calculation: transactionId {}, accountId {}, startingDate {}", transactionId, accountId, startingDate);
        Session session = entityManager.unwrap(Session.class);
        String result = session.doReturningWork(
                connection -> {
                    try (CallableStatement function = connection.prepareCall("call core.sp_balance_calculation(?,?,?,?)" )) {
                        if (transactionId == null) {
                            function.setNull(1, Types.BIGINT);
                        } else {
                            function.setObject(1, transactionId, Types.BIGINT);
                        }
                        if (accountId == null) {
                            function.setNull(2, Types.BIGINT);
                        } else {
                            function.setLong(2, accountId.longValue());
                        }
                        function.setDate( 3, startingDate);
                        function.registerOutParameter(4, Types.VARCHAR);
                        function.execute();
                        return function.getString( 4 );
                    }
                } );
        log.info("result  = {}", result);
        session.close();
    }

日志 ：

15:28:15.830 [http-nio-8082-exec-1] INFO  c.c.c.a.d.s.impl.AccountServiceImpl - start call sp_balance_calculation: transactionId null, accountId 37, startingDate 2020-05-27
15:28:15.836 [http-nio-8082-exec-1] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 42883
15:28:15.836 [http-nio-8082-exec-1] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ERROR: procedure core.sp_balance_calculation(bigint, bigint, unknown) does not exist
  Hint: No procedure matches the given name and argument types. You might need to add explicit type casts.
  Position: 6
15:28:15.845 [http-nio-8082-exec-1] ERROR c.c.c.a.a.v.e.RestEndpointExceptionHandler - ERROR: procedure core.sp_balance_calculation(bigint, bigint, unknown) does not exist
  Hint: No procedure matches the given name and argument types. You might need to add explicit type casts.
  Position: 6
org.hibernate.exception.SQLGrammarException: error executing work
    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.JdbcCoordinatorImpl.coordinateWork(JdbcCoordinatorImpl.java:311)
    at org.hibernate.internal.AbstractSharedSessionContract.doWork(AbstractSharedSessionContract.java:1084)
    at org.hibernate.internal.AbstractSharedSessionContract.doReturningWork(AbstractSharedSessionContract.java:1080)
    at cash.continuity.cc.api.db.service.impl.AccountServiceImpl.reCalculateBalance(AccountServiceImpl.java:114)
    at cash.continuity.cc.api.db.service.impl.AccountServiceImpl.createAccount(AccountServiceImpl.java:106)
    at cash.continuity.cc.api.db.service.impl.AccountServiceImpl$$FastClassBySpringCGLIB$$f65d7c8c.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)

过程：sp_balance_calculation

core.sp_balance_calculation (
    IN      p_transaction_id    BIGINT,
    IN      p_account_id        BIGINT,
    IN      p_starting_date     DATE,
    INOUT   p_error_code        VARCHAR 
)

看来是日期字段转换不正确，但是查看日志时，我看到输入正常为：transactionId = null，accountId = 37，startingDate = '2020-05-27'。请帮我检查为什么它会为日期字段抛出未知数？

标签： javapostgresqlhibernatespring-bootprocedure

解决方案

我需要为 out 参数设置值。

function.setNull( 4, Types.VARCHAR);

设置后，我可以从 java 执行程序。

推荐阅读