MybBatis动态SQL_mybbatis <include>-优快云博客

本文链接：https://blog.youkuaiyun.com/liukezheng3/article/details/109142768

本文介绍MyBatis框架中SQL映射文件的高级用法，包括如何使用IF、WHERE、CHOOSE、SET和FOREACH等标签来提高SQL语句的灵活性与复用性，并探讨如何解决实体类字段与数据库字段不一致的问题。

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

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sunreal.mapper.BlogMapper">
    <!--SQL片段,将常用SQL进行封装-->
    <sql id="if-title-author">
        <if test="title != null and title != ''">
            title = #{title}
        </if>
        <if test='state != null and state != "1"'>
            and author = #{author}
        </if>
    </sql>

    <!--如果实体类（createTime）,数据库（create_time）,
    需要的<settings>标签中设置,开启驼峰转换
    <setting name="mapUnderscoreToCamelCase" value="true"/>,
    或使用<resultMap>标签进行映射-->
    <insert id="addBlog" parameterType="Blog">
        INSERT INTO mybatis.blog (id, title, author, create_time, views)
        VALUES (#{id}, #{title}, #{author}, #{createTime}, #{views})
    </insert>

    <!--<where>标签可以自动过滤多余的and-->
    <!--注意：<test>标签中参数要是有双引号("")state != "1",否则无效-->
    <select id="queryBlog" resultType="Blog" parameterType="map">
        select *
        from mybatis.blog
        <where>
            <include refid="if-title-author"></include>
        </where>
    </select>

    <!--<choose>标签,相当于JAVA的switch语句，
    即使多个条件符合要求,也只会选择优先级最高的一条进行执行,
    同时也可以自动过滤多余（and）-->
    <select id="queryBlogChoose" resultType="Blog">
        select * from mybatis.blog
        <where>
            <choose>
                <when test="title != null and title != ''">
                    title = #{title}
                </when>
                <when test="author != null and author != ''">
                    and author = #{author}
                </when>
                <otherwise>
                    and views = '1000'
                </otherwise>
            </choose>
        </where>
    </select>

    <!--<set>标签,在更新操作中自动过滤多余的逗号(,)-->
    <update id="updateBlog" parameterType="map">
        UPDATE MYBATIS.BLOG
        <set>
            <if test="title != null and title != ''">
                TITLE = #{title},
            </if>
            <if test="author != null and author != ''">
                AUTHOR = #{author}
            </if>
        </set>
        WHERE ID = #{id}
    </update>

    <!--foreach，需要传入集合（ids）-->
    <!--select * from mybatis.blog WHERE title = ? and ( id = ? or id = ? )-->
    <select id="queryBlogFReach" resultType="Blog" parameterType="map">
        select *
        from mybatis.blog
        <where>
            <if test="title != null and title != ''">
                title = #{title}
            </if>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id = #{id}
            </foreach>
        </where>
    </select>
</mapper>