Mybatis学习笔记

Mybatis入门

Mybatis简介

官网：https://mybatis.org/mybatis-3/zh/index.html

入门程序

查询user表中数据

mapper接口

package com.itheima.mapper;

import com.itheima.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

//运行时，会自动生成该接口的实现类对象（动态代理，代理对象），并且将该对象交给IOC容器管理
@Mapper
public interface UserMapper {

//    查询sql语句
    @Select("select * from user")
    public List<User> list();

}

实体User类

package com.itheima.pojo;

public class User {
    private Integer id;
    private String name;
    private Short age;
    private Short gender;
    private String phone;

    public User(Integer id, String name, Short age, Short gender, String phone) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.gender = gender;
        this.phone = phone;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Short getAge() {
        return age;
    }

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

    public Short getGender() {
        return gender;
    }

    public void setGender(Short gender) {
        this.gender = gender;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

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

数据库配置信息

application.properties文件

#驱动类名称
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#数据库连接的url
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis
#连接数据库的用户名
spring.datasource.username=root
#连接数据库的密码
spring.datasource.password=1234

测试类

package com.itheima;

import com.itheima.mapper.UserMapper;
import com.itheima.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

//Springboot整合单元测试的注解
@SpringBootTest
class SpringbootMybatisQuickstartApplicationTests {

    @Test
    void contextLoads() {
    }

//    依赖注入
    @Autowired
    private UserMapper userMapper;

    @Test
    public void testListUser() {
        List<User> userList = userMapper.list();
        userList.stream().forEach(user -> {
            System.out.println(user);
        });
    }

}

运行结果

JDBC介绍（不需要掌握）

数据库连接池

德鲁伊：https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

lombok

lombok是一个实用的Java类库，能通过注解的形式自动生成构造器、getter/setter、equals、hashcode、toString等方法，并可以自动化生成日志变量，简化java开发、提高效率。

Mybatis基础操作

删除

package com.itheima.mapper;

import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

//Mapper接口
@Mapper
public interface EmpMapper {

    //    删除员工
    @Delete("delete from emp where id = #{id}")
//    void delete(Integer id);
//    返回删除几条数据
    int delete(Integer id);
}

package com.itheima;

import com.itheima.mapper.EmpMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

//测试类
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
//    防止标红 required
    @Autowired(required = false)
    private EmpMapper empMapper;

    @Test
    void contextLoads() {
        empMapper.delete(17);
    }
}

SQL注入是通过操作输入的数据来修改事先定义好的SQL语句，以达到执行代码对服务器进行攻击的方法。

增添

实体类

package com.itheima.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.time.LocalDate;
import java.time.LocalDateTime;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp {
    private Integer id;
    private String username;
    private String password;
    private String name;
    private Short gender;
    private String image;
    private Short job;
    private LocalDate entrydate;
    private Integer deptId;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
}

接口

package com.itheima.mapper;

import com.itheima.pojo.Emp;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

//Mapper接口
@Mapper
public interface EmpMapper {
    
//    添加员工
    @Insert("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})")
    void insert(Emp emp);

}

测试接口

package com.itheima;

import com.itheima.mapper.EmpMapper;
import com.itheima.pojo.Emp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.time.LocalDate;
import java.time.LocalDateTime;

//测试类
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
//    防止标红 required
    @Autowired(required = false)
    private EmpMapper empMapper;
    @Test
    void testAdd() {
//        创建一个实体类emp
        Emp emp = new Emp();
        emp.setUsername("Tom3");
        emp.setName("汤姆3");
        emp.setImage("1.jpg");
        emp.setGender((short)1);
        emp.setJob((short)1);
        emp.setEntrydate(LocalDate.of(2000,1,1));
        emp.setCreateTime(LocalDateTime.now());
        emp.setUpdateTime(LocalDateTime.now());
        emp.setDeptId(1);

        empMapper.insert(emp);
    }

}

主键返回

描述：在数据添加成功后，需要获取插入数据库数据的主键。如：添加套餐数据时，还需要维护套餐菜品关系表数据。

更新

Mapper接口

package com.itheima.mapper;

