MyBatis_动态SQL

一、动态SQL

 动态SQL,主要用于解决查询条件不确定的情况:在程序运行期间,根据提交的询问条件进行查询。

 动态SQL,即透过MyBatis提供的各种标签对极作出判断为贯彻动态拼接SQL语句。

其次、使用动态SQL原因

 提供的询问条件不同,执行的SQL语句不同。若以每种可能的景象均逐一列有,就拿面世大量之SQL语句。

三、<if/>标签

注意事项:

 

(1)

 

1 @Test
2     public void test08() {
3         Student stu = new Student("明", 20, 0);
4         List<Student> students = dao.selectStudentsByCondition(stu);
5         for (Student student : students) {
6             System.out.println(student);
7         }
8 
9     }

com.jmu.test.MyTest

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/>标签–遍历数组

该标签用于落实对数组与聚集的遍历,对该采用要专注:

  • collection表示要遍历的聚众类型,这里是屡组,即array。
  • open、close、separate为遍历内容之SQL拼接。

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

九、SQL片段

网站地图xml地图