Use Spring Data JPA, QueryDSL to update a bunch of records

Question

I'm refactoring a code base to get rid of SQL statements and primitive access and modernize with Spring Data JPA (backed by hibernate). I do use QueryDSL in the project for other uses.

I have a scenario where the user can "mass update" a ton of records, and select some values that they want to update. In the old way, the code manually built the update statement with an IN statement for the where for the PK (which items to update), and also manually built the SET clauses (where the options in SET clauses can vary depending on what the user wants to update).

In looking at QueryDSL documentation, it shows that it supports what I want to do. http://www.querydsl.com/static/querydsl/4.1.2/reference/html_single/#d0e399

I tried looking for a way to do this with Spring Data JPA, and haven't had any luck. Is there a repostitory interface I'm missing, or another library that is required....or would I need to autowire a queryFactory into a custom repository implementation and very literally implement the code in the QueryDSL example?

Answer 1

You can either write a custom method or use @Query annotation.

For custom method;

public interface RecordRepository extends RecordRepositoryCustom, 
                                          CrudRepository<Record, Long> 
{
}

public interface RecordRepositoryCustom {
      // Custom method
      void massUpdateRecords(long... ids);
}

public class RecordRepositoryImpl implements RecordRepositoryCustom {
      @Override
      public void massUpdateRecords(long... ids) {
           //implement using em or querydsl
      }
}

For @Query annotation;

public interface RecordRepository extends CrudRepository<Record, Long> 
{
      @Query("update records set someColumn=someValue where id in :ids")
      void massUpdateRecords(@Param("ids") long... ids);
}

There is also @NamedQuery option if you want your model class to be reusable with custom methods;

@Entity
@NamedQuery(name = "Record.massUpdateRecords", query = "update records set someColumn=someValue where id in :ids")
@Table(name = "records")
public class Record {   
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      private Long id;
      //rest of the entity...
}

public interface RecordRepository extends CrudRepository<Record, Long> 
{
      //this will use the namedquery
      void massUpdateRecords(@Param("ids") long... ids);
}

Check repositories.custom-implementations, jpa.query-methods.at-query and jpa.query-methods.named-queries at spring data reference document for more info.