英文:
How can I read a json column from postgresql into java?
问题
这个 PostgreSQL 查询：
SELECT array_to_json(array_agg(row_to_json(c))) FROM contacts c;
返回一个类型为 json 的单列。以下是在 pgadmin 中的可行查询...
我想要从我的 Java 服务器执行相同的查询，该服务器使用 Jackson 库。我应该用什么类型来接收响应？我尝试过将响应接收为 PGobject 变量，但在查询后，我发现该变量为 null。
我还尝试过将响应接收为 String，但是会抛出以下错误：
java.lang.ClassCastException: class org.postgresql.util.PGobject cannot be cast to class java.lang.String (org.postgresql.util.PGobject is in unnamed module of loader 'app'; java.lang.String is in module java.base of loader 'bootstrap')
英文:
This postgresql query
SELECT array_to_json(array_agg(row_to_json(c))) FROM contacts c;
brings back a single column of type json. Here is the working query in pgadmin...
I would like to execute this same query from my Java server, which uses Jackson. What type should I read the response into? I have tried receiving the response as a PGobject variable, but after querying, I see that the variable is null.
I have also tried receiving the response into a String, but this throws the error:
java.lang.ClassCastException: class org.postgresql.util.PGobject cannot be cast to class java.lang.String (org.postgresql.util.PGobject is in unnamed module of loader 'app'; java.lang.String is in module java.base of loader 'bootstrap')
答案1
得分: 1
如果结果是 JSON（或 jsonb），您可以使用 ResultSet.getString() 来读取它。然后将该对象传递给 Jackson 的 ObjectMapper 来将其转换为其他内容。
顺便说一句：您可以简化聚合查询如下：
select jsonb_agg(to_jsonb(c))
from contacts c;
英文:
If the result is a json (or jsonb) you can use ResultSet.getString() to read it. Then pass that Jackson's ObjectMapper to convert it to something else.
Btw: you can simplify your aggregation to:
select jsonb_agg(to_jsonb(c))
from FROM contacts c;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论