• 基本介绍
• 环境
• insert
  • 返回主键
• delete
• update
• 参数处理
  • 单个参数
  • 多个参数
  • ${}
• select
  • 返回list
  • 映射
    • 自动映射
    • 自定义映射
      • association
        • 分步查询
        • 延迟加载
      • collection
        • 分步查询
    • 继承和引用ResultMap

基本介绍

环境

/*
 Navicat Premium Data Transfer

 Source Server         : school
 Source Server Type    : MySQL
 Source Server Version : 80022
 Source Host           : localhost:3306
 Source Schema         : school

 Target Server Type    : MySQL
 Target Server Version : 80022
 File Encoding         : 65001

 Date: 15/11/2021 19:49:16
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`  (
  `gradeId` int(0) NOT NULL AUTO_INCREMENT,
  `gradeName` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`gradeId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES (1, 'S1');
INSERT INTO `grade` VALUES (2, 'Y1');

SET FOREIGN_KEY_CHECKS = 1;

/*
 Navicat Premium Data Transfer

 Source Server         : school
 Source Server Type    : MySQL
 Source Server Version : 80022
 Source Host           : localhost:3306
 Source Schema         : school

 Target Server Type    : MySQL
 Target Server Version : 80022
 File Encoding         : 65001

 Date: 15/11/2021 19:49:31
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `stuId` int(0) NOT NULL AUTO_INCREMENT,
  `stuName` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `stuAge` int(0) NULL DEFAULT NULL,
  `stuBirth` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `gradeId` int(0) NOT NULL,
  PRIMARY KEY (`stuId`) USING BTREE,
  INDEX `gradeId`(`gradeId`) USING BTREE,
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`gradeId`) REFERENCES `grade` (`gradeId`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三', 18, '2010-10-02', 1);
INSERT INTO `student` VALUES (2, '李四', 18, '2002-10-02', 2);
INSERT INTO `student` VALUES (3, '王五', 16, '2005-10-02', 2);
INSERT INTO `student` VALUES (5, '赵六', 22, '2020-10-27 00:00:00.0', 1);
INSERT INTO `student` VALUES (6, '赵六', 22, '2020-10-27 00:00:00.0', 1);
INSERT INTO `student` VALUES (7, '田七', 22, '2021-05-18 17:13:40', 2);

SET FOREIGN_KEY_CHECKS = 1;

<?xml version="1.0" encoding="UTF-8" ?>



    
    
    
        
        
    

    
        
    

    
        
        
            
            
                
                
                
                
            
        
    

    
        
       
    

db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
db.username=root
db.password=123456
#dbconfig.properties

package com.fly.entity;

import java.util.Date;

/**
 * @author 26414
 */
public class Student {

  private Integer stuId;
  private String stuName;
  private Integer stuAge;
  private Date stuBirth;

  @Override
  public String toString() {
    return "Student{" +
            "stuId=" + stuId +
            ", stuName='" + stuName + '\'' +
            ", stuAge=" + stuAge +
            ", stuBirth=" + stuBirth +
            ", grade=" + grade +
            '}';
  }

  private Grade grade;

  public Integer getStuId() {
    return stuId;
  }

  public void setStuId(Integer stuId) {
    this.stuId = stuId;
  }

  public String getStuName() {
    return stuName;
  }

  public void setStuName(String stuName) {
    this.stuName = stuName;
  }

  public Integer getStuAge() {
    return stuAge;
  }

  public void setStuAge(Integer stuAge) {
    this.stuAge = stuAge;
  }

  public Date getStuBirth() {
    return stuBirth;
  }

  public void setStuBirth(Date stuBirth) {
    this.stuBirth = stuBirth;
  }

  public Grade getGrade() {
    return grade;
  }

  public void setGrade(Grade grade) {
    this.grade = grade;
  }
}

package com.fly.dao;

import com.fly.entity.Grade;

/**
 * @author 26414
 */
public interface GradeMapper {

  /**
   * 根据id查询年级
   * @param gradeId 年级id
   * @return gradeId对应的年级
   */
  Grade getGradeById(Integer gradeId);

}

insert

 /**
   * GradeMapper
   * 添加年级
   * @param grade 年级对象
   */
  void insert(Grade grade);

   
    
        insert into grade values(null,#{gradeName})
    

  @Test
  public void test4() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
      GradeMapper mapper = sqlSession.getMapper(GradeMapper.class);
      Grade grade = new Grade();
      grade.setGradeName("一年级");
      mapper.insert(grade);
      //手动提交事务
      sqlSession.commit();
    }
  }

返回主键

    
        insert into grade values(null,#{gradeName})
    

 @Test
  public void test4() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
      GradeMapper mapper = sqlSession.getMapper(GradeMapper.class);
      Grade grade = new Grade();
      grade.setGradeName("二年级");
      mapper.insert(grade);
      //手动提交事务
      sqlSession.commit();
      System.out.println("grade.getGradeId() = " + grade.getGradeId());
    }
  }

delete

 /**
   * GradeMapper
   * 根据id删除年级
   * @param gradeId 年级id
   */
  void deleteById(Integer gradeId);

   
    
        delete from grade where gradeId = #{gradeId}
    

 @Test
  public void test5() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
      GradeMapper mapper = sqlSession.getMapper(GradeMapper.class);
      mapper.deleteById(8);
      //手动提交事务
      sqlSession.commit();
    }
  }

update

 /**
   * GradeMapper
   * @param grade 年级对象
   */
  void updateById(Grade grade);

  
    
        update grade set gradeName = #{gradeName} where gradeId = #{gradeId}
    

 @Test
  public void test6() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
      GradeMapper mapper = sqlSession.getMapper(GradeMapper.class);
      Grade grade = new Grade();
      grade.setGradeId(6);
      grade.setGradeName("一年级...");
      mapper.updateById(grade);
      //手动提交事务
      sqlSession.commit();
    }
  }

