动态sql

搭建环境

create table `blog`(
	`id` varchar(50) not null comment '博客id',
	`title` varchar(100) not null comment '博客标题',
	`author` varchar(30) not null comment '博客作者',
	`create_time` datetime not null comment '创建时间',
	`views` int(30) not null comment '浏览量'
)engine = innodb default charset=utf8

创建基础工程

1. 导包

2. 编写配置文件

3. 编写实体类

   @Data
   public class Blog {
        private int id;
        private String title;
        private String author;
        private Date createTime;
        private int views;
   }
   

4. 编写实体类对应的mappper接口和mapper.xml

IF

<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <if test="title != null">
            title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </where>
</select>

choose

<select id="queryBlogChoose"  parameterType="map" resultType="blog">
    select * from blog
    <where>
        <choose>
            <when test="title != null">
                title = #{title}
            </when>
            <when test="author != null">
                author = #{author}
            </when>
            <otherwise>
                and views = #{views}
            </otherwise>
        </choose>
    </where>
</select>

SQL片段

将一些功能的部分抽取出来方便复用

1. 使用sql标签抽取公共的部分

   <sql id="if-title-author">
       <if test="title != null">
           title = #{title}
       </if>
       <if test="author != null">
           and author = #{author}
       </if>
   </sql>
   

2. 在需要使用的地方使用include标签引用即可

   <select id="queryBlogIF" parameterType="map" resultType="blog">
       select * from blog
       <where>
           <include refid="if-title-author"></include>
       </where>
   </select>
   

注意事项：

• 最好使用单表来定义sql片段
• 不要存在where标签

Foreach

select * from user where 1=1 and
	<foreach item="id" conllection="ids"
		open="("separator="or" close=")">
		#{id}
	</foreach>
(id=1 or id=2 or id=3) 

<select id="queryBlogForeach" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <foreach collection="ids" item="id" open="and(" close=")" separator="or">
            id = #{id}
        </foreach>
    </where>
</select>