目录
概述
元素 | 作用 | 备注 |
if | 判断语句 | 单条件分支判断 |
choose(when,otherwise) | 相当于Java中的switch和case语句 | 多条件分支判断 |
trim(where,set) | 辅助元素,用于处理特定的SQL拼装问题,比如去掉多余的and,or等 | 用于处理SQL拼装的问题 |
foreach | 循环语句 | 在(select)in语句等列举条件常用 |
一.if元素
!!(要在接口方法的参数里@param("name"))
mapper.java
List<Dy> selectScoredayu60(@Param("name")String name);
<?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="dynamicSQL.DynamicSQLMapper">
<select id="selectScoredayu60" parameterType="string" resultType="dynamicSQL.Dy">
select * from dy where 1=1
<if test="name != null">
and name like concat('%',#{name},'%')
</if>
</select>
</mapper>
二.choose,when,otherwise
大于 >
小于<
Mapper.java
List<Dy> selectScores(@Param("score")Float score);
<select id="selectScores" parameterType="float" resultType="dynamicSQL.Dy">
select * from dy where 1=1
<choose>
<when test="score >60">and score>60</when>
<when test="score <60">and score<60</when>
<otherwise>and score=60</otherwise>
</choose>
</select>
三.trim,where,set
where省掉了where 1 = 1
<select id="selectScores" parameterType="float" resultType="dynamicSQL.Dy">
select * from dy
<where>
<choose>
<when test="score >60">and score>60</when>
<when test="score <60">and score<60</when>
<otherwise>and score=60</otherwise>
</choose>
</where>
</select>
trim标签省掉了where 和要忽略的关键字
<select id="selectScores" parameterType="float" resultType="dynamicSQL.Dy">
select * from dy
<trim prefix="where" prefixOverrides="and">
<choose>
<when test="score >60">and score>60</when>
<when test="score <60">and score<60</when>
<otherwise>and score=60</otherwise>
</choose>
</trim>
</select>
set用在update中
mapper.java
int updateScore(@Param("name")String name,@Param("score")Float score);
<update id="updateScore">
update dy
<set>
<if test="score>60">score=#{score},</if>
<if test="score<60">score=101</if>
</set>
where name=#{name}
</update>
main() 注意要提交session.commit();
public class TestDemo {
public static void main(String[] args) {
SqlSession session = DBhelper.getFactory().openSession(false);
DynamicSQLMapper dynamicSQLMapper = session.getMapper(DynamicSQLMapper.class);
int bool = dynamicSQLMapper.updateScore("强月城",(float) 50);
System.out.println(bool);
session.commit();
session.close();
}
}
四.foreach
mapper.java
List<Dy> select1819Stu(List<String> list);
<select id="select1819Stu" resultType="dynamicSQL.Dy">
select * from dy where SUBSTRING(id,1,4) in
<foreach collection="list" item="listid" index="index" open="(" separator="," close=")">
#{listid}
</foreach>
</select>
main
public class TestDemo {
public static void main(String[] args) {
SqlSession session = DBhelper.getFactory().openSession(false);
DynamicSQLMapper dynamicSQLMapper = session.getMapper(DynamicSQLMapper.class);
List<String> listj = new ArrayList<String>();
listj.add("2018");
listj.add("2019");
List<Dy> list = dynamicSQLMapper.select1819Stu(listj);
session.commit();
session.close();
for(Dy dy:list) {
System.out.println(dy);
}
}
}
补充 bind元素
bind元素用于通过OGNL表达式去自定义一个上下文变量,这样更方便使用。
- 在进行模糊查询时,MySQL数据库有concat,name like concat('%',#{name},'%')
- Oracle数据库则没有,所以使用bind元素即可实现
mapper.java
List<Dy> selectScoredayu60(@Param("name")String name);
<select id="selectScoredayu60" parameterType="string" resultType="dynamicSQL.Dy">
<bind name="pattern" value="'%' + name + '%'"/>
select * from dy where 1=1
<if test="name != null">
and name like #{pattern}
</if>
</select>