How to call postgresql function with more than one input and output parameter from Java?

Question

There a function written in postgresql(V_11) with more than one input parameters and more than one output parameters returned as table.

CREATE OR REPLACE FUNCTION schema_name.function_name(param1 varchar, param2 int, param3 int)
RETURNS TABLE (column1 int, column2 numeric, column3 int, column3 int)
AS $$
    return query
    SELECT column1, column2, column3, column3
    FROM schema_name.table
    WHERE {condition}
BEGIN

How to call this function from Java Spring boot using JPA and directly assign it to a List ?

If we can make a class with same name of column name returned from function.

Answer 1

I got the answer after trying with multiple methods and permutations. You can call the db function by nativeQuery or using StoredProcedureQuery(which internally uses the same nativequery, same like CallableStatement)

You can autowired your entityManager class :

 @Autowired
EntityManager entityManager;

You can call a storedProcedure or function by StoredProcedureQuery like this :

StoredProcedureQuery query = entityManager
        .createStoredProcedureQuery("schema_name.function_name")
        .registerStoredProcedureParameter(1, String.class, ParameterMode.IN)
        .registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
        .registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
        .setParameter(1, param1)
        .setParameter(2, param2)
        .setParameter(3, param3);

query.execute();
List<Object[]> dataList= query.getResultList();

You will get the resultList in List form. Object[] will have values mapped to your return type defined by function in schema and you need to iterate to convert in your object.

(You can get the field names (ColumnIndexNames) in query object in debug mode : Please let me know if one can able to get results directly in List form)

iterate the dataList and convert into the MyClass object list.

List<MyClass> myClassObjectList = new ArrayList<>();
dataList.forEach( (data) -> {
    MyClass myClassObject= new MyClass();
    myClassObject.setField1(Integer.parseInt(slsTOData[0].toString()));
    // code goes on for conversion logic
}