Spring Data JPA Update Query not updating

Question

I have an update query    Modifying  Transactional  Query  UPDATE Admin SET firstname    firstname  lastname    lastname  login    login  superAdmin    superAdmin  preferenceAdmin    preferenceAdmin  address     address  zipCode    zipCode  city    city  country    country  email    email  profile    profile  postLoginUrl    postLoginUrl WHERE id    id   public void update  Param  firstname   String firstname   Param  lastname   String lastname   Param  login   String login   Param  superAdmin   boolean superAdmin   Param  preferenceAdmin   boolean preferenceAdmin   Param  address   String address   Param  zipCode   String zipCode   Param  city   String city   Param  country   String country   Param  email   String email   Param  profile   String profile   Param  postLoginUrl   String postLoginUrl   Param  id   Long id     I m trying to use it in an integration test   adminRepository update  Toto    LeHeros   admin0 getLogin    admin0 getSuperAdmin    admin0 getPreferenceAdmin    admin0 getAddress    admin0 getZipCode    admin0 getCity    admin0 getCountry    admin0 getEmail    admin0 getProfile    admin0 getPostLoginUrl    admin0 getId     Admin loadedAdmin   adminRepository findOne admin0 getId     assertEquals  Toto   loadedAdmin getFirstname     assertEquals  LeHeros   loadedAdmin getLastname       But the fields are not updated and retain their initial values  the test thus failing   I tried adding a flush right before the findOne query   adminRepository flush      But the failed assertion remained identical   I can see the update sql statement in the log   update admin set firstname  Toto   lastname  LeHeros   login  stephane   super admin 0  preference admin 0  address NULL  zip code NULL  city NULL  country NULL  email  stephane thalasoft com   profile NULL  post login url NULL where id 2839   But the log shows no sql that could relate to the finder   Admin loadedAdmin   adminRepository findOne admin0 getId     The finder sql statement is not making its way to the database    Is it ignored for some caching reason    If I then add a call to the findByEmail and findByLogin finders as in   adminRepository update  Toto    LeHeros    qwerty   admin0 getSuperAdmin    admin0 getPreferenceAdmin    admin0 getAddress    admin0 getZipCode    admin0 getCity    admin0 getCountry    admin0 getEmail    admin0 getProfile    admin0 getPostLoginUrl    admin0 getId     Admin loadedAdmin   adminRepository findOne admin0 getId     Admin myadmin   adminRepository findByEmail admin0 getEmail     Admin anadmin   adminRepository findByLogin  qwerty    assertEquals  Toto   anadmin getFirstname     assertEquals  Toto   myadmin getFirstname     assertEquals  Toto   loadedAdmin getFirstname     assertEquals  LeHeros   loadedAdmin getLastname       then I can see in the log the sql statement being generated   But the assertion   assertEquals  Toto   myadmin getFirstname       still fails even though the trace shows the same domain object was retrieved   TRACE  BasicExtractor  found  1037  as column  id14     One other thing that puzzles me with this other finder is that it shows a limit 2 clause even though it is supposed to return only one Admin object   I thought there would always be a limit 1 when returning one domain object  Is this a wrong assumption on Spring Data    When pasting in a MySQL client  the sql statements displayed in the console log  the logic works fine   mysql gt  insert into admin  version  address  city  country  email  firstname  lastname  login  password   - gt  password salt  post login url  preference admin  profile  super admin  zip code  values  0  - gt  NULL  NULL  NULL   zemail thalasoft com039    zfirstname039    zlastname039    zlogin039   - gt   zpassword039       NULL  0  NULL  1  NULL   Query OK  1 row affected  0 07 sec   mysql gt  select   from admin   ------ --------- --------------- -------------- ----------- -------------- --------------- ------------- ------------------ --------- ---------- ------ --------- ------------------------- --------- ----------------    id   version   firstname   lastname   login   password   password salt   super admin   preference admin   address   zip code   city   country   email   profile   post login url    ------ --------- --------------- -------------- ----------- -------------- --------------- ------------- ------------------ --------- ---------- ------ --------- ------------------------- --------- ----------------    1807   0   zfirstname039   zlastname039   zlogin039   zpassword039     1   0   NULL   NULL   NULL   NULL   zemail thalasoft com039   NULL   NULL     ------ --------- --------------- -------------- ----------- -------------- --------------- ------------- ------------------ --------- ---------- ------ --------- ------------------------- --------- ----------------  1 row in set  0 00 sec   mysql gt  update admin set firstname  Toto   lastname  LeHeros   login  qwerty   super admin 0  preference admin 0  address NULL  zip code NULL  city NULL  country NULL  email  stephane thalasoft com   profile NULL  post login url NULL where id 1807  Query OK  1 row affected  0 07 sec  Rows matched  1 Changed  1 Warnings  0  mysql gt  select   from admin   ------ --------- ----------- ---------- -------- -------------- --------------- ------------- ------------------ --------- ---------- ------ --------- ------------------------ --------- ----------------    id   version   firstname   lastname   login   password   password salt   super admin   preference admin   address   zip code   city   country   email   profile   post login url    ------ --------- ----------- ---------- -------- -------------- --------------- ------------- ------------------ --------- ---------- ------ --------- ------------------------ --------- ----------------    1807   0   Toto   LeHeros   qwerty   zpassword039     0   0   NULL   NULL   NULL   NULL   stephane thalasoft com   NULL   NULL     ------ --------- ----------- ---------- -------- -------------- --------------- ------------- ------------------ --------- ---------- ------ --------- ------------------------ --------- ----------------  1 row in set  0 00 sec   mysql gt  select admin0  id as id14   admin0  version as version14   admin0  address as address14   admin0  city as city14   admin0  country as country14   admin0  email as email14   admin0  firstname as firstname14   admin0  lastname as lastname14   admin0  login as login14   admin0  password as password14   admin0  password salt as password11 14   admin0  post login url as post12 14   admin0  preference admin as preference13 14   admin0  profile as profile14   admin0  super admin as super15 14   admin0  zip code as zip16 14  from admin admin0  where admin0  email  stephane thalasoft com  limit 2   ------- ------------ ------------ --------- ------------ ------------------------ -------------- ------------- ---------- -------------- ---------------- ------------ ------------------ ------------ ------------- -----------    id14    version14    address14    city14    country14    email14    firstname14    lastname14    login14    password14    password11 14    post12 14    preference13 14    profile14    super15 14    zip16 14     ------- ------------ ------------ --------- ------------ ------------------------ -------------- ------------- ---------- -------------- ---------------- ------------ ------------------ ------------ ------------- -----------    1807   0   NULL   NULL   NULL   stephane thalasoft com   Toto   LeHeros   qwerty   zpassword039     NULL   0   NULL   0   NULL     ------- ------------ ------------ --------- ------------ ------------------------ -------------- ------------- ---------- -------------- ---------------- ------------ ------------------ ------------ ------------- -----------  1 row in set  0 00 sec   mysql gt  select admin0  id as id14   admin0  version as version14   admin0  address as address14   admin0  city as city14   admin0  country as country14   admin0  email as email14   admin0  firstname as firstname14   admin0  lastname as lastname14   admin0  login as login14   admin0  password as password14   admin0  password salt as password11 14   admin0  post login url as post12 14   admin0  preference admin as preference13 14   admin0  profile as profile14   admin0  super admin as super15 14   admin0  zip code as zip16 14  from admin admin0  where admin0  login  qwerty  limit 2   ------- ------------ ------------ --------- ------------ ------------------------ -------------- ------------- ---------- -------------- ---------------- ------------ ------------------ ------------ ------------- -----------    id14    version14    address14    city14    country14    email14    firstname14    lastname14    login14    password14    password11 14    post12 14    preference13 14    profile14    super15 14    zip16 14     ------- ------------ ------------ --------- ------------ ------------------------ -------------- ------------- ---------- -------------- ---------------- ------------ ------------------ ------------ ------------- -----------    1807   0   NULL   NULL   NULL   stephane thalasoft com   Toto   LeHeros   qwerty   zpassword039     NULL   0   NULL   0   NULL     ------- ------------ ------------ --------- ------------ ------------------------ -------------- ------------- ---------- -------------- ---------------- ------------ ------------------ ------------ ------------- -----------  1 row in set  0 00 sec    So why is this not reflected at the Java level

