一、查询
1、查询所有数据
(1)方法一:
<select id="selectAll" resultType="brand">
select * from tb_brand;
</select>
(2)方法二:
<select id="selectAll" resultType="brand">
select id,brand_name as brandName,company_name as companyName,ordered,description,status
from tb_brand;
</select>
(3)方法三:
<!--
sql片段:
-->
<sql id="brand_column">
id,brand_name as brandName,company_name as companyName,ordered,description,status
</sql>
<select id="selectAll" resultType="brand">
select
<include refid="brand_column"/>
from tb_brand;
</select>
(4)方法四:
<resultMap id="Brand_resultMap" type="brand">
<!--
id:用来完成主键字段的映射
column:表的列名称
property:实体类的属性名
result:用来完成一般字段的映射
column:表的列名称
property:实体类的属性名
-->
<result column="brand_name" property="brandName"/>
<result column="company_name" property="companyName"/>
</resultMap>
<select id="selectAll" resultMap="Brand_resultMap">
select
*
from tb_brand;
</select>
2、查询详情
(1)方法一:
<!--
* 参数占位符:
1.#{}:会将其替换为 ?,为了防止sql注入
2.${}:直接拼 sql,会存在SQL注入问题
3.使用时机:
* 参数传递的时候:#{}
* 表名或者列名不固定的情况下:${},会存在SQL注入问题
* 参数类型:
parameterType:可以省略
* 特殊字符处理:
1.转义字符: < 写成 <(小于符号)
2.CDATA区;
-->
<select id="selectById" parameterType="int" resultMap="Brand_resultMap">
select *
from tb_brand where id = #{id};
</select>
(2)方法二:
<select id="selectById" parameterType="int" resultMap="Brand_resultMap">
select *
from tb_brand where id
<![CDATA[
<
]]>
#{id};
</select>
3、条件查询
(1)
<select id="selectByCondition" resultMap="Brand_resultMap">
/*一般情况下都写成对象的属性名,会规避很多问题*/
select *
from tb_brand
where
status = #{status}
and company_name like #{companyName}
and brand_name like #{brandName}
</select>
(2)多条件——动态条件查询
<select id="selectByCondition" resultMap="Brand_resultMap">
select *
from tb_brand
/* where 1 = 1*/
<where>
<if test="status != null">
and status = #{status}
</if>
<!--
因为是字符串处理判断不等于空,还要判断字符串不等于空字符串company_name != ''(不能使用equals)
不能写sql中字段的名称,应该写取到companyName 的值的字符串
-->
<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>
一般情况下都写成对象的属性名,会规避很多问题。
注意:where后面加了1=1,第一个前面也加上and,语法就满足要求了,就可以把三个注释都注销也可以。
(3)单条件——动态查询
(1)
<select id="selectByConditionSingle" resultMap="Brand_resultMap">
select *
from tb_brand
where
<choose> <!--相当于switch-->
<when test="status != null"> <!--相当于case-->
status = #{status}
</when>
<when test="companyName != null and companyName != ''"> <!--相当于case-->
company_name like #{companyName}
</when>
<when test="brandName != null and brandName != ''"> <!--相当于case-->
brand_name like #{brandName}
</when>
<otherwise> <!--相当于default-->
1 = 1
</otherwise>
</choose>
</select>
(2)把otherwise标签去掉
<!--这个操作是把otherwise标签去掉-->
<select id="selectByConditionSingle" resultMap="Brand_resultMap">
select *
from tb_brand
<where>
<choose> <!--相当于switch-->
<when test="status != null"> <!--相当于case-->
status = #{status}
</when>
<when test="companyName != null and companyName != ''"> <!--相当于case-->
company_name like #{companyName}
</when>
<when test="brandName != null and brandName != ''"> <!--相当于case-->
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>
三、修改
1、修改全部字段
<!--<update id="update">
update tb_brand
set
brand_name = #{brandName},
company_name = #{companyName},
ordered = #{ordered},
description = #{description},
status = #{status}
where id = #{id};
</update>
2、修改动态字段:用户给我们传递什么样的数据,我们就修改什么样的数据,没修改的也不会出现null了
<update id="update">
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>
四、删除
1、删除一个:根据id删除
<delete id="deleteByid">
delete from tb_brand where id = #{id};
</delete>
2、批量删除
<delete id="deleteByids">
delete from tb_brand where id
in (
<foreach collection="ids" item="id" separator=",">
#{id}
</foreach>
);
</delete>
注意:MyBatis会将数组的参数,封装为一个Map集合。
* 默认:array = 数组A
* 使用@Param注解改变map集合的默认key的名称(把array改成了ids)