目录
需求：
- Mybatis映射配置文件中，前面所用的SQL都是比较简单的
- 有时候业务逻辑复杂时，我们的SQL就是动态变化的，此时在前面学习的SQL就不能满足要求了
if标签的使用
- <where>:条件标签
- 如果有动态条件，则使用该标签代替where关键字
- <if>:条件判断标签
- <if test="条件判断">
- 查询条件拼接
- </if>
实例操作
//多条件查询 public abstract List<Student> selectCondition(Student stu);
<select id="selectCondition" resultType="student" parameterType="student"> SELECT * FROM student <where> <if test="sid != null"> sid = #{sid} </if> <if test="name != null"> AND name = #{name} </if> <if test="age != null"> AND age = #{age} </if> </where> </select>
@Test public void selectConditon() throws Exception{ //1.加载核心配置文件 InputStream is = Resources.getResourceAsStream("MybatisConfig.xml"); //2.获取SqlSession工厂对象 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is); //3.通过工厂对象获取SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(true); //4.获取StudentMapper接口的实现类对象 StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); Student stu = new Student(); stu.setSid(2); stu.setName("李四"); //stu.setAge(24); List<Student> list = mapper.selectCondition(stu); for(Student student : list){ System.out.println(student); } sqlSession.close(); is.close(); }
<foreach>:循环遍历标签
- 适用于多个参数或者的关系
实例操作
//根据多个id查询 public abstract List<Student> selectByIds(List<Integer> sids);
<select id="selectByIds" resultType="student" parameterType="list"> SELECT * FROM student <where> <foreach collection="list" open="sid IN (" close=")" item="sid" separator=","> #{sid} </foreach> </where> </select>
@Test public void selectByIds() throws Exception{ //1.加载核心配置文件 InputStream is = Resources.getResourceAsStream("MybatisConfig.xml"); //2.获取SqlSession工厂对象 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is); //3.通过工厂对象获取SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(true); //4.获取StudentMapper接口的实现类对象 StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Integer> sids = new ArrayList<>(); sids.add(1); sids.add(2); List<Student> list = mapper.selectByIds(sids); for(Student student : list){ System.out.println(student); } sqlSession.close(); is.close(); }
sql片段的抽取
- 我们可以将一些重复性的SQL语句进行抽取，以达到复用的效果
- <sql>:抽取SQL语句的标签
- <sql id="片段唯一标识">抽取的SQL语句</sql>
- <include>:引入SQL片段标签
- <include refid="片段唯一标识" />
<sql id="select">SELECT * FROM student</sql><select id="selectAll" resultType="student"> <include refid="select"/> </select> <select id="selectByIds" resultType="student" parameterType="list"> <include refid="select"/> <where> <foreach collection="list" open="sid IN (" close=")" item="sid" separator=","> #{sid} </foreach> </where> </select>