import com.itheima.pojo.Emp;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

//Mapper接口
//运行时，会自动生成该接口的实现类对象（动态代理，代理对象），并且将该对象交给IOC容器管理
@Mapper
public interface EmpMapper {
//    修改员工
    @Update("update emp set username = #{username}, name = #{name}, gender = #{gender}, image = #{image}, job = #{job}, " +
            "entrydate = #{entrydate}, dept_id = #{deptId}, update_time = #{updateTime} where id = #{id}")
    void update(Emp emp);
}

测试类

package com.itheima;

import com.itheima.mapper.EmpMapper;
import com.itheima.pojo.Emp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.time.LocalDate;
import java.time.LocalDateTime;

//测试类
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
//    依赖注入
//    防止标红 required
    @Autowired(required = false)
    private EmpMapper empMapper;

    @Test
    void testUpdate() {
        Emp emp = new Emp();
        emp.setId(22);
        emp.setUsername("更新操作");
        emp.setName("汤姆666");
        emp.setImage("1.jpg");
        emp.setGender((short)1);
        emp.setJob((short)1);
        emp.setEntrydate(LocalDate.of(2000,1,1));
        emp.setUpdateTime(LocalDateTime.now());
        emp.setDeptId(2);

        empMapper.update(emp);
    }
}

查询

Mapper接口

package com.itheima.mapper;

import com.itheima.pojo.Emp;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

//Mapper接口
//运行时，会自动生成该接口的实现类对象（动态代理，代理对象），并且将该对象交给IOC容器管理
@Mapper
public interface EmpMapper {
//    查询员工
    @Select("select * from emp where id = #{id}")
    Emp selectById(Integer id);
}

测试类

package com.itheima;

import com.itheima.mapper.EmpMapper;
import com.itheima.pojo.Emp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.time.LocalDate;
import java.time.LocalDateTime;

//测试类
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
//    依赖注入
//    防止标红 required
    @Autowired(required = false)
    private EmpMapper empMapper;
    
    @Test
    void testSelect() {
        Emp emp = empMapper.selectById(22);
        System.out.println(emp);
    }
}

运行结果在下面

数据封装

问题：数据库中字段名与实体类中属性名不一致

解决方案

• 方案一：起别名

@Select("select id, username, password, name, gender, image, job, entrydate, " +
        "dept_id deptId, create_time createTime, update_time updateTime from emp where id = #{id}")
Emp selectById(Integer id);

• 方案二：@Results({@Result()})注解

@Results({
        @Result(column = "dept_id", property = "deptId"),
        @Result(column = "create_time", property = "createTime"),
        @Result(column = "update_time", property = "updateTime")
})
@Select("select * from emp where id = #{id}")
Emp selectById(Integer id);

• 方案三：开启驼峰命名

#开启驼峰命名用于主句封装，前提是数据库字段明明严格采用下划线命名并且实体类属性名采用驼峰明明
mybatis.configuration.map-underscore-to-camel-case=true

然后直接select

传递多个参数

Mapper接口，多个参数要写@Param注解

package com.itheima.mapper;

import com.itheima.pojo.Emp;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.util.List;

//Mapper接口
//运行时，会自动生成该接口的实现类对象（动态代理，代理对象），并且将该对象交给IOC容器管理
@Mapper
public interface EmpMapper {
//    单个参数查询
//    用$代表字串拼接
//    @Select("select * from emp where name like '%${name}%'" +
//            " order by update_time desc ")
//    List<Emp> selectByInfo(String name);

//    多参数要@Param注释
    @Select("select * from emp where name like concat('%', #{name}, '%') and gender = #{gender} and " +
            "entrydate between #{begin} and #{end} order by update_time desc ")
//    List<Emp> selectByInfo(String name, Short gender, LocalDate begin , LocalDate end);
    List<Emp> selectByInfo(@Param("name")String name, @Param("gender")Short gender, @Param("begin")LocalDate begin , @Param("end")LocalDate end);

//    或者多个参数封装到对象里
    @Select("select * from emp where name like concat('%', #{name}, '%') and gender = #{gender} and entrydate >= #{entrydate} order by update_time desc")
    List<Emp> selectMulti(Emp emp);
}

