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

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

How to insert multiple rows via a parametized query String

问题

以下是翻译好的部分:

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

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

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

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

private Single<ResultSet> insertAll(List<MyRequest> requests) {
    String query = "INSERT INTO myDb.myTable(id, name, status, updated_time) " +
            "VALUES (?, ?, ?, current_timestamp), " +
            "VALUES (?, ?, ?, current_timestamp)";
    List<Object> params =
            Arrays.asList(
                    requests.get(0).getId(),
                    requests.get(0).getName(),
                    requests.get(0).getStatus(),
                    requests.get(1).getId(),
                    requests.get(0).getName(),
                    requests.get(1).getStatus()
                    // ..... 继续下去 ...
    );
    return client.rxQueryWithParams(query, new JsonArray(params));
}

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

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

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.

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

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.

private Single&lt;ResultSet&gt; insertAll(List&lt;MyRequest&gt; requests) {
    String query = &quot;INSERT INTO myDb.myTable(id, name, status, updated_time) &quot; +
            &quot;VALUES (?, ?, ?, current_timestamp), &quot; +
            &quot;VALUES (?, ?, ?, current_timestamp)&quot;;
    List&lt;Object&gt; params =
            Arrays.asList(
                    requests.get(0).getId(),
                    requests.get(0).getName(),
                    requests.get(0).getStatus(),
                    requests.get(1).getId(),
                    requests.get(0).getName(),
                    requests.get(1).getStatus()
                    // ..... and on and on ... 
    );
    return client.rxQueryWithParams(query, new JsonArray(params));
}

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

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

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:

确定