Java - How to send JSON to stored procedure PostgreSQL 9.6

Question

I have an Postgresql Stored Procedure that receive an input parameter of json type and output parameter of text type, i have an java application that call the stored procedure, but when call it from java obtain this error when call from postman:

[stdout] (default task-4) [EL Warning]: 2020-01-06 19:36:19.087--UnitOfWork(1801127126)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
[stdout] (default task-4) Internal Exception: org.postgresql.util.PSQLException: No hstore extension installed.
(default task-4) Error Code: 0
[stdout] (default task-4) Call: {?= CALL get_json(?)}
[stdout] (default task-4)   bind => [2 parameters bound]
[stdout] (default task-4) Query: ResultSetMappingQuery()

This is a little of my Stored Procedure

CREATE OR REPLACE FUNCTION get_json(IN object_json json, OUT result text) 
RETURNS text AS $$
DECLARE
.......
BEGIN
  ......
END;
$$ LANGUAGE plpgsql;

For execute from the DataBase:

SELECT consulta_cotizacion('{"productos":[{"id_cotizacion":1,"modelo":"BOLSA PLANA TROQUEL PEQUENA","tela":"KAMBREL O POLITEX 70GR","cantidad":300,"ancho":20,"alto":20,"fuelle":30,"manija":20,"destino":167,"accesorios_modelo":[{"id":10,"ancho":10,"alto":20},{"id":8,"ancho":20,"alto":30},{"id":2,"ancho":20,"alto":30}],"accesorios_adicionales":[{"id":17,"ancho":3,"alto":22}],"estampado_cara_frontal":"","estampado_cara_posterior":"1 TINTA X 1 CARA DORADO","estampado_fuelle_izquierdo":"1 TINTA X 1 CARA DORADO","estampado_fuelle_derecho":"","estampado_fuelle_base":"1 TINTA X 1 CARA DORADO"},{"id_cotizacion":1,"modelo":"BOLSA PLANA TROQUEL PEQUENA","tela":"KAMBREL O POLITEX 70GR","cantidad":500,"ancho":20,"alto":20,"fuelle":30,"manija":20,"destino":167,"accesorios_modelo":[{"id":10,"ancho":10,"alto":20},{"id":8,"ancho":20,"alto":30},{"id":2,"ancho":20,"alto":30}],"accesorios_adicionales":[{"id":17,"ancho":3,"alto":22}],"estampado_cara_frontal":"","estampado_cara_posterior":"1 TINTA X 1 CARA DORADO","estampado_fuelle_izquierdo":"1 TINTA X 1 CARA DORADO","estampado_fuelle_derecho":"","estampado_fuelle_base":"1 TINTA X 1 CARA DORADO"},{"id_cotizacion":2,"modelo":"BOLSA PLANA TROQUEL PEQUENA","tela":"KAMBREL O POLITEX DE 90GR","cantidad":244,"ancho":20,"alto":20,"fuelle":30,"manija":20,"destino":167,"accesorios_modelo":[{"id":10,"ancho":10,"alto":20},{"id":8,"ancho":20,"alto":30},{"id":2,"ancho":20,"alto":30}],"accesorios_adicionales":[{"id":17,"ancho":3,"alto":22}],"estampado_cara_frontal":"","estampado_cara_posterior":"1 TINTA X 1 CARA DORADO","estampado_fuelle_izquierdo":"1 TINTA X 1 CARA DORADO","estampado_fuelle_derecho":"","estampado_fuelle_base":"1 TINTA X 1 CARA DORADO"},{"id_cotizacion":2,"modelo":"BOLSA PLANA TROQUEL PEQUENA","tela":"KAMBREL O POLITEX DE 90GR","cantidad":5000,"ancho":20,"alto":20,"fuelle":30,"manija":20,"destino":167,"accesorios_modelo":[{"id":10,"ancho":10,"alto":20},{"id":8,"ancho":20,"alto":30},{"id":2,"ancho":20,"alto":30}],"accesorios_adicionales":[{"id":17,"ancho":3,"alto":22}],"estampado_cara_frontal":"","estampado_cara_posterior":"1 TINTA X 1 CARA DORADO","estampado_fuelle_izquierdo":"1 TINTA X 1 CARA DORADO","estampado_fuelle_derecho":"","estampado_fuelle_base":"1 TINTA X 1 CARA DORADO"}]}')

Java Code (Using JPA)

JsonReader jsonReader = Json.createReader(new StringReader(clientData));
JsonObject clientJson = jsonReader.readObject();

EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory( "BioBolsaWS" );
EntityManager em = entityManagerFactory.createEntityManager();

StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("get_json");
storedProcedure.registerStoredProcedureParameter("object_json", JsonObject.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter("result", String.class, ParameterMode.OUT);
storedProcedure.setParameter("object_json", clientJson);
storedProcedure.execute();

String final = (String) storedProcedure.getOutputParameterValue("result");
em.close();
System.out.println("SP..." + final);

I have other stored procedures that works with text as input, but in this case i need a json of input, how can achieve that?.... Any suggestions/solutions would be much appreciated.

Answer 1

Usually I receive json object as a text inside my postgresql procedure. You can also do like this. Inside procedure you will convert json text to json object and parse it. You should also send json object as string into procedure. Inside procedure you should know json array size as well.

CREATE OR REPLACE FUNCTION get_json(IN object_json text, OUT result text) 
RETURNS text AS $$
DECLARE
my_json json;
incr integer := 0;
json_array_size := 0;
return_value text;
BEGIN
        my_json := (SELECT cast(object_json AS json));
        while incr < json_size loop
          code_val := (SELECT my_json -> 'productos' ->incr->>'modelo');
        end loop;


        return result;
END;
$$ LANGUAGE plpgsql;

In case you need json input, it is also the same. But inside java you should send input as json data type.

CREATE OR REPLACE FUNCTION get_json(object_json json) 
RETURNS text AS $$
DECLARE

incr integer := 0;
json_array_size := 0;
result text := 'success';
BEGIN

        while incr < json_size loop
          code_val := (SELECT $1 -> 'productos' ->incr->>'modelo');
        end loop;


        return result;
END;
$$ LANGUAGE plpgsql;