What is the best way to execute a stored procedure of the database?
I didn’t see this in the documentation so I was using This article as a reference but it requires the use of an @Procedure annotation which I don’t know the source of this annotation to add it to my list of imports.
After working through the article I found a solution but uncovered another problem related to using the PostgreSQL JDBC driver.
Things I have done to execute a stored procedure from the UI:
GenNode), I added the annotation @NamedStoredProcedure.GenNodeRepository) that extends CrudRepository, I added a method annotated with @Procedure associated with the name equal to @NamedStoredProcedure name from GenNode.@Autowired) the repo interface (GenNodeRepository) and called the repo method, which causes execution of the stored procedure attached to GenNode.Note: After adding the @Procedure annotation I needed to restart IntelliJ for it to recognize the annotation.
After doing this I was able to execute the stored procedure, however the following runtime error manifested:
PSQLException: ERROR: gen_node_pr_upd() is a procedure
Hint: To call a procedure, use CALL.
Position: 15
In a system with a PostgreSQL database combined with JPA it needs help to determine whether to call a given function or procedure name is in fact a function or procedure. By default, it thinks the name is a function and so trying to execute the stored procedure gen_node_pr_upd generates an error.
This problem was asked and answered in Stackoverflow at https://stackoverflow.com/questions/65696904/postgresql-11-stored-procedure-call-error-to-call-a-procedure-use-call-java
The answer requires the use of the PostgreSQL JDBC driver parameter escapeSyntaxCallMode.
This parameters determines the mode PostgreSQL will use to execute a given name as a function or as a stored procedure.
There are 3 modes:
callIfNoReturn ", as I wanted PostgreSQL to auto detect whether I am calling function or procedureThis only seems to be a problem in a system with PostgreSQL ver >= 11.0 using JDBC driver ver >= 42.2.5.
The I added the JDBC property escapeSyntaxCallMode to my application.properties property of main.datasource.url but it appears to have no effect and I am assuming this parameter is not actually passed along to the JDBC driver.
Does anyone have issues passing JDBC parameters to the JDBC driver using the application property main.datasource.url ?
See these related webpages:
Also, I have created a branch and issue rated to this subject for review.
Any help is appreciated.
It looks like the issue may be with with how Stored Procedures are executed under Java Persistence API (JPA), but I can’t narrow it down.
I was able to verify that the JDBC URL is being passed correctly to the PostgreSQL JDBC driver and it is aware of the escapeSyntaxCallMode property being passed.
I appears that when I declare a stored procedure with NO parameters, the JPA architecture thinks the stored procedure is a function and executes it as a SELECT query.
However, if I declare a stored procedure with one in parameter it recognizes it as a call that has a stored procedure associated with it but for some reason still thinks it is a SELECT query and generates the following runtime error and stack trace:
SQLException: Connection is closed
java.sql.SQLException: Connection is closed
at com.zaxxer.hikari.pool.ProxyConnection$ClosedConnection.lambda$getClosedConnection$0(ProxyConnection.java:515)
at jdk.proxy3/jdk.proxy3.$Proxy165.rollback(Unknown Source)
at com.zaxxer.hikari.pool.ProxyConnection.rollback(ProxyConnection.java:396)
at com.zaxxer.hikari.pool.HikariProxyConnection.rollback(HikariProxyConnection.java)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicRollbackTransaction(DatabaseAccessor.java:1791)
at org.eclipse.persistence.internal.databaseaccess.DatasourceAccessor.rollbackTransaction(DatasourceAccessor.java:692)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.rollbackTransaction(DatabaseAccessor.java:1781)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicRollbackTransaction(AbstractSession.java:831)
at org.eclipse.persistence.sessions.server.ClientSession.basicRollbackTransaction(ClientSession.java:211)
at org.eclipse.persistence.internal.sessions.AbstractSession.rollbackTransaction(AbstractSession.java:3983)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.rollbackTransaction(UnitOfWorkImpl.java:4869)
at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.rollbackTransaction(RepeatableWriteUnitOfWork.java:534)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.release(UnitOfWorkImpl.java:4669)
at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.rollback(EntityTransactionImpl.java:189)
at org.springframework.orm.jpa.JpaTransactionManager.doRollback(JpaTransactionManager.java:589)
at io.jmix.data.impl.JmixTransactionManager.doRollback(JmixTransactionManager.java:66)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:835)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:809)
at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:672)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:392)
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)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
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 jdk.proxy2/jdk.proxy2.$Proxy205.execGenNodePrUpd2(Unknown Source)
at ca.ampautomation.ampata.screen.gennode.FinTxferBrowse2.onUpdateAllRowAllBackEndCalcBtnClick(FinTxferBrowse2.java:620)
at io.jmix.core.common.event.EventHub.publish(EventHub.java:170)
at io.jmix.ui.component.impl.AbstractComponent.publish(AbstractComponent.java:85)
at io.jmix.ui.component.impl.ButtonImpl.buttonClicked(ButtonImpl.java:78)
at io.jmix.ui.widget.JmixButton.fireClick(JmixButton.java:77)
at com.vaadin.ui.Button$1.click(Button.java:57)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at com.vaadin.server.ServerRpcManager.applyInvocation(ServerRpcManager.java:153)
at com.vaadin.server.ServerRpcManager.applyInvocation(ServerRpcManager.java:115)
at com.vaadin.server.communication.ServerRpcHandler.handleInvocation(ServerRpcHandler.java:442)
at com.vaadin.server.communication.ServerRpcHandler.handleInvocations(ServerRpcHandler.java:407)
at com.vaadin.server.communication.ServerRpcHandler.handleRpc(ServerRpcHandler.java:275)
at com.vaadin.server.communication.UidlRequestHandler.synchronizedHandleRequest(UidlRequestHandler.java:83)
at com.vaadin.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:40)
at com.vaadin.server.VaadinService.handleRequest(VaadinService.java:1636)
at com.vaadin.server.VaadinServlet.service(VaadinServlet.java:465)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:750)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:327)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:115)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:81)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:122)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:116)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:126)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:81)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:109)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:102)
at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:93)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:149)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:147)
at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:125)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:103)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:89)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:90)
at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:75)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:110)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:80)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:55)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:211)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:183)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:354)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:267)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:889)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:833)
So as a workaround I am executing a native query using EntityManager through 2 classes: GenNodeRepositoryCustom.java
package ca.ampautomation.ampata.entity;
public interface GenNodeRepositoryCustom {
void execGenNodePrUpdNative();
void execFinTxferPrUpdNative();
}
GenNodeRepositoryCustomImpl.java
package ca.ampautomation.ampata.entity;
import org.springframework.transaction.annotation.Transactional;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
public class GenNodeRepositoryCustomImpl implements GenNodeRepositoryCustom {
@PersistenceContext
private EntityManager em;
@Override
@Transactional
public void execGenNodePrUpdNative(){
this.em.createNativeQuery("call Gen_Node_Pr_Upd()").executeUpdate();
}
@Override
@Transactional
public void execFinTxferPrUpdNative(){
this.em.createNativeQuery("call Fin_Txfer_Pr_Upd()").executeUpdate();
}
}
For now I am using this workaround and can be found in Ampata main branch
https://github.com/AmpAutomation/Ampata/tree/main
Have you tried to use plain JDBC or Spring’s JdbcTemplate for working with stored procedures? What do you need from JPA in this regard?
Just in case, you can easily create a JdbcTemplate instance as follows:
@Component
public class MyDao {
@Autowired
private DataSource dataSource;
public String executeMyProc() {
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
// ...
I struggled a little to get here, it may not be the best process, but for me it works, it executes a function inside the database and returns data, for UPDATE functions it is not much different, just a few adjustments and committing the transaction at the end
@Component("rn_ExportacaoTeiaCardBean")
public class ExportacaoTeiaCardBean implements Serializable {
private static final Logger log = LoggerFactory.getLogger(ExportacaoTeiaCardBean.class);
@PersistenceContext(unitName = "STORE_NAME") // store name
private EntityManager entityManager;
@Autowired
private DataManager dataManager;
public List<clsRetExp> ExportarVendas(Filtros filtros) {
log.debug("");
List<clsRetExp> objRet = new ArrayList<>();
String sFormatoData = "yyyyMMdd";
String sComando;
String separador = "";
List<ConfiguracaoExportacao> cfg = dataManager.load(ConfiguracaoExportacao.class)
.all().list();
if (cfg.size() > 0) {
if (cfg.get(0).getSeparador() != null)
separador = cfg.get(0).getSeparador();
}
sComando = "SELECT * FROM rp_retorno_vendas_teia( p_empresa, p_data_inicial, p_data_final, p_separado)";
log.trace("Comando -> {}", sComando);
if (filtros.getEmpresa() != null)
sComando = sComando.replace("p_empresa", filtros.getEmpresa().getId().toString());
else
sComando = sComando.replace("p_empresa", "0");
DateFormat df = new SimpleDateFormat(sFormatoData);
String sData = df.format(filtros.getDataInicial());
sComando = sComando.replace("p_data_inicial", "'" + sData + "'");
sData = df.format(filtros.getDataFinal());
sComando = sComando.replace("p_data_final", "'" + sData + "'");
sComando = sComando.replace("p_separado", "'" + separador + "'");
log.trace("Comando -> {}", sComando);
List lista = entityManager.createNativeQuery(sComando).getResultList().stream().toList();
Iterator it;
StringBuilder sb = new StringBuilder();
clsRetExp obj = new clsRetExp();
int numeroArquivo;
String linhaArquivo;
for (it = lista.iterator(); it.hasNext(); ) {
Object[] row = (Object[]) it.next();
int iLoteServico = (int) row[1];
linhaArquivo = row[6].toString();
if (iLoteServico == 0) {
if (sb.toString().length() > 0) {
obj.setLinha(sb);
objRet.add(obj);
obj = new clsRetExp();
sb = new StringBuilder();
}
obj.setNomeArquivo(row[3].toString() + "-" + row[0].toString() + ".txt");
numeroArquivo = retornaNSA();
linhaArquivo = linhaArquivo.replace("ZZZZZZZ", StringUtils.leftPad(String.valueOf(numeroArquivo), 7, '0'));
}
sb.append(linhaArquivo);
sb.append("\n");
}
if (sb.toString().length() > 0) {
obj.setLinha(sb);
objRet.add(obj);
}
return objRet;
}