dup135 dup135 - 3 months ago 32
Java Question

Split string in hibernate

I have query like this.

SELECT *
FROM test.config
WHERE loginname = 'login'
AND SUBSTRING_INDEX(SUBSTRING_INDEX(`value`,',',-1),',',2) BETWEEN '2014-11-05 09:00:00' AND '2014-11-05 10:00:00';


value field consist of comma separated value like '1234,2014-11-05 00:00:00'

How can I convert this in hibernate criteria? Any help?

EDIT

I tried to use sqlRestriction, but no query results.

DetachedCriteria dc = DetachedCriteria.forClass(Config.class);
dc.add(Restrictions.eq("loginname",loginname));
dc.add(Restrictions.sqlRestriction("substring_index(substring_index(`value`,',',-1),',',2)
between ? and ?", new Object[]{startDate,endDate}, new
Type[]{StandardBasicTypes.DATE,StandardBasicTypes.DATE}));

Answer

You're almost there. You've forgot the {alias}.

Apply a constraint expressed in SQL, with the given JDBC parameter. Any occurrences of {alias} will be replaced by the table alias.

So you need to replace value with {alias}.value.

DetachedCriteria dc = DetachedCriteria.forClass(Config.class); dc.add(Restrictions.eq("loginname",loginname));
dc.add(Restrictions.sqlRestriction("substring_index(substring_index({alias}.value,',',-1),',',2) between ? and ?", new Object[]{startDate,endDate}, new Type[]{StandardBasicTypes.DATE,StandardBasicTypes.DATE}));

Remove also extra substring_index. They will have same results.

dc.add(Restrictions.sqlRestriction("substring_index({alias}.value,',',-1) between ? and ?", new Object[]{startDate,endDate}, new Type[]{StandardBasicTypes.DATE,StandardBasicTypes.DATE}));