How to update the record correctly if it exists or add if it's not (UPSERT)?

Question

There is a collection with many objects. The collection is displayed in a table. The user is offline can add and delete records, as well as update the previously received via SELECT * FROM ...

I want to implement the function "Save All", in which the program would go through all the collections and update the records in the database, if they exist, and add new ones if they do not exist.

Came to the following solution (java side):

@Override
public void updateAll(List<User> users)
{
    //online
    Connection connection = null;
    PreparedStatement statement = null;

    try
    {
        connection = Database.getInstance().getConnection();
        connection.setAutoCommit(false);

        for (User user : users)
        {
            statement = connection.prepareStatement("");  //THIS IS WHERE A CORRECT QUERY SHOULD BE CREATED
            statement.execute();
        }

        connection.commit();
    }
    catch (SQLException ex)
    {
        Log.error("Unable to updateAll users. " + ex.getMessage());
    }
    finally
    {
        Database.closeQuietly(statement);
        Database.closeQuietly(connection);
    }
}

Since the contents of the table could be updated significantly, but it’s necessary to save quickly, I disable the auto-commit and send one large batch of requests at a time.

Now about the request itself ... I found that PostgreSQL supports the functionality I need:

INSERT INTO the_table (id, column_1, column_2) 
VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')
ON CONFLICT (id) DO UPDATE 
  SET column_1 = excluded.column_1, 
      column_2 = excluded.column_2;

Immediately a few questions arose:

Now I need to put this in PreparedStatement. I know how to safely insert the data in the INSERT part of the request, but what about the UPDATE part, where you need to list all the data in brackets - except string concatenation, nothing came to mind, but as far as I know, this is not the Jedi way.
Since I need to update data more often than insert new ones, is it correct to use the INSERT part as a basis, and only then execute UPDATE if it exists? Maybe I should not bother in vain, and maybe it will really affect the speed? Swap? But how?

https://stackoverflow.com/a/1109198/5756557 you can check this link probaly get answer — anandchaugule, Apr 12 '19 at 07:25
What do you mean with _"but what about the UPDATE part, where you need to list all the data in brackets "_? — Mark Rotteveel, Apr 12 '19 at 07:27
possible duplicate of https://stackoverflow.com/questions/37778798/postgresql-upsert-with-jdbc-parameter-markers — Scary Wombat, Apr 12 '19 at 07:28
What exactly is your question about the UPDATE part? What "brackets" are you talking about? Regarding the performance: there is not really a difference between an INSERT ON CONFLICT UPDATE and an UPDATE in case the row exists. — , Apr 12 '19 at 07:39
And again, that doesn't clarify what you are asking for: you need to parameterize the values clause. And otherwise you really should illustrate your question with a better example that clearly shows your problem. — Mark Rotteveel, Apr 12 '19 at 08:47
@MarkRotteveel I need to create a query via a `PreparedStatement`. This query consists of 2 parts: 1) insert 2) on conflict update. I pass the actual data to the query via `statement.setString(parameterIndex, str)`. It means that I leave `?` signs instead of actual data in the query string. "insert" part has a fixed number of parameters, while "update" part depend on the amount of items in the collection. — CorellianAle, Apr 12 '19 at 08:51
Please provide a [mcve] in your question. It is unclear to me why the number of items in the collection will in anyway influence the number of parameters. — Mark Rotteveel, Apr 12 '19 at 08:52
@MarkRotteveel I think I finally understand how to ask my question: "How to turn `INSERT INTO the_table (id, column_1, column_2) VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z') ON CONFLICT (id) DO UPDATE SET column_1 = excluded.column_1, column_2 = excluded.column_2;` this query into Java JDBC statement?` — CorellianAle, Apr 12 '19 at 08:55
I'm still not entirely sure what your problem is, but as a guess: use `INSERT INTO the_table (id, column_1, column_2) VALUES (?, ?, ?) ON CONFLICT (id) DO UPDATE SET column_1 = excluded.column_1, column_2 = excluded.column_2`, set parameters per item and either execute individually or use `addBatch` per item and `executeBatch` add the end? — Mark Rotteveel, Apr 12 '19 at 09:01

How to update the record correctly if it exists or add if it's not (UPSERT)?

0 Answers0