[FIXED] How to pass in array when calling stored procedure with Spring Data JPA

Issue

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{
}

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); 

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?

Solution

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);
        }
    });

Answered By - ddd