通过参数化的查询字符串如何插入多行数据

huangapple
未分类评论58阅读模式
英文:

How to insert multiple rows via a parametized query String

问题

以下是翻译好的部分：

我有以下查询，用于执行单行插入时有效。但是，如何在同一时间内使用 RxJava 连续进行多次插入（大约 3000 行插入）到 Postgres 数据库中呢？

以下是我目前正在进行的单行插入，已经在单行情况下有效。

  1. private Single<ResultSet> insertion(MyRequest request) {
  2.     List<Object> params = Arrays.asList(request.getId(), request.getName(), request.getStatus());
  3.     String query = "INSERT INTO myDb.myTable(id, name, status, updated_time) " +
  4.             "VALUES (?, ?, ?, current_timestamp)" +
  5.             "RETURNING id, name, updated_time";
  6.     return client.rxQueryWithParams(query, new JsonArray(params));
  7. }

我不能使用以下方法，因为无法适应所有 3000 个查询，因此正在寻找更好的构建参数化查询的方式。请给予建议。谢谢。

  1. private Single<ResultSet> insertAll(List<MyRequest> requests) {
  2.     String query = "INSERT INTO myDb.myTable(id, name, status, updated_time) " +
  3.             "VALUES (?, ?, ?, current_timestamp), " +
  4.             "VALUES (?, ?, ?, current_timestamp)";
  5.     List<Object> params =
  6.             Arrays.asList(
  7.                     requests.get(0).getId(),
  8.                     requests.get(0).getName(),
  9.                     requests.get(0).getStatus(),
  10.                     requests.get(1).getId(),
  11.                     requests.get(0).getName(),
  12.                     requests.get(1).getStatus()
  13.                     // ..... 继续下去 ...
  14.     );
  15.     return client.rxQueryWithParams(query, new JsonArray(params));
  16. }

或者，您也可以按以下方式进行循环。如果有更好的解决方案，请告知。谢谢。

  1. private Single<ResultSet> insertAll(List<AddEventRequest> requests) {
  2.     StringBuilder query = new StringBuilder("INSERT INTO myDb.myTable(id, name, status, updated_time) ");
  3.     List<Object> params = new ArrayList<>();
  4.     for(AddEventRequest request : requests) {
  5.         query.append("VALUES (?, ?, ?, current_timestamp), ");
  6.         params.add(request.getId());
  7.         params.add(request.getName());
  8.         params.add(request.getStatusCode());
  9.     }
  10.     query.setLength(query.length() - 2);
  11.     return client.rxQueryWithParams(query.toString(), new JsonArray(params));
  12. }
英文:

I have the following query which works when I want to perform a single row insert.
But how can I do it for multiple inserts (about 3000 row inserts) at the same time in RxJava into a Postgres DB?

This is what I'm currently doing which works fine for a single row.

  1. private Single&lt;ResultSet&gt; insertion(MyRequest request) {
  2.     List&lt;Object&gt; params = Arrays.asList(request.getId(), request.getName(), request.getStatus());
  3.     String query = &quot;INSERT INTO myDb.myTable(id, name, status, updated_time) &quot; +
  4.             &quot;VALUES (?, ?, ?, current_timestamp)&quot; +
  5.             &quot;RETURNING id, name, updated_time&quot;;
  6.     return client.rxQueryWithParams(query, new JsonArray(params));
  7. }

I can't be doing the following which is not feasible to accommodate all 3000 queries.
Thus looking for a better way to construct the Parametized query. Please advice. Thanks.

  1. private Single&lt;ResultSet&gt; insertAll(List&lt;MyRequest&gt; requests) {
  2.     String query = &quot;INSERT INTO myDb.myTable(id, name, status, updated_time) &quot; +
  3.             &quot;VALUES (?, ?, ?, current_timestamp), &quot; +
  4.             &quot;VALUES (?, ?, ?, current_timestamp)&quot;;
  5.     List&lt;Object&gt; params =
  6.             Arrays.asList(
  7.                     requests.get(0).getId(),
  8.                     requests.get(0).getName(),
  9.                     requests.get(0).getStatus(),
  10.                     requests.get(1).getId(),
  11.                     requests.get(0).getName(),
  12.                     requests.get(1).getStatus()
  13.                     // ..... and on and on ... 
  14.     );
  15.     return client.rxQueryWithParams(query, new JsonArray(params));
  16. }

Alternatively I colud loop it as following. Looking for better solution if there is one.

  1. private Single&lt;ResultSet&gt; insertAll(List&lt;AddEventRequest&gt; requests) {
  2.     StringBuilder query = new StringBuilder(&quot;INSERT INTO myDb.myTable(id, name, status, updated_time) &quot;);
  3.     List&lt;Object&gt; params = new ArrayList&lt;&gt;();
  4.     for(AddEventRequest request : requests) {
  5.         query.append(&quot;VALUES (?, ?, ?, current_timestamp), &quot;);
  6.         params.add(request.getId());
  7.         params.add(request.getName());
  8.         params.add(request.getStatusCode());
  9.     }
  10.     query.setLength(query.length() - 2);
  11.     return client.rxQueryWithParams(query.toString(), new JsonArray(params));
  12. }

huangapple
  • 本文由 发表于 2020年7月27日 06:31:05
  • 转载请务必保留本文链接：https://java.coder-hub.com/63106425.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定