使用Spring Data JPA调用存储过程时如何传递数组 [英] How to pass in array when calling stored procedure with Spring Data JPA

问题描述

我正在按照以下示例调用存储使用Spring Data JPA的过程.这个想法是创建一个实体并定义这样的namedstoredprocedure.

I am following this example to call stored procedure using Spring Data JPA. The idea is to create an entity and define namedstoredprocedure like this.

@Entity
@Table(name = "TRFTABLE")
@NamedStoredProcedureQuery(
        name = "processFmTrf", 
        procedureName = "FM_TRF_UTIL.process_fm_trf",
        parameters = {
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "i_trf_list", type = String.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "i_source_system", type = String.class)
        }
)
public class TrfTable implements Serializable{
}

对于具有原始类型的输入参数非常简单，例如String.class.但是，我存储的proc的输入之一是数组.我知道如何通过使用CallableStatement调用存储的proc来处理数组，如下所示:

It is pretty straightforward for input parameters with primitive types, e.g. String.class. However, one of the inputs of my stored proc is an array. I know how to handle array by calling stored proc using CallableStatement as follows:

    Connection con = getConnection();
    OracleConnection oraCon = con.unwrap(OracleConnection.class);
    // fileArray predefined.
    Array array = oraCon.createARRAY("VARCHAR2_TAB_T", fileArray); 
    CallableStatement stmt = con.prepareCall("{call FILE_TRANSFER_AUDIT_UTIL.update_file_queue_id(?,?,?,?,?,?)}");
    stmt.setArray(1, array); 

似乎必须建立数据库连接才能将数组传递给存储的proc.如何使用NamedStoredProcedure为我的实体类完成此操作?

It looks like DB Connection must be established in order to pass in array to stored proc. How do I accomplish this using NamedStoredProcedure for me entity class?

推荐答案

NamedStoredProcedureQuery不适用于传入数组，因为它需要数据库连接.我所做的是从entitymanager创建一个会话，并在doWork方法中使用数组调用存储过程:

NamedStoredProcedureQuery does not work for passing in array since it requires database connection. What I did is create a session from entitymanager and call stored procedure with array in doWork method:

    EntityManager em = Persistence.createEntityManagerFactory("DEV").createEntityManager();
    Session session = em.unwrap( Session.class );
    final String[] trfArray = trfs.toArray(new String[trfs.size()]);
    final String src = source;

    session.doWork( new Work(){
        public void execute(Connection conn) throws SQLException {
            CallableStatement stmt = null;               
            OracleConnection oraCon = conn.unwrap(OracleConnection.class);
            Array array = oraCon.createARRAY("VARCHAR2_TAB_T", trfArray);
            stmt = conn.prepareCall("{? = call FM_TRF_UTIL.process_fm_trf(?,?)}");
            stmt.registerOutParameter(1, Types.INTEGER);
            stmt.setArray(2, array);
            stmt.setString(3, src);
            stmt.execute();
            returnVal = stmt.getInt(1);
        }
    });

这篇关于使用Spring Data JPA调用存储过程时如何传递数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！