在写查询的时候,例如一个User表如下

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class QeUser {
    private Long userId;
    private String userName;
    private String email;
    private String phoneNumber;
    private String sex;
    private String avatar;
    private String password;
    private Boolean status;
    private String loginIp;
    private LocalDateTime loginDate;
    private String signature;
    private Long grade;
    private Long experience;
    private Boolean emailStatus;
    private Long schoolId;
    private Boolean deleteFlag;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
}

那么在一般的业务中,会有很多种查询方式,例如,我需要按照Id来查,按照用户名来查,按照性别来查,或者按照注册时间段来查,以及还有多种组合的查询方式。那么单纯的使用一个个的查询命令显然就不太现实了,所以需要一种通用的组合查询方式,这里是参考了若依管理系统的查询方式。

首先,创建一个专门用来查询的类,即一个UserQueryVO,用来接收查询数据,毕竟肯定是跟password无关的,所以不需要将password加入到其中。

例如我的UserQueryVO.java​是这样子的

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class QeUserVO {
  	private Long userId;
    private String userName;
    private String email;
    private String phoneNumber;
    private String sex;
	private Boolean status;
    private String loginIp;
    private LocalDateTime loginDate;
	private Long grade;
    private Long experience;
    private Boolean emailStatus;
    private Long schoolId;
    private Boolean deleteFlag;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
}

然后将其传递到Mapper层,在Mapper层中使用MyBatis的动态查询来判断每个值是否为空,如果为空的话则不拼接其值,如果不为空的话则将其值拼接到查询条件中。

例子如下

 <select id="commonSelectQeArticleQuery" resultMap="QeArticleQueryMap">
        select qe_article.article_id,
               qe_article.article_title,
               qe_article.article_content,
               qe_article.article_image,
               qe_article.section_id            as article_section_id,
               qe_article.section_type_id,
               qe_article.author_id,
               qe_article.status,
               qe_article.article_weight,
               qe_article.article_top,
               qe_article.delete_flag           as article_delete_flag,
               qe_article.create_time           as article_create_time,
               qe_article.update_time           as article_update_time,
               qe_section.section_id,
               qe_section.section_name,
               qe_section.section_introduce,
               qe_section.section_logo,
               qe_section.section_background,
               qe_section.section_admin_user_id,
               qe_section.section_weight,
               qe_section.section_top,
               qe_section.delete_flag,
               qe_section.create_time,
               qe_section.update_time,
               count(qal.get_like_qearticle_id) as like_count,
               COUNT(qc.collection_id)          AS collection_count,
               count(q.comment_id)              as comment_count
        from qe_article
                 left join qe_section on qe_article.section_id = qe_section.section_id
                 left join qe_collection as qc on qe_article.article_id = qc.article_id
                 left join qe_comment as q on qe_article.article_id = q.article_id
                 left join qe_article_like as qal on qe_article.article_id = qal.get_like_qearticle_id
        group by qe_article.article_id
        <where>
            <if test="articleId != null">
                qe_article.article_id = #{articleId}
            </if>
            <if test="articleTitle != null and articleTitle != ''">
                and
                    qe_article
                    .
                    article_title
                    = #{articleTitle}
            </if>
            <if test="sectionId != null">
                and
                    qe_article
                    .
                    section_id
                    = #{sectionId}
            </if>
            <if test="authorId != null">
                and
                    qe_article
                    .
                    author_id
                    = #{authorId}
            </if>
            <if test="articleTop != null">
                and
                    qe_article
                    .
                    article_top
                    = #{articleTop}
            </if>
        </where>
    </select>

这样就可以不用一个个的写查询语句了,实现了简洁高效的动态查询方法。