Java教程

MyBatis_动态SQL

本文主要是介绍MyBatis_动态SQL,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

实现动态SQL语句: 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--绑定名称空间-->
<mapper namespace="mybatis.dao.EmployeeMapperDynamicSQL">
    <!--测试动态SQL的xml文件-->
    <!--实现动态SQL的标签
    if:判断
    choose(when,otherwise);分支选择
    trim(where(封装),set(封装条件)):字符截取
    foreach
    -->
    <!--查询员工,要求,携带了哪个字段查询条件就带上这个字段的值
    //按照条件查询
    public List<Employee> getEmpsByCondutionIf(Employee employee);
    -->
    <select id="getEmpsByCondutionIf" resultType="mybatis.bean.Employee">
        select id,last_name lastName,gender,email  FROM tbl_employee
        <where><!--where标签代替sql语句中的where防止出现id=null但是后面有不等于null而出现的sql语法错误  也可以用1=1代替-->
        <!--test:判断表达式(OGNL)-->
        <if test="id!=null">
             id=#{id}
        </if>

        <if test="lastName !=null and lastName != '' ">
            AND last_name like #{lastName}
        </if>

        <!-- email.trim-->
        <if test="email !=null and email.trim()!='' ">
            AND email = #{email}
        </if>

        <if test="gender == 0 or gender == 1">
            AND gender = #{gender}
        </if>
        </where>
    </select>


    <!--
    //测试trim标签
    public List<Employee> getEmpsByCondutionIfAndTrim(Employee employee);
    -->
    <select id="getEmpsByCondutionIfAndTrim" resultType="mybatis.bean.Employee">
    select id,last_name lastName,gender,email
    FROM tbl_employee
        <!--trim 标签 sql语句中后面多出的and 和 or  where标签不能解决
            trim标签体中是整个字符串拼接后的结果
                prefix:给拼串后的字符串加一个前缀
                prefixOverrides:前缀覆盖;前面多了什么给去掉
                suffix:给拼串后的字符串加一个后缀
                suffixOverrides:后缀覆盖;后面多了什么给去掉
        -->
        <trim prefix="where" suffixOverrides="and">

        <if test="id!=null">
            id=#{id} AND
        </if>

        <if test="lastName !=null and lastName != '' ">
            last_name like #{lastName} AND
        </if>

        <!-- email.trim-->
        <if test="email !=null and email.trim()!='' ">
             email = #{email} AND
        </if>

        <if test="gender == 0 or gender == 1">
            gender = #{gender}
        </if>
        </trim>
    </select>


    <!--
    查询员工:如果带了id用id查,带了lastName 用lastName查.....多选一 都不带 查所有
    //按照id或者lastName查询员工
    public List<Employee> getEmpsByCondutionChoose(Employee employee);
    -->
    <select id="getEmpsByCondutionChoose" resultType="mybatis.bean.Employee">
        <!--bind 绑定 -->
        <bind name="my_lastname" value="'%'+lastName+'%'"></bind>
        SELECT id,last_name lastName,gender,email
        FROM tbl_employee
        <where>
            <choose>
                <when test="id != null">
                    id=#{id}
                </when>
                <when test="lastName != null and lastName != '' ">
                    last_name LIKE #{my_lastname}
                </when>
                <when test="email != null and email != '' ">
                    email LIKE #{email}
                </when>
                <when test="gender == 0 and gender == 1 ">
                    gender = #{gender}
                </when>
                <otherwise >
                    1=1
                </otherwise>
            </choose>

        </where>

    </select>


    <!--
    要求,传入的employee带了什么参数则更新哪个参数(区别之前的全更新)

    //employee 传入什么则更新什么
    public void updateEmp(Employee employee);
    -->
    <update id="updateEmp">
        UPDATE tbl_employee
        <set>
            <if test="lastName!=null and lastName !='' ">
                last_name = #{lastName},
            </if>
            <if test="email !=null and email !='' ">
                email = #{email},
            </if>
            <if test="gender == 0 or gender == 1">
                gender = #{gender},
            </if>
        </set>
        <where>
            id=#{id}
        </where>
    </update>


    <!--
    //测试foreach 传入一个id的list集合查询员工
    public List<Employee> getEmpsByList (List<Integer> list);
    -->
    <select id="getEmpsByList" resultType="mybatis.bean.Employee">
        SELECT id,last_name lastName,gender,email
        FROM tbl_employee
        WHERE id IN
        <!--
        foreach:遍历集合
            collection:需要遍历哪个集合
            item:将遍历出的数据赋值给谁
            separator:每个遍历中间用什么隔开
            open:遍历的集合用什么开始
            close:遍历的集合用什么结束
        -->
        <foreach collection="list" item="ltem_id" separator="," open="(" close=")">
          #{ltem_id}
        </foreach>

    </select>

    <!--
     //测试利用foreach标签实现批量插入
    public void addEmpsByForeach(@Param("emps")List<Employee> employees);
    -->
    <insert id="addEmpsByForeach">
        INSERT INTO tbl_employee( last_name,gender,email)
        VALUES
        <foreach collection="emps" item="item_emps" separator=",">
            (#{item_emps.lastName},#{item_emps.gender},#{item_emps.email})
        </foreach>
    </insert>


    <!--
    两个内置参数:
    _parameter:代表整个参数
            单个参数:_parameter就是代表这个参数
            多个参数:_parameter就是代表这个map
    _databaseId:如果配置了databaseIdProvider标签
            _databaseId就是代表当前数据库的别名


    -->
    
</mapper>

这篇关于MyBatis_动态SQL的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!