S
S
SailfishSnail2019-04-11 17:47:58
PostgreSQL
SailfishSnail, 2019-04-11 17:47:58

Java + JDBC + PostgreSQL - how to correctly update a record if it exists or add if it does not exist?

There is a collection with many objects. The collection is displayed in a table. An offline user can add and delete records, as well as update previously received via SELECY * FROM ...
I want to implement the "Save All" function, 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 don't exist.
Came to the following solution from the 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("");  //вот тут правильно составить запрос
                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 you need to save quickly, I disable autocommit and will send one large batch of requests at a time.
Now about the query itself... In the vastness of a well-known site, I found that PostgreSQL supports the functionality I need in the form:
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;

Several questions arose immediately.
  1. Now I need to put it in a PreparedStatement. I know how to safely insert data into the INSERT part of the query, but what about the update part, where you need to list all the data in brackets - nothing came to mind except concatenation, but this is not the way of the Jedi.
  2. Since I need to update data more often than insert new data, is it correct to use the INSERT part as a basis, and only then do an UPDATE if it exists? Maybe I'm bothering in vain, or maybe it will really affect the speed? Swap? But how?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question