MyBatis联合查询

参考https://www.cnblogs.com/yinjw/p/11757109.html

SpringBoot整合mybatis的两种实现方式

1. 采用@mapper注解,并在启动类中通过@MapperScan(value = “com.atguigu.springboot.mapper”)注解来扫描mapper文件

package com.atguigu.springboot.mapper;

import com.atguigu.springboot.bean.Department;
import org.apache.ibatis.annotations.*;


//指定这是一个操作数据库的mapper
@Mapper
public interface DepartmentMapper {

    @Select("select * from department where id=#{id}")
    public Department getDeptById(Integer id);

    @Delete("delete from department where id=#{id}")
    public int deleteDeptById(Integer id);

    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into department(department_name) values(#{departmentName})")
    public int insertDept(Department department);

    @Update("update department set department_name=#{departmentName} where id=#{id}")
    public int updateDept(Department department);
}

2. 在mapper.xml文件中编写SQL语句，并在application.yml配置文件中指定mapper.xml的位置
   EmployeeMapper.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.atguigu.springboot.mapper.EmployeeMapper">
   <!--    public Employee getEmpById(Integer id);

    public void insertEmp(Employee employee);-->
    <select id="getEmpById" resultType="com.atguigu.springboot.bean.Employee">
        SELECT * FROM employee WHERE id=#{id}
    </select>

    <insert id="insertEmp">
        INSERT INTO employee(lastName,email,gender,d_id) VALUES (#{lastName},#{email},#{gender},#{dId})
    </insert>

    <resultMap id="WithDeptResultMap" type="com.atguigu.springboot.bean.Employee">
        <id column="id" jdbcType="INTEGER" property="id"></id>
        <result column="lastName" jdbcType="VARCHAR" property="lastName"></result>
        <result column="gender" jdbcType="INTEGER" property="gender"></result>
        <result column="email" jdbcType="VARCHAR" property="email"></result>
        <result column="d_id" jdbcType="INTEGER" property="dId"></result>
        <!--封装联合查询的部门信息 -->
        <association property="department" javaType="com.atguigu.springboot.bean.Department">
            <id column="id" jdbcType="INTEGER" property="id"></id>
            <result column="departmentName" jdbcType="VARCHAR" property="departmentName"></result>
        </association>
    </resultMap>
    <select id="geEmps" resultMap="WithDeptResultMap">
        select e.* ,d.departmentName from employee e LEFT join department d on e.id=d.id
    </select>
</mapper>

application.yml的内容如下：

mybatis:
  # 指定全局配置文件位置
  config-location: classpath:mybatis/mybatis-config.xml
  # 指定sql映射文件位置
  mapper-locations: classpath:mybatis/mapper/*.xml

此时对应的EmployeeMapper.java文件只需要定义接口即可，内容如下：

package com.atguigu.springboot.mapper;

import com.atguigu.springboot.bean.Employee;

import java.util.List;

//1.注解版：@Mapper或者@MapperScan将接口扫描装配到容器中
//2.配置文件版：通过EmployeeMapper.xml配置文件定义sql语句，并在application.yml配置中指定指定映射文件的位置
public interface EmployeeMapper {

    public Employee getEmpById(Integer id);

    public void insertEmp(Employee employee);

    public List<Employee> geEmps();
}