前言:

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

多对一处理

StudentMapper.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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中的子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?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中的连表查询

1
2
3
4
5
6
7
8
9
10
11
<!--    按照结果嵌套处理-->
<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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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;
}

搭建好之后简单测试

我们发现,学生信息为空

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
....
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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);
}

按照结果嵌套处理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?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>

按照查询嵌套处理

1
2
3
4
5
6
7
8
9
10
<!--按查询嵌套-->
<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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    <?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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    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. 简单测试添加

    运行结果

    1
    2
    3
    4
    5
    6
    7
    8
    9
    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