参数处理

单个参数

MyBatis可直接使用这个参数，不需要经过任何处理。

多个参数

会被MyBatis重新包装成一个Map传入。Map的key是param1，param2，arg0，arg1…，值就是参数的值。

package com.fly.dao;

import com.fly.entity.Student;

/**
 * @author 26414
 */
public interface StudentMapper {

  /**
   * StudentMapper
   * 根据姓名和年级查询学生
   * @param stuName 学生姓名
   * @param stuAge 学生年龄
   * @return 学生
   */
  Student selectByNameAndAge(String stuName,Integer stuAge);

}

 
    
        select * from student where stuName = #{arg0} and stuAge = #{arg1}
    

  @Test
  public void test1() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
      StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
      Student student = mapper.selectByNameAndAge("张三", 18);
      System.out.println("student = " + student);
      //手动提交事务
      sqlSession.commit();
    }
  }

···xml

    select * from student where stuName = #{param1} and stuAge = #{param2}

![](https://img2020.cnblogs.com/blog/2276392/202111/2276392-20211115234705480-1575124065.png)
##命名参数
为参数使用@Param起一个名字，MyBatis就会将这些参数封装进map中，key就是我们自己指定的名字
```java
  /**
   * StudentMapper
   * 根据姓名和年级查询学生
   * @param stuName 学生姓名
   * @param stuAge 学生年龄
   * @return 学生
   */
  Student selectByNameAndAge(@Param("stuName") String stuName, @Param("stuAge") Integer stuAge);

 
    
        select * from student where stuName = #{stuName} and stuAge = #{stuAge}
    

${}

'#'{key}：获取参数的值，预编译到SQL中。安全。
${key}：获取参数的值，拼接到SQL中。有SQL注入问题。

select

返回list

 /**
   * GradeMapper
   * 查询所有年级
   * @return 所有年级
   */
  List getAll();

 
    
    
        select * from grade
    

  @Test
  public void test9() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
      GradeMapper mapper = sqlSession.getMapper(GradeMapper.class);
      List grades = mapper.getAll();
      System.out.println("grades = " + grades);
    }
  }

映射

自动映射

1）autoMappingBehavior默认是PARTIAL，开启自动映射的功能。唯一的要求是列名和javaBean属性名一致
2）如果autoMappingBehavior设置为null则会取消自动映射
3）数据库字段命名规范，POJO属性符合驼峰命名法，如A_COLUMN?aColumn，我们可以开启自动驼峰命名规则映射功能，mapUnderscoreToCamelCase=true。

自定义映射

association

/**
   * StudentMapper
   * 根据id查学生
   * @param stuId 学生id
   * @return 学生对象
   */
  Student selectById(@Param("stuId") Integer stuId);

 
        
        
        
        
        
            
            
        
    

    
        SELECT * FROM student s, grade g where stuId = #{stuId} and
            s.gradeId = g.gradeId
    

 @Test
  public void test2() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
      StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
      Student student = mapper.selectById(1);
      System.out.println("student = " + student);
    }
  }

分步查询

 /**
   * StudentMapper
   * 根据id查学生
   * @param stuId 学生id
   * @return 学生对象
   */
  Student selectByIdAndStep(@Param("stuId") Integer stuId);

 
        
        
        
        
        
    

    
        select * from student where stuId = #{stuId}
    

  @Test
  public void test3() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
      StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
      Student student = mapper.selectByIdAndStep(1);
      System.out.println("student = " + student);
    }
  }

延迟加载

collection

  /**
   * Grade
   */
  private List students;

  public List getStudents() {
    return students;
  }

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

  @Override
  public String toString() {
    return "Grade{" +
            "gradeId=" + gradeId +
            ", gradeName='" + gradeName + '\'' +
            ", students=" + students +
            '}';
  }

  /**
   *GradeMapper
   * 根据id查询年级
   * @param gradeId 年级id
   * @return 年级
   */
  Grade selectGradeById(Integer gradeId);

 
    
        
        
        
            
            
            
            
        
    

    
        select * from grade g LEFT JOIN student s on g.gradeId = s.gradeId where g.gradeId = #{gradeId}
    

  @Test
  public void test7() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
      GradeMapper mapper = sqlSession.getMapper(GradeMapper.class);
      Grade grade = mapper.selectGradeById(1);
      System.out.println("grade = " + grade);
    }
  }

分步查询

  /**
   * StudentMapper
   * 查询年级id为 gradeId 的所有学生
   * @param gradeId 年级id
   * @return 年级id为 gradeId 的所有学生
   */
  List selectByGradeId(Integer gradeId);

    
        select * from student where gradeId = #{gradeId}
    

  /**
   * GradeMapper
   * 根据id查询年级，分步查询
   * @param gradeId 年级id
   * @return 年级
   */
  Grade selectGradeByIdStep(Integer gradeId);

  
    
        
        
        
    

    
        select * from grade where gradeId = #{gradeId}
    

@Test
  public void test8() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
      GradeMapper mapper = sqlSession.getMapper(GradeMapper.class);
      Grade grade = mapper.selectGradeByIdStep(1);
      System.out.println("grade = " + grade);
    }
  }

继承和引用ResultMap

   
    
        
        
        
        
    

    
        
    
 
        SELECT * FROM student s, grade g where stuId = #{stuId} and
            s.gradeId = g.gradeId
    

@Test
  public void test2() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
      StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
      Student student = mapper.selectById(1);
      System.out.println("student = " + student);
      //手动提交事务
      sqlSession.commit();
    }
  }