英文:
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<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));
}
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<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()
// ..... 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<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));
}
专注分享java语言的经验与见解，让所有开发者获益！
评论