mybatis06–动态sql详解编程语言

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或者 amp;
02.因为不确定用户输入的到底是哪个参数 所以 where 之后必须加上 1=1 而且 每个条件之前加上 and select id="selectStudentsByIf" resultType="Student" select id,name,age from student where 1=1 if test="name!=null amp; 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 amp; 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.choose标签 遍历数组

 

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.choose标签 遍历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.choose标签 遍历自定义类型集合

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); 

 }

原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/12147.html

cjavaxml