Mybatis应用

• mybatis-spring-boot-starter
  • 多表关联

mybatis-spring-boot-starter

pom.xml

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.0</version>
</dependency>
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.10</version>
</dependency>

application.yml

spring:
  datasource:
	driver-class-name: com.mysql.cj.jdbc.Driver
	url: jdbc:mysql://localhost:3306/xxx?serverTimezone=GMT
	username: root
	password: 123456
	type: com.zaxxer.hikari.HikariDataSource #连接池的类型，可以省略
mybatis:
  configuration:
	map-underscore-to-camel-case: true #是否启动驼峰标识
	mapper-locations: classpath:mapper/*.xml #定义mapper映射文件的位置
	type-aliases-package: com.lxw.entity #定义别名包的位置

• 扫描包：启动类上加 @MapperScan("com.lxw.mapper")

EmployeeMapper.java

import com.lxw.homework.po.Employee;
import org.apache.ibatis.annotations.Param;
import java.util.List;

public interface EmployeeMapper {

    //返回所有
    List<Employee> list();
    //根据firstName和lastName进行模糊查询
    List<Employee> findByName(@Param("firstName") String
                              firstName,@Param("lastName") String lastName);
    //根据主键查询
    Employee findById(Integer employeeId);
    //根据薪水范围查询
    List<Employee> findBySalary(@Param("begin") int
                                begin,@Param("end") int end);

    //查询最高薪资的员工
    Employee findMaxSalary();
    //获取每个部门的平均薪水
    List<Integer> findAvgSalaryByDep();
    //根据分页参数查询
    List<Employee> page(@Param("currentPage") int
                        currentPage,@Param("pageSize") int pageSize);
    //增加一个员工，并返回该员工的自增id
    int add(Employee e);
    //修改员工
    int update(Employee e);
    //根据主键删除
    int delete(Integer id);
}

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.lxw.homework.dao.EmployeeMapper">
    <!--定义实体和表之间字段映射关系-->
    <resultMap id="myEmployees" type="Employee" autoMapping="false">
        <id column="EMPLOYEE_ID" property="employeeId"/>   
        <result column="FIRST_NAME" property="firstName"/>   
        <result column="LAST_NAME" property="lastName"/>   
        <result column="EMAIL" property="email"/>   
        <result column="PHONE_NUMBER" property="phoneNumber"/>   
        <result column="HIRE_DATE" property="hireDate"/>   
        <result column="JOB_ID" property="jobId"/>   
        <result column="SALARY" property="salary"/>   
        <result column="COMM" property="comm"/>   
        <result column="MANAGER_ID" property="managerId"/>   
        <result column="DEPARTMENT_ID" property="departmentId"/>
    </resultMap>

    <select id="list" resultMap="myEmployees">
        select * from t_employees
    </select>

    <select id="findByName" resultMap="myEmployees">
        select * from t_employees where FIRST_NAME like concat('%',#
        {firstName},'%') and LAST_NAME like concat('%',#{lastName},'%')
    </select>

    <select id="findById" resultMap="myEmployees">
        select * from t_employees where EMPLOYEE_ID=#{employeeId};
    </select>

    <select id="findBySalary" resultMap="myEmployees">
        select * from t_employees where SALARY between #{begin} and #
        {end}
    </select>

    <select id="findMaxSalary" resultMap="myEmployees">
        select * from t_employees order by SALARY desc limit 1
    </select>

    <select id="findAvgSalaryByDep" resultType="int">
        select avg(SALARY) from t_employees group by DEPARTMENT_ID
    </select>

    <select id="page" resultMap="myEmployees">
        select * from t_employees limit (#{currentPage}-1)*#
        {pageSize},#{pageSize}
    </select>

    <!-- 主键回填 -->
    <insert id="add" parameterType="Employee">
        <selectKey keyProperty="employeeId" keyColumn="EMPLOYEE_ID"
                   resultType="int" order="AFTER">
            select LAST_INSERT_ID()
        </selectKey>
        insert into t_employees values(null,#{firstName},#{lastName},#
        {email},#{phoneNumber},#{hireDate},#{jobId},#{salary},#{comm},#
        {managerId},#{departmentId})
    </insert>

    <update id="update" parameterType="Employee">
        update t_employees set FIRST_NAME=#{firstName},LAST_NAME=#
        {lastName},EMAIL=#{email},PHONE_NUMBER=#{phoneNumber},HIRE_DATE=#
        {hireDate},JOB_ID=#{jobId},SALARY=#{salary},COMM=#
        {comm},MANAGER_ID=#{managerId},DEPARTMENT_ID=#{departmentId}
        where EMPLOYEE_ID=#{employeeId}
    </update>

    <delete id="delete" parameterType="int">
        delete from t_employees where EMPLOYEE_ID=#{employeeId}
    </delete>
</mapper>

多表关联

<!--namespace命名空间表示限定具体的接口类型，需要提供该接口的全类型-->
<mapper namespace="">
    <!--定义实体和表之间字段映射关系-->
    <resultMap id="xxx" type="数据类型" autoMapping="false">
        <!--主键映射：column:表的字段名 property:实体的属性名-->
        <id column=""property=""/>
        <result column="" property=""/>
        <result column="" property=""/>
        <result column="" property=""/>

        <!--1对1关系中互相设置一个对象-->
        <association property="属性名" javaType="类">
            <id property="" column=""/>
            <result property="" column=""/>
            <result property="" column=""/>
            ...
        </association>

        <!--1对多关系:1中设置一个集合-->
        <collection property="属性名" ofType="集合中对象类型">
            <id column="" property=""/>
            <result column="" property=""/>
            <result column="" property=""/>
            ...
        </collection>
    </resultMap>

    <sql id="fields">
        <!--定义SQL片段-->
    </sql>

    <select id="方法名" resultMap="xx">
        select <include refid="fields"/> from 表名
        <!--WHERE条件-->
        <where>
            <if test="字段1 != null">
                字段1 = #{值1}
            </if>
            <if test="字段2 != null">
                and 字段2 = #{值2}
            </if>
        </where>

        <!--FOREACH条件-->
        select * from 表名 where 字段 in
        <foreach collection="array" open="(" close=")" separator=","item="a">
            #{a}
        </foreach>
    </select>

    <update id="" parameterType="">
        update 表名
        <!--SET条件-->
        <set>
            <if test="字段1 != null">
                字段1 = #{值1},
            </if>
            <if test="字段2 != null">
                字段2 = #{值2}
            </if>
        </set>
        where 字段3 = #{值3};
    </update>

</mapper>

参数	说明	取值
collection	类型	list,array,map
open	起始符号	(
close	结束符号	)
separator	分隔符	,
item	当前项	名称任意，在循环中通过#{名称}去取值
index	遍历的下标	从0开始