MyBatis映射文件

MyBatis 的真正强大在于它的语句映射，这是它的魔力所在。由于它的异常强大，映射器的 XML 文件就显得相对简单。如果拿它跟具有相同功能的 JDBC 代码进行对比，你会立即发现省掉了将近 95% 的代码。MyBatis 致力于减少使用成本，让用户能更专注于 SQL 代码。
新建student表

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Student.java

package com.ljessie.mybatisdemo.entity;


import java.io.Serializable;

public class Student implements Serializable {
    private int id;
    private String name;
    private int age;
    private Department department;


    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

    public Student() {
    }

    public Student(int id,String name, int age,int departmentId) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.department = new Department();
        this.department.setId(departmentId);
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", department=" + department +
                '}';
    }

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

1、select

MyBatis 的基本原则之一是：在每个插入、更新或删除操作之间，通常会执行多个查询操作。因此，MyBatis 在查询和结果映射做了相当多的改进。一个简单查询的 select 元素是非常简单的。比如：

    <select id="findStudentByID" resultType="Student">
        select * from student where id = #{ID}
    </select>

select.jpg

2、insert, update 和 delete

数据变更语句 insert，update 和 delete 的实现非常接近：

<!--
void addStudent(Student student);
parameterType可以省略;
useGeneratedKeys="true"使用自增主键策略
keyProperty="id"获取到主键值以后，赋值给JavaBean的哪个属性
-->
    <insert id="addStudent" parameterType="Student" useGeneratedKeys="true" keyProperty="id">
        insert into student (name,age)
        values (#{name},#{age})
    </insert>
<!--    void updateStudent(Student student);-->
    <update id="updateStudent">
        update student
            set name = #{name},age = #{age}
            where id = #{id}
    </update>
<!--    void deleteStudentById(String id);-->
    <delete id="deleteStudentById">
        delete from student where id = #{id}
    </delete>

iud.jpg

3、参数

StudentDao.java

package com.ljessie.mybatisdemo.dao;

import com.ljessie.mybatisdemo.entity.Student;
import com.ljessie.mybatisdemo.entity.StudentResultMap;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

/**
 * #{}：可以获取map参数中或者pojo对象属性的值，以预编译的形式，将参数设置到sql语句中，可以防sql注入
 * ${}：可以获取map参数中或者pojo对象属性的值，取出的值直接拼接到sql语句中
 * 区别：
 * ==>  Preparing: select * from student where id = 3 and name = ?
 * ==> Parameters: Jessie(String)
 * <==    Columns: id, name, age
 * <==        Row: 3, Jessie, 22
 * <==      Total: 1
 * Student{id=3, name='Jessie', age=22}
 * 大多情况下，取参数的值都应该使用#{}
 *原生JDBC不支持占位符的地方，就可以使用${}取值
 *  例如from子句中，不支持?占位符，就可以使用${}动态设置查询哪张表
 *
 * #{}:
 */
public interface StudentDao {

     Student findStudentByID(int id);

     void addStudent(Student student);

     void updateStudent(Student student);

     void deleteStudentById(int id);

    /**
     * 当有多个参数时，需要添加注解@Param，在sql语句中，使用#{}获取到传入参数的值。
     * @param id
     * @param name
     * @return
     */
     Student findStudentByIdAndName(@Param("id")int id,@Param("name")String name);

    /**
     * 将传入的多个参数封装为一个map
     * @param map
     * @return
     */
     Student findStudentByMap(Map<String,Object> map);

    /**
     * 返回类型是一个集合
     * @return
     */
     List<Student> findAllStudents();

    /**
     * 返回一个map，key是列名，value是列名对应的值
     */
    Map<String,Object> getStudentByIdReturnMap(int id);

    /**
     * 返回多条记录封装一个Map<Integer,Student>：key是student的主键id，value是单条记录封装的Student对象
     * @MapKey("id")：告诉Mybatis封装这个map的时候，使用哪个属性作为map的key
     */
    @MapKey("id")
    Map<Integer,Student> getStudentsReturnMap();

    /**
     * 封装结果集ResultMap的使用
     * @param id
     * @return
     */
    StudentResultMap findStudentResultById(int id);


    List<Student> findStudentsByDepartment(int deptId);
}

StudentDao.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.ljessie.mybatisdemo.dao.StudentDao">
<!--    eviction：缓存的回收策略，默认LRU（最近最少使用）还有FIFO、SOFT、WEAK等
        flushInterval：缓存刷新间隔，缓存多长时间清空一次，默认不清空，单位为ms
        readOnly：是否只读，从缓存中获取数据的操作都是只读操作。默认是false
        size：缓存存放多少元素
        type：指定自定义缓存的类型，实现Cache接口，然后将全类名写在type里面。
        -->
    <cache eviction="LRU" flushInterval="600000" size="1024" readOnly="false"/>


<!--
ResultMap：自定义结果集映射规则
type：自定义规则的Java类型
id：方便引用，
-->
    <resultMap id="studentResultMap" type="com.ljessie.mybatisdemo.entity.StudentResultMap">
<!--
指定主键列的封装规则，也可以用result来定义主键，但是用id底层有优化
column：指定数据库的哪一列
property：指定对应的JavaBean的属性
-->
        <id column="id" property="idResult"></id>
<!--定义普通列封装规则-->
        <result column="name" property="nameResult"></result>
        <result column="age" property="ageResult"></result>
<!--        如果没有指定封装规则，则会自动封装，但是推荐把所有的列都写上-->

    </resultMap>





<!--    Student findStudentByID(String ID);-->
    <select id="findStudentByID" resultType="Student">
        select * from student where id = #{ID}
    </select>

<!--
void addStudent(Student student);
parameterType可以省略;
useGeneratedKeys="true"使用自增主键策略
keyProperty="id"获取到主键值以后，赋值给JavaBean的哪个属性
-->
    <insert id="addStudent" parameterType="Student" useGeneratedKeys="true" keyProperty="id">
        insert into student (name,age)
        values (#{name},#{age})
    </insert>
<!--    void updateStudent(Student student);-->
    <update id="updateStudent">
        update student
            set name = #{name},age = #{age}
            where id = #{id}
    </update>
<!--    void deleteStudentById(String id);-->
    <delete id="deleteStudentById">
        delete from student where id = #{id}
    </delete>

<!--    Student findStudentByIdAndName(@Param("id")int id,@Param("name")String name);-->
    <select id="findStudentByIdAndName" resultType="Student">
        select * from student where id = #{id} and name = #{name}
    </select>

<!--    Student findStudentByMap(Map<String,Object> map);-->
    <select id="findStudentByMap" resultType="Student">
        select * from ${tableName} where id = ${id} and name = #{name}
    </select>

<!--    List<Student> findAllStudents();-->
<!--    如果返回值是一个集合，要写集合中元素的类型-->
    <select id="findAllStudents" resultType="Student">
        select * from student
    </select>

<!--    Map<String,Object> getStudentByIdReturnMap(int id);-->
<!--    返回值类型是一个map-->
    <select id="getStudentByIdReturnMap" resultType="map">
        select * from student where id = #{id}
    </select>

<!--    Map<Integer,Student> getStudentsReturnMap();-->
    <select id="getStudentsReturnMap" resultType="Student">
        select * from student
    </select>

<!--    StudentResultMap findStudentResultById(int id);-->
    <select id="findStudentResultById" resultMap="studentResultMap">
        select * from student where id = #{id}
    </select>


<!--    List<Student> findStudentsByDepartment(int id);-->
    <select id="findStudentsByDepartment" resultType="Student">
        select * from student where department_id = #{deptId}
    </select>
</mapper>

测试代码

package com.ljessie.mybatisdemo;

import com.ljessie.mybatisdemo.dao.StudentDao;
import com.ljessie.mybatisdemo.entity.StudentResultMap;
import org.apache.ibatis.session.SqlSession;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Test {
    public static void main(String[] args) {
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        try{
            StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
//            Student student = new Student();
//            student.setName("张三");
//            student.setAge(25);
//            studentDao.addStudent(student);
//            System.out.println(student.getId());

//            Student student = studentDao.findStudentByID(1);
//            studentDao.deleteStudentById(1);
//            studentDao.updateStudent(new Student(2,"Bowen",28));


//            Student student = studentDao.findStudentByIdAndName(3,"Jessie");

//            Map<String,Object> parames = new HashMap<>();
//            parames.put("id",3);
//            parames.put("name","Jessie");
//            parames.put("tableName","student");
//            Student student = studentDao.findStudentByMap(parames);
//            System.out.println(student);

//            List<Student> allStudents = studentDao.findAllStudents();
//            System.out.println(allStudents);

//            Map<String, Object> map = studentDao.getStudentByIdReturnMap(2);

//            Map<Integer, Student> map = studentDao.getStudentsReturnMap();
//            System.out.println(map);
            StudentResultMap studentResultById = studentDao.findStudentResultById(3);
            System.out.println(studentResultById);
            sqlSession.commit();
        }finally {
            sqlSession.close();
        }
    }
}

3、结果映射

resultMap 元素是 MyBatis 中最重要最强大的元素。它可以让你从 90% 的 JDBC ResultSets 数据提取代码中解放出来，并在一些情形下允许你进行一些 JDBC 不支持的操作。实际上，在为一些比如连接的复杂语句编写映射代码的时候，一份 resultMap 能够代替实现同等功能的数千行代码。ResultMap 的设计思想是，对简单的语句做到零配置，对于复杂一点的语句，只需要描述语句之间的关系就行了。
新建department表

CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `department_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Department.java

package com.ljessie.mybatisdemo.entity;

import java.io.Serializable;
import java.util.List;

public class Department implements Serializable {
    private int id;
    private String departmentName;
    private List<Student> students;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public List<Student> getStudents() {
        return students;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", departmentName='" + departmentName + '\'' +
                ", students=" + students +
                '}';
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }

    public Department(int id, String departmentName) {
        this.id = id;
        this.departmentName = departmentName;
    }

    public Department() {
    }

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }
}

ResultMapDemo.java

package com.ljessie.mybatisdemo.dao;

import com.ljessie.mybatisdemo.entity.Student;

public interface ResultMapDemo {

    Student getStudentAndDepatmentById(int id);

    /**
     * 分步查询Student：先查出来student的departmentId，再根据departmentId去department表里查department
     * @param id
     * @return
     */
    Student getStudentByIdViaStepping(int id);
}

ResultMapDemo.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.ljessie.mybatisdemo.dao.ResultMapDemo">
<!--    =======================场景一：Student里面关联了一个Department属性=======================-->

<!--    联合查询1：级联属性封装结果集-->
    <resultMap id="studentWithDepartment" type="com.ljessie.mybatisdemo.entity.Student">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="age" property="age"></result>
        <result column="department_id" property="department.id"></result>
        <result column="department_name" property="department.departmentName"></result>
    </resultMap>

<!--    联合查询2：使用association封装联合对象-->
    <resultMap id="studentWithDepartment2" type="com.ljessie.mybatisdemo.entity.Student">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="age" property="age"></result>
<!--    association可以指定联合的javaBean对象
        property="department"：用来指定哪个属性是联合对象
        javaType：用来指定联合对象的类型-->
        <association property="department" javaType="com.ljessie.mybatisdemo.entity.Department">
            <id column="did" property="id"></id>
            <result column="department_name" property="departmentName"></result>
        </association>
    </resultMap>

<!--  Student有个department属性，在查询Student的同时，把department也封装到Student里面  -->
<!--    Student getStudentAndDepatmentById(int id);-->
    <select id="getStudentAndDepatmentById" resultMap="studentWithDepartment2">
        select s.id id,s.name name,s.age age,s.department_id department_id,d.id did,d.department_name department_name
            from student s,department d
            where s.id = #{id} and s.department_id = d.id
    </select>


<!--    使用association进行分步查询：-->
    <resultMap id="getStudentViaStepping" type="com.ljessie.mybatisdemo.entity.Student">
        <id property="id" column="id"></id>
        <result column="name" property="name"></result>
        <result column="age" property="age"></result>

<!--    select：表明当前属性是调用select指定的方法查询出来的结果
        column：指定将哪一列的值传递给select-->
        <association property="department"
                     select="com.ljessie.mybatisdemo.dao.DepartmentDao.findDepartmentById"
                     column="department_id"></association>


    </resultMap>
<!--Student getStudentByIdViaStepping(int id);-->
    <!--    延迟加载：只有在需要的时候，才会去数据库查询需要的信息。去mybatis-config.xml中配置两个setting属性。
            比如在代码中，没有用到department信息，将不会执行第二条sql语句；只有当用到的时候，才会加载-->
    <select id="getStudentByIdViaStepping" resultMap="getStudentViaStepping">
        select * from student where id = #{id}
    </select>

<!--==========================场景二：Department里面有个List<Student>属性，在departmentDao中演示======================-->

</mapper>

DepartmentDao.java

package com.ljessie.mybatisdemo.dao;

import com.ljessie.mybatisdemo.entity.Department;

public interface DepartmentDao {

    Department findDepartmentById(int id);

    /**
     * 查询department时候，带出来List<Student>
     * @param id
     * @return
     */
    Department findDepartmentWithStudents(int id);

    /**
     * 分步查询department和关联的List<Student>
     * @param id
     * @return
     */
    Department findDepartmentWithStudentsViaStepping(int id);
}

DepartmentDao.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.ljessie.mybatisdemo.dao.DepartmentDao">

    <cache/>
<!--    Department findDepartmentById(int id);-->
    <select id="findDepartmentById" resultType="Department">
        select * from department where id = #{id}
    </select>

<!--    嵌套结果集的方式，使用collection标签定义关联的集合属性-->

    <resultMap id="resultOfDepartmentWithStudents" type="com.ljessie.mybatisdemo.entity.Department">
        <id column="did" property="id"></id>
        <result column="department_name" property="departmentName"></result>

<!--    collection：定义关联集合的属性
        ofType：定义集合里面元素的类型-->
        <collection property="students" ofType="com.ljessie.mybatisdemo.entity.Student">
<!--            定义这个集合中，元素的封装规则-->
            <id column="id" property="id"></id>
            <result column="name" property="name"></result>
            <result column="age" property="age"></result>
        </collection>
    </resultMap>
<!--    Department findDepartmentWithStudents(int id);-->
    <select id="findDepartmentWithStudents" resultMap="resultOfDepartmentWithStudents">
        select d.id did, d.department_name, s.*
            from department d
            left join student s
            on d.id = s.department_id
            where d.id = #{id}
    </select>



<!--分步查询-->
<!--    扩展：多列的值，传递给第二步，作为参数时。
        需要将多列的值封装为一个map：column{key1 = value1,key2 = value2}
        fetchType="lazy"：表示使用延迟加载；fetchType="eager"：表示立刻加载-->
    <resultMap id="resultOfDepartmentWithStudentsViaStepping" type="com.ljessie.mybatisdemo.entity.Department">
        <id column="id" property="id"></id>
        <result column="department_name" property="departmentName"></result>
        <collection property="students"
                    select="com.ljessie.mybatisdemo.dao.StudentDao.findStudentsByDepartment"
                    column = "{deptId = id}"
                    fetchType="lazy">
<!--                    column="id"-->

        </collection>
    </resultMap>

<!--    Department findDepartmentWithStudentsViaStepping(int id);-->
    <select id="findDepartmentWithStudentsViaStepping" resultMap="resultOfDepartmentWithStudentsViaStepping">
        select * from department where id = #{id}
    </select>
</mapper>