Hibernate criteria for difference between 2 columns

Question

I have a trivial hibernate entity with 2 fields - a and b:

@Entity
public class PlayerEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(nullable = false)
    private Integer a;

    @Column(nullable = false)
    private Integer b;
}

I need to select all the players where a - b > 5.
Can this be ever done using standard Hibernate Criteria API? Can I somehow avoid using SQL/HQL for this rather typical condition?
Thanks!

Answer 1

You can use Restrictions.sqlRestriction() to produce a Criterion using a SQL condition:

List<PlayerEntity> playerList = (List<PlayerEntity>)session.createCriteria(PlayerEntity.class)
.add(Restrictions.sqlRestriction("(a- b) > 5")).list();

which will generate the SQL : select * from PlayerEntity where (a-b) > 5

If you don't want to use the SQL to specify condition in the Criteria API ,you can define (a - b) as the derived property using the @Formula :

@Entity
public class PlayerEntity {

  @Column(nullable = false)
  private Integer a;

  @Column(nullable = false)
  private Integer b;

  @Formula("a - b") 
  private Integer delta
}

List<PlayerEntity> playerList = (List<PlayerEntity>)session.createCriteria(PlayerEntity.class)
.add(Restrictions.gt("delta", 5).list();

Please note that the value of @Formula is the actual column name instead of the mapped property names.