Hibernate query for searching part of string

Question

I'm trying to write a hibernate query to search if table Room contains roomname which contains part of string.The string value is in a variable. I wrote a query to get exact room name from the table.

findRoom(String name) {
        Query query = em.createQuery("SELECT a FROM Room a WHERE a.roomname=?1");
        query.setParameter(1, name);
        List rooms = query.getResultList();
        return rooms;
         }

In sql the query is something like this:

mysql_query("
SELECT *
FROM `table`
WHERE `column` LIKE '%"name"%' or '%"name"' or '"name"%'
");

I want to know the hql query for searching the table that matches my query. I can not use string directly, so the search query has to be veriable based and I need all three types in a query, if it's begin with name, or contains name or ends name.

Maurice Perry · Accepted Answer · 2015-02-17 06:39:39Z

9

I would do something like that:

findRoom(String name) {
        Query query = em.createQuery("SELECT a FROM Room a"
                + "WHERE a.roomname LIKE CONCAT('%',?1,'%')");
        query.setParameter(1, name);
        List rooms = query.getResultList();
        return rooms;
         }

answered Feb 17, 2015 at 6:39

Maurice Perry

32.8k9 gold badges72 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jens · Accepted Answer · 2015-02-17 06:32:04Z

7

Use like instead of =:

Query query = em.createQuery("SELECT a FROM Room a WHERE a.roomname like ?1");

query.setParameter(1, "%"+name+"%");

answered Feb 17, 2015 at 6:32

Jens

69.6k15 gold badges107 silver badges133 bronze badges

4 Comments

Mahin Over a year ago

So, %name%' or '%name' or 'name%' is not needed?! @jens

Mahin Over a year ago

I need all three types in a query, if it's begin with name, or contains name or ends name.can you help me there?

Maurice Perry Over a year ago

I believe that "%"+name+"%" includes the other cases

Jens Over a year ago

@Mahin % stands for zero up to n characters. So this %name% includes both of the other.

Collectives™ on Stack Overflow

Hibernate query for searching part of string

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related