User · Accepted Answer

The EntityManager doesn t flush change automatically by default  You should use the following option with your statement of query    Modifying clearAutomatically   true   Query  update RssFeedEntry feedEntry set feedEntry read   isRead where feedEntry id   entryId   void markEntryAsRead  Param  entryId   Long rssFeedEntryId   Param  isRead   boolean isRead

User · Answer

I was able to get this to work  I will describe my application and the integration test here   The Example Application  The example application has two classes and one interface that are relevant to this problem    The application context configuration class The entity class The repository interface   These classes and the repository interface are described in the following   The source code of the PersistenceContext class looks as follows   import com jolbox bonecp BoneCPDataSource  import org springframework beans factory annotation Autowired  import org springframework context annotation Bean  import org springframework context annotation Configuration  import org springframework context annotation PropertySource  import org springframework core env Environment  import org springframework data jpa repository config EnableJpaRepositories  import org springframework orm jpa JpaTransactionManager  import org springframework orm jpa LocalContainerEntityManagerFactoryBean  import org springframework orm jpa vendor HibernateJpaVendorAdapter  import org springframework transaction annotation EnableTransactionManagement   import javax sql DataSource  import java util Properties    Configuration  EnableTransactionManagement  EnableJpaRepositories basePackages    net petrikainulainen spring datajpa todo repository    PropertySource  classpath application properties   public class PersistenceContext        protected static final String PROPERTY NAME DATABASE DRIVER    db driver       protected static final String PROPERTY NAME DATABASE PASSWORD    db password       protected static final String PROPERTY NAME DATABASE URL    db url       protected static final String PROPERTY NAME DATABASE USERNAME    db username        private static final String PROPERTY NAME HIBERNATE DIALECT    hibernate dialect       private static final String PROPERTY NAME HIBERNATE FORMAT SQL    hibernate format sql       private static final String PROPERTY NAME HIBERNATE HBM2DDL AUTO    hibernate hbm2ddl auto       private static final String PROPERTY NAME HIBERNATE NAMING STRATEGY    hibernate ejb naming strategy       private static final String PROPERTY NAME HIBERNATE SHOW SQL    hibernate show sql        private static final String PROPERTY PACKAGES TO SCAN    net petrikainulainen spring datajpa todo model         Autowired     private Environment environment        Bean     public DataSource dataSource             BoneCPDataSource dataSource   new BoneCPDataSource             dataSource setDriverClass environment getRequiredProperty PROPERTY NAME DATABASE DRIVER            dataSource setJdbcUrl environment getRequiredProperty PROPERTY NAME DATABASE URL            dataSource setUsername environment getRequiredProperty PROPERTY NAME DATABASE USERNAME            dataSource setPassword environment getRequiredProperty PROPERTY NAME DATABASE PASSWORD             return dataSource              Bean     public JpaTransactionManager transactionManager             JpaTransactionManager transactionManager   new JpaTransactionManager             transactionManager setEntityManagerFactory entityManagerFactory   getObject              return transactionManager              Bean     public LocalContainerEntityManagerFactoryBean entityManagerFactory             LocalContainerEntityManagerFactoryBean entityManagerFactoryBean   new LocalContainerEntityManagerFactoryBean             entityManagerFactoryBean setDataSource dataSource             entityManagerFactoryBean setJpaVendorAdapter new HibernateJpaVendorAdapter             entityManagerFactoryBean setPackagesToScan PROPERTY PACKAGES TO SCAN            Properties jpaProperties   new Properties            jpaProperties put PROPERTY NAME HIBERNATE DIALECT  environment getRequiredProperty PROPERTY NAME HIBERNATE DIALECT            jpaProperties put PROPERTY NAME HIBERNATE FORMAT SQL  environment getRequiredProperty PROPERTY NAME HIBERNATE FORMAT SQL            jpaProperties put PROPERTY NAME HIBERNATE HBM2DDL AUTO  environment getRequiredProperty PROPERTY NAME HIBERNATE HBM2DDL AUTO            jpaProperties put PROPERTY NAME HIBERNATE NAMING STRATEGY  environment getRequiredProperty PROPERTY NAME HIBERNATE NAMING STRATEGY            jpaProperties put PROPERTY NAME HIBERNATE SHOW SQL  environment getRequiredProperty PROPERTY NAME HIBERNATE SHOW SQL             entityManagerFactoryBean setJpaProperties jpaProperties            return entityManagerFactoryBean            Let s assume that we have a simple entity called Todo which source code looks as follows    Entity  Table name  todos   public class Todo        public static final int MAX LENGTH DESCRIPTION   500      public static final int MAX LENGTH TITLE   100        Id      GeneratedValue strategy   GenerationType AUTO      private Long id        Column name    description   nullable   true  length   MAX LENGTH DESCRIPTION      private String description        Column name    title   nullable   false  length   MAX LENGTH TITLE      private String title        Version     private long version      Our repository interface has a single method called updateTitle   which updates the title of a todo entry  The source code of the TodoRepository interface looks as follows   import net petrikainulainen spring datajpa todo model Todo  import org springframework data jpa repository JpaRepository  import org springframework data jpa repository Modifying  import org springframework data jpa repository Query  import org springframework data repository query Param   import java util List   public interface TodoRepository extends JpaRepository lt Todo  Long gt          Modifying      Query  Update Todo t SET t title  title WHERE t id  id       public void updateTitle  Param  id   Long id   Param  title   String title       The updateTitle   method is not annotated with the  Transactional annotation because I think that it is best to use a service layer as a transaction boundary   The Integration Test  The Integration Test uses DbUnit  Spring Test and Spring-Test-DBUnit  It has three components which are relevant to this problem    The DbUnit dataset which is used to initialize the database into a known state before the test is executed  The DbUnit dataset which is used to verify that the title of the entity is updated  The integration test    These components are described with more details in the following   The name of the DbUnit dataset file which is used to initialize the database to known state is toDoData xml and its content looks as follows    lt dataset gt       lt todos id  1  description  Lorem ipsum  title  Foo  version  0   gt       lt todos id  2  description  Lorem ipsum  title  Bar  version  0   gt   lt  dataset gt    The name of the DbUnit dataset which is used to verify that the title of the todo entry is updated is called toDoData-update xml and its content looks as follows  for some reason the version of the todo entry was not updated but the title was  Any ideas why      lt dataset gt       lt todos id  1  description  Lorem ipsum  title  FooBar  version  0   gt       lt todos id  2  description  Lorem ipsum  title  Bar  version  0   gt   lt  dataset gt    The source code of the actual integration test looks as follows  Remember to annotate the test method with the  Transactional annotation    import com github springtestdbunit DbUnitTestExecutionListener  import com github springtestdbunit TransactionDbUnitTestExecutionListener  import com github springtestdbunit annotation DatabaseSetup  import com github springtestdbunit annotation ExpectedDatabase  import org junit Test  import org junit runner RunWith  import org springframework beans factory annotation Autowired  import org springframework test annotation Rollback  import org springframework test context ContextConfiguration  import org springframework test context TestExecutionListeners  import org springframework test context junit4 SpringJUnit4ClassRunner  import org springframework test context support DependencyInjectionTestExecutionListener  import org springframework test context support DirtiesContextTestExecutionListener  import org springframework test context transaction TransactionalTestExecutionListener  import org springframework transaction annotation Transactional    RunWith SpringJUnit4ClassRunner class   ContextConfiguration classes    PersistenceContext class    TestExecutionListeners   DependencyInjectionTestExecutionListener class          DirtiesContextTestExecutionListener class          TransactionalTestExecutionListener class          DbUnitTestExecutionListener class     DatabaseSetup  todoData xml   public class ITTodoRepositoryTest         Autowired     private TodoRepository repository        Test      Transactional      ExpectedDatabase  toDoData-update xml       public void updateTitle ShouldUpdateTitle             repository updateTitle 1L   FooBar              After I run the integration test  the test passes and the title of the todo entry is updated  The only problem which I am having is that the version field is not updated  Any ideas why   I undestand that this description is a bit vague  If you want to get more information about writing integration tests for Spring Data JPA repositories  you can read my blog post about it