package com.itheima;

import com.itheima.mapper.EmpMapper;
import com.itheima.pojo.Emp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

//测试类
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
//    依赖注入
//    防止标红 required
    @Autowired(required = false)
    private EmpMapper empMapper;
    
    @Test
    void testSelectByInfo() {
//        测试查找单个参数，带有模糊匹配
//        List<Emp> list = empMapper.selectByInfo((short) 1, LocalDate.of(2010, 1, 1), LocalDate.of(2020, 1, 1));
//        List<Emp> empList = empMapper.selectByInfo("张");
//        测试查找多个参数
        List<Emp> empList = empMapper.selectByInfo("张", (short) 1, LocalDate.of(2010, 1, 1), LocalDate.of(2020, 1, 1));
        System.out.println(empList);
        System.out.println(empList.size());
    }

//    测试查找多个参数
    @Test
    void testSelectMulti() {
        Emp emp = new Emp();
        emp.setName("张");
        emp.setGender((short) 1);
        emp.setEntrydate(LocalDate.of(2010, 1, 1));
        List<Emp> list = empMapper.selectMulti(emp);
        System.out.println(list);
    }
}

可能存在的问题，Mapper传参要写@Param注解，否则传参失败报错

XML映射文件

三点规范：

• XML映射文件的名称与Mapper接口名称一致，并且将XML映射文件和Mapper接口放置在相同包下（同包同名）。

• XML映射文件的namespace属性为Mapper接口全限定名一致。

• XML映射文件中sql语句的id与Mapper 接口中的方法名一致，并保持返回类型一致。

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">
<!--    resultType:单条记录所封装的类型的全限定类名即Emp类-->
    <select id="selectByInfo" resultType="com.itheima.pojo.Emp">
        select * from emp where name like concat('%', #{name}, '%') and gender = #{gender} and entrydate between #{begin} and #{end} order by update_time desc
    </select>

</mapper>

Mapper中的XML映射

package com.itheima.mapper;

import com.itheima.pojo.Emp;
import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.util.List;

//Mapper接口
//运行时，会自动生成该接口的实现类对象（动态代理，代理对象），并且将该对象交给IOC容器管理
@Mapper
public interface EmpMapper {
//    xml映射
    List<Emp> selectByInfo(@Param("name")String name, @Param("gender")Short gender, @Param("begin")LocalDate begin , @Param("end")LocalDate end);
}

package com.itheima;

import com.itheima.mapper.EmpMapper;
import com.itheima.pojo.Emp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

//测试类
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
//    依赖注入
//    防止标红 required
    @Autowired(required = false)
    private EmpMapper empMapper;

    @Test
    void testSelectByInfo() {
//        测试查找单个参数，带有模糊匹配
//        List<Emp> list = empMapper.selectByInfo((short) 1, LocalDate.of(2010, 1, 1), LocalDate.of(2020, 1, 1));
//        List<Emp> empList = empMapper.selectByInfo("张");
//        测试查找多个参数
        List<Emp> empList = empMapper.selectByInfo("张", (short) 1, LocalDate.of(2010, 1, 1), LocalDate.of(2020, 1, 1));
        System.out.println(empList);
        System.out.println(empList.size());
    }
}

动态SQL

<if> 和 <where>

where标签可以自动删除语句开头的and

注意SQL语句中的and在每个if开头不可省略

<?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">

<!--    动态查询-->
    <!--    resultType:单条记录所封装的类型的全限定类名即Emp类-->
        <select id="selectByInfo" resultType="com.itheima.pojo.Emp">
            select * from emp
            <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>
            </where>
            order by update_time desc
        </select>
</mapper>

mapper

package com.itheima.mapper;

import com.itheima.pojo.Emp;
import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.util.List;

//Mapper接口
//运行时，会自动生成该接口的实现类对象（动态代理，代理对象），并且将该对象交给IOC容器管理
@Mapper
public interface EmpMapper {
//    xml映射
//    动态查询
    List<Emp> selectByInfo(@Param("name")String name, @Param("gender")Short gender, @Param("begin")LocalDate begin , @Param("end")LocalDate end);
}

