Blog

Discussing 2 options to bulk update records in the database

Introduction

• Updation of records in bulk is very common use case of backend engineering.
• In previous article we wrote about how to bulk insert records.
• In this article we will discuss 2 options to update records in bulk in database.

Use Case

• We have Entity class with below fields. 

@Entity
@Table(name="ACCOUNTS")
public class Account {
    @Id @GeneratedValue(strategy= GenerationType.SEQUENCE, generator = "accounts_seq")
    @SequenceGenerator(name = "accounts_seq", sequenceName = "accounts_seq", allocationSize = 1)
    @Column(name = "user_id")
    private int userId;
    private String username;
    private String password;
    private String email;
    private Timestamp createdOn;
    private Timestamp lastLogin;

    @OneToOne
    @JoinColumn(name = "permissions_id")
    private Permission permission;

    // getters , setters

}

• Our use case is to update the permission on list of users. we are changing permission type from reader to admin for selected users.

Option 1

• Since we have been provided with list of usernames, we can query database and get all the account entity associated with that.
• We can define derive query in repository so that JPA will generate query for us automatically.

List<Account> findByUsernameIn(List<String> usernames);

• Once we have list of associated account , we can iterate over each account and set latest permission type.

    @Transactional
    public void bulkUpdateAccount(List<String> usernames, String permission){
        List<Account> accounts = accountRepository.findByUsernameIn(usernames);
        Permission permissionType = permissionRepository.findByType(permission);
        System.out.println(permissionType);
        accounts.forEach(System.out::println);
        accounts.forEach(account -> account.setPermission(permissionType));
    }

• As we can see in the log our logic executes 7 JDBC statements , out of that 4 is update query. Our logic executing issuing multiple update query which is not performant when the user count is large.

• Very minimal thing we can do is perform batching . JDBC driver supports batching of queries. All we have to do is configure it in application.properties file.

spring.jpa.properties.hibernate.jdbc.batch_size=20
spring.jpa.properties.hibernate.order_updates=true

• Now JDBC count drops to 4 statements since updates get batched.

Option 2

• We can also use JPQL to update bulk records. 
• In below example we are updating bulk record permission for list of usernames.

@Transactional
public void bulkUpdateAccount1(List<String> usernames, String permission){
    Permission permissionType = permissionRepository.findByType(permission);
    int i = em.createQuery("update Account set permission = :permission where username IN(:usernames)")
            .setParameter("permission", permissionType)
            .setParameter("usernames", usernames)
            .executeUpdate();

    System.out.println("update count" + i);
}

• As we can see in the logs that updates are batched.

Conclusion

• In this article we explored 2 options to bulk update records in database using Spring Data JPA.
• Both the options are good and decision depends on how complex query logic is.

You may also want to checkout how to insert bulk records.

Before You Leave

• Upgrade your Java skills with Grokking the Java Interview

• If you want to upskill your Java skills, you should definitely check out
  [NEW] Master Spring Boot 3 & Spring Framework 6 with Java
  [ 38 hrs content, 4.7/5 stars, 6+ students already enrolled]