CODEWITHSUNDEEP
javamysqlhibernate
Raj Bhatia
Raj Bhatia

Reputation: 1108

how to use like clause in mysql for variable of double type using hibernate

I have table shown below. The clause of like is not working for variable of double type in hibernate and mysql. how like clause will work double or what could be the work around to achieve this. I'm getting no exception just the obj the returned is null.

Table:-

CREATE TABLE `loc_location` (
  `location_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` char(1) NOT NULL DEFAULT 'A' COMMENT 'A-Approved\nD-Drafted\nP-Pending\nR-Reject\nS-Scheduled',
  PRIMARY KEY (`location_id`),
  UNIQUE KEY `location_id_UNIQUE` (`location_id`),
  UNIQUE KEY `latitude_UNIQUE` (`latitude`),
  UNIQUE KEY `longitude_UNIQUE` (`longitude`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

Table Values:-

    location_id latitude    longitude   creation_date       status
    1       19.175934   72.8622649  2015-08-22 14:09:31     A
    2       19.177283   72.8636139  2015-08-22 14:09:31     A
    3       19.174585   72.8609159  2015-08-22 14:09:31     A

Query:- longitude = 72.862 and latitude = 19.175

select this_.location_id as location1_16_0_, this_.creation_date as creation2_16_0_, this_.latitude as latitude3_16_0_, this_.longitude as longitud4_16_0_, this_.status as status5_16_0_ from loc_location this_ where this_.longitude like ? and this_.latitude like ?

Hibernate method:-

public <T extends Serializable> T getEntityWithLikeClause(T x, Map<String, Object> ceriteriaMap, Session session) throws PersistenceException{
    T obj=null;
    Criteria criteria = null;
    try {
        criteria= session.createCriteria(x.getClass());
        if(ceriteriaMap!=null && criteria !=null){
            Set<String> set=ceriteriaMap.keySet();
            for (Iterator<String> iterator = set.iterator(); iterator.hasNext();) {
                String ceriteria = iterator.next();
                Object value = ceriteriaMap.get(ceriteria);
                criteria.add(Restrictions.like(ceriteria, value));
            }
        }
        obj = (T) criteria.uniqueResult();
    }catch (Exception e) {
        throw new PersistenceException(e);
    }
    return obj;
}

Where ceriteriaMap as following and longitude1 = 72.862 and latitude1 = 19.175:-

        Map<String, Object> ceriteriaMap = new HashMap<String, Object>();
        ceriteriaMap.put("lat", latitude1);
        ceriteriaMap.put("lng", longitude1);

Upvotes: 0

Views: 450

Answers (2)

The Coder
The Coder

Reputation: 2632

Restrictions.ilike cannot be used for numeric fields.

Default Syntax:

Restrictions.ilike(fieldName, SearchTerm, MatchMode.ANYWHERE (Optional) )
                       |          |                 |
                     String     String      MatchMode (optional argument)

So you can't be able to pass a numeric value to a String argument. Even if you pass it as String.valueOf(numbercValue), you'll get exception because hibernate will try to convert it to numeric datatype as per your hbm or annotation configuration

I've used the following code to mix like with criteria.

critria.add(
    Restrictions.sqlRestriction(" column_name LIKE '%"+SearchTerm+"%' "))
                                    |                  |
                   Column Name (Not fieldName)  Search Term prefixed and Suffixed with % as per needs

Upvotes: 1

Oleksandr Loushkin
Oleksandr Loushkin

Reputation: 1529

You can't use LIKE for non varchar(string) types. It works only for String patterns. In any numeric cases, you have to use gt/lt logic or change field to varchar and parse this fiels to double via row mapper.

Upvotes: 0

Related Questions