首页 > 代码库 > MyBatis动态SQL

MyBatis动态SQL

1,<!-- if+where查询 -->

<select id="queryLikeByStuName2" parameterType="map" resultMap="studentMap">
  select * from student 
  <where>
    <if test="studentNo != null and studentNo != ‘‘" >
      and StudentNo = #{studentNo}    
    </if>
    <if test="studentName != null and studentName != ‘‘" >
      and StudentName like CONCAT(‘%‘,#{studentName},‘%‘)
    </if>
  </where>
</select>

===>注意:传参使用 Map, like 查询使用 CONCAT(‘%‘,#{studentName},‘%‘)

2,<!-- trim代替where实现多条件查询 -->

<select id="queryByTrim" parameterType="map" resultMap="studentMap">
  select * from student
  <trim prefix="where" prefixOverrides="and | or">
    <if test="studentName != null and studentName != ‘‘">and StudentName like CONCAT("%",#{studentName},"%" )</if>
    <if test="phone !=null and phone !=‘‘">and Phone=#{phone}</if>
    </trim>
</select>
==>注意:每一个if内语句前都要加 and 
<if test="studentName != null and studentName != ‘‘">and StudentName like CONCAT("%",#{studentName},"%" )</if>
  ==>使用 bind元素 <bind name="pattern" value=" ‘%‘ + studentName + ‘%‘ " />
<if test="studentName != null and studentName != ‘‘">and StudentName like #{pattern} </if>

3,<!-- if+set动态修改 -->

<update id="updateStudent" parameterType="Student">
  update student 
<set>
  <if test="loginPwd != null and loginPwd != ‘‘">LoginPwd=#{loginPwd},</if>
  <if test="studentName != null and studentName != ‘‘">StudentName=#{studentName},</if>
  <if test="sex != null and sex != ‘‘">Sex=#{sex},</if>
  <if test="gradeId !=null and gradeId != ‘‘">GradeId=#{gradeId},</if>
  <if test="phone !=null and phone !=‘‘">Phone=#{phone},</if>
</set>
  where StudentNo = #{studentNo}
</update>

注意:每一个if语句后面加 ",",传的是要修改的对象

4,<!-- 使用trim代替set来实现动态修改 -->

<update id="updateByTrim" parameterType="Student">
  update student
  <trim prefix="set" suffixOverrides=",">
    <if test="loginPwd != null and loginPwd != ‘‘">LoginPwd=#{loginPwd},</if>
    <if test="studentName != null and studentName != ‘‘">StudentName=#{studentName},</if>
    <if test="sex != null and sex != ‘‘">Sex=#{sex},</if>
    <if test="gradeId !=null and gradeId != ‘‘">GradeId=#{gradeId},</if>
    <if test="phone !=null and phone !=‘‘">Phone=#{phone},</if>
  </trim>
  where StudentNo = #{studentNo}
</update>

注意:是 前缀,后 覆盖

5, 用List形式foreach遍历数据来实现动态查询
接口方法:List<Student> queryByIn(List<Integer> list);
配置文件:

<select id="queryByIn" parameterType="java.util.List" resultMap="studentMap">
  select * from student where StudentNo in
  <foreach collection="list" item="list" open="(" separator="," close=")">
    #{list}
  </foreach>
</select>

测试方法:

@Test
public void queryByIn(){
  List<Integer> list = new ArrayList<Integer>();
  list.add(1001);
  list.add(1003);
  list.add(1005);
  List<Student> stuList = studentDao.queryByIn(list);
  for (Student stu : stuList) {
    System.out.println(stu);
  }
}

6,用array形式来遍历数据实现动态查询
接口方法:List<Student> queryByIn2(Integer[] array);
配置文件:

<select id="queryByIn2" parameterType="java.lang.Integer" resultMap="studentMap">
  select * from student where StudentNo in
  <foreach collection="array" item="array" open="(" separator="," close=")">
    #{array}
  </foreach>
</select>

测试方法:

@Test
public void queryByIn2(){
  Integer[] arrays = {1001,1003,1005};
  List<Student> stuList = studentDao.queryByIn2(arrays);
  for (Student stu : stuList) {
    System.out.println(stu);
  }
}

注意:parameterType="java.lang.Integer" 传的是数组值的基本数据类型

7,用map-key形式来遍历数据实现动态查询

Map<String,Integer> map = new HashMap<String,Integer>();
map.put("pageNo", pageNo);
map.put("pageSize", pageSize);
List<Student> list = studentDao.listStudentByPage(map);

<select id="listStudentByPage" parameterType="map" resultMap="studentMap">
  SELECT * FROM student WHERE id LIMIT #{pageNo},#{pageSize}
</select>

8,分页查询
同上
9,使用choose来实现动态查询

<select id="listStudentByChoose" parameterType="Student" resultMap="studentMap">
  SELECT * FROM student WHERE city = ‘北京‘
  <choose>
    <when test="stuName != null">
      and name like CONCAT(‘%‘,#{stuName},‘%‘)
    </when>
    <when test="stuAge != null">
      and age = #{stuAge}
    </when>
    <otherwise>
      and gradeId = 1703
    </otherwise>
  </choose>
</select>

MyBatis动态SQL