Hibernate HQL query by using like operator

Question

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 ?

@Arthur - not answering your question (seems you've figured it out by yourself anyway) but I've just got to ask - why? I'm genuinely interested - what use case do you have that requires LIKE comparison for what seems to be a surrogate key? — ChssPly76, Dec 14 '09 at 20:33
@ChssPly76 - Hi Chss. It is a legacy system. It is tree structure where a parent group id match all children's id. For instance, if i have a group id = 22100 and i want to retrieve its children's, by matching 221 prefix, i use >>> like '221%'. — Arthur Ronald, Dec 14 '09 at 20:51
@Arthur - I see. Considering that the underlying column is numeric (and, being a PK, likely indexed) you're likely to get a better performance doing numeric comparison. E.g. for your example above, you'd do `where userId > 22100 and userId < 22200`. Depending on your database engine and table size the difference may be quite drastic (because index will be used here but won't be used for LIKE) — ChssPly76, Dec 14 '09 at 21:01
@ChssPly76 Thanks for your reply. It is a really good idea. In that case, i think i need to use a regex to retrieve its prefix. But there is some limitations because AT SOME CASES i need to retrieve some children, not all. So your solution may not work as expected. — Arthur Ronald, Dec 14 '09 at 21:12
@ChssPly76 I would use a regex to retrieve its prefix (221) and suffix (00). Then i need to get the next prefix (222). And then, compare both values as proposed. — Arthur Ronald, Dec 14 '09 at 21:26

score 32 · Accepted Answer · answered Dec 14 '09 at 19:06

32

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

answered Dec 14 '09 at 19:06

Arthur Ronald

33,349
20
110
136

Yes it is. str() gave me the solution. I struggled wtih same issue because my entity class has LONG field. I used Hibernate like operator and It gave me cast exception. Solution found with Hiberanate str() method and i used hibernate queries. Please find sample query. select b.billNumber from Bill b where str(b.billNumber) like :billNumber").setString("billNumber,your_parameter).list(); – saminda konkaduwa Nov 16 '12 at 08:53

score 7 · Answer 2 · answered Dec 14 '09 at 18:56

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

Hibernate HQL query by using like operator

2 Answers2

Linked

Related