spring-data-jpa JPA标准API使用SQL Server转换函数

bmp9r5qi  于 2022-11-10  发布在  Spring
关注(0)|答案(1)|浏览(174)

我的数据库有'm_date',它是字符串类型,并将日期存储为“dd-MM-yyyy”格式。现在,我想借助JPA筛选器之间的条件查询从表中获取记录。现在,由于m_date存储字符串日期,因此我不能直接使用criteriaBuilder.between函数。但在此之前,我需要使用SQL内置函数CONVERT将其转换为日期(date,m_date,103).我尝试了类似于将字符串日期转换为日期的方法。
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'.

有人能帮助我吗,我怎么能把“日期”作为转换函数的第一个参数?我被卡住了这么久。帮助将是非常感激的。提前感谢。

oknwwptz

oknwwptz1#

你应该可以使用root.get("mDate").as(java.sql.Date.class)来代替。如果这不起作用,你将不得不创建一个自定义的SQLFunction,例如在Hibernate中的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;
    }

    @Override
    public String render(Type firstArgumentType, List args, SessionFactoryImplementor factory) throws QueryException {
        return "convert(date," + args.get(0) + ",103)";
    }
}

相关问题