<if>动态标签:判断参数时满足test指定的条件,如果满足,就执行if(增加if标签中的SQL语句);
注意:test里面使用的参数,可以是mybatis的默认参数,也可以是实体属性名,但是不能是没有指定别名的参数名(尤其是单个参数,也必须起别名,否则异常);
单独使用if,如果不满足条件会SQL拼接出问题,一般我门都跟where一起使用;
<!-- List<Anime> selectAnimesByConditionUserIf(@Param("cid") Integer cid,@Param("author") String author); -->
<select id="selectAnimesByConditionUserIf" resultType="com.kgc.mybatis.bean.Anime">
select `id`,
`cid`,
`name`,
`author`,
`actor`,
`produce`,
`create_date`
from `animes`
where `create_date` < now()
<if test="cid != null and cid != 0 ">
cid = #{cid}
</if>
<if test="author != null">
and author like concat('%',#{author},'%')
</if>
</select>
执行SQL:
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` where `create_date` < now() and author like concat('%',?,'%')
@Test
public void testMybatisMapperDynamicSQlUserIf() throws IOException {
SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
//获取mapper接口的代理实现类对象
AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
//执行动态SQL,查询动漫列表
List<Anime> animeList = animeMapper.selectAnimesByConditionUserIf(0, "土豆");
animeList.forEach(System.out::println);
}
<!-- List<Anime> selectAnimesByConditionUserIfWhere(@Param("cid") Integer cid,@Param("author") String author); -->
<select id="selectAnimesByConditionUserIfWhere" resultType="com.kgc.mybatis.bean.Anime">
select `id`,
`cid`,
`name`,
`author`,
`actor`,
`produce`,
`create_date`
from `animes`
<where>
<if test="cid != null and cid != 0 ">
and cid = #{cid}
</if>
<if test="author != null">
and author like concat('%',#{author},'%')
</if>
</where>
</select>
执行SQL:
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE author like concat('%',?,'%')
@Test
public void testMybatisMapperDynamicSQlUserIfWhere() throws IOException {
SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
//获取mapper接口的代理实现类对象
AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
//执行动态SQL,查询动漫列表
List<Anime> animeList = animeMapper.selectAnimesByConditionUserIfWhere(0, "土豆");
animeList.forEach(System.out::println);
}
<!-- List<Anime> selectAnimesByConditionUserIfTrim(@Param("cid") Integer cid,@Param("author") String author); -->
<select id="selectAnimesByConditionUserIfTrim" resultType="com.kgc.mybatis.bean.Anime">
select `id`,
`cid`,
`name`,
`author`,
`actor`,
`produce`,
`create_date`
from `animes`
<trim prefix=" where " prefixOverrides="and |or" suffixOverrides="and |or" suffix=";">
<if test="cid != null and cid != 0 ">
cid = #{cid} and
</if>
<if test="author != null">
author like concat('%',#{author},'%') and
</if>
</trim>
</select>
执行SQL:
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` where author like concat('%',?,'%') ;
@Test
public void testMybatisMapperDynamicSQlUserIfTerm() throws IOException {
SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
//获取mapper接口的代理实现类对象
AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
//执行动态SQL,查询动漫列表
List<Anime> animeList = animeMapper.selectAnimesByConditionUserIfTrim(0, "土豆");
animeList.forEach(System.out::println);
}
<!-- int updateAnimeByConditionUserIfSet(Anime animeFOrm); -->
<update id="updateAnimeByConditionUserIfSet">
update `animes`
<set>
<if test="cid != null"> `cid` = #{cid},</if>
<if test="name != null"> `name` = #{name},</if>
<if test="author != null"> `author` = #{author},</if>
<if test="actor != null"> `actor` = #{actor},</if>
<if test="produce != null"> `produce` = #{produce},</if>
<if test="createDate != null"> `create_date` = #{createDate},</if>
</set>
where `id` = #{id}
</update>
执行SQL:
Preparing: update `animes` SET `name` = ?, `author` = ? where `id` = ?
@Test
public void testMybatisMapperDynamicSQlIfSetUpd() throws IOException {
SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
//获取mapper接口的代理实现类对象
AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
//模拟前端提供更新参数,实现动态更新,给说明值,就更新什么指端
Anime animeForm = new Anime();
animeForm.setId(637);
animeForm.setName("武动乾坤KGC");
animeForm.setAuthor("土豆KGC");
int row = animeMapper.updateAnimeByConditionUserIfSet(animeForm);
System.out.println(row);
}
<!-- int updateAnimeByConditionUserIfTrim(Anime animeFOrm); -->
<update id="updateAnimeByConditionUserIfTrim">
<trim prefix="update `animes` set " prefixOverrides="," suffixOverrides=",">
<if test="cid != null"> `cid` = #{cid},</if>
<if test="name != null"> `name` = #{name},</if>
<if test="author != null"> `author` = #{author},</if>
<if test="actor != null"> `actor` = #{actor},</if>
<if test="produce != null"> `produce` = #{produce},</if>
<if test="createDate != null"> `create_date` = #{createDate},</if>
</trim>
where `id` = #{id}
</update>
执行SQL:
Preparing: update `animes` set `name` = ?, `author` = ? where `id` = ?
@Test
public void testMybatisMapperDynamicSQlIfTrimUpd() throws IOException {
SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
//获取mapper接口的代理实现类对象
AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
//模拟前端提供更新参数,实现动态更新,给说明值,就更新什么指端
Anime animeForm = new Anime();
animeForm.setId(637);
animeForm.setName("武动乾坤22KGC");
animeForm.setAuthor("土豆22KGC");
int row = animeMapper.updateAnimeByConditionUserIfTrim(animeForm);
System.out.println(row);
}
这个场景主要在传过来的参数,与放进SQL中的参数不一致的时候使用;
比如,前端传过来男/女,但是数据库中查询的时候需要使用1/2;(当然参数也可以在前端或者业务层处理好再放进SQL)
test整体用单引号,里面的判断条件双引号;
<!--List<Anime> selectAnimesByConditionUserChooseWhenOtherwise(@Param("cid") String cid); -->
<select id="selectAnimesByConditionUserChooseWhenOtherwise" resultType="com.kgc.mybatis.bean.Anime">
select `id`,
`cid`,
`name`,
`author`,
`actor`,
`produce`,
`create_date`
from `animes`
<where>
<choose>
<!-- test整体使用单引号,判断条件使用双引号 -->
<when test='cid != null and cid == "1"'>
and cid = 1
</when>
<when test='cid != null and cid == "2"'>
and cid = 2
</when>
<otherwise>
and cid = 3
</otherwise>
</choose>
</where>
</select>
List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");
执行SQL:
-- SQL正常
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
-- 可以以查到正确数据
Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)
test整体用双引号,里面的判断条件单引号;
...
<choose>
<!-- test整体使用双引号,判断条件使用单引号 -->
<when test="cid != null and cid == '1'">
and cid = 1
</when>
<when test="cid != null and cid == '2'">
and cid = 2
</when>
<otherwise>
and cid = 3
</otherwise>
</choose>
...
List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");
执行SQL:
-- SQL没有报错,但是 cid == 2 的条件没有成立,而是走了默认参数 cid = 3
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 3
-- 可以查到数据,但是数据不正确,是cid=3的数据
Anime(id=301, cid=3, name=完美世界, author=辰东, actor=石昊, produce=玄机科技, createDate=Tue Apr 05 00:00:00 CST 2022)
test整体用双引号,里面的判断条件单引号并且判断条件加了toString();
...
<choose>
<!-- test整体使用双引号,判断条件使用单引号 -->
<when test="cid != null and cid == '1'.toString()">
and cid = 1
</when>
<when test="cid != null and cid == '2'.toString()">
and cid = 2
</when>
<otherwise>
and cid = 3
</otherwise>
</choose>
...
List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");
执行SQL:
-- SQL正常
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
-- 可以以查到正确数据
Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)
总结:
...
<choose>
<when test='cid != null and cid == "1"'>
and cid = 1
</when>
<when test='cid != null and cid == "2"'>
and cid = 2
</when>
<otherwise>
and cid = 3
</otherwise>
</choose>
...
List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");
执行SQL:
-- SQL正常
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
-- 可以查到正确数据
Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)
...
<choose>
<when test='cid != null and cid eq "1"'>
and cid = 1
</when>
<when test='cid != null and cid eq "2"'>
and cid = 2
</when>
<otherwise>
and cid = 3
</otherwise>
</choose>
...
List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");
执行SQL:
-- SQL正常
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
-- 可以查到正确数据
Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)
...
<choose>
<when test='cid != null and cid eq "1"'>
and cid = 1
</when>
<when test='cid != null and cid eq "2"'>
and cid = 2
</when>
<otherwise>
and cid = 3
</otherwise>
</choose>
...
List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");
执行SQL:
-- SQL正常
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
-- 可以查到正确数据
Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)
...
<choose>
<when test='cid != null and cid eq "1".toString()'>
and cid = 1
</when>
<when test='cid != null and cid eq "2".toString()'>
and cid = 2
</when>
<otherwise>
and cid = 3
</otherwise>
</choose>
...
List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");
执行SQL:
-- SQL正常
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
-- 可以查到正确数据
Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)
...
<choose>
<when test='cid != null and cid eq "A"'>
and cid = 1
</when>
<when test='cid != null and cid eq "B"'>
and cid = 2
</when>
<otherwise>
and cid = 3
</otherwise>
</choose>
...
List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("B");
执行SQL:
-- SQL正常
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
-- 可以查到正确数据
Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)
...
<choose>
<when test='cid != null and cid eq "A".toString()'>
and cid = 1
</when>
<when test='cid != null and cid eq "B".toString()'>
and cid = 2
</when>
<otherwise>
and cid = 3
</otherwise>
</choose>
...
List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("B");
执行SQL:
-- SQL正常
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
-- 可以查到正确数据
Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)
<choose>
<when test='cid != null and cid eq "11"'>
and cid = 1
</when>
<when test='cid != null and cid eq "22"'>
and cid = 2
</when>
<otherwise>
and cid = 3
</otherwise>
</choose>
List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("22");
执行SQL:
-- SQL正常
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
-- 可以查到正确数据
Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)
...
<choose>
<when test='cid != null and cid eq "11"toString()'>
and cid = 1
</when>
<when test='cid != null and cid eq "22"toString()'>
and cid = 2
</when>
<otherwise>
and cid = 3
</otherwise>
</choose>
...
List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("22");
执行SQL:
-- SQL正常
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
-- 可以查到正确数据
Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)
...
<choose>
<when test='cid != null and cid eq "AA"'>
and cid = 1
</when>
<when test='cid != null and cid eq "BB"'>
and cid = 2
</when>
<otherwise>
and cid = 3
</otherwise>
</choose>
...
List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("BB");
执行SQL:
-- SQL正常
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
-- 可以查到正确数据
Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)
...
<choose>
<when test='cid != null and cid eq "AA".toString()'>
and cid = 1
</when>
<when test='cid != null and cid eq "BB".toString()'>
and cid = 2
</when>
<otherwise>
and cid = 3
</otherwise>
</choose>
...
List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("BB");
执行SQL:
-- SQL正常
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
-- 可以查到正确数据
Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)
...
<choose>
<when test='cid != null and cid eq "任意字符1"'>
and cid = 1
</when>
<when test='cid != null and cid eq "任意字符2"'>
and cid = 2
</when>
<otherwise>
and cid = 3
</otherwise>
</choose>
...
List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("任意字符2");
执行SQL:
-- SQL正常
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
-- 可以查到正确数据
Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)
...
<choose>
<when test='cid != null and cid eq "任意字符1".toString()'>
and cid = 1
</when>
<when test='cid != null and cid eq "任意字符2".toString()'>
and cid = 2
</when>
<otherwise>
and cid = 3
</otherwise>
</choose>
...
List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("任意字符2");
执行SQL:
-- SQL正常
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
-- 可以查到正确数据
Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)
只需要将test整体用单引号,里面的判断条件双引号,就可以,加不加.toString(),并不影响;
根据id集合查询动漫集合;
使用 in;
<!--List<Anime> selectAnimesByConditionUserForeach(@Param("ids") List<Integer> ids);-->
<select id="selectAnimesByConditionUserForeach" resultType="com.kgc.mybatis.bean.Anime">
select `id`,
`cid`,
`name`,
`author`,
`actor`,
`produce`,
`create_date`
from `animes`
<where>
<foreach collection="ids" item="id" open="id in(" close=" )" separator=", ">
#{id}
</foreach>
</where>
</select>
执行SQL:
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE id in( ? , ? , ? )
使用 in;
<!-- List<Anime> selectAnimesByConditionUserForeach( List<Integer> ids); -->
<select id="selectAnimesByConditionUserForeach" resultType="com.kgc.mybatis.bean.Anime">
select `id`,
`cid`,
`name`,
`author`,
`actor`,
`produce`,
`create_date`
from `animes`
<where>
<foreach collection="list" item="id" open="id in(" close=" )" separator=", ">
#{id}
</foreach>
</where>
</select>
执行SQL:
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE id in( ? , ? , ? )
不用where标签;
使用 in;
<!--List<Anime> selectAnimesByConditionUserForeach(@Param("ids") List<Integer> ids);-->
<select id="selectAnimesByConditionUserForeach" resultType="com.kgc.mybatis.bean.Anime">
select `id`,
`cid`,
`name`,
`author`,
`actor`,
`produce`,
`create_date`
from `animes`
<foreach collection="ids" item="id" open=" where id in(" close=" )" separator=", ">
#{id}
</foreach>
</select>
执行SQL:
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` where id in( ? , ? , ? )
不用where标签;
使用 in;
通过7.1.3和7.1.4 可以总结,trim 和 foreach 都有前缀,后缀和分隔符,可以根据情况进项选择使用;
<!--List<Anime> selectAnimesByConditionUserForeach(@Param("ids") List<Integer> ids);-->
<select id="selectAnimesByConditionUserForeach" resultType="com.kgc.mybatis.bean.Anime">
select `id`,
`cid`,
`name`,
`author`,
`actor`,
`produce`,
`create_date`
from `animes`
<trim prefix=" where id in ">
<foreach collection="ids" item="id" open=" (" close=" )" separator=", ">
#{id}
</foreach>
</trim>
</select>
执行SQL:
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` where id in ( ? , ? , ? )
不用where标签;
使用 or;
<!--List<Anime> selectAnimesByConditionUserForeach(@Param("ids") List<Integer> ids);-->
<select id="selectAnimesByConditionUserForeach" resultType="com.kgc.mybatis.bean.Anime">
select `id`,
`cid`,
`name`,
`author`,
`actor`,
`produce`,
`create_date`
from `animes`
<foreach collection="ids" item="id" open=" where " close=" " separator=" or ">
id = #{id}
</foreach>
</select>
执行SQL:
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` where id = ? or id = ? or id = ?
@Test
public void testMybatisMapperDynamicSQlUserForeach() throws IOException {
SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
//获取mapper接口的代理实现类对象
AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
//执行动态SQL,查询动漫列表
List<Anime> animeList = animeMapper.selectAnimesByConditionUserForeach(Arrays.asList(101,102,103));
animeList.forEach(System.out::println);
}
<!-- int insertAnimeByConditionIfTrim(Anime animeForm); -->
<insert id="insertAnimeByConditionIfTrim">
<trim prefix="insert into `animes` ( " suffix=")" suffixOverrides=",">
<if test="cid != null"> `cid`,</if>
<if test="name != null"> `name`,</if>
<if test="author != null"> `author`,</if>
<if test="actor != null"> `actor`,</if>
<if test="produce != null"> `produce`,</if>
<if test="createDate != null"> `create_date`,</if>
</trim>
<trim prefix="values ( " suffix=")" suffixOverrides=",">
<if test="cid != null"> #{cid},</if>
<if test="name != null"> #{name},</if>
<if test="author != null"> #{author},</if>
<if test="actor != null"> #{actor},</if>
<if test="produce != null"> #{produce},</if>
<if test="createDate != null"> #{createDate},</if>
</trim>
</insert>
执行SQL:
insert into `animes` ( `cid`, `name`, `author`, `actor`, `produce` ) values ( ?, ?, ?, ?, ? )
@Test
public void testMybatisMapperDynamicSQlIfTrimInsert() throws IOException {
SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
//获取mapper接口的代理实现类对象
AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
Anime animeForm = new Anime();
animeForm.setCid(1);
animeForm.setName("知否知否");
animeForm.setAuthor("关心则乱");
animeForm.setActor("明兰");
animeForm.setProduce("优酷");
//指定if+ trim 冬天SQL,新增动漫
int row = animeMapper.insertAnimeByConditionIfTrim(animeForm);
System.out.println(row);
}
使用注释添加动漫;
@Select("select `id`,`cid`,`name`,`author`,`actor`,`produce`,`create_date` from `animes` where id = #{id} ")
Anime selectAnimesByConditionUserAnnotationSelect(Integer id);
执行SQL:
Preparing: select `id`,`cid`,`name`,`author`,`actor`,`produce`,`create_date` from `animes` where id = ?
@Test
public void testAnimesByConditionUserAnnotationSelect() throws IOException {
SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
//获取mapper接口的代理实现类对象
AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
Anime anime = animeMapper.selectAnimesByConditionUserAnnotationSelect(653);
System.out.println(anime);
}
@Delete({"<script>",
"delete from `animes`",
"<foreach collection='ids' item='id' open=' where id in ( ' close= ' ) ' separator= ' , '> " ,
" #{id} ",
"</foreach>" ,
"</script>"})
int deleteAnimesByConditionUserAnnotationDelete(@Param("ids") List<Integer> ids);
执行SQL:
Preparing: delete from `animes` where id in ( ? , ? , ? )
@Test
public void testDeleteAnimesByConditionUserAnnotationDelete() throws IOException {
SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
//获取mapper接口的代理实现类对象
AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
int row = animeMapper.deleteAnimesByConditionUserAnnotationDelete(Arrays.asList(649, 651, 652));
System.out.println(row);
}
<!-- int insertCategoryBatchUserFoeEach(List<Category> categoryList) -->
<!-- int insertCategoryBatchUserFoeEach(@Param("categoryList") List<Category> categoryList); -->
<insert id="insertCategoryBatchUserFoeEach" >
insert into `category` (`name`) values
<!--
默认参数:collection(不自定义参数名的时候可以使用Available parameters are [collection, list])
自定义参数:categoryList
-->
<foreach collection="collection" item="category" separator=", ">
(#{category.name})
</foreach>
</insert>
执行SQL:
Preparing: insert into `category` (`name`) values (?) , (?) , (?)
@Test
public void testInsertCategoryBatchUserFoeEach() throws IOException {
SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
//获取mapper接口的代理实现类对象
AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
Category category1 = new Category();
Category category2 = new Category();
Category category3 = new Category();
category1.setName("aaa");
category2.setName("bbb");
category3.setName("ccc");
List<Category> categoryList = new ArrayList<>();
categoryList.add(category1);
categoryList.add(category2);
categoryList.add(category3);
int row = animeMapper.insertCategoryBatchUserFoeEach(categoryList);
System.out.println(row);
}
参考博客:mybatis动态sql排序无效问题
在使用MyBatis解析xml进行排序的时候,遇见排序无效的问题!
如:order by #{user_id},如果传入的值是111,那么解析成sql时的值为order by “111”, 如果传入的值是id,则解析成的sql为order by “id”。多加了双引号;
如果传入的值是111,那么解析成sql时的值为order by 111, 如果传入的值是id,则解析成的sql为order by id。参数正常;
通过Mybatis,自动映射出来的时间,获取出来后时间格式不正确:
显示的格式为: Sat Dec 10 00:00:00 CST 1983
想要显示的格式: 1983-12-10
因为MyBatis映射的格式就有问题,所以可以在get方法中将时间格式化;
public String getBirthday() {
return new SimpleDateFormat("yyyy-MM-dd").format(this.birthday);
}
通过格式化标签库的 <fmt:formatDate value="" pattern = "" > 进行格式化
<fmt:formatDate value='${userInfo.birthday}' pattern='yyyy-MM-dd'/>