3. MyBatis动态SQL操作

3.1、 IF语句

修改配置文件deptMapper.xml，添加

    <!-- 根据部门地址查询多个部门信息 -->
    <!-- 如果返回的是list，resultMap指定的值是list集合里元素的类型-->
    <select id="selectListUseIf" parameterType="Dept" resultMap="deptResultMap" >
         select dept_id,dept_name,dept_address from dept where 1=1
         <if test="deptId!=null">and dept_id = #{deptId}</if>
         <if test="deptName!=null">and dept_name = #{deptName}</if> 
         <if test="deptAddress!=null">and dept_address = #{deptAddress}</if> 
    </select>

修改DeptDaoImpl.java，添加selectListUseIf方法：

package com.demo.dao.imp;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import com.demo.entity.Dept;
import com.demo.util.MyBatisUtil;
public class DeptDaoImpl {
    SqlSession session;
    // 使用if动态生成sql
    public List<Dept> selectListUseIf(Dept dept) {
         List<Dept> depts = null;
         try {
             session = MyBatisUtil.getSession();
             depts = session.selectList(
                     "com.demo.entity.DeptMapper.selectListUseIf", dept);
         } catch (Exception e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
         } finally {
             try {
                 MyBatisUtil.closeSession();
             } catch (Exception e) {
                 // TODO Auto-generated catch block
                 e.printStackTrace();
             }
         }
         return depts;
    }
}

添加测试代码，TestDeptDaoImpl.java

package com.demo.test;
import java.util.List;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import com.demo.dao.imp.DeptDaoImpl;
import com.demo.entity.Dept;
public class TestDeptDaoImpl {
    private static DeptDaoImpl deptDaoImpl;
    @BeforeClass
    public static void setUpBeforeClass() throws Exception {
        deptDaoImpl = new DeptDaoImpl();
    }

    @AfterClass
    public static void tearDownAfterClass() throws Exception {
        deptDaoImpl = null;
    }

    @Test
    public void testSelectListUseIf() {
        Dept paramDept = new Dept();
        paramDept.setDeptId(1);
        paramDept.setDeptName("研发部一部");
        paramDept.setDeptAddress("北京");
        List<Dept> depts = deptDaoImpl.selectListUseIf(paramDept);
        for (Dept dept : depts) {
            System.out.println("部门信息："+dept);
        }
    }
}

3. 2、WHERE语句

修改配置文件deptMapper.xml，添加

    <!-- 动态Where条件 ,一般也需要与if结合使用，与纯if比较，省略了where 1=1-->
    <select id="selectListUseWhere" parameterType="Dept" resultMap="deptResultMap" >
         select dept_id,dept_name,dept_address from dept
         <where>
             <if test="deptId!=null">and dept_id = #{deptId}</if>
             <if test="deptName!=null">and dept_name = #{deptName}</if>    
             <if test="deptAddress!=null">and dept_address = #{deptAddress}</if> 
         </where>
    </select>

修改DeptDaoImpl.java，添加selectListUseWhere方法：

    // 使用where动态生成sql
    public List<Dept> selectListUseWhere(Dept dept) {
         List<Dept> depts = null;
         try {
             session = MyBatisUtil.getSession();
             depts = session.selectList(
                     "com.demo.entity.DeptMapper.selectListUseWhere", dept);
         } catch (Exception e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
         } finally {
             try {
                 MyBatisUtil.closeSession();
             } catch (Exception e) {
                 // TODO Auto-generated catch block
                 e.printStackTrace();
             }
         }
         return depts;
    }

添加测试代码，TestDeptDaoImpl.java

    @Test
    public void testSelectListUseWhere() {
         Dept paramDept = new Dept();
         paramDept.setDeptId(1);
         paramDept.setDeptName("研发部一部");
         paramDept.setDeptAddress("北京");
         List<Dept> depts = deptDaoImpl.selectListUseWhere(paramDept);
         for (Dept dept : depts) {
             System.out.println("部门信息："+dept);
         }
    }

3.3、choose(when,otherwise)语句

修改配置文件deptMapper.xml，添加

    <select id="selectListUseChoose" parameterType="Dept" resultMap="deptResultMap" >
         select dept_id,dept_name,dept_address from dept
         <where>
             <choose>
                 <when test="deptId!=null">and dept_id = #{deptId}</when>
                 <when test="deptName!=null">and dept_name = #{deptName}</when>
                 <when test="deptAddress!=null">and dept_address = #{deptAddress}</when>
                 <otherwise>and 1=2</otherwise>     
             </choose>
         </where>
    </select>

修改DeptDaoImpl.java，添加selectListUseChoose方法

    // 使用choose动态生成sql
    public List<Dept> selectListUseChoose(Dept dept) {
         List<Dept> depts = null;
         try {
             session = MyBatisUtil.getSession();
             depts = session.selectList(
                     "com.demo.entity.DeptMapper.selectListUseChoose", dept);
         } catch (Exception e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
         } finally {
             try {
                 MyBatisUtil.closeSession();
             } catch (Exception e) {
                 // TODO Auto-generated catch block
                 e.printStackTrace();
             }
         }
         return depts;
    }

