Calling stored procedure with typed parameter from java spring boot using @Procedure

Question

I've developed a stored procedure to do some task in the DB which gets a custom type parameter.

DROP TYPE IF EXISTS custom_type CASCADE;
CREATE TYPE custom_type AS (
    field1 integer,
    field2 integer
);

CREATE OR REPLACE PROCEDURE my_proc(param1 custom_type) AS $$
BEGIN
    -- DO NOTHING FOR NOW
END;
$$ LANGUAGE 'plpgsql';

in my java code I've created this custom type as a class and added connection to the procedure in my repository as followed..

@Data
public class CustomType {
    int field1;
    int field2;
    public CustomType(Integer field1, Integer field2) {
        this.field1 = field1;
        this.field2 = field2;
    }
}

public interface MyRepo extends RdbFilterQueryRepository<MyEntity> {

   @Procedure(value = "my_proc")
   void myProc(@Param("param1") CustomType myParam1);

}

When I call the procedure I get this error: Caused by: java.lang.IllegalArgumentException: Cannot determine the bindable type for procedure parameter: null

I've tried many ways but nothing worked out. I've changed the custom type to a simple integer and it works.. I understand that it is connected to the parameter type and binding between the type in the DB to my class type. But I don't find a way to do it. What am I missing?

---UPDATE---

I've managed to make it work with a simple custom type (containing single field), by implementing UserType<T> as a type converter, and registering it by another component implementation for TypeContributor.

In our case the custom type has more than one field. So I've tried to implement the type converter CompositeUserType<T> but sadly I don't manage to register it, because the TypeContributor register function gets only UserType<T>.

I've created a ticket in hibernate for it. They suggested a workaround using @CompositeTypeRegistration but it does not work as well..

Answer 1

Finally Found a way to make it work. It is not optimal but it works.

Our java type - equivalent to our postgres type custom_type (check out DB declaration at my post)

public record MyType(int field1, int field2) {
        @Override
        public String toString() {
            return "(" + field1 + "," + field2 + ")";
        }
    }

Declaring the converter type

class MyUserType implements UserType<MyType> {
    ...

    public static final MyUserType INSTANCE = new MyUserType();


    @Override
    public void nullSafeSet(PreparedStatement st, MyType value, int index, 
    SharedSessionContractImplementor session) throws SQLException {
        st.setString(index, value.toString());
    }

    ...
}

Registering our custom type

public class MyUserTypesContributor implements TypeContributor {

    public MyUserTypesContributor () {
    }

    @Override
    public void contribute(TypeContributions typeContributions, ServiceRegistry serviceRegistry) {
        typeContributions.contributeType(MyUserType.INSTANCE);
    }
}

create the file ./resources/META-INF.services/org.hibernate.boot.model.TypeContributor content of the file will be the full package of this type contributor

com......hibernate.types.MyUserTypesContributor

This way our types contributor will be loaded by hibernate and our type will be registered

In the repository

public interface MyRepository extends RdbFilterQueryRepository<...> {
    ...

    @Procedure("my_procedure")
    @Transactional
    @Modifying
    void myProc(@Param("param1")  MyType val);
     
    ...
}

and what left to do is to call our procedure

myRepo.myProc(new MyType(123,345));