baseMapper 动态sql 动态sql如何实现

当我们想按价格区间或者折扣等等因素去搜索我们想要的东西，在原来的JDBC中的时候我们只能使用if来判断，然后拼接SQL语句的方式来实现。

select * from product where discount=80% and productName like '%中%' ....

一、动态SQL

在使用 JDBC 的过程中， 根据条件进行 SQL 的拼接是很麻烦且很容易出错的。动态SQL是一个可以变化的sql语句，MyBatis 动态 SQL 的出现就能够使用MyBatis通过 OGNL 来进行动态 SQL 的使用的。它可以帮助我们方便的在SQL语句中实现某些逻辑，有如下的一些标签：

二、if的使用

实体对象：

@Data
public class Emp {
    private Short empno;
    private String ename;
    private String job;
    private Short mgr;
    private Date hiredate;
    private Double sal;
    private Double comm;
    private Dept dept;
}

① if+where 实现多条件查询

接口：

public interface EmpMapper {
    //动态sql：if的使用
    public List<Emp> selectEmpsByCodition(@Param("ename") String ename, @Param("sal") double sal);
}

映射文件：

<mapper namespace="com.acoffee.maven.mapper.EmpMapper">

    <select id="selectEmpsByCodition" resultType="Emp">
        select * from emp
        <if test="ename!=null and ename!=''">
            where ename like concat ('%',#{ename},'%')
        </if>

        <if test="sal!=null&&sal!=0.0">
            and sal>#{sal}
        </if>
    </select>

@Test
    public void test1(){
        SqlSession sqlSession = MyBatisUtil.createSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        List<Emp> emps = empMapper.selectEmpsByCodition(null, 1200);
        for (Emp emp: emps) {
            logger.info(emp);
        }

        MyBatisUtil.closeSession(sqlSession);
    }

当我们的测试文件param1参数为null ，param2参数有值这个时候就会报错：因为拼接起来是sql语句不对的select * from emp and sal > 1200,没有where。因为where在里面，当我们想直接通过sal去查的时候就没有where，为了保证查询的sql语句每条都有where，我们可以将where移出去。

修改如下：

</select>
    <select id="selectEmpsByCodition" resultType="Emp">
        select * from emp where 1=1
        <if test="ename!=null and ename!=''">
            and ename like concat ('%',#{ename},'%')
        </if>

        <if test="sal!=null&&sal!=0.0">
            and sal>#{sal}
        </if>
    </select>

但是我们怎么做还是不完美的，因为这个1=1在业务上来说是没有任何意义的。所以我们引入where标签：

<select id="selectEmpsByCodition" resultType="Emp">
        select * from emp
        <where>
            <if test="ename!=null and ename!=''">
                and ename like concat ('%',#{ename},'%')
            </if>

            <if test="sal!=null&&sal!=0.0">
                and sal>#{sal}
            </if>
        </where>
    </select>

这种写法我们不管param1，param2 有无参数都可以实现查询。

② if+set 实现更新操作

接口：

public interface EmpMapper {
    //修改员工信息
    public void update(Emp emp);
}

映射文件：

<mapper>
    <update id="update" parameterType="emp">
        update emp
        <set>
            <if test="ename!=null and ename!=''">
                ename=#{ename}
            </if>
            <if test="job!=null and job!=''">
                job=#{job}
            </if>
            <if test="mgr!=null and mgr!=''">
                mgr=#{mgr}
            </if>
            <if test="hiredate!=null and hiredate!=''">
                hiredate=#{hiredate}
            </if>
            <if test="sal!=null and sal!=''">
                sal=#{sal}
            </if>
            <if test="comm!=null and comm!=''">
                comm=#{comm}
            </if>
        </set>
        <where>
            empno=#{empno}
        </where>
    </update>
</mapper>

测试文件：

@Test
    public void testUpdate(){
        SqlSession sqlSession = MyBatisUtil.createSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        Emp emp = new Emp();
        emp.setEmpno((short) 7369);
        emp.setSal((double) 1000);
        empMapper.update(emp);
        sqlSession.commit();
        MyBatisUtil.closeSession(sqlSession);
    }

执行结果：

我们加大难度，做一个关联查询：

实体对象：

Emp 类：

@Data
public class Emp {
    private Short empno;
    private String ename;
    private String job;
    private Short mgr;
    private Date hiredate;
    private Double sal;
    private Double comm;
    private Dept dept;
}

Dept 类：

@Data
public class Dept {
    private Byte deptno;
    private String dname;
    private String loc;
    private List<Emp> emps;
}

接口：

public interface EmpMapper {
    //修改员工信息
    public void update(Emp emp);
}

映射文件：

<mapper>
   <update id="update" parameterType="emp">
        update emp
        <set>
            <if test="ename!=null and ename!=''">
                ename=#{ename},
            </if>
            <if test="job!=null and job!=''">
                job=#{job},
            </if>
            <if test="mgr!=null and mgr!=''">
                mgr=#{mgr},
            </if>
            <if test="hiredate!=null and hiredate!=''">
                hiredate=#{hiredate},
            </if>
            <if test="sal!=null and sal!=''">
                sal=#{sal},
            </if>
            <if test="comm!=null and comm!=''">
                comm=#{comm},
            </if>
       		<!--这里是sql字段所以使用deptno，我们在这里设置另一张表的字段，从而实现关联操作-->
            <if test="dept.deptno!=null and dept.deptno!=0">
                deptno=#{dept.deptno}, 
            </if>
        </set>
        <where>
            empno=#{empno}
        </where>
    </update>
</mapper>

测试文件：

@Test
    public void testUpdate(){
        SqlSession sqlSession = MyBatisUtil.createSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        Emp emp = new Emp();
        emp.setEmpno((short) 7369);
        emp.setSal((double) 1000);
        emp.setEname("frank");

        Dept dept = new Dept();
        dept.setDeptno((byte) 10);//给部门设置编号
        emp.setDept(dept);//给员工设置部门
        empMapper.update(emp);
        sqlSession.commit();
        MyBatisUtil.closeSession(sqlSession);
    }

执行结果：

三、trim标签

1.trim实现多条件查询
接口：

public interface EmpMapper {
    public List<Emp> selectEmpsByCoditionWithTrim(@Param("ename") String ename, @Param("sal") double sal);

}

映射文件：

<!--prefix:前缀  prefixOverrides:前缀的覆盖-->
    <select id="selectEmpsByCoditionWithTrim" resultType="emp">
        select * from emp
        <trim prefix="where" prefixOverrides="and">
            <if test="ename!=null and ename!=''">
                and ename like concat ('%',#{ename},'%')
            </if>

            <if test="sal!=null&&sal!=0.0">
                and sal>#{sal}
            </if>
        </trim>
    </select>

测试结果：

@Test
    public void testTrim(){
        SqlSession sqlSession = MyBatisUtil.createSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        List<Emp> emps = empMapper.selectEmpsByCoditionWithTrim("s", 1200);
        for (Emp emp: emps) {
            logger.info(emp);
        }

        MyBatisUtil.closeSession(sqlSession);
    }

执行结果：

2.trim实现修改操作

接口：

public interface EmpMapper {
    public void updateWithTrim(Emp emp);
}

映射文件：

<mapper namespace="com.acoffee.maven.mapper.EmpMapper">
   <update id="updateWithTrim" parameterType="emp">
        update emp
        <trim prefix="set" suffix="where empno=#{empno}" suffixOverrides=",">
            <if test="ename!=null and ename!=''">
                ename=#{ename},
            </if>
            <if test="job!=null and job!=''">
                job=#{job},
            </if>
            <if test="mgr!=null and mgr!=''">
                mgr=#{mgr},
            </if>
            <if test="hiredate!=null and hiredate!=''">
                hiredate=#{hiredate},
            </if>
            <if test="sal!=null and sal!=''">
                sal=#{sal},
            </if>
            <if test="comm!=null and comm!=''">
                comm=#{comm},
            </if>
            <!--这里是sql字段所以使用deptno-->
            <if test="dept.deptno!=null and dept.deptno!=0">
                deptno=#{dept.deptno},
            </if>
        </trim>
    </update>
</mapper>

测试文件：

@Test
    public void testUpdateTrim1(){
        SqlSession sqlSession = MyBatisUtil.createSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        Emp emp = new Emp();
        emp.setEmpno((short) 7499);
        emp.setSal((double) 2000);
        emp.setEname("balnk");

        Dept dept = new Dept();
        dept.setDeptno((byte) 20);//给部门设置编号
        emp.setDept(dept);//给员工设置部门
        empMapper.update(emp);
        sqlSession.commit();
        MyBatisUtil.closeSession(sqlSession);
    }

执行结果：

四、 foreach 标签的使用

1.数组
接口：

public interface EmpMapper {
    public List<Emp> selectEmpsByEmpnos(Short[] empnos);
}

映射文件：

<mapper namespace="com.acoffee.maven.mapper.EmpMapper">
    <!--collection:如果是数组的话就写array item：写接口中传递的名字 -->
    <select id="selectEmpsByEmpnos" resultType="emp">
        select * from emp where empno in
        <foreach collection="array" item="empnos" open="(" separator="," close=")">
          #{empnos}
        </foreach>
    </select>
</mapper>

测试文件：

@Test
    public void testForEach(){
        SqlSession sqlSession = MyBatisUtil.createSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        Short[] empnos={7499,7788,};
        List<Emp> emps = empMapper.selectEmpsByEmpnos(empnos);
        for (Emp emp: emps) {
            logger.info(emp);
        }
        MyBatisUtil.closeSession(sqlSession);
    }

执行结果：

2.List类型

接口：

public interface EmpMapper {
    //foreach标签：list集合
    public List<Emp> selectEmpsByEmpnos1(List empnos);
}

映射文件：

<!--collection:如果是List集合，就写list  -->
<mapper namespace="com.acoffee.maven.mapper.EmpMapper">
    <select id="selectEmpsByEmpnos1" resultType="emp">
        select * from emp where empno in
        <foreach collection="list" item="empnos" open="(" separator="," close=")">
            #{empnos}
        </foreach>
    </select>

</mapper>

测试文件：

@Test
    public void testForEach2() {
        SqlSession sqlSession = MyBatisUtil.createSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        List<Short> empnos = new ArrayList();
        empnos.add((short) 7499);
        empnos.add((short) 7369);
        List<Emp> emps = empMapper.selectEmpsByEmpnos1(empnos);
        for (Emp emp : emps) {
            logger.info(emp);
        }

        MyBatisUtil.closeSession(sqlSession);
    }

执行结果：

3.Map类型

接口：

public interface EmpMapper {
    //foreach标签：Map集合
    public List<Emp> selectEmpsByEmpnos2(Map empnos);
}

映射文件：

<!--collection:如果是Map集合能随便，但是要与测试代码中保持一致  -->
<mapper namespace="com.acoffee.maven.mapper.EmpMapper">
    <select id="selectEmpsByEmpnos2" resultType="emp">
        select * from emp where empno in
        <foreach collection="key" item="empnos" open="(" separator="," close=")">
            #{empnos}
        </foreach>
    </select>
</mapper>

测试文件：

@Test
    public void testForEach3() {
        SqlSession sqlSession = MyBatisUtil.createSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);

        List<Short> All = new ArrayList();
        All.add((short) 7499);
        All.add((short) 7369);
        Map<String,List<Short>> empnos =new HashMap<>();
        empnos.put("key",All);
        List<Emp> emps = empMapper.selectEmpsByEmpnos2(empnos);
        for (Emp emp : emps) {
            logger.info(emp);
        }
        MyBatisUtil.closeSession(sqlSession);
    }

五、 bind标签 的使用

原来在完成模糊查询的时候，使用concat函数来完成，仅能用在mysql上，如果大家使用其他数据库，比如oracle，或者sqlserver，为了适用于多种数据库平台，所以就引入bind标签。

接口：

public interface EmpMapper {
    //bind标签
    public List<Emp> selectEmpsByEnameWithBind(@Param("ename") String name);
}

映射文件：

<mapper namespace="com.acoffee.maven.mapper.EmpMapper">
    <!--可以跨数据库-->
    <select id="selectEmpsByEnameWithBind" parameterType="string" resultType="emp">
        <bind name="_ename" value="'%'+ename+'%'"></bind>
        select * from emp where ename like #{_ename}
    </select>
</mapper>

测试文件：

@Test
    public void testBind() {
        SqlSession sqlSession = MyBatisUtil.createSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);

        List<Emp> emps = empMapper.selectEmpsByEnameWithBind("S");
        for (Emp emp:emps ) {
            logger.info(emp);
        }
        MyBatisUtil.closeSession(sqlSession);
    }

执行结果：

六、 sql提取

public interface EmpMapper {
    //bind标签
    public List<Emp> selectEmpsByEnameWithBind(@Param("ename") String name);
}

映射文件：

我们将要查询的字段封装到sql标签中，为其取名为Base_column然后在后续查询中只要是查询这几个字段我们就可以直接使用<include refid="Base_column"></include>即可

<mapper namespace="com.acoffee.maven.mapper.EmpMapper">
    <sql id="Base_column">
        empno,ename,sal
    </sql>
    
    <select id="selectEmpsByEnameWithBind" parameterType="string" resultType="emp">
        <bind name="_ename" value="'%'+ename+'%'"></bind>
        select
        <include refid="Base_column"></include>
        from emp where ename like #{_ename}
    </select>
</mapper>

测试文件：

@Test
    public void testBind() {
        SqlSession sqlSession = MyBatisUtil.createSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);

        List<Emp> emps = empMapper.selectEmpsByEnameWithBind("S");
        for (Emp emp:emps ) {
            logger.info(emp);
        }
        MyBatisUtil.closeSession(sqlSession);
    }

执行结果：