Mybatis 常用sql-CSDN博客

本文链接：https://blog.csdn.net/tumu6889/article/details/126903310

本文详细介绍了MyBatis框架中动态SQL的使用方法，包括resultMap、where、set/trim、choose、foreach等标签的应用场景及示例代码。通过这些标签可以实现更灵活的数据查询与更新。

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

Sql标签:

<sql id="Base_Column_List">
    id, user_name,age, pass_word
</sql>

resultMap标签

<resultMap id="BaseResultMap" type="com.feng.xxx.User">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="user_name" jdbcType="VARCHAR" property="userName" />
    <result column="create_time" jdbcType="DATE" property="createTime" />
    <collection property="" select="">
</resultMap>

where 标签

id 或 idList 不能为空

<select id="getById" resultMap="Base_Column_List">
        select <include refid="sqlField" />
        from t_pub_user
        where id in
            <if test="idList != null and idList.size() > 0">
                <foreach collection="idList" item="id" separator="," open="(" close=")">
                    #{id}
                </foreach>
            </if>
        

</select>

Set / trim 标签:

<set>用在update语句上面的,会自动加上set关键字, 会自动去除最后一个更新字段的","

<update id="updateUser">
    update emp
    <!--
        <trim prefix="set" suffixOverrides=",">
            <if test="userName != null and userName != '' ">
                user_name = #{userName},
            </if>
            <if test="createDate != null and createDate != '' ">
                create_date = #{createDate},
            </if>
            <if test="deptId != null and deptId != '' ">
                dept_id= #{deptId}
            </if>
        </trim>
    -->
    <set>
            <if test="userName != null and userName != '' ">
                user_name = #{userName},
            </if>
            <if test="createDate != null and createDate != '' ">
                create_date = #{createDate},
            </if>
            <if test="deptId != null and deptId != '' ">
                dept_id= #{deptId}
            </if>
    </set>
    where id = #{id}

</update>

trim标签:

prefixOverrides : 在所有包含的SQL后面加上去除指定的字符串

<select id = "queryEmp" resultType="Emp">
    <include refid="selectEmp">
        <property name="tableName" value="emp">
    </include>
    <trim prefix="WHERE" prefixOverrides="and|or">
        <if test="id != null and id > 0">
            and id = #{id}
        </if>
        <!-- OGNL 调用对象的方法 -->
        <if test="userName != null and userName.indexOf('许') > -1 ">
            and user_name = #{userName}
        </if>
        <if test="begindate != null ">
            and create_date <![CDATA[ >= ]]> #{begindate}
        </if>
        <if test="endDate != null ">
            and create_date <![CDATA[ <= ]]> #{endDate}
        </if>
    </trim>

</select>

choose标签:

<select id="queryEmp2" resultType="Emp">
    <include refid="selectEmp">
        <property name="columns" value="id,dept_id" />
    </include>
    <where>
        <choose>
            <when test="deptName == '经理' ">
                dept_id = 1
            </when>
            <when test="deptName == '普通员工' ">
                dept_id = 2
            </when>
            <otherwise>
                dept_id = #{id}
            </otherwise>

         </choose>
    </where>

</select>

foreach标签:

<select id="queryEmp3" resultType="Emp">
    <include refid="selectEmp">
        <property name="columns" value="id,user_name, dept_id" />
    </include>
    <where>
        <foreach collection="userNames" item="userName" separator="," open="" close="" index="">
           #{userName}
         </foreach>
    </where>

</select>