Hibernate HQL query by using like operator

Question

Hi,

Seu the following mapping

@Entity
public class User {

    private Integer id;

    @Id;
    private Integer getId() {
        return this.id;
    }

}

Notice id is an Integer. Now i need this HQL query by using like operator

Query query = sessionFactory.getCurrentSession().createQuery("from User u where u.id like :userId");

ATT: IT IS like operator NOT = (equals operator)

Then i use

List<User> userList = query.setParameter("userId", userId + "%").list();

But does not work because Hibernate complains IllegalArgumentException occured calling getter of User.id

Even when i use

query.setString("userId", userId + "%");

It does not work

What should i use to pass the query ?

Answer 1

Well, LIKE operator is usually used with textual data i.e. with VARCHAR or CHAR columns, and you have numeric id column (INTEGER).

Maybe you could try to map id field also as string and use that field in query. This may or may not work depending on your database engine. Note that you should handle all updates via setId() and consider idAsString field to be read-only.

@Entity
public class User {

    private Integer id;
    private String idAsString;

    @Id;
    private Integer getId() {
        return this.id;
    }

    private void setId(Integer id) {
        this.id = id;
    }

    @Column(name="id", insertable=false, updatable=false)
    private String getIdAsString() {
       return this.idAsString;
    }

    private void setIdAsString(String idAsString) {
       this.idAsString = idAsString;
    }
}

Then the query would be:

Query query = sessionFactory.getCurrentSession().createQuery("from User u where u.idAsString like :userId");
List<User> userList = query.setParameter("userId", userId + "%").list();

Answer 2

According to Hibernate reference:

str() is used for converting numeric or temporal values to a readable string

So when i use

from User u where str(u.id) like :userId

It works fine