Spring Data JPA Update @Query不更新?

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:

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

adminRepository.flush();

But the failed assertion remained identical.

但是失败的断言仍然是相同的。

I can see the update sql statement in the log:

我可以在日志中看到update 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='[e-mail]', profile=NULL,
post_login_url=NULL where id=2839

But the log shows no sql that could relate to the finder:

但是日志中没有显示与查找程序相关的sql:

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:

如果我随后向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());

then I can see in the log the sql statement being generated:

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

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.

另一个令我困惑的问题是，它显示了一个limit 2子句，即使它只返回一个Admin对象。

I thought there would always be a limit 1 when returning one domain object. Is this a wrong assumption on Spring Data ?

我认为当返回一个域对象时，总有一个限制1。这是对Spring数据的错误假设吗?

When pasting in a MySQL client, the sql statements displayed in the console log, the logic works fine:

当粘贴到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, '[e-mail]039', '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 | [e-mail]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='[e-mail]', 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 | [e-mail] | 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='[e-mail]' 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 | [e-mail] | 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 | [e-mail] | 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 ?

那么，为什么这在Java级别上没有反映出来呢?

3 个解决方案

#1

The EntityManager doesn't flush change automatically by default. You should use the following option with your statement of query:

EntityManager在默认情况下不会自动刷新更改。您应该在查询语句中使用以下选项:

@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

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.

当在保存一个对象的语句上创建一个集成测试时，建议将该实体管理器刷新，以避免任何假阴性，即避免测试运行良好，但在生产过程中运行会失败。实际上，测试可以正常运行，仅仅是因为没有刷新第一级缓存，并且没有写入操作会影响数据库。要避免这种错误的负集成测试，请在测试主体中使用显式刷新。注意，生产代码不应该使用任何显式刷新，因为决定何时刷新是ORM的角色。

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.

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

My test now works just fine.

我的考试现在很好。

#3

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:

示例应用程序有两个类和一个与此问题相关的接口:

1. The application context configuration class
2. 应用程序上下文配置类
3. The entity class
4. 实体类
5. The repository interface
6. 存储库接口

These classes and the repository interface are described in the following.

下面将描述这些类和存储库接口。

The source code of the PersistenceContext class looks as follows:

PersistenceContext类的源代码如下所示:

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:

假设我们有一个简单的实体叫做Todo，源代码如下所示:

@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:

我们的存储库接口有一个名为updateTitle()的方法，用于更新todo条目的标题。TodoRepository接口的源代码如下:

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<Todo, Long> {

    @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.

updateTitle()方法没有使用@Transactional注释，因为我认为最好使用服务层作为事务边界。

The Integration Test

集成测试

The Integration Test uses DbUnit, Spring Test and Spring-Test-DBUnit. It has three components which are relevant to this problem:

集成测试使用DbUnit、Spring测试和Spring-Test- DbUnit。它有三个与这个问题有关的组成部分:

1. The DbUnit dataset which is used to initialize the database into a known state before the test is executed.
2. DbUnit数据集，用于在执行测试之前将数据库初始化为已知状态。
3. The DbUnit dataset which is used to verify that the title of the entity is updated.
4. DbUnit数据集，用于验证更新了实体的标题。
5. The integration test.
6. 集成测试。

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:

用于将数据库初始化为已知状态的DbUnit数据集文件的名称是toDoData。xml及其内容如下:

<dataset>
    <todos id="1" description="Lorem ipsum" title="Foo" version="0"/>
    <todos id="2" description="Lorem ipsum" title="Bar" version="0"/>
</dataset>

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?):

用于验证todo条目标题已更新的DbUnit数据集的名称称为toDoData-update。xml及其内容如下(出于某种原因，todo条目的版本没有更新，但标题是)。任何想法为什么?):

<dataset>
    <todos id="1" description="Lorem ipsum" title="FooBar" version="0"/>
    <todos id="2" description="Lorem ipsum" title="Bar" version="0"/>
</dataset>

The source code of the actual integration test looks as follows (Remember to annotate the test method with the @Transactional annotation):

实际集成测试的源代码如下(记得用@Transactional注释注释测试方法):

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?

在运行集成测试之后，测试通过并更新todo条目的标题。我遇到的唯一问题是版本字段没有更新。任何想法为什么?

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.

我认为这种描述有点模糊。如果您想获得关于为Spring Data JPA存储库编写集成测试的更多信息，您可以阅读我的博客文章。