SpringBoot + JOOQ 动态条件查询
定义Payload类(类似bo)
@Data
public class ContentPayload {
private Long columnId;
private String title;
private String brief;
private String author;
private Timestamp startDate;
private Timestamp endDate;
}
Controller层
@OpenApiMethod(name = "findByConditions", intro = "动态条件查询所有文章")
public ApiResponse<List<ContentArticle>> findByConditions(ApiRequest<ContentPayload> request) {
ContentPayload contentPayload = request.getPayload();
Long columnId = contentPayload.getColumnId();
String title = contentPayload.getTitle();
String brief = contentPayload.getBrief();
String author = contentPayload.getAuthor();
Timestamp startDate = contentPayload.getStartDate();
Timestamp endDate = contentPayload.getEndDate();
return ApiResponse.from(contentArticleService.findByConditions(columnId, title, brief,author,startDate, endDate));
}
Interface层
/**
* 根据多种条件查询文章
* @param columnId 分类id
* @param title 标题
* @param brief 副标题
* @param author 作者
* @param startDate 开始时间
* @param endDate 结束时间
* @return 文章列表
*/
List<ContentArticle> findByConditions(Long columnId, String title, String brief, String author, Timestamp startDate, Timestamp endDate);
Service层
@Override
public List<ContentArticle> findByConditions(Long columnId, String title, String brief, String author, Timestamp startDate, Timestamp endDate) {
try {
DSLContext create = DSL.using(configuration);
SelectConditionStep<Record> query = create.select()
.from(Tables.CONTENT_ARTICLE)
.where(true);// 默认添加 true 条件,以便在后面添加其他条件
if (columnId != null) {
query.and(Tables.CONTENT_ARTICLE.COLUMN_ID.eq(columnId));
}
if (title != null && !title.isEmpty()) {
query.and(Tables.CONTENT_ARTICLE.TITLE.like(title + "%"));
}
if (brief != null && !brief.isEmpty()) {
query.and(Tables.CONTENT_ARTICLE.BRIEF.eq(brief + "%"));
}
if (author != null && !author.isEmpty()) {
query.and(Tables.CONTENT_ARTICLE.AUTHOR.eq(author));
}
if (startDate != null && endDate != null) {
query.and(Tables.CONTENT_ARTICLE.UPDATED_AT.between(startDate, endDate));
}
return query.fetchInto(ContentArticle.class);
} catch (Exception ex) {
log.warn("findByConditions", ex);
return null;
}
}
评论区