Mybatis 常用sql

落日映苍穹つ 2024-04-06 10:03 58阅读 0赞

**目录**

resultMap标签

where 标签

Set / trim 标签:

choose标签:

foreach标签:

--------------------

### Sql标签: ###

### 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
        
        <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>
            
            <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标签: ###

### 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>