Announcement Announcement Module
Collapse
No announcement yet.
Update @Query not updating ? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Update @Query not updating ?

    Hi,

    I have an update query:
    Code:
    @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:
    Code:
    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:
    Code:
    adminRepository.flush();
    But the failed assertion remained identical.

    Any clue ?

    Thanks.

  • #2
    I can see the update sql statement in the log:
    Code:
    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='[email protected]', profile=NULL,
    post_login_url=NULL where id=2839
    But the log shows no sql that could relate to the finder:
    Code:
    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 ?

    Comment


    • #3
      If I then add a call to the findByEmail finder
      Code:
      public Admin findByEmail(String email);
      as in:
      Code:
      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());
      Admin myadmin = adminRepository.findByEmail(admin0.getEmail());
      assertEquals("Toto", myadmin.getFirstname());
      assertEquals("Toto", loadedAdmin.getFirstname());
      assertEquals("LeHeros", loadedAdmin.getLastname());
      then I can see in the log the sql statement being generated:
      Code:
      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='[email protected]'
      limit 2
      But the assertion:
      Code:
      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 ?

      Comment


      • #4
        When pasting in a MySQL client, the sql statements displayed in the console log, the logic works fine:
        mysql> insert into admin (version, address, city, country, email, firstname, lastname, login, password,
        -> password_salt, post_login_url, preference_admin, profile, super_admin, zip_code) values (0,
        -> NULL, NULL, NULL, '[email protected]', 'zfirstname039', 'zlastname039', 'zlogin039',
        -> 'zpassword039', '', NULL, 0, NULL, 1, NULL);
        Query OK, 1 row affected (0.07 sec)

        mysql> 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 | [email protected]039 | NULL | NULL |
        +------+---------+---------------+--------------+-----------+--------------+---------------+-------------+------------------+---------+----------+------+---------+-------------------------+---------+----------------+
        1 row in set (0.00 sec)

        mysql> 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='[email protected]', 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> 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 | [email protected] | NULL | NULL |
        +------+---------+-----------+----------+--------+--------------+---------------+-------------+------------------+---------+----------+------+---------+------------------------+---------+----------------+
        1 row in set (0.00 sec)

        mysql> 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='[email protected]' 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 | [email protected] | Toto | LeHeros | qwerty | zpassword039 | | NULL | 0 | NULL | 0 | NULL |
        +-------+------------+------------+---------+------------+------------------------+--------------+-------------+----------+--------------+----------------+------------+------------------+------------+-------------+-----------+
        1 row in set (0.00 sec)

        mysql> 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 | [email protected] | 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 ?

        Comment


        • #5
          Hi Stephane,

          I wrote an answer to your Stack Overflow question. I hope that it will help you to solve your problem.

          Comment


          • #6
            I thought that by default an ORM would make the persistence layer invisible and allow me to think in terms of Java objects only.

            I can see it it not the case and there is I'm sure some good and valid reason for that.

            I tried using the clearAutomatically attribute as in: @Modifying(clearAutomatically = true) but it has an unexpected side effect that prevents my Maven build: the web.xml file is suddenly required. Without the (clearAutomatically = true) statement, the command: mvn clean install -Pmysql-db -Pjpa is a successful Maven build. But with it it fails with a: Error assembling WAR: webxml attribute is required (or pre-existing WEB-INF/web.xml if executing in update mode).

            Indeed, I have a Java based configuration of the web application, running on the Tomcat 7 plugin, and can deploy and run using the command: mvn clean tomcat7:run -Pmysql-db (Notice the tomcat7). I wonder why the clearAutomatically makes the web.xml file suddenly required.

            My final question is then: how to write an integration test that checks for an update and is it an interesting test to have ?

            Comment


            • #7
              At http://blog.xebia.com/2009/07/11/jpa...terns-testing/ one can read:
              By default the JPA context is not flushed until the transaction is committed or a query is executed. So unless your test includes a query, any modifications are not actually propagated to the database which can hide problems with invalid mappings and such.
              My test does include a query, but contrary to what is being stated above, the query does not commit the transaction and flush the context. The database is not being updated.

              The same paragraph also reads:
              You’re probably not even hitting the database as the JPA provider will return a reference to the object that you just saved!
              Well, contrary to the above statement, even if I'm not hitting the database, the retrieved object doesn't seem to be the one being saved, it doesn't show the updated state.

              I'm missing something...

              Comment


              • #8
                Someone has a different view point: JPA cannot use cached data when executing these queries, so only stuff that's actually in the DB will be read
                at http://stackoverflow.com/questions/4...-jpa-hibernate

                The recommended way is to do an explicit flush after the update query and before the select query. Indeed, the flushing should not be part of the repository as the production code needs no flushing. Only in the test should the flush be done explicitly.

                As in:
                Code:
                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());
                adminRepository.flush();
                Admin loadedAdmin = adminRepository.findOne(admin0.getId());
                assertEquals("Toto", loadedAdmin.getFirstname());
                assertEquals("LeHeros", loadedAdmin.getLastname());
                But the assertion fails as no update seems to be flushed.

                Comment


                • #9
                  Petri,

                  Looking at your example code in the StackOverflow thread, I can see you do not have any assert in your test checking for the actual update being done in the database. The test I'm trying to code here is about checking this very issue. I wonder why you have not any assert in there.

                  Kind Regards,

                  Comment


                  • #10
                    I now tried with an entity manager injected instance now and did the following flush

                    entityManager.flush();

                    But the same error remained.

                    Comment


                    • #11
                      Hi Petri,

                      I finally understood what was going on. I wrote a guideline about it. You may want to mention this in your book.

                      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.

                      Kind Regards,

                      Comment


                      • #12
                        Originally posted by stephaneeybert View Post
                        I finally understood what was going on. I wrote a guideline about it. You may want to mention this in your book
                        I am happy to hear that you solved your problem!

                        If I have a chance to write a second edition of my book, I will probably add a lot of new stuff in it (update @Query is one of them). It seems that it is hard to get it right at the first time

                        Comment

                        Working...
                        X