MybatisCRUD

一，动态修改

咱们修改一个对象,咱们只能修改有数据的部分

<!--动态修改-->
<update id="update" parameterType="cn.itsource._02_mapper.domain.Employee">
    UPDATE employee
    <trim prefix="SET" suffixOverrides=",">
        <if test="name!=null and name!=''">
          name = #{name},
         </if>
        <if test="age!=null">
          age=#{age},
        </if>
        <if test="sex!=null">
          sex = #{sex}
        </if>
    </trim>
    WHERE id = #{id}
</update>
更好的方案
<update id="update" parameterType="cn.itsource._02_mapper.domain.Employee">
    UPDATE employee
    <set>
        <if test="name!=null and name!=''">
            name = #{name},
        </if>
        <if test="age!=null">
            age=#{age},
        </if>
    </set>
    WHERE id=#{id}
</update>

二，多对一

准备(两张表employee,department,两个domain,两个mapper.xml)
查询(嵌套结果[一条sql],嵌套查询[n+1条sql])

1.1 准备domain

Employee
public class Employee {

    private Long id;
    private String name;
    private Integer age;
    private Boolean sex;

    private Department dept;
    //... getter,setter与toString
}
Department
public class Department {
    private Long id;
    private String name;
    //... getter,setter与toString
}

1.2 嵌套结果

查询的sql要关连多张表(一定要取别名,不然有些名称会产生冲突)
当我们使用了association 后默认的映射失败,需要自己手动完成映射

<select id="findAll" resultMap="employeeMap">
    select e.id eid,e.name ename,e.age,e.sex,d.id did,d.name dname
      from employee e join department d on d.id = e.dept_id
</select>

<resultMap id="employeeMap" type="employee">
    <!-- 手动完成基本映射 -->
    <id property="id" column="eid" />
    <result property="name" column="ename" />
    <result property="sex" column="sex" />
    <result property="age" column="age" />
    <!-- property:属性名,javaType:属性类型 -->
    <association property="dept" javaType="department">
        <id property="id" column="did" />
        <result property="name" column="dname" />
    </association>
</resultMap>

1.3 嵌套查询

会产生n+1条sql
需要去找到对应的那条sql并且执行
保证MyBatis能找到这两个xml

employeeMapper.xml
<select id="findAll" resultMap="employeeMap">
    SELECT * FROM employee
</select>
<!--嵌套查询方案-->
<resultMap id="employeeMap" type="employee">
    <id property="id" column="id" />
    <result property="name" column="name" />
    <result property="sex" column="sex" />
    <result property="age" column="age" />
<!--
    查询相应的部门
        column:数据库对应的列 select:查询对应的sql的路径
 -->
    <association property="dept" javaType="department"
             column="dept_id" select="cn.itsource._04_many2one.DepartmentMapper.findById" />
</resultMap>
departmentMapper.xml
<mapper namespace="cn.itsource._04_many2one.DepartmentMapper">
    <select id="findById" parameterType="long" resultType="department">
        select * from department where id = #{id}
    </select>
</mapper>

三，一对多

1.2 准备domain

Employee
public class Employee {

    private Long id;
    private String name;
    private Integer age;
    private Boolean sex;
     //... getter,setter与toString
}
Department
public class Department {
    private Long id;
    private String name;

    private List<Employee> employees = new ArrayList<>();

1.2 级连保存

准备两个Mapper
保存部门后需要马上拿到它的id
保存员工传的是Map{List,deptId}

departmentMapper.xml
<!--保存后需要拿到id-->
<insert id="save" parameterType="department"
        useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    insert into department (name) values (#{name})
</insert>
employeeMapper.xml
<insert id="batchSave" parameterType="map">
    insert into employee (name,age,sex,dept_id) values
    <foreach collection="list" item="emp" separator=",">
        (#{emp.name},#{emp.age},#{emp.sex},#{deptId})
    </foreach>
</insert>

1.3 嵌套结果

查询的sql要关连多张表(一定要取别名,不然有些名称会产生冲突)
当我们使用了collection 后默认的映射失败,需要自己手动完成映射

<resultMap id="departmentMap" type="department">
    <id property="id" column="did" />
    <result property="name" column="dname" />
    <collection property="employees" ofType="employee">
        <id property="id" column="eid" />
        <result property="name" column="ename" />
        <result property="age" column="age" />
        <result property="sex" column="sex" />
    </collection>
</resultMap>

<select id="findAll" resultMap="departmentMap">
    select d.id did,d.name dname,e.id eid,e.name ename,e.age,e.sex
    from department d left join employee e on e.dept_id = d.id
</select>

1.4 嵌套查询

依赖需要找到对应的SQL

departmentMapper.xml
<resultMap id="departmentMap" type="department">
    <id property="id" column="id" />
    <result property="name" column="name" />
    <collection property="employees" ofType="employee" column="id" select="cn.itsource._05_one2many.EmployeeMapper.findByDeptId">
    </collection>
</resultMap>
<select id="findAll" resultMap="departmentMap">
    select *  from department
</select>
employeeMapper.xml
<!--这个命名空间的名称就是咱们Mapper接口的全限定名-->
<mapper namespace="cn.itsource._05_one2many.EmployeeMapper">

    //...
    
    <select id="findByDeptId" resultType="employee" parameterType="long">
        select * from employee where dept_id = #{deptId}
    </select>

</mapper>

四.缓存

自带一级级联
二级缓存需要加上标签
二级缓存的对象必需是序列化对象 … implements Serializable

五， #与$符号的区别 ，面试题

①，共同点

：
#和$都是用来取值的
参数传递普通类型(8大基本数据类型8大包装类。再加一个String)
#: 正常发送的sql语句 SELECT * FROM t_student WHERE id=?
$: 传递普通类型，它是要报错的，取不到值
参数传递对象
#: 正常发送的sql语句 SELECT * FROM t_student WHERE id=?
$: 正常发送sql语句 SELECT * FROM t_student WHERE id=2

②，区别

：

                1.
                    #它可以取任意类型的参数
                    $只能取对象中的值，不能取普通类型的值
               
                2.  #取值可以有效防止sql注入  ，$符号取值它是sql拼接，不能有效防止sql注入

                3.  #取值让sql语句支持预编译的功能，而$符号是不支持的，所以在性能上来说#取值性能要高于$符号

                4. 使用#一般是用来取值的，而$符号一般用于动态排序

六，映射