春季数据JPA更新@Query没有更新？

java jpa spring-data

2022-08-31 14:07:24

我有一个更新查询：

@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);

我试图在集成测试中使用它：

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());

但是字段不会更新并保留其初始值，因此测试失败。

我尝试在findOne查询之前添加一个刷新：

adminRepository.flush();

但失败的断言仍然相同。

我可以在日志中看到更新 sql 语句：

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

但是日志显示没有可能与查找器相关的 sql：

Admin loadedAdmin = adminRepository.findOne(admin0.getId());
The finder sql statement is not making its way to the database.

它是否由于某些缓存原因而被忽略？

如果我随后添加对findByEmail和findByLogin查找器的调用，如下所示：

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());

然后我可以在日志中看到正在生成的sql语句：

但断言：

assertEquals("Toto", myadmin.getFirstname());

即使跟踪显示检索了相同的域对象，仍然失败：

TRACE [BasicExtractor] found [1037] as column [id14_]

另一件让我困惑的另一件事是，它显示了一个限制2子句，即使它应该只返回一个Admin对象。

我以为返回一个域对象时总是有一个限制1。这是对春季数据的错误假设吗？

在MySQL客户端粘贴时，控制台日志中显示的sql语句，逻辑工作正常：

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, 'zemail@thalasoft.com039', '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 | zemail@thalasoft.com039 | 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='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> 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> 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> 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)

那么，为什么这没有反映在Java级别呢？

答案 1

默认情况下，实体管理器不会自动刷新更改。您应该对查询语句使用以下选项：

@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);

答案 2

我终于明白发生了什么。

在保存对象的语句上创建集成测试时，建议刷新实体管理器，以避免任何漏报，即避免测试运行良好，但其操作在生产环境中运行时会失败。实际上，测试可能运行良好，因为第一级缓存未刷新，并且数据库没有写入。为避免这种假阴性集成测试，请在测试正文中使用显式刷新。请注意，生产代码永远不需要使用任何显式刷新，因为 ORM 的作用是决定何时刷新。

在更新语句上创建集成测试时，可能需要清除实体管理器，以便重新加载第一级高速缓存。实际上，更新语句完全绕过第一级高速缓存并直接写入数据库。然后，第一级缓存将不同步，并反映已更新对象的旧值。要避免对象的这种陈旧状态，请在测试正文中使用显式清除。请注意，生产代码永远不需要使用任何显式清除，因为ORM的角色是决定何时清除。

我的测试现在工作正常。