Updating Entities with Update Query in Spring Data JPA

Published on March 25, 2015 by

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.

Author avatar
Bo Andersen

About the Author

I am a back-end web developer with a passion for open source technologies. I have been a PHP developer for many years, and also have experience with Java and Spring Framework. I currently work full time as a lead developer. Apart from that, I also spend time on making online courses, so be sure to check those out!

13 comments on »Updating Entities with Update Query in Spring Data JPA«

  1. Akash Gawade

    You are doing a great job !! Thanks.

  2. suon

    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
  3. yael

    any idea what happens with @version in this case, where the jpa query updates more than one row ?

  4. Adit

    Hi, I am calling a service where a user is created and the record created is later updated with a unique id. Im using @Query for this.
    @Modifying(clearAutomatically = true)
    @Query(value=”UPDATE public.userDetails SET uniqid = ?1 WHERE userid=?2″, nativeQuery=true)
    void updateId(int uniqid , int userid);

    i Have @rollbackFor annotation for the main service which is calling the above. At the end of the service call I can see the data inserted into the table, However my update is not taking effect for uniqid. Any help on this?

  5. Olivier Dupuy

    Thanks for the article.

    Take note that in the query @Query(“UPDATE Company c SET c.address = :address WHERE c.id = :companyId”) we have “Company”, “address” and “id” being the class name and field names. They are not the table name and column name.

    As well I had to add @Transactional to the method. Without it I had a “InvalidDataAccessApiUsageException” caused by a “TransactionRequiredException” with message “Executing an update/delete query”.

    Last, I had to create SessionFactory because I had another exception to specify the dialect and the annotated class.

    package com.gemalto.dpaas.admin.jpa;

    import org.hibernate.SessionFactory;
    import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;

    @Configuration
    public class MappingConfiguration {
    public MappingConfiguration() {
    System.out.println();
    }

    @Bean
    public SessionFactory getSessionFactory() {
    org.hibernate.cfg.Configuration configuration = new org.hibernate.cfg.Configuration();
    configuration.addAnnotatedClass(Company.class);
    configuration.setProperty(“hibernate.dialect”, “org.hibernate.dialect.MySQLDialect”);

    StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder()
    .applySettings(configuration.getProperties());
    SessionFactory sessionFactory = configuration.buildSessionFactory(builder.build());

    return sessionFactory;
    }
    }

  6. Brendan

    The title “Updating Entities” misleads and the example is about just one entity (even one record) update. I hoped to see multiple entities updateS by any concise SpringData tips.

  7. suresh

    Hi Andersen,

    using Spring data Jpa,
    can we update multiple records with list of id’s as input? if yes can you please post implementation here..

    Thanks
    Suresh

  8. Jayakumar Jayaraman

    This is an example to update multiple rows

    @Transactional
    @Modifying
    @Query(value = “UPDATE Price p SET p.active=:active WHERE p.id IN (:priceIds)”)
    public void updateActiveByIds(@Param(“priceIds”) List priceIds, @Param(“active”) boolean active);

  9. Jay Jay

    Thanks for the nice post. Is it possible to update multiple columns using @Modifying
    I tried the below and got a syntax error

    @Transactional
    @Modifying
    @Query(value = “UPDATE Price p SET p.active=:active, p.validTo=:validTo WHERE p.id IN (:priceIds)”)
    public void updateActiveByIds(@Param(“priceIds”) List priceIds, @Param(“active”) boolean active, @Param(“validTo”) Date validTo);

    Error:-
    SQL Error: 1064, SQLState: 42000
    ERROR 21060 — [nio-8090-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘)’ at line 1

  10. Abhishek Balani

    Thanks. I wasted a lot of time debugging because I hadn’t set clearAutomatically = true`.

  11. iby

    hello i have the same syntax error
    Error:-
    SQL Error: 1064, SQLState: 42000
    ERROR 21060 — [nio-8090-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘)’ at line 1
    how can i resolve it thank you

  12. EricNguyen

    Hi. You said that “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”
    I understand that it will clear all in 1st level-cache and then synchronize data in database with object in EntitiManager.
    But what happend with object is not flushed ?
    Will it be lost ? or update to database and if flush in database then when ?

  13. Benedek Fauszt

    Hi Bo,

    Great article, thanks, this helped me solving my problem.

    There is one thing though that I think is worth mentioning. The EntityManager can be autowired, and then calling the clear method on it has the same effect as the clearAutomatically parameter. The benefit is that you can tell exactly when the cache gets cleared, which might be a lot later than immediately after the update statement. At least this was my case.

Leave a Reply

Your e-mail address will not be published.