Mybatis复杂查询

1.前置环境

1.1 mysql

需要了解多表之间的关系。

掌握一些简单的查询操作（嵌套查询等）。

1.2 需要了解resultMap的操作。

可以查看之前的：Mybatis-配置和映射文件博客

也可以查看官方文档的Mybatis-映射文件章节：https://mybatis.org/mybatis-3/zh/sqlmap-xml.html#Result_Maps

了解到两个子元素：

• association：一个关联类型，可以将结果包装成关联。
• collection：一个集合类型，可以将结果包装成集合。

1.3 查看简单的日志

日志的查看和配置我在之前的文章：Mybatis-配置和映射文件 中已经讲到了，在此就不多赘述了。

也可以查看官方文档的：Mybatis-配置 章节来找到setting目录查看相关设置
https://mybatis.org/mybatis-3/zh/configuration.html#settings

2.多对一 和 一对多

2.1 准备

一个公司内：

多个员工对应一个职位，一个职位对应多个用户。

比如：1，2号对应职员，3号是老板一样的关系。

职位表：

create table role(
id int not null ,
role_name varchar(30) ,
primary key (id)
);

insert into role(id,role_name) values(1,'employees');
insert into role(id,role_name) values(2,'manager');
insert into role(id,role_name) values(3,'boss');

用户表：

create table user(
id int primary key,
name varchar(40),
pwd varchar(40),
rid int,
foreign key (rid) references role(id)
);
insert into user(id,name,pwd,rid) values(1,'a','1','1');
insert into user(id,name,pwd,rid) values(2,'b','2','1');
insert into user(id,name,pwd,rid) values(3,'c','3','3');

然后构建出项目结构。

src
	- main
		- java
            - com.admin
                - dao
                    - RoleMapper
                    - UserMapper
                - pojo
                    - Role
                    - User
                - utils
                    -MybatisUtils
		- resources
			- com.admin.dao
				- RoleMapper.xml
				- UserMapper.xml
			- db.properties
			- mybatis-config.xml
	- test

1. 写出配置文件和静态工具类等东西。
2. 建立数据库。
3. 根据数据库写出实体类
4. 写接口
5. 根据接口配置mapper
6. 写测试类来测试。

2.2 功能实现1(多对一)

根据id查询user的信息以及对应的职位。

设计出我们的user实体类和role实体类：

User：

import lombok.Data;

@Data
public class User {
    private int id;
    private String name;
    private String pwd;
    private Role role;
}

Role：

import lombok.Data;

@Data
public class Role {

    private int id;
    private String roleName;
}

这里我们使用的lombok插件，效果自行百度或者查看之前的文章：Mybatis的扩展操作。

2.2.1 问题分析：

首先根据id查出user，然后根据user的rid查出role中rid对应相同的元素。

大概写一下sql：

select * from user u,role r where u.rid = r.id;

在mysql中测试一下：
完美！

然后向之前一样吧sql扔到mapper的select中查询。

注意：这里和下面的所有实体类用的都是别名，别名在前面的配置中说过了，这里就不多说了。不用别名的使用全限定类名也可以：com.admin.pojo.User

<select id="findById" parameterType="int" resultType="User">
    select * from user u,role r where u.id = #{id} and u.rid = r.id;
</select>

查看输出结果：User(id=1,name=a,pwd=1,role=null)

role对象为null。

2.2.2 解决办法：

这时候用到了之前的resultMap的关联和集合的属性。

查阅文档或阅读之前的博客可以了解。

由于前文的限制，所以这里的情况就是多个user对应一个职位。所以每次查询user的时候，只会返回一个结果，因此使用关联关系将结果关联起来即可。

2.2.2.1 嵌套子查询

在一个查询结果中，再次查询另一个结果。

看一下结果，再看一下日志：
发现确实查出来了，role以结果集的形式输出了出来（红色框框中的绿色框框）。

但是注意一下，蓝色框框还是null，结合我们以前学过的知识，可以知道这是因为数据库中列明为role_name而实体类中为roleName。

因此我们只需要进行resultMap整理一下role映射就好了：

可以看出来，我们稍微修改了一下子查询的映射方式resultMap，然后加上了resultMap的对应属性。就可以轻松查出来我们想要的了。

2.2.2.2 结果集嵌套映射

首先先看一下我们一开始的查询语句的查询结果日志：

这里发现查询的返回结果是正常的，我们所期望的，但是在转换成User的时候出了问题。因此我们要想着如何使他们的列名和类中属性名相对应起来。

这里由于出现了重复的id等属性，因此我们要做出一些调整后再使用resultMap：

select u.id,u.name,r.id as rid,r.role_name from user u,role r where u.id = #{id} and u.rid = r.id;

