调用数据库函数

jpa使用specification调用数据库函数。例如调用postgresql中to_char(timestamp,text)。

create table duty_time
(
    startTime      timestamp(6)
);

@Test
void testFunction() {

    final List<DutyTime> all = dutyTimeDao.findAll((Specification<DutyTime>) (root, query, cb) -> {
        List<Predicate> predicates = Lists.newArrayList();
        final Predicate equal = cb.equal(cb.function("to_char", String.class, root.get("startTime"), cb.literal("YYYY-MM-DD")), "2019-09-12");
        predicates.add(equal);
        return cb.and(predicates.toArray(new Predicate[0]));
    });
    log.info(all.toString());

}

子查询

List<Deparment> all = deparmentDao.findAll((Specification<Deparment>) (root, query, cb) -> {
    List<Predicate> predicates = Lists.newArrayList();

    predicates.add(cb.equal(root.get("flag"), true));
    predicates.add(cb.equal(root.get("name"), dDepartmentName));
    if (StringUtils.isNotBlank(parentDepartmentName)) {
        Subquery<String> subQuery = query.subquery(String.class);
        Root<Deparment> subRoot = subQuery.from(Deparment.class);
        Subquery<String> where = subQuery.select(subRoot.get("name")).where(cb.equal(root.get("parentId"), subRoot.get("id")), cb.equal(subRoot.get("name"), parentDepartmentName));
        predicates.add(cb.exists(where));
    }

    return cb.and(predicates.toArray(new Predicate[0]));
});

query dsl调用函数

QDuty duty = QDuty.duty;
StringTemplate template = Expressions.stringTemplate("to_char({0},'YYYY-MM-DD')", duty.startTime);
jpaQueryFactory.select(duty.department, duty.departmentId.max()).from(duty).where(template.goe(startDate), template.loe(endDate)).groupBy(duty.department).fetch();

引用

https://www.logicbig.com/tutorials/java-ee-tutorial/jpa/criteria-api-calling-database-function.html