How to insert multiple rows via a parametized query String

Viewed 52

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));
}
0 Answers
Related