調(diào)用數(shù)據(jù)庫函數(shù)
jpa使用specification調(diào)用數(shù)據(jù)庫函數(shù)氢惋。例如調(diào)用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調(diào)用函數(shù)
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
