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

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.


The following solution works, however I recommend you to just wrap your saveOrUpdate method in a way that you ends up using a more natural approach. Mine is fine... but is a bit hacky.

Solution:

You can create your own annotation and inject your extra condition to hibernate save method using a hibernate interceptor. The steps are the following:

1. Create a class level annotation:

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface ForcedCondition {
    String columnName() default "";
    String attributeName() default ""; // <-- this one is just in case your DB column differs from your attribute's name
}

2. Annotate your entity specifying your column DB name and your entity attribute name

@ForcedCondition(columnName = "col_3", attributeName= "col_3")
@Entity
@Table(name="test_entity")
public class TestEntity implements Serializable {

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

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

    public String getCol_3() {
        return col_3;
    }
    ... getters & setters

}

3. Add a Hibernate interceptor and inject the extra condition:

public class ForcedConditionInterceptor extends EmptyInterceptor {
    private boolean forceCondition = false;
    private String columnName;
    private String attributeValue;

    @Override
    public boolean onSave(
            Object entity,
            Serializable id,
            Object[] state,
            String[] propertyNames,
            Type[] types) {
        // If your annotation is present, backup attribute name and value
        if (entity.getClass().isAnnotationPresent(ForcedCondition.class)) {
            // Turn on the flag, so later you'll inject the condition
            forceCondition = true;
            // Extract the values from the annotation
            columnName = entity.getClass().getAnnotation(ForcedCondition.class)).columnName();
            String attributeName = entity.getClass().getAnnotation(ForcedCondition.class)).attributeName();
            // Use Reflection to get the value 
            // org.apache.commons.beanutils.PropertyUtils
            attributeValue = PropertyUtils.getProperty(entity, attributeName);
        }
        return super.onSave(entity, id, state, propertyNames, types);
    }

    @Override
    public String onPrepareStatement(String sql) {
        if (forceCondition) {
            // inject your extra condition, for better performance try java.util.regex.Pattern
            sql = sql.replace(" where ", " where " + columnName + " = '" + attributeValue.replaceAll("'", "''") + "' AND ");
        }
        return super.onPrepareStatement(sql);
     }
}

After all that everytime you call entity.save() or session.update(entity) over an entity annotated with @ForcedCondition, the SQL will be injected with the extra condition you want.

BTW: I didn't tested this code but it should get you along the way. If I did any mistake please tell me so I can correct.