hibernate中的java分割字符串

Question

我有这样的疑问

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';

值字段由逗号分隔的值组成，如“12342014-11-05 00:00:00”

如何在hibernate标准中转换？有什么帮助吗

编辑

我尝试使用sqlRestriction，但没有查询结果

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 1

你就快到了。你忘了{alias}

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

所以你需要用{alias}.value替换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}
  )
);

同时删除额外的子串索引。他们会有同样的结果

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