MyBatis动态SQL
一.为了提高SQL语句的灵活性,我们选用动态SQL
1.查询
<select id="findAll" resultMap="UserMap"> SELECT id,userName,password FROM USER <where> <if test="userName!=null and userName!=''"> AND userName like concat('%',#{userName},'%') </if> <if test="password!=null and password!=''"> and password=#{password} </if> </where> </select>
相信小伙伴们一眼就看了出来,上面这条动态SQL语句就是把原来的where判断条件换成了
<where></where>,其中test就是判断条件,用来约束判断条件的。如果条件不满足,这条语句压根就不会
执行,以此类推。
2.修改
<update id="update" parameterType="User"> update user <trim prefix="set" suffixOverrides=","> <if test="userName!=null and userName!=''"> userName=#{userName}, </if> <if test="password!=null and password!=''"> password=#{password}, </if> </trim> <where> id=#{id} </where> </update>
trim 的作用就是清除多余的成分啦,prefix="set" 就是标记,标记以这个点开始进行操作,
prefixOverrides=“ ” 作用是清除标记之前的多余代码,suffixOverrides=“ ” 作用是清除标记之后的代码
3.按条件查询
<select id="choose" resultMap="UserMap"> <include refid="selectAllCloumn"/> <where> <choose> <when test="userName!=null and userName!=''"> AND userName like concat('%',#{userName},'%') </when> <when test="password!=null and password!=''"> and password=#{password} </when> <otherwise> id=#{id} </otherwise> </choose> </where> </select>
choose 的作用类似于 switch 满足第一个when后面的条件都不会执行,而otherwise相当于default
如果when都不满足后,就会执行otherwise
4.foreach 遍历输出
<!--foreach遍历输出--> <select id="selectArray" resultMap="UserMap"> <include refid="selectAllCloumn"/> <where> <if test="array.length>0"> id IN <foreach collection="array" open="(" close=")" separator="," item="myId"> #{myId} </foreach> </if> </where> </select>
test 中的 array 只能填 array,因为输出的是数组,所以它的类型是 array 。
如果这里换成 a 代替的话就会报以下错误
open :表示以“( ” 开头,
close :表示以“ ) ” 结束,
separator :表示以“ ,”作为分隔符来遍历,
item :作为foreach的变量名。
5.遍历循环list集合
<select id="selectList" resultMap="UserMap"> <include refid="selectAllCloumn"/> <where> <if test="list.size>0"> id in <foreach collection="list" open="(" close=")" separator="," item="mylist"> #{mylist} </foreach> </if> </where> </select>
6.用foreach添加
<insert id="addMyUser"> INSERT INTO user (userName,password)VALUES <foreach collection="list" separator="," item="adduser"> (#{adduser.userName},#{adduser.password}) </foreach> </insert>
7.遍历输出Map集合(以key输出)
List<User> selectByMapKey(@Param("myMap") Map<String,Integer> map);
<select id="selectByMapKey" resultMap="UserMap"> <include refid="selectAllCloumn"/> <where> <if test="myMap.keys.size>0"> id in <foreach collection="myMap.keys" item="myKey" open="(" separator="," close=")"> #{myKey} </foreach> </if> </where> </select>
7.遍历输出Map集合(以value输出)
<select id="selectByMapValue" resultMap="UserMap"> <include refid="selectAllCloumn"/> <where> <if test="myMap.keys.size>0"> id in <foreach collection="myMap.keys" item="myKey" open="(" separator="," close=")"> #{myMap[${myKey}]} </foreach> </if> </where> </select>
7.遍历输出Map集合(以对象输出)
<select id="selectByMaps" resultMap="UserMap"> <include refid="selectAllCloumn"/> <where> <if test="myMap.keys.size>0"> id in <foreach collection="myMap.keys" item="myKey" open="(" separator="," close=")"> #{myMap[${myKey}].id} </foreach> </if> </where> </select>