Query to update many records on a Spring/Hibernate project

Question

I have a spring project and I'm using Hibernate and JpaRepository. Now I want to run an update on many records at the same time, I have a map of ids and a field to be updated. The amount of records is variable as it depends on a request. This is not really ORM-level but lower level. How should I go about it? I'm new to Spring and Hibernate, so, I don't know which API I should use to tackle this issue.

I obtain the data as a Map<UUID, Integer> and I want to turn it into a set of values for a query like this one: Update multiple rows in same query using PostgreSQL. Essentially I'm doing a batch update.

You can use `JdbcTemplate` batchUpdate method, but it is not ORM level, you need to write your SQL manually. — Bogdan Oros, Feb 01 '18 at 15:36
@BogdanOros: I have no problem writing my own SQL, I was expecting it with this problem. — Pablo Fernandez, Feb 01 '18 at 17:14
Look the posted answer @jcrada, but I prefer to use `NamedParameterJdbcTemplate`, it's much better and looks more `supportable`) — Bogdan Oros, Feb 01 '18 at 17:16

score 1 · Accepted Answer · answered Feb 01 '18 at 16:12

I have face similar situations in the past and I do not see why not mix jpa and jdbc technology which indeed is designed for this kind of task. Even a jdbc template is configured for you for "free" if you are using spring boot and add spring-boot-starter-jdbc dependency e.g. (from spring documentation)

public class JdbcActorDao implements ActorDao {

    private final JdbcTemplate jdbcTemplate;

    public int[] batchUpdate(final List<Actor> actors) {
        return this.jdbcTemplate.batchUpdate(
            "update t_actor set first_name = ?, last_name = ? where id = ?",
            new BatchPreparedStatementSetter() {
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    ps.setString(1, actors.get(i).getFirstName());
                    ps.setString(2, actors.get(i).getLastName());
                    ps.setLong(3, actors.get(i).getId().longValue());
                }
                public int getBatchSize() {
                    return actors.size();
                }
            });
    }
}

This seems to have worked. But, I don't understand `batchUpdate`; wouldn't this generate N-queries? — Pablo Fernandez, Feb 01 '18 at 20:09

Query to update many records on a Spring/Hibernate project

1 Answers1