java - JPA / Hibernate:使用输入和输出参数调用存储过程
问题描述
我在使用 JPA 调用 Java 中的排序过程时遇到问题
这是Java代码:
StoredProcedureQuery storedProcedureQuery = getEntityManager().createNamedStoredProcedureQuery("RECPOCH");
storedProcedureQuery.setParameter(Pochette.SP_RECPOCH_PARAM_STRSTARTSIM, pochetteCriteria.getDebutNumPochette());
storedProcedureQuery.setParameter(Pochette.SP_RECPOCH_PARAM_STRLASTSIM, pochetteCriteria.getFinNumPochette());
storedProcedureQuery.setParameter(Pochette.SP_RECPOCH_PARAM_NACTELID, pochetteCriteria.getActelId());
storedProcedureQuery.execute();
Integer result = (Integer) storedProcedureQuery.getOutputParameterValue(Pochette.SP_RECPOCH_PARAM_NRESULT);
return Long.valueOf(result);
这是甲骨文的代码:
"RECPOCH" ( NACTELID IN NUMBER, STRSTARTSIM IN VARCHAR, STRLASTSIM IN VARCHAR, NRESULT OUT INTEGER )
跟踪异常:
13:24:40.295 [http-nio-8080-exec-6] DEBUG o.s.orm.jpa.JpaTransactionManager - Participating in existing transaction
Hibernate: {call RECPOCH(?,?,?,?)}
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Error calling CallableStatement.getMoreResults
at org.hibernate.jpa.internal.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:230)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.hibernate.exception.GenericJDBCException: Error calling CallableStatement.getMoreResults
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.jpa.internal.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:223)
... 90 more
Caused by: java.sql.SQLException: Le nombre de noms de paramètre ne concorde pas avec celui des paramètres inscrits
at oracle.jdbc.driver.OracleSql.setNamedParameters(OracleSql.java:198)
at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:197)
13:24:40.833 [http-nio-8080-exec-6] DEBUG o.s.orm.jpa.JpaTransactionManager - Initiating transaction commit
at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:197)
13:24:40.833 [http-nio-8080-exec-6] DEBUG o.s.orm.jpa.JpaTransactionManager - Committing JPA transaction on EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@6db6eb5c]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyCallableStatement.execute(HikariProxyCallableStatement.java)
at org.hibernate.result.internal.OutputsImpl.(OutputsImpl.java:52)
... 95 more
实体中的命名排序过程定义:
@NamedStoredProcedureQueries({
@NamedStoredProcedureQuery(name = Pochette.SP_RECPOCH_NAME, procedureName = "RECPOCH", parameters = {
@StoredProcedureParameter(name = Pochette.SP_RECPOCH_PARAM_NRESULT, mode = ParameterMode.OUT, type = Long.class),
@StoredProcedureParameter(name = Pochette.SP_RECPOCH_PARAM_STRSTARTSIM, mode = ParameterMode.IN, type = String.class),
@StoredProcedureParameter(name = Pochette.SP_RECPOCH_PARAM_STRLASTSIM, mode = ParameterMode.IN, type = String.class),
@StoredProcedureParameter(name = Pochette.SP_RECPOCH_PARAM_NACTELID, mode = ParameterMode.IN, type = Long.class)
})
})
请问你有什么想法吗?
解决方案
您还需要为存储过程注册参数,因此您的代码如下所示:
StoredProcedureQuery storedProcedureQuery = getEntityManager().createNamedStoredProcedureQuery("RECPOCH");
storedProcedureQuery.registerStoredProcedureParameter(Pochette.SP_RECPOCH_PARAM_STRSTARTSIM, String.class, ParameterMode.IN);
storedProcedureQuery.setParameter(Pochette.SP_RECPOCH_PARAM_STRSTARTSIM, pochetteCriteria.getDebutNumPochette());
storedProcedureQuery.registerStoredProcedureParameter(Pochette.SP_RECPOCH_PARAM_STRLASTSIM, String.class, ParameterMode.IN);
storedProcedureQuery.setParameter(Pochette.SP_RECPOCH_PARAM_STRLASTSIM, pochetteCriteria.getFinNumPochette());
storedProcedureQuery.registerStoredProcedureParameter(Pochette.SP_RECPOCH_PARAM_NACTELID, Integer.class, ParameterMode.IN);
storedProcedureQuery.setParameter(Pochette.SP_RECPOCH_PARAM_NACTELID, pochetteCriteria.getActelId());
storedProcedureQuery.registerStoredProcedureParameter(Pochette.SP_RECPOCH_PARAM_NRESULT, Integer.class, ParameterMode.OUT);
storedProcedureQuery.execute();
Integer result = (Integer) storedProcedureQuery.getOutputParameterValue(Pochette.SP_RECPOCH_PARAM_NRESULT);
return Long.valueOf(result);
编辑:这对 OP 的问题没有帮助,因为createNamedStoredProcedureQuery
使用了它,它会自动声明和注册参数。为了完整起见,我将把它留在这里。
推荐阅读
- reactjs - 我该如何解决这个错误 undefined is not an object(evalating'navigation.Navigate')?
- azure-sql-database - 一段时间后第一次请求的 Azure SQL 超时
- python - 在 azure Databricks 中跨笔记本使用用 python 编写的用户定义函数
- vue.js - Bootstrap VueJS无法添加类
- .net-core - 如何使用 OWL Microsoft Exchange Server 连接 MailKit?
- css - 创建每 10 个项目添加的动态 div 元素
- java - 当我在 Java 中使用 jaxb 时,如何使用 gradle 将我的自定义注释添加到生成的类中?
- spring - 获取 yaml 文件而不是 application.properties
- go - 如何在 Golang 中的并发代码中使用 defer
- django - 从文件 Django-Rest_Framework 序列化许多对象的问题