I am encountering an issue with Hibernate 6's NativeQuery when trying to pass a parameter of type array to a database function. The problem arises when the parameter is passed as a single value or as a list of values.

The issue occurs in the following method:

public Integer getTotalHousedPoultry(List<Long> idOwners) {
    // Query
    StringBuilder sql = new StringBuilder("SELECT calculate_batch_poultry_quantity_housed_with_mortality_on_date(:idOwners)");
    try {
        NativeQuery<Integer> query = getSession().createNativeQuery(sql.toString(), Integer.class);
        query.setParameterList("idOwners", idOwners);
        return query.getSingleResult();
    } catch (Exception ex) {
        printError(getClass(), ex);
        return 0;
    }
}

The function declaration in the database is as follows:

CREATE FUNCTION calculate_batch_poultry_quantity_housed_with_mortality_on_date(id_owners BIGINT[]);

Details:

• When a single value is passed in the parameter (list size equals 1), the following error occurs:

ERROR: function calculate_batch_poultry_quantity_housed_with_mortality_on_date(bigint) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 8

• When two or more values are passed in the parameter (list size greater than 1), the following error occurs:

ERROR: function calculate_batch_poultry_quantity_housed_with_mortality_on_date(record) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 8

Attempted Solutions:

I have tried converting the list to an array using idOwners.toArray() when setting the parameter, but the same issue persists. I also attempted to pass the parameter as a direct string in the SQL query using the syntax "ARRAY[1, 2]". Although this approach seems to work, it is not a recommended solution as the syntax for array parameters may vary across different database systems, posing compatibility issues.

Expected Behavior:

The Hibernate NativeQuery should correctly pass the parameter as bigint[] to match the function declaration in the database, regardless of whether it is a single value or a list of values.