Spring Boot - PostgreSQL Function Array Parameter

Question

When I use a postgresql function with spring boot, it returns a data mismatch error when I use the array parameter.

I have a model file. I'm saving data in sequence to this model file. When I send this to the function in the following way, I get an error.

        StoredProcedureQuery subscriber = entityManager.createStoredProcedureQuery("fn_subscriber_to_iservice");
    subscriber.registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN);
    subscriber.registerStoredProcedureParameter(2, Integer.class, ParameterMode.IN);
    subscriber.registerStoredProcedureParameter(3, String.class, ParameterMode.IN);
    subscriber.registerStoredProcedureParameter(4, String.class, ParameterMode.IN);
    subscriber.registerStoredProcedureParameter(5, Double.class, ParameterMode.IN);
    subscriber.registerStoredProcedureParameter(6, Double.class, ParameterMode.IN);
    subscriber.registerStoredProcedureParameter(7, Double.class, ParameterMode.IN);
    subscriber.registerStoredProcedureParameter(8, Integer.class, ParameterMode.IN);
    subscriber.registerStoredProcedureParameter(9, Integer.class, ParameterMode.IN);
    **subscriber.registerStoredProcedureParameter(10, Object[].class, ParameterMode.IN)**;


subscriber.setParameter(1, Integer.valueOf(String.valueOf(payload.getService_id())));
    subscriber.setParameter(2, Integer.valueOf(String.valueOf(userDetail.getId())));
    subscriber.setParameter(3, String.valueOf(payload.getStart_date()));
    subscriber.setParameter(4, String.valueOf(payload.getEnd_date()));
    subscriber.setParameter(5, payload.getTotal_price());
    subscriber.setParameter(6, 0.00);
    subscriber.setParameter(7, payload.getPrice_per_person());
    subscriber.setParameter(8, Integer.valueOf(String.valueOf(payload.getStart_station_id())));
    subscriber.setParameter(9, Integer.valueOf(String.valueOf(payload.getEnd_station_id())));
    **subscriber.setParameter(10, payload.getPassengers().toArray());**

PostgreSQL function parameters

CREATE OR REPLACE FUNCTION "public"."fn_subscriber_to_iservice"("_service_id" int4, "user_id" int4, "_start_date" varchar, "_end_date" varchar, "total_price" float8, "total_month" float8, "price_per_person" float8, "_start_station_id" int4, "_end_station_id" int4, "passengerlist" json)

ERROR

ERROR 15464 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: Error calling CallableStatement.getMoreResults] with root cause org.postgresql.util.PSQLException: ERROR: function fn_subscriber_to_iservice(integer, integer, character varying, character varying, double precision, double precision, double precision, integer, integer, bytea) does not exist İpucu: No function matches the given name and argument types. You might need to add explicit type casts. Position: 15

Thanks.

you don't need a "stored procedure" object to call a function. Can you tell Hibernate to simply run a select query, e.g. select fn_subscriber_to_iservice(?,?...) — user330315
– user330315, Commented Nov 16, 2018 at 7:50

coladict · Accepted Answer · 2018-11-16 11:29:23Z

The problem is you're passing an array to a json parameter over here:

subscriber.setParameter(10, payload.getPassengers().toArray());

You need to convert it to a JSON string first. Assuming the function actually wants passenger ids, what you can do is the following.

StringBuilder ab = new StringBuilder();
ab.append('{');
for (Passenger p : payload.getPassengers()) {
    ab.append(p.getId()).append(',');
}
ab.replace(ab.length()-1, ab.length(), "}");
subscriber.setParameter(10, ab.toString());

And change the function definition's final parameter from "passengerlist" json to "passengerlist" bigint[] or int[], depending on the ID type. This will also save you the painful conversion from json array to integer ids you could use.

Note, that this algorithm describes the stringified version of PostgreSQL arrays and is not compatible with other databases. It also does not describe arrays containing strings or their escape sequences. This simple version only works with numeric values.

Ultimately this might also not work, if an automatic typecast function is not registered from string to the given type.

Collectives™ on Stack Overflow

Spring Boot - PostgreSQL Function Array Parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related