Mybatis:增删改查

BrandMapper

多条件查询  动态查询
            if,choose (when, otherwise),trim (where, set),foreach
        choose很像switch when很像case otherwise很像default
        switch判断 set主要用于修改 foreach用于删除

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
    namespace名称空间
-->
<mapper namespace="com.itheima.mapper.BrandMapper">

    <!--
            数据库表的字段名称 与 实体类的属性名称不一样，则不能自动提取数据
                   起别名:对不一样的列名起别名，让别名和实体类的属性名一样
                        缺点:不太方便
                            sql片段 缺点 不灵活
                            ***** resultMap 映射******
                                1.定义<resultMap>标签
                                2.使用resultMap的属性替换 resultType属性
    <!-1.起别名-->
    <!--<select id="selectAll" resultType="brand">
        select id, brand_name as brandName, company_name as companyName, ordered, description, status
        from tb_brand;
    </select>-->
    <!--2.sql片段-->
<!--    <select id="selectAll" resultType="brand">-->
<!--        select-->
<!--            <include refid="brand_column"></include>-->
<!--        from tb_brand;-->
<!--    </select>-->
<!--    ***** resultMap 映射******-->
    <!--
            id :唯一标识
            type:映射的类型支持别名
    -->
    <resultMap id="brandResultMap" type="brand">
        <!--
                id:完成主键字段的映射
                    column :表的列明
                    property:属性名
                result:完成一般字段的映射
        -->
        <result column="brand_name" property="brandName"/>
        <result column="company_name" property="companyName"/>
    </resultMap>
    <select id="selectAll" resultMap="brandResultMap">
        select *
        from tb_brand;
    </select>



    <!--参数占位符        select * from tb_user where id=?;
                           1. #{}：会替换成?,为了防止sql注入
                           2. ${}：拼sql，会存在sql注入问题
                           3. 使用时机
                                *参数产地的时候:#{}
                                *${} 表名或列名不固定的时候可以使用,存在问题
                           4.参数类型 : parameterType:可以省略
                           5.特殊字符的处理 ：> = <，<不能用
                                1.转义字符 < &lt;select *
                                                    from tb_brand where id &lt; #{id};
                                2.CDATA区   CD  字符多可以使用
    -->
    <select id="selectById" parameterType="int" resultMap="brandResultMap">
        select *
        from tb_brand where id=#{id};
    </select>

    <select id="selectById2" parameterType="int" resultMap="brandResultMap">
      /*  select *
        from tb_brand where id &lt; #{id};*/
      select *
      from tb_brand where id
        <![CDATA[
            <
        ]]> #{id};
    </select>

   <!-- &lt;!&ndash;多条件查询&ndash;&gt;
    <select id="selectByCondition" resultMap="brandResultMap">
        select *
        from tb_brand
        where status=#{status}
            and company_name like #{companyName}
            and brand_name like #{brandName};
    </select> -->
    <!--多条件查询  动态查询
                if,choose (when, otherwise),trim (where, set),foreach
-->
    <!--
    第一个条件注释掉就会报错 因为第二个参数有and
    解决 ： 1.加恒等式 1=1
            2.<where> 标签 替换where关键字
    -->
    <select id="selectByCondition" resultMap="brandResultMap">
        select *
        from tb_brand
        <where>
            <if test="status != null">
                and status=#{status}
            </if>
            <if test="companyName != null and companyName != ''">
                and company_name like #{companyName}
            </if>
            <if test="brandName != null and brandName != ''">
                and brand_name like #{brandName}
            </if>
        </where>
    </select>

    <!--单条件动态查询choose (when, otherwise)  choose很像switch when很像case otherwise很像default
            switch判断-->
    <!--<select id="selectByConditionSingle" resultMap="brandResultMap">
        select *
        from tb_brand
        where
        <choose>
            <when test="status != null">
                status=#{status}
            </when>
            <when test="companyName != null and companyName!=''">
                company_name like #{companyName}
            </when>
            <when test="brandName != null and brandName!=''">
                brand_name like #{brandName}
            </when>
            <otherwise>
                1=1
            </otherwise>
        </choose>

    </select>-->
    <!--单条件动态查询choose (when, otherwise)  choose很像switch when很像case otherwise很像default
            switch判断-->
    <select id="selectByConditionSingle" resultMap="brandResultMap">
        select *
        from tb_brand
        <where>
            <choose>
                <when test="status != null">
                    status=#{status}
                </when>
                <when test="companyName != null and companyName!=''">
                    company_name like #{companyName}
                </when>
                <when test="brandName != null and brandName!=''">
                    brand_name like #{brandName}
                </when>
                <otherwise>
                    1=1
                </otherwise>
            </choose>
        </where>
    </select>

    <!--添加 主键返回useGeneratedKeys="true" keyProperty="id"-->
    <insert id="add" useGeneratedKeys="true" keyProperty="id">
        insert into tb_brand ( brand_name, company_name, ordered, description, status)
        values (#{brandName},#{companyName},#{ordered},#{description},#{status});
    </insert>

    <!--修改-修改全部字段-->
    <update id="update">
        update tb_brand
         set brand_name=#{brandName},
             company_name=#{companyName},
             ordered=#{ordered},
             description=#{description},
             status=#{status}
        where id=#{id};
    </update>
    <!--动态修改-->
    <update id="updateCondition">
        update tb_brand
        <set>
            <if test="brandName != null and brandName != ''">
                brand_name=#{brandName},
            </if>
            <if test="companyName != null and companyName != ''">
                company_name=#{companyName},
            </if>
            <if test="ordered != null ">
                ordered=#{ordered},
            </if>
            <if test="description != null and description != ''">
                description=#{description},
            </if>
            <if test="status != null">
                status=#{status}
            </if>

        </set>
        where id=#{id};
    </update>

    <!--删除一个-->
    <delete id="deleteById">
        delete from tb_brand where id=#{id}
    </delete>
    <!--批量删除 封装成id数组 若要使用手写sql要添加多个占位符 遍历数组
    void deleteByIds(@Param("ids") int[] ids);
    foreach标签用于数组的遍历  collection遍历数组
    mybatis会将数组参数：封装成一个map集合
            默认 ： array=数组
            设置为 id ：要在Mapper中设置为@Param注解,来改变map集合的默认key的名称
              数组名字 默认array           分隔符         开始加(    结束加);
            collection="ids" item="id" separator="," open="(" close=");"-->
    <delete id="deleteByIds">
        delete from tb_brand
            where id in
            <foreach collection="ids" item="id" separator="," open="(" close=");">
                #{id}
            </foreach>
    </delete>




</mapper>