可以看出，此是的返回值更加容易理解。

然后就是进行结果集映射了：

可以看出，此是就可以正常来显示了。

2.2.3 小结

使用resultMap可以进行关联的嵌套。官方文档说的很详细，但是我还是用自己的话来总结一下吧。

关联关系（association）中：

• property对应实体类中的字段。
• javaType是映射的实体类的类型，使用别名或者全限定类名都可以。
• column对应的是数据库中返回字段的名字，有别名是别名。

大概注意的也就这三点了，其他的在之前和以后会讲到。

2.3 功能的实现2(一对多)

根据职位的id，查询所有是这个职位的user的id和name。

稍微修改一下我们的user表和role表：

User：

import lombok.Data;

@Data
public class User {
    private int id;
    private String name;
    private String pwd;
    private int rid;
}

Role：

import lombok.Data;

import java.util.List;

@Data
public class Role {

    private int id;
    private String roleName;
    //获得的user集合存入userList中
    private List<User> userList;
}

2.3.1 问题分析

在职位表中查出职位id，然后再在user表中查出所有rid=id的user。

大概写一下sql：

select * from role as r,user as u where r.id = u.rid;

和之前一样，将这个sql放入mapper中查看一下效果：

这里报错了，但是通过查看日志可以看出来，其实我们已经查到了，但是返回结果集的时候，由于这里只需要一个，但是我们返回了两个查询结果，所以报错。

2.3.2 解决办法：

回忆之前的映射文件中resultMap中还有一个属性，就是将结果集封装到集合collection中，这里我们就尝试着去使用。

这里由于一个职位中可能会有多个人，因此我们要将结果存到一个集合中。

2.3.2.1 按结果嵌套查询

稍微修改一下sql，变得更容易理解：

select r.id rid,r.role_name,u.id uid,u.name uname
from role r,user u
where r.id = #{id} and r.id = u.rid;

分层，然后取一下别名，看起来清楚多了。

然后使用collection，将结果按照列明和属性名一一对应。

这里的ofType和上面的JavaType不一样，原因是：此处使用的是集合，集合中存的是泛型，而JavaType指的是一个特定的类型。ofType才能取到泛型。

2.3.2.2 嵌套子查询

另一种查询方法，嵌套子查询，先根据id查出来role，然后再根据rid查找user表找到对应的user，放到结果集中。

通过查看collection的属性效果，我们可以写出下面的查询语句：

通过日志我们可以看出来：（蓝色框框）

先查询了role表，获得一个返回值

然后查询了user表，获得了两个返回值，将这两个返回值放入前面的role的list中。

2.3.3 小结

注意理解javaType和ofType。

• javaType：对象的类型
• ofType：泛型的类型

注意column：可以作为参数传给下一个子sql。

3.多对多

3.1 准备

现实生活中：

一个用户对应多个身份，一个身份对应多个用户。

一个人，在家子女，在学校学生，在大街上路人。

当子女的可以很多人，身份是学生的也可以很多人。

上面的职位表（role）和用户表（user）加一个关系表（relation）

职位表和用户表之间没有映射关系（取消user表的外键）。

role：

create table role(
id int not null ,
role_name varchar(30) ,
primary key (id)
);

insert into role(id,role_name) values(1,'employees');
insert into role(id,role_name) values(2,'manager');
insert into role(id,role_name) values(3,'boss');

user：

create table user(
id int primary key,
name varchar(40)
);
insert into user(id,name) values(1,'a');
insert into user(id,name) values(2,'b');
insert into user(id,name) values(3,'c');

relation:

create table relation(
uid int(11) not null ,
rid int(11) not null,
primary key(uid,rid),
foreign key (rid) references role(id),
foreign key (uid) references user(id)
);

insert into relation(uid,rid) values(1,1),(2,1),(3,3);

测试一下结果：

select * 
from user,role,relation 
where user.id = relation.uid and relation.rid = role.id;

查询成功。（一个公司两个职员）

3.2 问题分析

然后发现，多对多 可以理解为 一对多。

类比之前的一对多，修改一下sql，很容易得出这样的mapper。

3.3 解决办法

接口和pojo就不写了，还是那点东西。

mapper：稍微改了一下sql就可以了。

测试类：

@Test
public void Test(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    User user = sqlSession.getMapper(UserMapper.class).getUserWithRoleById(1);
    System.out.println(user);
    sqlSession.close();
}

4. 总结

• 其实多对一，一对多就是两个元素的使用：association和collection。
• 多对多就是一对多的变形。
• 注意一下column和property的使用。
• 多看官方文档。
• 注意sql的效率。