mybatis----条件查询

 第一种接口方法使用拼接字符串的方法${}来拼接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>