添加测试代码，TestDeptDaoImpl.java

    @Test
    public void testSelectListUseChoose() {
         Dept paramDept = new Dept();
         paramDept.setDeptId(1);
         paramDept.setDeptName("研发部一部");
         paramDept.setDeptAddress("北京");
         List<Dept> depts = deptDaoImpl.selectListUseChoose(paramDept);
         for (Dept dept : depts) {
             System.out.println("部门信息："+dept);
         }
    }

3.4、SET语句

修改配置文件deptMapper.xml，添加
    <update id="updateDeptUseSet" parameterType="dept" >
         update dept
        <set>
             <if test="deptName!=null">dept_name = #{deptName},</if>
             <if test="deptAddress!=null">dept_address=#{deptAddress},</if>   
        </set>   
         where dept_id = #{deptId}
    </update>

修改DeptDaoImpl.java，添加updateDeptUseChoose方法

    // 使用Set动态生成sql
    public int updateDeptUseChoose(Dept dept) {
         int  i =0;
         try {
             session = MyBatisUtil.getSession();
             i = session.update("com.demo.entity.DeptMapper.updateDeptUseSet", dept);
             session.commit();
         } catch (Exception e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
             session.rollback();
         } finally {
             try {
                 MyBatisUtil.closeSession();
             } catch (Exception e) {
                 // TODO Auto-generated catch block
                 e.printStackTrace();
             }
         }
         return i;
    }

添加测试代码，TestDeptDaoImpl.java

    @Test
    public void testUpdateDeptUseChoose() {
         Dept paramDept = new Dept();
         paramDept.setDeptId(1);
         paramDept.setDeptName("研发中心");
         paramDept.setDeptAddress("沈阳");
         System.out.println("受影响的行数："+deptDaoImpl.updateDeptUseChoose(paramDept));      
    }

测试效果：

image.png image.png

3.5、ForEach语句

修改配置文件deptMapper.xml，添加

    <!-- 定义根据多个部门ID查询部门相关部门信息的SQL语句 ,resultMap的值是指集合里元素的类型,parameterType不用指定 -->
    <select id="selectListUseForeach" resultMap="deptResultMap">
         select * from dept where dept_id in
         <!--collection的值为"array"表示来的参数为数组（有[]）;值为list,表示来的参数是个list集合类型  -->
             <foreach collection="list" item="deptId" separator="," open="(" close=")">
                 #{deptId}
             </foreach>
    </select>

修改DeptDaoImpl.java，添加selectListUseForeach方法

    // 使用foreach动态生成sql
    public List<Dept> selectListUseForeach(List<Integer> deptIds) {
         List<Dept> depts = null;
         try {
             session = MyBatisUtil.getSession();
             depts = session.selectList(
                     "com.demo.entity.DeptMapper.selectListUseForeach", deptIds);
         } catch (Exception e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
         } finally {
             try {
                 MyBatisUtil.closeSession();
             } catch (Exception e) {
                 // TODO Auto-generated catch block
                 e.printStackTrace();
             }
         }
         return depts;
    }

添加测试代码，TestDeptDaoImpl.java

    @Test
    public void testSelectListUseForeach() {
         List<Integer> deptIds = new ArrayList<Integer>();
         deptIds.add(1);
         deptIds.add(3);
         deptIds.add(6);
         List<Dept> depts = deptDaoImpl.selectListUseForeach(deptIds);
         for (Dept dept : depts) {
             System.out.println("部门信息："+dept);
         }
    }

测试效果：

image.png

3.6、include语句

修改配置文件deptMapper.xml，添加
     <!-- 使用include语句动态插入表的字段及对应的值 -->
     <insert id="insertDeptUseInclude" parameterType="dept">
         insert into dept
         <include refid="key"></include>
         values
         <include refid="value"></include>
    </insert>
    <sql id="key">
         <trim suffixOverrides="," prefix="(" suffix=")">
             <if test="deptName!=null">dept_name,</if>
             <if test="deptAddress!=null">dept_address,</if>
         </trim>
    </sql>
    <sql id="value">
         <trim suffixOverrides="," prefix="(" suffix=")">
             <if test="deptName!=null">#{deptName},</if>
             <if test="deptAddress!=null">#{deptAddress},</if>
         </trim>
    </sql>   

修改DeptDaoImpl.java，添加insertDeptUseInclude方法

    // 使用include动态生成sql
    public int insertDeptUseInclude(Dept dept) {
         int  i =0;
         try {
             session = MyBatisUtil.getSession();
             i = session.insert("com.demo.entity.DeptMapper.insertDeptUseInclude", dept);
             session.commit();
         } catch (Exception e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
             session.rollback();
         } finally {
             try {
                 MyBatisUtil.closeSession();
             } catch (Exception e) {
                 // TODO Auto-generated catch block
                 e.printStackTrace();
             }
         }
         return i;
    }

添加测试代码，TestDeptDaoImpl.java

    @Test
    public void testInsertDeptUseInclude() {
         Dept dept = new Dept();
         dept.setDeptName("市场部");
         dept.setDeptAddress("杭州");
         System.out.println("受影响的行数："+ deptDaoImpl.insertDeptUseInclude(dept));
    }

测试效果：

image.png image.png