文章目录
0、表结构和实体类
CREATE TABLE blog(
id VARCHAR(20) PRIMARY KEY,
title VARCHAR(10),
author VARCHAR(10),
create_date DATE,
views INT
);
public class Blog {
private String id;
private String title;
private String author;
private Date createDate;
private int views;
}
1、if
test添加正确则拼接这条语句
title==null&&author==null
SELECT * FROM blog WHERE 1=1
title!=null&&author==nul
lSELECT * FROM blog WHERE 1=1 AND title=#{title}
title!=null&&author!=nul
lSELECT * FROM blog WHERE 1=1 AND title=#{title} AND author=#{author}
<select id="getBlogIf" parameterType="map" resultType="Blog">
SELECT * FROM blog WHERE 1=1
<if test="title!=null">
AND title=#{title}
</if>
<if test="author!=null">
AND author=#{author}
</if>
</select>
2、choose,when,otherwise
switch一样,只拼接一个
when->case:当。。。
如果when全部不满足,则拼接otherwise里的语句:否则。。。。
<select id="getBlogChoose" parameterType="map" resultType="Blog">
SELECT * FROM blog WHERE 1=1
<choose>
<when test="title!=null">
AND title=#{title}
</when>
<when test="author!=null">
AND author=#{author}
</when>
<otherwise>
AND author="严奶奶"
</otherwise>
</choose>
</select>
3、where(条件拼接)
当满足条件 if 就拼接语句,如果and或者or在首项(前面没有语句),会自动去掉,所以不用在查询语句后面拼接where 1=1
(1)普通where
<!--where 如果and在前面会自动去掉-->
<select id="getBlogWhere" 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>
(2)自定义where(trim)
-
prefix:使用该标签,如果后面有拼接语句,则拼接上该标签where
-
prefixOverride:前缀覆盖字段(and和or都是在语句前面的)
- and:and在首项去掉,不在则拼接 - and|or:and或者or在首项则去掉,不在则拼接
title==null&&author!=null
SELECT * FROM blog WHERE author=#{author}
<select id="getBlogTrim" parameterType="map" resultType="Blog">
SELECT * FROM blog
<trim prefix="where" prefixOverrides="AND|OR">
<if test="title!=null">title=#{title}</if>
<if test="author!=null">AND author=#{author}</if>
</trim>
</select>
4、set(修改拼接)
(1)普通set
满足条件就拼接,如果后面没有语句了逗号自动去除
<update id="updateBlogSet" parameterType="Blog">
UPDATE blog
<set>
<if test="title!=null">title=#{title},</if>
<if test="author!=null">author=#{author},</if>
<if test="views!=null">views=#{views}</if>
</set>
WHERE id=#{id}
</update>
(2)自定义set(trim)
-
prefix:使用该标签
-
suffixOverrides:后缀覆盖(set语句后面跟逗号,)
-,后面没有语句则去掉, -,后面有语句就放着
<update id="updateBlogTrim" parameterType="Blog">
UPDATE blog
<trim prefix="set" suffixOverrides=",">
<if test="title!=null">title=#{title},</if>
<if test="author!=null">author=#{author},</if>
<if test="views!=null">views=#{views}</if>
</trim>
WHERE id=#{id}
</update>
5、foreach
批量插入List
- item:是集合元素,index是索引
- separator:如果集合不止一个会拼接
- open:拼接在最开始
- close:拼接在最后
因为是个对象集合,所以不用加open和close
<insert id="batchSave" parameterType="Blog">
INSERT INTO blog(id,title,author,create_date,views) VALUES
<foreach collection="blogs" item="blog" index="index" separator=",">
(#{blog.id},#{blog.title},#{blog.author},#{blog.createDate},#{blog.views})
</foreach>
</insert>
INSERT INTO blog(id,title,author,create_date,views) VALUES (?,?,?,?,?) , (?,?,?,?,?) , (?,?,?,?,?) , (?,?,?,?,?) , (?,?,?,?,?)
批量插入Map
- item:value
- index:key