test测试类

package com.itheima;

import com.itheima.mapper.EmpMapper;
import com.itheima.pojo.Emp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

//测试类
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
//    依赖注入
//    防止标红 required
    @Autowired(required = false)
    private EmpMapper empMapper;

    @Test
    void testSelectByInfo() {

//        动态SQL
//        List<Emp> empList = empMapper.selectByInfo("张", (short) 1, null, null);
//        List<Emp> empList = empMapper.selectByInfo("张", null, null, null);
        List<Emp> empList = empMapper.selectByInfo(null, null, null, null);
        System.out.println(empList);
    }
}

<set>

自动删除语句最后的逗号

动态更新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="updateDynamic">
        update emp
        <set>
            <if test="username != null">username = #{username},</if>
            <if test="name != null">name = #{name},</if>
            <if test="gender != null">gender = #{gender},</if>
            <if test="image != null">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>
</mapper>

mapper

package com.itheima.mapper;

import com.itheima.pojo.Emp;
import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.util.List;

//Mapper接口
//运行时，会自动生成该接口的实现类对象（动态代理，代理对象），并且将该对象交给IOC容器管理
@Mapper
public interface EmpMapper {
//    动态修改
    void updateDynamic(Emp emp);
}

测试类

package com.itheima;

import com.itheima.mapper.EmpMapper;
import com.itheima.pojo.Emp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

//测试类
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
//    依赖注入
//    防止标红 required
    @Autowired(required = false)
    private EmpMapper empMapper;

    //    案例：动态更新
    @Test
    void testUpdateDynamic() {
//        封装的对象属性是根据每一条SQL语句set，不灵活，如果缺少了某条会直接赋值null
        Emp emp = new Emp();
        emp.setId(26);
        emp.setUsername("动态更新");
        emp.setName("动态更新666");
//        emp.setImage("1.jpg");
        emp.setGender((short)2);
        emp.setJob((short)1);
//        emp.setEntrydate(LocalDate.of(2000,1,1));
        emp.setUpdateTime(LocalDateTime.now());
        emp.setDeptId(2);

//        empMapper.update(emp);
//        动态更新
        empMapper.updateDynamic(emp);
    }
}

<foreach>

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">

<!--    批量删除-->
<!--
    collection: 遍历的集合
    item: 遍历出来的元素
    separator: 分隔符
    open: 遍历开始前拼接的SQL片段
    close: 遍历结束后拼接的SQL片段
-->
    <delete id="deleteByIds">
        delete from emp where id in
        <foreach collection="ids" item="id" separator=", " open="(" close=")">
            #{id}
        </foreach>
    </delete>

</mapper>

mapper

package com.itheima.mapper;

import com.itheima.pojo.Emp;
import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.util.List;

//Mapper接口
//运行时，会自动生成该接口的实现类对象（动态代理，代理对象），并且将该对象交给IOC容器管理
@Mapper
public interface EmpMapper {
//    xml映射
//    批量删除，返回删除几条数据
    int deleteByIds(@Param("ids") List<Integer> ids);
}

test测试类

package com.itheima;

import com.itheima.mapper.EmpMapper;
import com.itheima.pojo.Emp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Arrays;
import java.util.List;

//测试类
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
//    依赖注入
//    防止标红 required
    @Autowired(required = false)
    private EmpMapper empMapper;

    @Test
    void testDeleteByIds() {
//        工具类Arrays的asList方法创建集合
        List<Integer> ids = Arrays.asList(13, 14, 15);
        int cnt = empMapper.deleteByIds(ids);
        System.out.println(cnt);
    }
}

<sql>和<include>

• ：定义可重用的 SQL 片段。

• ：通过属性refid，指定包含的sql片段。

以动态查询为例

<?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">

<!--    sql片段-->
    <sql id="commonSelect">select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from emp</sql>

<!--    动态查询-->
        <select id="selectByInfo" resultType="com.itheima.pojo.Emp">
            <!--    引入sql片段-->
            <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>
            </where>
            order by update_time desc
        </select>
</mapper>