1

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));
}
1
  • Noting this other answer here... it's a lot older, but may help someone get on the right track, as it lists a few different approaches that could be looked up for their current equivalent, stackoverflow.com/questions/20045940 Commented Apr 4, 2024 at 13:17

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.