hibernate range query

Question

I want to do range query like ,

select u.age from user u where age between 10 and 20. These 10 and 20 will be dynamic values.

I tried parameter as,

a=10;
b=20; 
Query q = session.createQuery("select u.age from user u where age between a and b");
q.setInteger("a",a);
q.setInteger("b",b);  

This is giving me error. I am missing something after between word. Can anybody tell me how this can be achived ?

Answer 1

The more optimal solution is using a hibernate Criteria, rather than hard-coded SQL queries. You should declare a @Entity annotation for your User class, and create private fields corresponding to the columns of your table.

Let's say you have a User table with an id, name, and age. The User class will then look like:

@Entity
@Table(name = "User")
public class User{

    private long id;
    private String userName;
    private int age;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(unique = true, nullable = false)
    public Long getId() {
        return this.id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    @Column(name = "userName", nullable = false)
    public String getUserName() {
        return this.userName;
    }

    @Column(name = "age")
    public void setAge(int age) {
        this.age = age;
    }

    public int getAge() {
    return this.age;
}

Note: if the columns in the database table have exactly the same name as your fields, the @Column (name = "..") annotation is not necessary.

Then, retrieving the age would be :

int userAge = session.createCriteria(User.class)
                         .add(Restrictions.ge("age", a))
                         .add(Restrictions.le("age", b))
                         .setProjection(Property.forName("age"))
                         .uniqueResult();

Creating a criteria by User class tells hibernate to make a select from User table. Then, the restrictions mean where age >= a and age <= b, respectively. The uniqueResult() tells hibernate that we expect a single result, while the projection is needed to retrieve a certain column (the projection there means select age from User). If no projection is specified, the criteria would return by default the entire class, i.e. a select * from User.

I know this seems a little too complicated for what you need, but working with entities that match your tables and with the Criteria is the right way to go.

Answer 2

You need to make named parameters.

a=10;
b=20; 
Query q = session.createQuery("select u.age from user u where age between :a and :b");
q.setInteger("a",a);
q.setInteger("b",b);