User · Answer

The underlying problem here is the 1st level cache of JPA  From the JPA spec Version 2 2 section 3 1  emphasise is mine      An EntityManager instance is associated with a persistence context  A persistence context is a set of entity instances in which for any persistent entity identity there is a unique entity instance    This is important because JPA tracks changes to that entity in order to flush them to the database  As a side effect it also means within a single persistence context an entity gets only loaded once  This why reloading the changed entity doesn t have any effect   You have a couple of options how to handle this    Evict the entity from the EntityManager   This may be done by calling EntityManager detach  annotating the updating method with  Modifying clearAutomatically   true  which evicts all entities  Make sure changes to these entities get flushed first or you might end up loosing changes  Use EntityManager refresh    Use a different persistence context to load the entity   The easiest way to do this is to do it in a separate transaction  With Spring this can be done by having separate methods annotated with  Transactional on beans called from a bean not annotated with  Transactional   Another way is to use a TransactionTemplate which works especially nicely in tests where it makes transaction boundaries very visible

User · Answer

I struggled with the same problem where I was trying to execute an update query like the same as you did-    Modifying  Transactional  Query value    UPDATE SAMPLE TABLE st SET st status  flag WHERE se referenceNo in  ids   public int updateStatus  Param  flag  String flag   Param  ids  List lt String gt  references     This will work if you have put  EnableTransactionManagement annotation on the main class  Spring 3 1 introduces the  EnableTransactionManagement annotation to be used in on  Configuration classes and enable transactional support

User · Answer

I finally understood what was going on   When creating an integration test on a statement saving an object  it is recommended to flush the entity manager so as to avoid any false negative  that is  to avoid a test running fine but whose operation would fail when run in production  Indeed  the test may run fine simply because the first level cache is not flushed and no writing hits the database  To avoid this false negative integration test use an explicit flush in the test body  Note that the production code should never need to use any explicit flush as it is the role of the ORM to decide when to flush   When creating an integration test on an update statement  it may be necessary to clear the entity manager so as to reload the first level cache  Indeed  an update statement completely bypasses the first level cache and writes directly to the database  The first level cache is then out of sync and reflects the old value of the updated object  To avoid this stale state of the object  use an explicit clear in the test body  Note that the production code should never need to use any explicit clear as it is the role of the ORM to decide when to clear   My test now works just fine

[java] Spring Data JPA Update @Query not updating?

Examples related to java

Examples related to jpa

Examples related to spring-data