在一个查询中更新多行，但我们期望的输入来自多个数据的json对象 [英] update multiple rows in one query but the input we are expecting is from json object of multiple data
问题描述
update users as u set -- postgres FTW
email = u2.email,
first_name = u2.first_name,
last_name = u2.last_name
from (values
(1, 'hollis@weimann.biz', 'Hollis', 'O\'Connell'),
(2, 'robert@duncan.info', 'Robert', 'Duncan')
) as u2(id, email, first_name, last_name)
where u2.id = u.id;
上面的查询用于在一个查询中更新多行，它也有效地工作，但是下面有一个JSON:
Above query is used to update multiple rows in one query and it works efficiently as well but I have a JSON below:
Person:{[id:1,email:"[xyz@abc.com]",first_name:"John",last_name:"Doe"],[id:2,email:"[xyz@abc.com]",first_name:"Robert",last_name:"Duncan"],[id:3,email:"[xyz@abc.com]",first_name:"Ram",last_name:"Das"],[id:4,email:"[xyz@abc.com]",first_name:"Albert",last_name:"Pinto"],[id:5,email:"[xyz@abc.com]",first_name:"Robert",last_name:"Peter"],[id:6,email:"[xyz@abc.com]",first_name:"Christian",last_name:"Lint"],[id:7,email:"[xyz@abc.com]",first_name:"Mike",last_name:"Hussey"],[id:8,email:"[xyz@abc.com]",first_name:"Ralph",last_name:"Hunter"]};
此类JSON有1000个数据，我想使用JPA将其插入数据库中.目前，我是通过迭代此操作插入它的，这会使我的代码变慢，是否有其他可以实现的替代方法.
Such JSON has 1000 data which I want to insert in the Database using JPA. Currently I have inserted it by iterating this which makes my code slow,Is there any other alternative which can be implemented.
任何帮助将不胜感激.
这是我的Java代码:
Here is my Java Code :
public Boolean multiEditPerson(List<PersonList> personList) {
for (PersonList list : personList) {
Person personMstr = em.find(Person.class, list.getId());
personMstr.setFirstName(list.getFirstName());
personMstr.setLastName(list.getLastName());
personMstr.setEmail(Arrays.toString(list.getEmail()));
em.persist(personMstr);
}
return Boolean.TRUE;
}
推荐答案
您可以基于json文档进行批量插入.您应该重新格式化文档，因为问题中显示的格式很奇怪且不切实际.
You can do a bulk insert based on the json document. You should reformat the document as the format shown in the question is strange and unpractical.
完整的示例:
create table example(id int primary key, email text, last_name text, first_name text);
with jsondata(jdata) as (
values
(
'[
{"id": 1, "email": "[xyz@abc.com]", "first_name": "John", "last_name": "Doe"},
{"id": 2, "email": "[xyz@abc.com]", "first_name": "Robert", "last_name": "Duncan"},
{"id": 3, "email": "[xyz@abc.com]", "first_name": "Ram", "last_name": "Das"},
{"id": 4, "email": "[xyz@abc.com]", "first_name": "Albert", "last_name": "Pinto"},
{"id": 5, "email": "[xyz@abc.com]", "first_name": "Robert", "last_name": "Peter"},
{"id": 6, "email": "[xyz@abc.com]", "first_name": "Christian", "last_name": "Lint"},
{"id": 7, "email": "[xyz@abc.com]", "first_name": "Mike", "last_name": "Hussey"},
{"id": 8, "email": "[xyz@abc.com]", "first_name": "Ralph", "last_name": "Hunter"}
]'::jsonb)
)
insert into example
select (elem->>'id')::int, elem->>'email', elem->>'last_name', elem->>'first_name'
from jsondata,
jsonb_array_elements(jdata) as elem;
结果:
select *
from example
id | email | last_name | first_name
----+---------------+-----------+------------
1 | [xyz@abc.com] | Doe | John
2 | [xyz@abc.com] | Duncan | Robert
3 | [xyz@abc.com] | Das | Ram
4 | [xyz@abc.com] | Pinto | Albert
5 | [xyz@abc.com] | Peter | Robert
6 | [xyz@abc.com] | Lint | Christian
7 | [xyz@abc.com] | Hussey | Mike
8 | [xyz@abc.com] | Hunter | Ralph
(8 rows)
如果要更新表(而不是插入表):
If you want to update the table (instead of insert into it):
with jsondata(jdata) as (
-- values as above
)
update example set
email = elem->>'email',
last_name = elem->>'last_name',
first_name = elem->>'first_name'
from jsondata,
jsonb_array_elements(jdata) as elem
where id = (elem->>'id')::int;
这篇关于在一个查询中更新多行，但我们期望的输入来自多个数据的json对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
