• 基本介绍
• 环境
• if
• where
• set
• trim
• choose
• foreach
  • 批量查询
  • 批量保存
• 内置参数
• script
• bind
• 抽取可重用sql

基本介绍

环境

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 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: 18/11/2021 12:20:05
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `tea_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `tea_age` int(0) NULL DEFAULT NULL,
  `tea_email` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, 'tom', 26, 'tom@qq.com');
INSERT INTO `teacher` VALUES (2, 'lili', 22, 'lili@qq.com');
INSERT INTO `teacher` VALUES (3, 'lucy', 22, 'lucy@qq.com');
INSERT INTO `teacher` VALUES (4, 'lisi', 22, 'lisi@qq.com');

SET FOREIGN_KEY_CHECKS = 1;

package com.fly.entity;

/**
 * @author 26414
 */
public class Teacher {
  
  private Integer id;
  private String teaName;
  private Integer teaAge;
  private String teaEmail;

  @Override
  public String toString() {
    return "Teacher{" +
            "id=" + id +
            ", teaName='" + teaName + '\'' +
            ", teaAge=" + teaAge +
            ", teaEmail='" + teaEmail + '\'' +
            '}';
  }

  public Integer getId() {
    return id;
  }

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

  public String getTeaName() {
    return teaName;
  }

  public void setTeaName(String teaName) {
    this.teaName = teaName;
  }

  public Integer getTeaAge() {
    return teaAge;
  }

  public void setTeaAge(Integer teaAge) {
    this.teaAge = teaAge;
  }

  public String getTeaEmail() {
    return teaEmail;
  }

  public void setTeaEmail(String teaEmail) {
    this.teaEmail = teaEmail;
  }
}

package com.fly.dao;

/**
 * @author 26414
 */
public interface TeacherMapper {
}

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

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


    
    

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



    
    
    
        
        
        
        
    

    
        
    

    
        
        
            
            
                
                
                
                
            
        
    

    
        
    

if

  /**
   * TeacherMapper
   * if标签
   * @param teacher  老师对象
   * @return  符合条件的数据
   */
  List getTeaIf(Teacher teacher);

 
    
    
        select * from teacher where
            
                id = #{id}
            
            
                and tea_age = #{teaAge}
            
    

 @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()) {
      TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
      Teacher teacher = new Teacher();
      teacher.setId(1);
      List teachers = mapper.getTeaIf(teacher);
      System.out.println("teachers = " + teachers);
    }
  }

where

刚才的查询语句还有点问题，如果id没有带，sql语句为：select * from teacher where and tea_age = ?，这样肯定就会报错

  
    
    
        select * from teacher
           
               
                   id = #{id}
               
               
                   and tea_age = #{teaAge}
               
           
    

set

 /**
   * TeacherMapper
   * set标签
   * @param teacher 老师对象
   */
  void updateTeaSet(Teacher teacher);

    
        update teacher
        
            
                tea_name = #{teaName},
            
            
                tea_age = #{teaAge},
            
            
                tea_email = #{teaEmail}
            
        
        where id = #{id}
    

trim

如果and放在语句的后面，where标签解决不了，可以使用trim标签

 /**
   * TeacherMapper
   * trim标签
   * @param teacher 老师对象
   * @return 符合条件的数据
   */
  List getTeaTrim(Teacher teacher);

 
    
        select * from teacher
        
            
                id = #{id} and
            
            
                tea_age = #{teaAge}
            
        
    

choose

 /**
   * TeacherMapper
   * choose标签
   * @param teacher 老师对象
   * @return 符合条件的数据
   */
  List getTeaChoose(Teacher teacher);

    
        select * from teacher
        
            
                
                    id = #{id}
                
                
                    tea_age = #{teaAge}
                
                
                    1=1
                
            
        
    

可以看到，id和age都有值，但是只会拼接id

foreach

批量查询

/**
   * TeacherMapper
   * 查询id在集合中的老师
   * @param list id集合
   * @return id在集合中的老师
   */
  List getTeachersForeach(List list);

 
    
        select * from teacher
        
            #{item}
        
    

批量保存

 /**
   * TeacherMapper
   * 批量添加
   * @param teachers 老师集合
   */
  void addTeachers(@Param("teachers") List teachers);

  
    
        insert into teacher(tea_name,tea_age,tea_email) values
        
            (#{teacher.teaName},#{teacher.teaAge},#{teacher.teaEmail})
        
    

内置参数

script

bind

抽取可重用sql

 
    
    
        tea_name,tea_age,tea_email
    
    
    
        select  from teacher
        
            #{item}