Include additional columns in Where clause of Hibernate/JPA Generated UPDATE Query

Question

I am using Hibernate/JPA.

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=?

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=?**

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

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

Answer 1

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

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.

Consider the following entity:

@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

}

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

}

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=?

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.