【MyBatis】MyBatis基础教程(7)

前言：

本文内容：多对一处理、一对多处理、动态SQL环境搭建

推荐免费MyBatis基础教程视频：【狂神说Java】Mybatis最新完整教程IDEA版通俗易懂_哔哩哔哩_bilibili

多对一处理

StudentMapper.java

package com.jokerdig.mapper;

import com.jokerdig.pojo.Student;

import java.util.List;

/**
 * @author Joker大雄
 * @data 2022/4/30 - 15:38
 **/
public interface StudentMapper {
    // 查询所有学生信息以及对应老师的信息
    List<Student> getStudent();
    List<Student> getStudent2();
}

按照查询嵌套处理

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.jokerdig.mapper.StudentMapper">
<!--    查询学生信息和对应的老师-->
    <select id="getStudent" resultMap="StudentTeacher">
        select * from student
    </select>
    <!--        复杂的属性  需要单独处理-->
    <!--        对象用association  集合用collection-->
    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
<!--    查询老师-->
    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id = #{id}
    </select>
</mapper>

按照结果嵌套处理

SQL中的连表查询

<!--    按照结果嵌套处理-->
<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>

一对多处理

复制刚才的项目，修改实体类

Student.java

package com.jokerdig.pojo;

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

/**
 * @author Joker大雄
 * @data 2022/4/30 - 15:37
 **/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
    private int id;
    private String name;
    private int tid;
}

Teacher.java

package com.jokerdig.pojo;

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

import java.util.List;

/**
 * @author Joker大雄
 * @data 2022/4/30 - 15:37
 **/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Teacher {
    private int id;
    private String name;
    // 一对多 一个老师有多个学生
    private List<Student> students;
}

搭建好之后简单测试

我们发现，学生信息为空

....
Opening JDBC Connection
Created connection 597190999.
==>  Preparing: select * from teacher
==> Parameters: 
<==    Columns: id, name
<==        Row: 1, 张三老师
<==        Row: 2, 李四老师
<==      Total: 2
Teacher(id=1, name=张三老师, students=null)
Teacher(id=2, name=李四老师, students=null)
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@23986957]
Returned connection 597190999 to pool.

Process finished with exit code 0

解决一对多问题

TeacherMapper.java

package com.jokerdig.mapper;

import com.jokerdig.pojo.Teacher;
import org.apache.ibatis.annotations.Param;

/**
 * @author Joker大雄
 * @data 2022/4/30 - 15:38
 **/
public interface TeacherMapper {

    // 获取指定老师下所有学生的信息
    Teacher getTeacher(@Param("tid") int id);
}

按照结果嵌套处理

<?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.jokerdig.mapper.TeacherMapper">
<!--    解决一对多问题-->
    <!--按结果嵌套-->
    <select id="getTeacher" resultMap="TeacherStudent">
        select s.id sid,s.name sname,t.name tname,t.id tid from teacher t,student s where t.id = s.tid and t.id=#{tid}
    </select>
    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
<!--        一个老师对应多个学生 这里要用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>
</mapper>

按照查询嵌套处理

<!--按查询嵌套-->
    <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="getStudent" column="id"/>
    </resultMap>
    <select id="getStudent" resultType="Student">
        select * from student where tid = #{tid}
    </select>

小结

• 关联 - association(多对一)
• 集合 - collection(一对多)
• javaType ofType
  • javaType 指定实体类属性的类型
  • ofType 指定映射到List或集合中的pojo，泛型中的约束类型

拓展

面试高频

• Mysql引擎
• InnoDB底层
• 索引、索引优化

动态SQL环境搭建

动态SQL：动态SQL就是根据不同的条件生辰不同的SQL语句

学习的标签

• if
• choose (when, otherwise)
• trim (where, set)
• foreach

搭建环境

1. 导包

2. 创建SQL

   create table `blog`(
   	`id` varchar(50) not null comment '博客id',
   	`title` varchar(100) not null comment '博客标题',
   	`author` varchar(30) not null comment '博客作者',
   	`create_time` datetime not null comment '创建时间',
   	`views` int(30) not null comment '浏览量'
   )ENGINE=Innodb DEFAULT CHARSET=utf8
   
   -- 插入数据
   insert into blog(id,title,author,create_time,views)values(1,'Hey,Joker','JokerDaxiong','2021-1-13',1000);
   insert into blog(id,title,author,create_time,views)values(2,'Hey,小明','小明','2021-6-13',1000);
   insert into blog(id,title,author,create_time,views)values(3,'Hey,张三','张三','2021-7-13',2000);
   insert into blog(id,title,author,create_time,views)values(4,'Hey,李四','李四','2021-5-13',3000);
   insert into blog(id,title,author,create_time,views)values(5,'Hey,王五','王五','2021-1-13',4000);

3. 编写实体类

   Blog.java

   package com.jokerdig.pojo;
   
   import lombok.AllArgsConstructor;
   import lombok.Data;
   import lombok.NoArgsConstructor;
   
   import java.util.Date;
   
   /**
    * @author Joker大雄
    * @data 2022/5/5 - 12:10
    **/
   @Data
   @AllArgsConstructor
   @NoArgsConstructor
   public class Blog {
       private String id;
       private String title;
       private String author;
       private Date createTime;// 解决实体类和数据库不一致
       private int views;
   }

4. 编写核心配置文件

   mybatis-config.xml

   <?xml version="1.0" encoding="UTF-8" ?>
   <!DOCTYPE configuration
           PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-config.dtd">
   <!--核心配置文件-->
   <configuration>
       <!--    引入db.properties-->
       <!--  也可以在标签里传值，但优先使用外部配置-->
       <properties resource="db.properties"/>
   <!--    配置日志工厂-->
       <settings>
   <!--        标准日志工厂-->
           <setting name="logImpl" value="STDOUT_LOGGING"/>
   <!--        解决实体类和数据库不对应 允许驼峰对应数据库的_ -->
           <setting name="mapUnderscoreToCamelCase" value="true"/>
       </settings>
   <!--    配置别名 一-->
       <typeAliases>
           <typeAlias type="com.jokerdig.pojo.Blog" alias="Blog"/>
       </typeAliases>
       <environments default="development">
           <environment id="development">
               <transactionManager type="JDBC"/>
               <dataSource type="POOLED">
                   <property name="driver" value="${driver}"/>
                   <property name="url" value="${url}"/>
                   <property name="username" value="${username}"/>
                   <property name="password" value="${password}"/>
               </dataSource>
           </environment>
       </environments>
   <!--    绑定接口-->
       <mappers>
           <mapper resource="com/jokerdig/mapper/BlogMapper.xml"/>
       </mappers>
   </configuration>

5. 编写Mapper接口和xml

6. 创建IDUtils实体类

   IDUtils.java

   package com.jokerdig.util;
   
   import java.util.UUID;
   
   /**
    * @author Joker大雄
    * @data 2022/5/5 - 12:18
    **/
   public class IDUtils {
       // 在企业开发中中，id一般都是随机生成
       public static String getID(){
           return UUID.randomUUID().toString().replace("-","");
       }
   }

7. 简单测试添加

   运行结果

   Opening JDBC Connection
   Created connection 574568002.
   ==>  Preparing: insert into blog(id,title,author,create_time,views) values(?,?,?,?,?)
   ==> Parameters: f5623f1519f948fd83da899cdf080c62(String), 测试博客标题(String), 测试博主(String), 2022-05-05 12:35:07.168(Timestamp), 1000(Integer)
   <==    Updates: 1
   Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@223f3642]
   Returned connection 574568002 to pool.
   
   Process finished with exit code 0