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

Question

我有一个 Postgresql 存储过程，它接收 json 类型的输入参数和文本类型的输出参数，我有一个调用存储过程的 java 应用程序，但是当从 java 调用它时，从邮递员调用时会出现此错误：

[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;

从数据库执行：

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 输入，如何实现？....任何建议/解决方案将不胜感激。

标签： javapostgresql-9.6

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;