java - 在 hibernate 中拆分字符串

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

值字段由逗号分隔值组成，例如 '1234,2014-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.

因此您需要将 value 替换为 {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}
  )
);

同时删除额外的 substring_index。他们将有相同的结果。

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