第一种接口方法使用拼接字符串的方法${}来拼接name,有缺点
sql函数
--cncat拼接函数
select concat('hello','mysql','world')
第二中接口方法使用#{}可行
@Select("select * from emp where name like concat('%',#{name},'%') and gender = #{gender} and entrydate between #{begin} and #{end} order by update_time desc")
public List<Emp> list(String name, Short gender , LocalDate begin , LocalDate end);
xml映射文件
规范
XML映射文件的名称与Mapper接口名称一致,并且将XML映射文件和Mapper接口放置在相同包下(同包同名)。
XML映射文件的namespace属性为Mapper接口全限定名一致。
XML映射文件中sql语句的id与Mapper 接口中的方法名一致,并保持返回类型一致。
1.
xml约束
<?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="com.itheima.mapper.EmpMapper">
<update id="save">
insert into emp (username,name, gender,image,job,entrydate,dept_id,create_time,update_time)
values (#{username},#{name},#{gender},#{image},#{job},#{entrydate},#{deptId},#{createTime},#{updateTime})
</update>
<mapper>
namespace="mapper包的地址"
id="mapper中对应的方法名"
resultType=“mapper中对应的类型"
动态sql
<select id="page" resultMap="emp">
select * from emp
<where>
<if test="name != null and name !='' ">
name like concat('%',#{name},'%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
</where>
limit #{start},#{pageSize}
</select>
<update id="update">
update emp
<set>
<if test="username != null and username != '' ">username=#{username},</if>
<if test="password != null and password != ''">
password = #{password},
</if>
<if test="name != null and name != ''">
name = #{name},
</if>
<if test="gender != null">
gender = #{gender},
</if>
<if test="image != null and image != ''">
image = #{image},
</if>
<if test="job != null">
job = #{job},
</if>
<if test="entrydate != null">
entrydate = #{entrydate},
</if>
<if test="deptId != null">
dept_id = #{deptId},
</if>
<if test="updateTime != null">
update_time = #{updateTime}
</if>
</set>
where id=#{id};
</update>
<delete id="delete">
delete from emp where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
collection:遍历的集合
item:遍历出来的元素
separator:分隔符
open:遍历开始前拼接的sql片段
close:遍历开始后拼接的sql片段
sql&include
抽取sql语句
<sql id="commonSelect">
select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from emp
</sql>
<select id="list" resultType="com.itheima.pojo.Emp">
<include refid="commonSelect"/>
where
<if test="name != null">
name like concat('%',#{name},'%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
order by update_time desc
</select>