mybatis拼SQL时使用ifelse

最新推荐文章于 2025-05-21 17:50:42 发布

原创最新推荐文章于 2025-05-21 17:50:42 发布 · 2.7k 阅读

0 ·

CC 4.0 BY-SA版权

其他专栏收录该内容

10 篇文章

订阅专栏

本文详细解析了一个使用MyBatis动态SQL的示例，展示了如何通过条件判断（if、choose）、循环（foreach）及排序等动态构建SQL查询语句。此示例涵盖了多个查询字段，包括用户ID、用户名、部门ID等，并提供了灵活的排序和分页功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

<select id="list" resultType="com.bootdo.system.domain.UserDO">
        select
        `user_id`,`username`,`name`,`password`,`dept_id`,`email`,`mobile`,`status`,`user_id_create`,`gmt_create`,`gmt_modified`,`sex`,`birth`,`pic_id`,`live_address`,`hobby`,`province`,`city`,`district`
        from sys_user
        <where>
            <if test="userId != null and userId != ''">and user_id = #{userId}</if>
            <if test="username != null and username != ''">and username = #{username}</if>
            <if test="name != null and name != ''">and name = #{name}</if>
            <if test="password != null and password != ''">and password = #{password}</if>
            <if test="deptId != null and deptId != ''">and dept_id = #{deptId}</if>
            <if test="deptIds != null and deptIds.size() > 0">and dept_id in
                <foreach collection="deptIds" item="item" index="index" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test="email != null and email != ''">and email = #{email}</if>
            <if test="mobile != null and mobile != ''">and mobile = #{mobile}</if>
            <if test="status != null and status != ''">and status = #{status}</if>
            <if test="userIdCreate != null and userIdCreate != ''">and user_id_create = #{userIdCreate}</if>
            <if test="gmtCreate != null and gmtCreate != ''">and gmt_create = #{gmtCreate}</if>
            <if test="gmtModified != null and gmtModified != ''">and gmt_modified = #{gmtModified}</if>
            <if test="sex != null and sex != ''">and sex = #{sex}</if>
            <if test="birth != null and birth != ''">and birth = #{birth}</if>
            <if test="picId != null and picId != ''">and pic_id = #{picId}</if>
            <if test="liveAddress != null and liveAddress != ''">and live_address = #{liveAddress}</if>
            <if test="hobby != null and hobby != ''">and hobby = #{hobby}</if>
            <if test="province != null and province != ''">and province = #{province}</if>
            <if test="city != null and city != ''">and city = #{city}</if>
            <if test="district != null and district != ''">and district = #{district}</if>
        </where>
        <choose>
            <when test="sort != null and sort.trim() != ''">
                order by ${sort} ${order}
            </when>
            <otherwise>
                order by user_id desc
            </otherwise>
        </choose>
        <if test="offset != null and limit != null">
            limit #{offset}, #{limit}
        </if>