@过程注释孤立数据库连接(spring-data-jpa) [英] @Procedure annotation orphaning database connections (spring-data-jpa)

问题描述

我们在生产中有一个批处理作业，它调用存储过程来更新某些表.此存储的proc不返回任何内容.现在，由于每次运行作业时都会孤立1个连接，因此我们的数据库连接用完了.该存储库使用带有过程注释的Spring Data CrudRepository.

We have a batch job in production that calls a stored procedure to update some tables. This stored proc does not return anything. Right now, we are running out of database connections as 1 connection gets orphaned every time the job is ran. The repository uses the Spring Data CrudRepository with the procedure annotation.

我们是否缺少有关呼叫和弹簧数据jpa的信息?预先感谢！

Are we missing something in regards to the call and spring data jpa? Thanks in advance!

@Repository
public interface CertificationRepository extends CrudRepository<Certification,     Integer> {
    @Procedure("usp_batch_update_certifications")
    void updateCertifications(Date previousFireTime);
}

推荐答案

您使用哪个Spring-Data-JPA版本和哪个PersistenceProvider? 您能否指出一个简化的示例应用程序来重现该问题?

Which Spring-Data-JPA Version and which PersistenceProvider are you using? Could you point me to a reduced example app that reproduces the problem?

正如评论中所说，我可以重现您的问题. 该过程的执行需要一个不存在的TX或周围的TX永远不会提交.

As said in the comments I could reproduce your problem. The procedure execution requires a TX which is either not present or the surrounding TX is never committed.

您可以尝试使用@Transactional

角色实体:

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.NamedStoredProcedureQueries;
import javax.persistence.NamedStoredProcedureQuery;
import javax.persistence.ParameterMode;
import javax.persistence.StoredProcedureParameter;

/**
 * Oracle PL/SQL
 *
 * <pre>
 *  CREATE or replace PROCEDURE update_roles (pattern_i IN varchar) AS
 *  BEGIN 
 *    DBMS_OUTPUT.put_line('update_roles Received pattern: ' || pattern_i);
 *  END;
 * /
 * 
 * <pre>
 */
@NamedStoredProcedureQueries({
@NamedStoredProcedureQuery(name = "Role.updateRoles"
                         , procedureName = "update_roles"
                         , parameters = { 
                            @StoredProcedureParameter(name = "pattern"
                                                    , mode = ParameterMode.IN
                                                    , type = String.class) })
})
@Entity
public class Role {

    @Id @GeneratedValue//
    private Long id;

    private String name;
    ...

RoleRepository:

The RoleRepository:

import java.io.Serializable;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

interface RoleRepository extends CrudRepository<Role, Serializable> {

    @Procedure
    @Transactional
    void updateRoles(@Param("pattern") String pattern);
}

这篇关于@过程注释孤立数据库连接(spring-data-jpa)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！