如何将JSON发送到存储过程PostgreSQL 9.6

Question

我有一个接收json类型的输入参数和text类型的输出参数的Postgresql存储过程，我有一个调用存储过程的java应用程序，但当从java调用它时，从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()

这是我的存储过程的一小部分

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

对于从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代码(使用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);

我还有其他使用文本作为输入的存储过程，但在这种情况下，我需要一个json of input，如何实现呢？任何建议/解决方案都将不胜感激。

Answer 1

通常，我会在postgresql过程中以文本形式接收json对象。你也可以这样做。在过程中，您将把json文本转换为json对象并对其进行解析。您还应该将json对象作为字符串发送到过程中。在过程中，你也应该知道json数组的大小。

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;

如果你需要json输入，它也是一样的。但是在java中，你应该以json数据类型发送输入。

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;

Answer 2

如果您的函数类似于

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

您可以使用：

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

如果上面的任何数据库类型是jsonb，则将::json替换为::jsonb。我没有验证代码的语法正确性。请自己改正小错误。