Postgres Procedure call from Spring boot with Composite Array type as Input and Output

Question

Good Day !

As a part of POC , I'm calling a procedure from my Spring boot code as below and I'm able to make the call with Composite Array type [In & Out]. However , I want to check if there is any better way for doing it. As you can see below , the input are being converted to string and output PgObject is being converted back to Java DTO from String.

Is there any library or a better way to achieve this. Something similar to STRUCT and ARRAY in Oracle JDBC ?

Postgres Type :

CREATE TYPE public.sidd_test_t AS
(
operation_seq_num integer,
component_item_id integer,
name text
);

sidd_test_t.java

@Data
@AllArgsConstructor
public class sidd_test_t {

private int operation_seq_num;
private int component_item_id;
private String name;

}

Method Call :

private boolean sidProcPassaRRAY2() throws SQLException {


SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate).withSchemaName("public")
        .withProcedureName("sid_passArray2").withoutProcedureColumnMetaDataAccess();

jdbcCall.addDeclaredParameter(new SqlParameter("arr_type_in", Types.ARRAY));
jdbcCall.addDeclaredParameter(new SqlOutParameter("arr_type_out", Types.ARRAY));

    
PGobject _pgObject1 = new PGobject();
_pgObject1.setType("sidd_test_t");
//_pgObject1.setValue("(23,218,Siddharth)");
_pgObject1.setValue(ObjToString(new sidd_test_t(23, 218, "Siddharth")));

PGobject _pgObject2 = new PGobject();
_pgObject2.setType("sidd_test_t");
//_pgObject2.setValue("(24,215,Siddharth2)");
_pgObject2.setValue(ObjToString(new sidd_test_t(23, 219, "Ashutosh")));

Object[] inObj = new Object[2];
inObj[0] = _pgObject1;
inObj[1] = _pgObject2;

final Array stringsArray = jdbcTemplate.getDataSource()
                            .getConnection()
                            .createArrayOf("sidd_test_t", inObj); 

Map<String, Object> inParams = new HashMap<String, Object>();

inParams.put("arr_type_in", stringsArray);

SqlParameterSource in = new MapSqlParameterSource(inParams);

Map outParams = jdbcCall.execute(in);


PgArray _pgArray = (PgArray)outParams.get("arr_type_out");

Object _pg1 =_pgArray.getArray();
    
Object[] _arr1 = (Object[]) _pgArray.getArray();
    
List<sidd_test_t> _retData = new ArrayList<sidd_test_t>();

for(Object _p : _arr1) {
    PGobject _o = (PGobject)_p;
    String resultAsString = _o.getValue();
    logger.debug("Result  : " + resultAsString);
    
    // Remove enclosing brackets received in result
    String finalRsult = resultAsString.substring(1, resultAsString.length() - 1);
            
    Object[] _params = finalRsult.split(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");

    
    int t1 = new Integer(_params[0].toString());
    int t2 =  new Integer(_params[1].toString());
    String t3 = _params[2].toString();
    
    _retData.add(new sidd_test_t(t1, t2, t3));
    
}



logger.debug("Size of return data : " + _retData.size());
logger.debug("return data value: " + _retData.get(1).getName());

return true;
}

Method ObjToString :

private String ObjToString(sidd_test_t param) {
    return "(" + param.getOperation_seq_num() + "," + param.getComponent_item_id() + "," + param.getName() + ")";
}

PostGres Procedure :

CREATE OR REPLACE PROCEDURE sid_passArray2(IN arr_type_in sidd_test_t[] , OUT arr_type_out sidd_test_t[]) 
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE 
BEGIN
    arr_type_out := arr_type_in;
END;
$BODY$;

Maven dependency:

<dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.5.0</version>
</dependency>

Postgres Procedure call from Spring boot with Composite Array type as Input and Output

0 Answers0