mybatis06--动态sql
1.if标签
public interface StudentDao { /** *动态sql的查询 参数是Student对象 不确定 用户输入几个属性值 */ List<Student> selectStudentsByIf(Student student); }
xml文件中的内容
<?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="cn.bdqn.dao.StudentDao"> <!-- 需要注意的事项： 01. 在xml文件中 特殊字符的使用 &&必须换成 and或者 & < < > > <= <= >= >= ' ' " " 02.因为不确定用户输入的到底是哪个参数 所以 where 之后必须加上 1=1 而且 每个条件之前加上 and --> <select id="selectStudentsByIf" resultType="Student"> select id,name,age from student where 1=1 <if test="name!=null & name!=''"> and name like '%' #{name} '%' </if> <if test="age>0"> and age > #{age} </if> </select> </mapper>
测试类
public class StudentTest { StudentDao dao; SqlSession session; @Before public void before() { // 因为需要关闭session 需要把session提取出去 session = SessionUtil.getSession(); dao = session.getMapper(StudentDao.class); } @After public void after() { if (session != null) { session.close(); } } // 01.动态查询 @Test public void test1() { Student stu=new Student(); //01.属性都不赋值 会查询所有 //02.只给年龄赋值stu.setAge(10); //03.只给姓名赋值stu.setName("小"); //04.同时给两个属性都赋值 stu.setAge(10); stu.setName("小"); List<Student> list = dao.selectStudentsByIf(stu); for (Student student : list) { System.out.println(student); } } }
2.where标签
上面的代码有点问题，就是在xml文件中的sql语句有where 1=1，如果查询条件多的话，性能是很低的，因为每次查询都需要判断一次！这时候 我们就需要使用 where 标签来代替！
public interface StudentDao { List<Student> selectStudentsByWhere(Student student); }
xml文件的配置 省略了 where 1=1
<?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="cn.bdqn.dao.StudentDao"> <select id="selectStudentsByWhere" resultType="Student"> select id,name,age from student <where> <!-- and 必须要加上mybatis只会减 不会加 --> <if test="name!=null & name!=''"> and name like '%' #{name} '%' </if> <if test="age>0"> and age > #{age} </if> </where> </select> </mapper>
测试类中新增
// 02.动态查询 where @Test public void test2() { Student stu=new Student(); //01.属性都不赋值 会查询所有 //02.只给年龄赋值stu.setAge(10); //03.只给姓名赋值stu.setName("小"); //04.同时给两个属性都赋值 stu.setAge(10); stu.setName("小"); List<Student> list = dao.selectStudentsByWhere(stu); for (Student student : list) { System.out.println(student); } }
运行即可得到相同的结果！
2.choose标签
比如说当姓名不为空的时候，按照姓名来查询，年龄不为空的时候按照年龄来查询！如果都为空则返回空！
public interface StudentDao { /** *动态sql的查询 参数是Student对象 */ List<Student> selectStudentsByChoose(Student student); }
xml文件中配置
<?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="cn.bdqn.dao.StudentDao"> <!-- 姓名不空 按照姓名查询 年龄不为空 按照年龄查询 只要满足一个when 则其他的when则不会执行！ 如果都不满足，则会执行otherwise 也就是没有查询结果 --> <select id="selectStudentsByChoose" resultType="Student"> select id,name,age from student <where> <choose> <when test="name!=null and name!=''"> and name like '%' #{name} '%' </when> <when test="age>0"> and age > #{age} </when> <otherwise> 1!=1 </otherwise> </choose> </where> </select> </mapper>
测试类代码
// 03.动态查询 choose @Test public void test3() { Student stu=new Student(); stu.setName("小"); //name 不会空 则会按照name来查询 其他的条件无效 stu.setAge(10); //如果都没有赋值 则没有返回结果 List<Student> list = dao.selectStudentsByChoose(stu); for (Student student : list) { System.out.println(student); } }
4.foreach标签 遍历数组
public interface StudentDao { List<Student> selectStudentsByForeach(int [] ids); }
xml文件中的配置
<?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="cn.bdqn.dao.StudentDao"> <select id="selectStudentsByForeach" resultType="Student"> <!-- 这就不是动态查询了 而是把参数写成固定的了 select id,name,age from student where id in(1,13,15) --> select id,name,age from student <if test="array.length>0"><!-- 看传递来的数组长度是否大于0,如果数组长度为0 则是查询所有信息--> where id in <foreach collection="array" item="myId" open="(" separator="," close=")"> #{myId} </foreach> </if> </select> </mapper>
测试代码
// 04.动态查询 foreach 遍历数组 @Test public void test4() { int [] ids={1,13,15}; List<Student> list = dao.selectStudentsByForeach(ids); for (Student student : list) { System.out.println(student); } }
4.foreach标签 遍历list集合
public interface StudentDao { List<Student> selectStudentsByForeachArray(List<Integer> ids); }
xml文件中的配置
<?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="cn.bdqn.dao.StudentDao"> <select id="selectStudentsByForeachArray" resultType="Student"> select id,name,age from student <if test="list.size>0"><!-- 看传递来的数组长度是否大于0,如果数组长度为0 则是查询所有信息--> where id in <foreach collection="list" item="myId" open="(" separator="," close=")"> #{myId} </foreach> </if> </select> </mapper>
测试代码
// 05.动态查询 foreach 遍历list集合 @Test public void test5() { List<Integer> ids=new ArrayList<Integer>(); ids.add(1); ids.add(13); ids.add(14); List<Student> list = dao.selectStudentsByForeachArray(ids); for (Student student : list) { System.out.println(student); } }
4.foreach标签 遍历自定义类型集合
public interface StudentDao { List<Student> selectStudentsByForeachStudent(List<Student> stus); }
xml文件中的配置
<?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="cn.bdqn.dao.StudentDao"> <!-- 遍历自定义类型的集合 --> <select id="selectStudentsByForeachStudent" resultType="Student"> select id,name,age from student <if test="list.size>0"><!-- 看传递来的数组长度是否大于0,如果数组长度为0 则是查询所有信息--> where id in <foreach collection="list" item="stu" open="(" separator="," close=")"> #{stu.id} </foreach> </if> </select> </mapper>
测试代码
// 06.动态查询 foreach 遍历自定义集合 @Test public void test6() { Student stu1 = new Student(); stu1.setId(1); Student stu2 = new Student(); stu2.setId(13); Student stu3 = new Student(); stu3.setId(15); List<Student> stus=new ArrayList<Student>(); stus.add(stu1); stus.add(stu2); stus.add(stu3); List<Student> list = dao.selectStudentsByForeachStudent(stus); for (Student student : list) { System.out.println(student); } }
5.sql片段
如果一个xml文件中的sql语句有很多相同的地方，则可以使用sql片段来替换！如：
public interface StudentDao { List<Student> selectStudentsBySql(List<Student> stus); }
xml文件中的配置
<?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="cn.bdqn.dao.StudentDao"> <!-- sql片段的使用 --> <select id="selectStudentsBySql" resultType="Student"> <include refid="selectStudent"/><!-- 引入sql片段 --> <if test="list.size>0"> where id in <foreach collection="list" item="stu" open="(" separator="," close=")"> #{stu.id} </foreach> </if> </select> <!-- 如果有需求不查询age了，之前需要在所有的查询中删除age字段，现在只需要在sql片段中删除即可！ --> <sql id="selectStudent"> select id,name,age from student </sql> </mapper>
测试代码
// 07.sql片段 @Test public void test7() { Student stu1 = new Student(); stu1.setId(1); Student stu2 = new Student(); stu2.setId(13); Student stu3 = new Student(); stu3.setId(15); List<Student> stus=new ArrayList<Student>(); stus.add(stu1); stus.add(stu2); stus.add(stu3); List<Student> list = dao.selectStudentsBySql(stus); for (Student student : list) { System.out.println(student); } }
