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.

Abdusoli · Accepted Answer · 2020-01-07 04:38:05Z

2

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;

edited Jan 7, 2020 at 4:38

answered Jan 7, 2020 at 4:31

Abdusoli

6591 gold badge10 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

davids182009 Over a year ago

send text (json) to stored procedure and parse it from there works for me! Thanks!!!

Divije Narasimhachar · Accepted Answer · 2020-11-23 17:43:30Z

1

If your function is something like

CREATE OR REPLACE FUNCTION public.function_that_takes_json(_iAmAJson json)
  RETURNS void AS

You could use:

Query query = em.createNativeQuery(select * from function_that_takes_json(?::json));
query.setParameter(1, your_json_string).getResultList();

If any of the db types above are jsonb then replace ::json with ::jsonb. I have not verified the grammatical correctness of the code. Please correct minor mistakes yourselves.

edited Nov 23, 2020 at 17:43

answered Nov 11, 2020 at 10:59

Divije Narasimhachar

3791 gold badge5 silver badges15 bronze badges

Collectives™ on Stack Overflow

Java - How to send JSON to stored procedure PostgreSQL 9.6

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related