在 Hibernate/JPA Generated UPDATE Query 的 Where 子句中包含其他列 [英] Include additional columns in Where clause of Hibernate/JPA Generated UPDATE Query

问题描述

我正在使用 Hibernate/JPA.

I am using Hibernate/JPA.

当我执行 entity.save() 或 session.update(entity) 时，hibernate 会生成这样的查询:-

When i do an entity.save() or session.update(entity), hibernate generates a query like this :-

update TABLE1 set COL_1=? , COL_2=? , COL_3=? where COL_PK=?

我可以通过实体中的任何注释在 WHERE 子句中包含一个额外的列，因此它可以导致如下查询:-

Can I include an additional column in the WHERE clause by means of any annotation in the entity, so it can result in a query like :-

update TABLE1 set COL_1=? , COL_2=? , COL_3=? where COL_PK=? **AND COL_3=?**

这是因为我们的数据库是基于 COL_3 分片的，这需要出现在 where 子句中

This is because our DB is sharded based on COL_3 and this needs to be present in where clause

我希望能够仅使用 session.update(entity) 或 entity.save() 来实现这一点.

I want to be able to achieve this using the session.update(entity) or entity.save() only.

推荐答案

如果我理解正确的话，基本上你所描述的是你希望 hibernate 表现得像你有一个复合主键，即使你的数据库有一个单一的 -列主键(您还有一个 @Version 列来执行乐观锁定).

If I understand things correctly, essentially what you are describing is that you want hibernate to act like you have a composite primary key even though your database has a single-column primary key (where you also have a @Version column to perform optimistic locking).

严格来说，您的休眠模型不需要与您的 db-schema 完全匹配.您可以将实体定义为具有复合主键，确保所有更新都基于两个值的组合进行.这里的缺点是您的加载操作稍微复杂一些.

Strictly speaking, there is no need for your hibernate model to match your db-schema exactly. You can define the entity to have a composite primary key, ensuring that all updates occur based on the combination of the two values. The drawback here is that your load operations are slightly more complicated.

考虑以下实体:

@Entity
@Table(name="test_entity", uniqueConstraints = { @UniqueConstraint(columnNames = {"id"})  })
public class TestEntity implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", nullable = false, unique = true)
    private Long id;

    @Id
    @Column(name = "col_3", nullable = false)
    private String col_3;

    @Column(name = "value", nullable = true)
    private String value;

    @Version
    @Column(nullable = false)
    private Integer version;

    ... getters & setters

}

然后你可以有以下方法(在我的例子中，我创建了一个简单的 JUnit 测试)

Then you can have the following method (in my case, I created a simple JUnit test)

@Test
public void test() {

    TestEntity test = new TestEntity();
    test.setCol_3("col_3_value");
    test.setValue("first-value");

    session.persist(test);

    long id = test.getId();
    session.flush();
    session.clear();

    TestEntity loadedTest = (TestEntity) session
            .createCriteria(TestEntity.class)
            .add(Restrictions.eq("id", id))
            .uniqueResult();

    loadedTest.setValue("new-value");
    session.saveOrUpdate(loadedTest);
    session.flush();

}

这会生成以下 SQL 语句(启用 Hibernate 日志记录)

This generates the following SQL statements (enabled Hibernate logging)

Hibernate: 
    call next value for hibernate_sequence
Hibernate: 
    insert 
    into
        test_entity
        (value, version, id, col_3) 
    values
        (?, ?, ?, ?)
Hibernate: 
    select
        this_.id as id1_402_0_,
        this_.col_3 as col_2_402_0_,
        this_.value as value3_402_0_,
        this_.version as version4_402_0_ 
    from
        test_entity this_ 
    where
        this_.id=?
Hibernate: 
    update
        test_entity 
    set
        value=?,
        version=? 
    where
        id=? 
        and col_3=? 
        and version=?

正如您所见，这使得加载稍微复杂一些 - 我在这里使用了一个条件，但它满足您的条件，即您的更新语句始终在where"子句中包含列 col_3.

This makes loading slightly more complicated as you can see - I used a criteria here, but it satisfies your criteria, that your update statements always include the column col_3 in the 'where' clause.

这篇关于在 Hibernate/JPA Generated UPDATE Query 的 Where 子句中包含其他列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！