ResultMap映射
<mapper namespace="com.it.mapper.BrandMapper">
<!-- id, brand_name as brandName, company_name as companyName, ordered, description, status -->
<!--
数据库表的字段名称 和 实体类的属性名称不一样,则不能自动封装数据
*起别名:对不一样的列名起别名,让别名和实体类的属性名一样
*缺点:每次查询都要定义一次别名
* sqL片段
*缺点:不灵活
* resultMap:
1·定义<resuLtMap>标签
2·在<select>标签中,使用resultMap属性替换resultType属性
-->
<!-- 注意:只需要定义字段名和属性名不一样的映射,而一样的则不需要专门定义出来。-->
<resultMap id="brandResultMap" type="brand">
<result column="brand_name" property="brandName"/>
<result column="company_name" property="companyName"/>
</resultMap>
查询全部SQL
<select id="selectAll" resultMap="brandResultMap">
select *
from tb_brand;
</select>
根据id查询
<select id="selectById" resultMap="brandResultMap">
select *
from tb_brand
where id = #{id};
</select>
多条件查询
<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>
单条件查询
<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>
</choose>
</where>
</select>
添加
<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>
根据id修改
<update id="updateId">
update tb_brand
<set>
<if test="status != null">
status= #{status}
</if>
<if test="companyName != null and companyName !='' ">
company_name = #{companyName}
</if>
<if test="brandName != null and brandName !='' ">
brand_name = #{brandName}
</if>
<if test="description != null and description !='' ">
description = #{description}
</if>
<if test="aordered != null ">
aordered = #{ordered}
</if>
</set>
where id = #{id};
</update>
根据id删除(删除单个)
<delete id="deleteById">
delete
from tb_brand
where id = #{id};
</delete>
根据id删除(批量删除)
<!--
mybatis会将数组参数,封装为一个Map集合。
*默认: array=数组
*使用@Param注解改变map集合的默认key的名称
-->
<delete id="deleteById">
delete from tb_brand where id in
<foreach collection="array" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>