我的数据库有字符串类型的'm_date‘,并将日期存储为“”格式。现在,我希望借助过滤器之间的JPA标准查询从表中获取记录。既然m_date存储字符串日期,我就不能直接使用criteriaBuilder.between函数。但在此之前,我需要使用函数转换( Date,m_date,103)将其转换为date。我试着把字符串日期转换成日期。
SQL查询如下所示
SELECT myId from table where status='PENDING' and CONVERT(date, m_date,103) between fromDate and toDate)
标准API
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<MyEntity> criteriaQuery = criteriaBuilder.createQuery(MyEntity.class); Root<MyEntity> root = criteriaQuery.from(MyEntity.class); Predicate statusPredicate = cb.equal(root.get("status"), cb.literal("PENDING")); Expression<String> convertFunction = cb.function("CONVERT", String.class, cb.literal("date"), root.get("mDate"), cb.literal(103)); Predicate datePredicate = cb.between(convertFunction, fromDate, toDate); // fromDate and toDate coming from request... CriteriaQuery<MyEntity> myQuery = criteriaQuery.select(root.get("myId")).where(statusPredicate, datePredicate); TypedQuery<MyEntity> query = entityManager.createQuery(myQuery); return query.getResultList();
我遇到了类似这样的错误
Hibernate: select myentity_0.my_id as col_0_0_ from table where myentity_0.status=? and (CONVERT(?,myentity.m_date,103) between ? and ?) com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P1'.
有人能帮我吗,我怎么能把“日期”作为转换函数的第一个参数?我已经被困了这么久了。我会很感激你的帮助。提前谢谢。
发布于 2022-10-04 15:42:22
您应该能够使用 root.get("mDate").as(java.sql.Date.class) 代替。如果这不起作用,您将不得不创建一个自定义 SQLFunction ,例如Hibernate中的 convert_date ,并调用它:
root.get("mDate").as(java.sql.Date.class)
SQLFunction
convert_date
Expression<Date> convertFunction = cb.function("convert_date", Date.class, root.get("mDate"));
它应该呈现您想要的SQL。可能是这样的:
public class ConvertDateFunction implements SQLFunction { @Override public boolean hasArguments() { return true; @Override public boolean hasParenthesesIfNoArguments() { return true; @Override public Type getReturnType(Type firstArgumentType, Mapping mapping) throws QueryException { return StandardBasicTypes.DATE;