Spring Data JPA: How to Bulk Update Records

  • Post last modified:March 25, 2024
  • Reading time:3 mins read

Discussing 2 options to bulk update records in the database


  • 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. 
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;

    @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.
    public void bulkUpdateAccount(List<String> usernames, String permission){
        List<Account> accounts = accountRepository.findByUsernameIn(usernames);
        Permission permissionType = permissionRepository.findByType(permission);
        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.
  • 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.
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)

    System.out.println("update count" + i);
  • As we can see in the logs that updates are batched.


  • 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

Leave a Reply