持续更新中…
查询时转换字段类型
// 转Stirng LiteralExpression
qBookCirculationOriginalRecord.createDate.stringValue().as("date"),
// 转其他
qBookCirculationOriginalRecord.bookId.count().doubleValue().as("bookCount")
查询条件拼接(1)
private BooleanBuilder deviceSummary(Date startTime, Date endTime, List<String> deviceSerialNumbers, String bookCirculationType){
BooleanBuilder booleanBuilder = new BooleanBuilder();
booleanBuilder.and(qBookCirculationOriginalRecord.bookCirculationType.eq(bookCirculationType)
.and(qBookCirculationOriginalRecord.createDate.between(startTime, endTime)));
if (deviceSerialNumbers != null && deviceSerialNumbers.size() > 0){
booleanBuilder.and(qBookCirculationOriginalRecord.deviceSerialNumber.in(deviceSerialNumbers));
}
return booleanBuilder;
}
查询条件拼接(2)- 字符串截取
public List<String> getReaderAddMonthSummaryCount(String libraryCode, String startDate, String endDate) {
BooleanExpression expression = qReaderAddDaySummary.libraryCode.eq(libraryCode);
if (!StringUtils.isEmpty(startDate)){
expression = expression.and(qReaderAddDaySummary.date.substring(-1, 7).goe(startDate));
}
if (!StringUtils.isEmpty(endDate)){
expression = expression.and(qReaderAddDaySummary.date.substring(-1, 7).loe(endDate));
}
return jpaQueryFactory.selectDistinct(qReaderAddDaySummary.date.substring(-1, 7))
.from(qReaderAddDaySummary).where(expression).fetch();
}
查询条件拼接(3)- 设置默认值
// 当shuce.shuceid为空时显示0
queryFactory.select(shuce.shuceid.coalesce(0L)).from(shuce)
行列转换
NumberExpression<Integer> jieyuecs = jhrz.operate.when("借出").then(1).otherwise(0).sum();
查询语句设置常量
// 这里面还有其他妙用,详情查看Expressions源码(Expressions可以创建所有类型的常量,不光是字符串)
Expressions.stringTemplate("'常量'")
StringTemplate stringTemplate = Expressions.stringTemplate("replace(qnlcBookInfo.isbn, '-', '')");
booleanBuilder.and(stringTemplate.eq(newisbn).or(stringTemplate.eq(data.getIsbn())));
使用某些没有的函数
// 这里面还有其他妙用,详情查看Expressions源码(Expressions可以创建所有类型的常量,不光是字符串)
tringTemplate substr = Expressions.stringTemplate("to_char({0},'yyyy-mm')", jhrz.optime);