Updating Entities with Update Query in Spring Data JPA

With object-relational mapping (ORM) tools such as Hibernate, one usually modifies entities by first fetching them from the database, modifying some fields, and then persisting the entities again. This is a good approach in many cases, but in some scenarios this may not be desirable. For instance, if you want to update a collection of entities (i.e. rows in the database), or if a lot of data is needed to construct an entity object. In the latter case, it may require too many resources to assemble an entity object just to change the value of a single field, for instance.

Using Spring Data JPA, one can easily create update queries with JPQL that translate into SQL update queries. This can be done as follows.


@Repository
public interface CompanyRepository extends JpaRepository<Company, Integer> {
    @Modifying
    @Query("UPDATE Company c SET c.address = :address WHERE c.id = :companyId")
    int updateAddress(@Param("companyId") int companyId, @Param("address") String address);
}

As you can see, the JPQL query is a valid SQL query in this particular case. We use the @Modifying annotation above the repository method because it modifies the state of the database and does not select data. The return type of the method is an Integer, which equals the number of affected rows, but one can set it to void if this is not desired or necessary.

Watch out for Unsynchronized Entities

One thing to watch out for when updating entities like this, is that the EntityManager may contain outdated entities after the query has executed. This could be the case if your JPA provider uses a cache (such as Hibernate’s second level cache), or if there are changes that are pending to be flushed. As a result, the state of the entities in the EntityManager may not be accurate. This would be the case if an entity affected by the update query is referenced in the EntityManager. One may wonder why the EntityManager is not just cleared after executing the query to avoid this inconsistency, but the reason for this is that there might be changes that have not yet been flushed, that would then be lost. However, you do get the option of configuring this by setting the clearAutomatically attribute of the @Modifying annotation to true.


@Repository
public interface CompanyRepository extends JpaRepository<Company, Integer> {
    @Modifying(clearAutomatically = true)
    @Query("UPDATE Company c SET c.address = :address WHERE c.id = :companyId")
    int updateAddress(@Param("companyId") int companyId, @Param("address") String address);
}

This ensures that the EntityManager is automatically cleared when the query has executed, ensuring that no entities are unsynchronized. This may be desirable in some situations, but it depends on the context in which the repository method is used, and thus you must be careful with this; it is easy to use such a method without noticing or remembering this side effect, and this could cause problems in your application. Hopefully unit tests would be in place to catch mistakes like this, but it may not be the case. So use this flag wisely.

As you can see, using update queries in Spring Data JPA is easy. Thank you for reading.

2 Comments

  1. Akash Gawade said:

    You are doing a great job !! Thanks.

    April 28, 2016
    Reply
  2. suon said:

    Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: customer is not mapped
    at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.requireClassPersister(SessionFactoryHelper.java:171) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
    at org.hibernate.hql.internal.ast.tree.FromElementFactory.addFromElement(FromElementFactory.java:91) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
    at org.hibernate.hql.internal.ast.tree.FromClause.addFromElement(FromClause.java:76) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
    at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromElement(HqlSqlWalker.java:321) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3701) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3590) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:716) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.updateStatement(HqlSqlBaseWalker.java:373) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:265) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:262) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:190) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
    … 58 common frames omitted

    September 6, 2016
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *