目 录CONTENT

文章目录

SpringBoot + JOOQ 动态条件查询

不争
2024-01-02 / 0 评论 / 0 点赞 / 53 阅读 / 5721 字

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;
        }
    }

测试结果

image-1701667948270

image-1701667900838

0

评论区