目录
动态 SQL的作用:可以很方便地根据不同条件拼接 SQL 语句
数据准备
首先创建如下数据库表及dao层接口:
package club.affengkuang.userinfo.dao;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import club.affengkuang.vo.UserInfo;
public interface IUserInfoDao {
List<UserInfo> select(@Param("userName")String userName,@Param("address")String address);
boolean delete(@Param("ids")String [] ids);
boolean update(@Param("id")String id,@Param("userName")String userName,@Param("address")String address);
}
一、if
假如我们要调用select方法,通过user_name、address字段查询表中数据,但是这两个参数都有可能传入null,这时我们需要在xml文件中判断传入的参数是否为null,如果不为null则将其拼接成SQL语句,在test属性中设置判断条件,用法如下:
<?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="club.affengkuang.userinfo.dao.IUserInfoDao">
<select id="select" resultType="club.affengkuang.vo.UserInfo">
select * from user_info where 1=1
<if test="userName!=null and userName!=''">
and user_name like #{userName}
</if>
<if test="address!=null and address!=''">
and address like #{address}
</if>
</select>
</mapper>
for(UserInfo userInfo:userInfoDao.select("%a%",null)) {
System.out.println(userInfo);
}
二、where
在上面的例子中where 1=1的作用是防止两个参数都为null时sql语句发生错误,但where元素就可以代替这个多余的语句:
where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“where”子句;而且,若语句的开头为“and”或“or”,where 元素也会将它们去除,所以不用担心拼接时会发生错误。
<?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="club.affengkuang.userinfo.dao.IUserInfoDao">
<select id="select" resultType="club.affengkuang.vo.UserInfo">
select * from user_info
<where>
<if test="userName!=null and userName!=''">
and user_name like #{userName}
</if>
<if test="address!=null and address!=''">
and address like #{address}
</if>
</where>
</select>
</mapper>
三、foreach
假如调用delete()方法删除多条数据时,传入的参数个数不确定,这时SQL语句的写法就要使用foreach元素:
foreach元素用于对一个集合进行遍历,构建 in 条件语句时常用该元素;
collection属性用来指定一个集合,也就是方法传入的String数组参数;
item属性指定索引变量,然后对集合进行遍历。
而且,open、close、separate分别可以指定开头与结尾的字符串以及在迭代结果之间放置分隔符。
<?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="club.affengkuang.userinfo.dao.IUserInfoDao">
<delete id="delete">
delete from user_info where id in
<foreach item="id" collection="ids" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
</mapper>
System.out.println(userInfoDao.delete(new String [] {"111","2"}));
四、set
假如调用update方法修改多个字段,这时便可以使用set元素,而且还可以删除结尾多余的逗号
<?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="club.affengkuang.userinfo.dao.IUserInfoDao">
<update id="update">
update user_info
<set>
<if test="id!=null and id!=''">
id = #{id},
</if>
<if test="address!=null and address!=''">
address = #{address},
</if>
</set>
<where>
user_name like #{userName}
</where>
</update>
</mapper>
System.out.println(userInfoDao.update("333","%Na%", null));
测试类
package club.affengkuang.test;
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import club.affengkuang.userinfo.dao.IUserInfoDao;
import club.affengkuang.vo.UserInfo;
public class Test {
public static void main(String[] args) {
try {
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
IUserInfoDao userInfoDao = sqlSession.getMapper(IUserInfoDao.class);
System.out.println(userInfoDao.delete(new String [] {"111","2"}));
for(UserInfo userInfo:userInfoDao.select("%a%", "%1%")) {
System.out.println(userInfo);
}
System.out.println(userInfoDao.update("333","%Na%", null));
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}