时间：2021-07-01 10:21:17 帮助过：7人阅读
1 public interface IStudentDao { 2 // 根据条件查询问题 3 List<Student> selectStudentsByCondition(Student student); 4 }com.jmu.dao.IStudentDao
1 <mapper namespace="com.jmu.dao.IStudentDao"> 2 <select id="selectStudentsByCondition" resultType="Student"> 3 select id,name,age,score 4 from student 5 where 6 <if test="name !=null and name !=‘‘"> 7 name like ‘%‘ #{name} ‘%‘ 8 </if> 9 <if test="age>0"> 10 and age >#{age} 11 </if> 12 </select> 13 </mapper>mapper.xml
输出：
0 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByCondition - ==> Preparing: select id,name,age,score from student where name like ‘%‘ ? ‘%‘ and age >? 48 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByCondition - ==> Parameters: 明(String), 20(Integer) 96 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByCondition - <== Total: 15 Student [id=159, name=明明, score=87.9, age=23] Student [id=173, name=小明明, score=99.5, age=23] Student [id=175, name=明明, score=99.5, age=23] Student [id=177, name=明明, score=99.5, age=23] Student [id=179, name=明明, score=99.5, age=23] Student [id=181, name=明明, score=99.5, age=23] Student [id=183, name=明明, score=99.5, age=23] Student [id=185, name=明明, score=99.5, age=23] Student [id=187, name=明明, score=99.5, age=23] Student [id=189, name=明明, score=99.5, age=23] Student [id=191, name=明明, score=99.5, age=23] Student [id=193, name=明明, score=99.5, age=23] Student [id=195, name=明明, score=99.5, age=23] Student [id=198, name=明明, score=99.5, age=23] Student [id=200, name=明明, score=99.5, age=23]output
（2） 针对第一个值为空，sql语句“where and”出错的情况
// Student stu = new Student("明", 20, 0);
Student stu = new Student("", 20, 0);
解决：
四、<where/>标签
当数据量特别大，做“where 1= 1”的判断，就降低了整个系统的执行效率
1 @Test 2 public void test02() { 3 // Student stu = new Student("明", 20, 0); 4 Student stu = new Student("", 20, 0); 5 List<Student> students = dao.selectStudentsByWhere(stu); 6 for (Student student : students) { 7 System.out.println(student); 8 } 9 10 }com.jmu.test.MyTest
import java.util.List; import java.util.Map; import com.jmu.bean.Student; public interface IStudentDao { // 根据条件查询问题 List<Student> selectStudentsByIf(Student student); List<Student> selectStudentsByWhere(Student student); }com.jmu.dao.IStudentDao
1 <select id="selectStudentsByWhere" resultType="Student"> 2 select id,name,age,score 3 from student 4 <where> 5 <if test="name !=null and name !=‘‘"> 6 and name like ‘%‘ #{name} ‘%‘ 7 </if> 8 <if test="age>0"> 9 and age >#{age} 10 </if> 11 </where> 12 </select>/mybatis7-dynamicSql/src/com/jmu/dao/mapper.xml
输出：
0 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByIf - ==> Preparing: select id,name,age,score from student where 1=1 and age >? 48 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByIf - ==> Parameters: 20(Integer) 86 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByIf - <== Total: 2 Student [id=198, name=明明, score=99.5, age=23] Student [id=200, name=明明, score=99.5, age=23] 111 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByWhere - ==> Preparing: select id,name,age,score from student WHERE age >? 111 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByWhere - ==> Preparing: select id,name,age,score from student WHERE age >? 111 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByWhere - ==> Parameters: 20(Integer) 111 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByWhere - ==> Parameters: 20(Integer) 115 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByWhere - <== Total: 2 115 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByWhere - <== Total: 2 Student [id=198, name=明明, score=99.5, age=23] Student [id=200, name=明明, score=99.5, age=23]output
五、<choose/>标签
该标签可以包含多个<when/>和一个<otherwise/>,它们联合使用来完成JAVA的开关语句switch...case功能。
1 <select id="selectStudentsByChoose" resultType="Student"> 2 select id,name,age,score 3 from student 4 <where> 5 <choose> 6 <when test="name !=null and name !=‘‘"> 7 and name like ‘%‘ #{name} ‘%‘ 8 </when> 9 <when test="age>0"> 10 and age>#{age} 11 </when> 12 <otherwise> 13 1=2 <!-- false,使得查询不到结果 --> 14 </otherwise> 15 </choose> 16 </where> 17 </select>/mybatis7-dynamicSql/src/com/jmu/dao/mapper.xml
1 import java.util.List; 2 import java.util.Map; 3 4 import com.jmu.bean.Student; 5 6 public interface IStudentDao { 7 // 根据条件查询问题 8 List<Student> selectStudentsByIf(Student student); 9 List<Student> selectStudentsByWhere(Student student); 10 List<Student> selectStudentsByChoose(Student student); 11 12 }com.jmu.dao.IStudentDao
1 } 2 @Test 3 public void test03() { 4 // Student stu = new Student("明", 20, 0); 5 Student stu = new Student("", 20, 0); 6 // Student stu = new Student("", 0, 0); 7 List<Student> students = dao.selectStudentsByChoose(stu); 8 for (Student student : students) { 9 System.out.println(student); 10 } 11 12 }MyTest
输出：
0 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByIf - ==> Preparing: select id,name,age,score from student where 1=1 and age >? 50 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByIf - ==> Parameters: 20(Integer) 86 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByIf - <== Total: 2 Student [id=198, name=明明, score=99.5, age=23] Student [id=200, name=明明, score=99.5, age=23] 104 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByWhere - ==> Preparing: select id,name,age,score from student WHERE age >? 104 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByWhere - ==> Preparing: select id,name,age,score from student WHERE age >? 105 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByWhere - ==> Parameters: 20(Integer) 105 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByWhere - ==> Parameters: 20(Integer) 109 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByWhere - <== Total: 2 109 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByWhere - <== Total: 2 Student [id=198, name=明明, score=99.5, age=23] Student [id=200, name=明明, score=99.5, age=23] 121 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByChoose - ==> Preparing: select id,name,age,score from student WHERE age>? 121 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByChoose - ==> Preparing: select id,name,age,score from student WHERE age>? 121 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByChoose - ==> Preparing: select id,name,age,score from student WHERE age>? 122 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByChoose - ==> Parameters: 20(Integer) 122 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByChoose - ==> Parameters: 20(Integer) 122 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByChoose - ==> Parameters: 20(Integer) 124 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByChoose - <== Total: 2 124 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByChoose - <== Total: 2 124 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByChoose - <== Total: 2 Student [id=198, name=明明, score=99.5, age=23] Student [id=200, name=明明, score=99.5, age=23]output
当查询的条件均不满足时：
Student stu = new Student("", 0, 0);
输出：
六、<foreach/>标签--遍历数组
该标签用于实现对数组与集合的遍历，对其使用需要注意：
1 List<Student> selectStudentsByForeach(int[] ids);com.jmu.dao.IStudentDao
1 @Test 2 public void test04() { 3 int[] ids={197,198,199}; 4 List<Student> students = dao.selectStudentsByForeach(ids); 5 for (Student student : students) { 6 System.out.println(student); 7 } 8 9 }MyTest
1 <select id="selectStudentsByForeach" resultType="Student"> 2 <!-- select id,name,age,score from student where id in (1,3,5) --> 3 select id,name,age,score 4 from student 5 <if test="array.length>0"> 6 where id in 7 <foreach collection="array" item="myid" open="(" close=")" 8 separator=","> 9 #{myid} 10 </foreach> 11 </if> 12 </select>mapper.xml
输出：
176 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByForeach - ==> Preparing: select id,name,age,score from student where id in ( ? , ? , ? ) 176 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByForeach - ==> Preparing: select id,name,age,score from student where id in ( ? , ? , ? ) 176 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByForeach - ==> Preparing: select id,name,age,score from student where id in ( ? , ? , ? ) 176 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByForeach - ==> Preparing: select id,name,age,score from student where id in ( ? , ? , ? ) 177 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByForeach - ==> Parameters: 197(Integer), 198(Integer), 199(Integer) 177 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByForeach - ==> Parameters: 197(Integer), 198(Integer), 199(Integer) 177 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByForeach - ==> Parameters: 197(Integer), 198(Integer), 199(Integer) 177 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByForeach - ==> Parameters: 197(Integer), 198(Integer), 199(Integer) 179 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByForeach - <== Total: 3 179 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByForeach - <== Total: 3 179 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByForeach - <== Total: 3 179 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByForeach - <== Total: 3 Student [id=197, name=明明, score=87.9, age=19] Student [id=198, name=明明, score=99.5, age=23] Student [id=199, name=明明, score=87.9, age=19]output
七、<foreach/>标签--遍历泛型为基本类型的List
1 @Test 2 public void test05() { 3 List<Integer> ids =new ArrayList<>(); 4 ids.add(198); 5 ids.add(199); 6 List<Student> students = dao.selectStudentsByForeach2(ids); 7 for (Student student : students) { 8 System.out.println(student); 9 } 10 11 }MyTest
1 import java.util.List; 2 import com.jmu.bean.Student; 3 4 public interface IStudentDao { 5 // 根据条件查询问题 6 List<Student> selectStudentsByIf(Student student); 7 List<Student> selectStudentsByWhere(Student student); 8 List<Student> selectStudentsByChoose(Student student); 9 List<Student> selectStudentsByForeach(int[] ids); 10 List<Student> selectStudentsByForeach2(List<Integer> ids); 11 }com.jmu.dao.IStudentDao
1 <select id="selectStudentsByForeach2" resultType="Student"> 2 <!-- select id,name,age,score from student where id in (1,3,5) --> 3 select id,name,age,score 4 from student 5 <if test="list.size>0"> 6 where id in 7 <foreach collection="list" item="myid" open="(" close=")" 8 separator=","> 9 #{myid} 10 </foreach> 11 </if> 12 </select>/mybatis7-dynamicSql/src/com/jmu/dao/mapper.xml
输出：
0 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByForeach2 - ==> Preparing: select id,name,age,score from student where id in ( ? , ? ) 48 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByForeach2 - ==> Parameters: 198(Integer), 199(Integer) 78 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByForeach2 - <== Total: 2 Student [id=198, name=明明, score=99.5, age=23] Student [id=199, name=明明, score=87.9, age=19]output
八、<foreach/>标签--遍历泛型为自定义类型的List
1 import java.util.List; 2 import com.jmu.bean.Student; 3 4 public interface IStudentDao { 5 // 根据条件查询问题 6 List<Student> selectStudentsByIf(Student student); 7 List<Student> selectStudentsByWhere(Student student); 8 List<Student> selectStudentsByChoose(Student student); 9 List<Student> selectStudentsByForeach(int[] ids); 10 List<Student> selectStudentsByForeach2(List<Integer> ids); 11 List<Student> selectStudentsByForeach3(List<Student> ids); 12 }com.jmu.dao.IStudentDao
1 @Test 2 public void test06() { 3 Student stu1 = new Student(); 4 stu1.setId(198); 5 Student stu2 = new Student(); 6 stu2.setId(199); 7 List<Student> stus =new ArrayList<>(); 8 stus.add(stu1); 9 stus.add(stu2); 10 List<Student> students = dao.selectStudentsByForeach3(stus); 11 for (Student student : students) { 12 System.out.println(student); 13 } 14 15 }MyTest
1 <select id="selectStudentsByForeach3" resultType="Student"> 2 select id,name,age,score 3 from student 4 <if test="list.size>0"> 5 where id in 6 <foreach collection="list" item="stu" open="(" close=")" 7 separator=","> 8 #{stu.id} 9 </foreach> 10 </if> 11 </select>mapper.xml
输出：
1 0 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByForeach3 - ==> Preparing: select id,name,age,score from student where id in ( ? , ? ) 2 49 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByForeach3 - ==> Parameters: 198(Integer), 199(Integer) 3 90 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentsByForeach3 - <== Total: 2 4 Student [id=198, name=明明, score=99.5, age=23] 5 Student [id=199, name=明明, score=87.9, age=19]output
MyBatis_动态SQL
标签：from close eee 功能 dea 原因 标签 output integer