1.MyBatis的动态SQL是基于OGNL表达式的，它可以帮助我们方便的在SQL语句中实现某些逻辑。
2.MyBatis中用于实现动态SQL的元素主要有
- if
- choose（when，otherwise）
- trim
- where
- set
- foreach
可以看出MyBatis的动态SQL的标签元素和接近JSP中的JSTL语法，下面我就分别详细的介绍一下
3.动态SQL中if的用法
<?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="com.mybatis3.mapping.StudentMapper">
<resultMap type="Student" id="StudentResultMap">
<id column="id" property="id"/>
<result column="sname" property="sname"/>
</resultMap>
<select id="getStudentMultiple" resultMap="StudentResultMap" parameterType="Student">
select * from student where 1=1
<if test="id != 0">
and id = #{id}
</if>
<if test="sname != null">
and sname = #{sname}
</if>
</select>
</mapper>
4.动态SQL中choose用法
<?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="com.mybatis3.mapping.StudentMapper">
<resultMap type="Student" id="StudentResultMap">
<id column="id" property="id"/>
<result column="sname" property="sname"/>
</resultMap>
<select id="getStudentMultipleChoose" resultMap="StudentResultMap" parameterType="Student">
select * from student where 1=1
<choose>
<when test="id != 0">
and id = #{id}
</when>
<when test="sname != null">
and sname = #{sname}
</when>
</choose>
</select>
</mapper>
5.动态SQL中where语句的作用主要是简化SQL语句中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="com.mybatis3.mapping.StudentMapper">
<resultMap type="Student" id="StudentResultMap">
<id column="id" property="id"/>
<result column="sname" property="sname"/>
</resultMap>
<select id="getStudentMultipleWhere" resultMap="StudentResultMap" parameterType="Student">
select * from student
<where>
<if test="id != 0">
and id = #{id}
</if>
<if test="sname != null">
and sname = #{sname}
</if>
</where>
</select>
</mapper>
注意： where元素的作用是给SQL语句添加一个条件判断. 如果输出后是and 开头的，MyBatis会把第一个and忽略，当然如果是or开头的，MyBatis也会把它忽略；此外，在where元素中你不需要考虑空格的问题，MyBatis会智能的帮你加上
6.动态SQL中set元素主要是用在更新操作的时候，它的主要功能和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="com.mybatis3.mapping.StudentMapper">
<resultMap type="Student" id="StudentResultMap">
<id column="id" property="id"/>
<result column="sname" property="sname"/>
</resultMap>
<update id="updateStudentMultipleSet" parameterType="Student">
update student
<set>
<if test="sname != null">
sname = #{sname},
</if>
</set>
where id = #{id}
</update>
</mapper>
7.动态SQL中foreach
- 主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。
- foreach元素的属性主要有item，index，collection，open，separator，close
<?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="com.mybatis3.mapping.StudentMapper">
<resultMap type="Student" id="StudentResultMap">
<id column="id" property="id"/>
<result column="sname" property="sname"/>
</resultMap>
<select id="getStudentMultipleForeach" resultMap="StudentResultMap">
select * from student where id in
<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
#{item}
</foreach>
</select>
</mapper>
注意：在Java代码中如何使用MyBaits的动态SQL语句
import java.io.IOException;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
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 org.junit.Test;
import com.mybatis3.entity.Student;
public class Test02 {
private static SqlSessionFactory sqlSessionFactory;
private static Reader reader;
static {
try {
reader = Resources.getResourceAsReader("SqlMapConfig.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 动态SQL中foreach用法
*/
@Test
public void m05() {
SqlSession session = sqlSessionFactory.openSession();
String statement = "com.mybatis3.mapping.StudentMapper.getStudentMultipleForeach";
List<Integer> list = new ArrayList<>();
list.add(1);
list.add(2);
list.add(3);
List<Student> studentList = session.selectList(statement, list);
session.close();
System.out.println(studentList);
}
}
8.动态SQL中trim语句
<select id="dynamicTrimTest" parameterType="Blog" resultType="Blog">
select * from t_blog
<trim prefix="where" prefixOverrides="and |or">
<if test="title != null">
and title = #{title}
</if>
<if test="content != null">
and content = #{content}
</if>
<if test="owner != null">
or owner = #{owner}
</if>
</trim>
</select>
