Mybatis(第三部分)（Java学习笔记）

九.1 Lombok注解

@Getter and @Setter
@FieldNameConstants
@ToString
@EqualsAndHashCode
@AllArgsConstructor, @RequiredArgsConstructor and @NoArgsConstructor
@Log, @Log4j, @Log4j2, @Slf4j, @XSlf4j, @CommonsLog, @JBossLog, @Flogger, @CustomLog
@Data
@Builder
@SuperBuilder
@Singular
@Delegate
@Value
@Accessors
@Wither
@With
@SneakyThrows

九.2 使用步骤

1. 在IDEA中安装Lombok插件
2. 在项目中导入Lombok包：

<!--        lombok-->
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.26</version>
        </dependency>

3. 在实体类添加注解

@Data
@AllArgsConstructor//有参构造
@NoArgsConstructor//无参构造
public class User {
    private int id;
    private String name;
    private String password;
}

十.多对一处理

多对一：
多个学生对应一个老师
对于学生而言，关联···多个学生关联一个老师【多对一】
对于老师而言，集合···一个老师有很多学生【一对多】

1. 数据库

CREATE TABLE `teacher` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师'); 

CREATE TABLE `student` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  `tid` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (1, '小明', 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (2, '小红', 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (3, '小张', 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (4, '小李', 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (5, '小王', 1);

2. 导入依赖包

            <!--        lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>

3. 在bean包下创建实体类

@Data
public class Teacher {
    private int id;
    private String name;
}

@Data
public class Student {
    private int id;
    private String name;
    //学生需要关联一个老师
    private Teacher teacher;
}

4.创建Mapper接口

public interface StudentMapper {

}

public interface TeacherMapper {
    @Select("select *from teacher where id=#{tid}")
    Teacher getTeacher(@Param("tid") int id);
}

在resource目录的mybatis里面创建xml配置文件
TeacherMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--核心配置文件-->
<mapper namespace="com.example.mybatistest.dao.TeacherMapper">

</mapper>

StudentMapper

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--核心配置文件-->
<mapper namespace="com.example.mybatistest.dao.StudentMapper">

</mapper>

5. 绑定注册我们的Mapper接口和配置文件

<mapper class="com.example.mybatistest.dao.TeacherMapper"/>
        <mapper class="com.example.mybatistest.dao.StudentMapper"/>

6. 测试

package com.example.mybatistest;

import com.example.mybatistest.bean.User;
import com.example.mybatistest.dao.UserDao;
import com.example.mybatistest.dao.UserMapper;
import com.example.mybatistest.util.MybatisUntils;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.HashMap;
import java.util.List;

@SpringBootTest
class MybatisTestApplicationTests {

    @Test
    void contextLoads() {
//        获取用户数据列表
//        第一步 获取SqlSession对象
        SqlSession sqlSession = MybatisUntils.getSqlSession();
//        执行sql
//        方式一： getMapper
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        List<User> userList = userDao.getUserList();

        for (User user:userList
             ) {
            System.out.println(user);
        }
//        关闭sqlSession会话
        sqlSession.close();
    }

//    通过id查询用户对象
    @Test
    public void getUserById(){
        SqlSession sqlSession = MybatisUntils.getSqlSession();
        UserDao mapper= sqlSession.getMapper(UserDao.class);
        User user = mapper.getUserById(1);
        System.out.println(user);
        sqlSession.close();
    }
//    增加用户
    @Test
    void addUserTest(){
        SqlSession sqlSession = MybatisUntils.getSqlSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        User user01 = new User(4,"用户4","password4");
        mapper.addUser(user01);
        List<User> userList = mapper.getUserList();
        for (User user:userList
             ) {
            System.out.println(user);
        }
        sqlSession.close();
    }
//    测试map形式插入数据
     @Test
    void mapTest(){
    SqlSession sqlSession = MybatisUntils.getSqlSession();
    UserDao mapper = sqlSession.getMapper(UserDao.class);

    HashMap<String, Object> map = new HashMap<>();
    map.put("userid",5);
    map.put("username","用户5");
    map.put("password","password5");
    mapper.addUser2(map);
    sqlSession.commit();
    sqlSession.close();
}

// 测试分页
    @Test
public void getUserByLimit(){
    SqlSession sqlSession = MybatisUntils.getSqlSession();
    UserDao mapper = sqlSession.getMapper(UserDao.class);

    HashMap<String, Integer> map = new HashMap<>();
    map.put("startIndex",1);
    map.put("pageSize",2);
    List<User> list = mapper.getUserByLimit(map);
    for (User user : list) {
        System.out.println(user.toString());
    }

    sqlSession.close();
}
//测试RowBounds
@Test
    public void getRowBounds(){
        //RowBounds实现
        RowBounds rowBounds = new RowBounds(0, 2);

        SqlSession sqlSession = MybatisUntils.getSqlSession();
        List<User> userList = sqlSession.selectList("com.example.mybatistest.dao.UserDao.getRowBounds",null,rowBounds);
        for (User user : userList) {
            System.out.println(user);
        }
        sqlSession.close();
    }

//    测试
    @Test
    public void getUsers(){
        SqlSession sqlSession = MybatisUntils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.getUsers();
        for (User user : userList) {
            System.out.println(user);
        }
        sqlSession.close();
}
}

十.2 按照查询嵌套处理

select s.id,s.name,t.name from student s,teacher t where s.tid=t.id

<!--
        思路：
        1. 查询所有的学生信息
        2. 根据查询出来的学生的tid，寻找对应的老师    子查询
        -->
    <select id="getStudent" resultMap="StudentTeacher">
        select * from student;
    </select>
    <resultMap id="StudentTeacher" type="student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--复杂的属性，需要单独处理   对象：association 集合：collection -->
        <association property="teacher" column="tid" javaType="student" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" resultType="teacher">
        select * from teacher where id = #{tid}
    </select>

resultType后面的类型报错就先给该类起别名

十.3 按照结果嵌套处理

<!--    按照结果嵌套处理-->
    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.name tname
        from student s,teacher t where s.tid=t.id
    </select>
    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>

十一. 一对多处理

1. 对实体类处理

@Alias("teacher")
@Data
public class Teacher {
    private int id;
    private String name;
    //一个老师拥有多个学生
    private List<Student> students;
}

@Alias("student")
@Data
public class Student {
    private int id;
    private String name;
    //学生需要关联一个老师
    private int tid;
}

2. mapper和配置绑定

十一. 1 按照结果嵌套处理

<!--按结果嵌套查询-->
<select id="getTeacher" resultMap="TeacherStudent">
    select  s.id sid,s.name sname,t.name tname,t.id tid
    from mybatis.student s,mybatis.teacher t
    where s.tid=t.id and t.id=#{tid}
</select>
<resultMap id="TeacherStudent" type="Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="tname"/>
    <!--复杂的属性我们需要单独处理-->
    <!--对象:association-->
    <!-- 集合：collection -->
    <!--        javaType=""指定属性的类型 集合中的泛型信息，我们使用ofType获取-->
    <collection property="students" ofType="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="tid" column="tid"/>
    </collection>
</resultMap>

十一.2 按照查询嵌套处理

<!--子查询-->
    <select id="getTeacher2" resultMap="TeacherStudent2">
        select * from teacher where id = #{tid}
    </select>

    <resultMap id="TeacherStudent2" type="teacher">
        <collection property="students" javaType="ArrayList" ofType="student" select="TeacherStudentByTeacherId" column="id"/>
    </resultMap>

    <select id="TeacherStudentByTeacherId" resultType="student">
        select * from student where tid = #{tid}
    </select>

十一.3 小结

1. 关联-association 【多对一】
2. 集合-collection 【一对多】
3. javaType & ofType
   . javaType用来指定实体类中属性的类型
   . ofType用来指定映射到List或者集合中的pojo类型，泛型中的约束类型

注意点：

保证SQL的可读性，尽量保证通俗易懂
注意一对多和多对一中，属性名和字段的问题
如果问题不好排查错误，可以使用日志，建议使用log4j

面试重点

1. MySql引擎
2. InnoDB底层原理
3. 索引
4. 索引优化