前言

创建数据库表（user和message），并插入数据：

CREATE TABLE `user` (

`id` varchar(64) NOT NULL ,

`name` varchar(64) NULL ,

PRIMARY KEY (`id`)

);

CREATE TABLE `info` (

`id` varchar(64) NOT NULL ,

`uid` varchar(64) NULL ,

`msg` varchar(255) NULL ,

PRIMARY KEY (`id`)

);

INSERT INTO `user` (`id`, `name`) VALUES ('1001', 'ahzoo');

INSERT INTO `user` (`id`, `name`) VALUES ('1002', 'ouo');

INSERT INTO `info` (`id`, `uid`, `msg`) VALUES ('2001', '1001', '测试信息');

INSERT INTO `info` (`id`, `uid`, `msg`) VALUES ('2002', '1001', '第二条信息');

再创建两个数据库表（role和user_role），并插入数据，用于多对多操作：

CREATE TABLE `role` (

`id` varchar(64) NOT NULL ,

`name` varchar(64) NULL ,

PRIMARY KEY (`id`)

);

CREATE TABLE `user_role` (

`user_id` varchar(64) NOT NULL ,

`role_id` varchar(64) NOT NULL

);

INSERT INTO `role` (`id`, `name`) VALUES ('3001', '用户');

INSERT INTO `role` (`id`, `name`) VALUES ('3002', '管理员');

INSERT INTO `user_role` (`user_id`, `role_id`) VALUES ('1001', '3001');

INSERT INTO `user_role` (`user_id`, `role_id`) VALUES ('1001', '3002');

INSERT INTO `user_role` (`user_id`, `role_id`) VALUES ('1002', '3002');

创建一个springboot项目，并生成增删改查，快速开始。

项目结构：

依赖：

<dependencies>

<dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

<dependency>

<groupId>org.mybatis.spring.boot</groupId>

<artifactId>mybatis-spring-boot-starter</artifactId>

<version>2.2.2</version>

</dependency>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter</artifactId>

</dependency>

<dependency>

<groupId>org.projectlombok</groupId>

<artifactId>lombok</artifactId>

<optional>true</optional>

</dependency>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-test</artifactId>

<scope>test</scope>

</dependency>

</dependencies>

一对一

一条信息对应一个发送者，查询信息的发送者。在Info实体类中增加User对象，这样在对user和info进行联查时，将User对象一起返回：entity/Info

import lombok.Data;

@Data

public class Info {

private String id;

private String uid;

private String msg;

// 接收一对一查询到的User对象

private User user;

}

映射文件：mapper/InfoDao.xml

<?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.example.demo.mapper.InfoMapper">



<select id="selectListToMap" resultType="Map">

SELECT u.id,u.name,i.id,i.uid,i.msg

FROM user u,info i

WHERE u.id=i.uid

</select>

<resultMap id="BaseResultMap" type="com.example.demo.entity.Info">

<id property="id" column="id" jdbcType="VARCHAR"/>

<result property="uid" column="uid" jdbcType="VARCHAR"/>

<result property="msg" column="msg" jdbcType="VARCHAR"/>

<association property="user" javaType="com.example.demo.entity.User">

持久层：mapper/InfoMapper

import com.example.demo.entity.Info;

import org.apache.ibatis.annotations.Mapper;

import java.util.List;

import java.util.Map;

@Mapper

public interface InfoMapper {

List<Map<String,String>> selectListToMap();

List<Info> selectListByMap();

}

测试：查询目标信息对应的用户信息

import com.example.demo.entity.Info;

import com.example.demo.mapper.InfoMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

import java.util.Map;

@SpringBootTest

class InfoTests {

@Autowired

InfoMapper infoMapper;

/**

* 使用Map对象作为返回结果

*/

@Test

public void toGetMapResult(){

List<Map<String, String>> infos = infoMapper.selectListToMap();

infos.forEach(System.out::println);

/*

{msg=测试信息, uid=1001, name=ahzoo, id=1001}

{msg=第二条信息, uid=1001, name=ahzoo, id=1001}

*/

}

/**

* 使用Info对象作为返回结果

*/

@Test

public void toGetInfoResult(){

List<Info> infos = infoMapper.selectListByMap();

infos.forEach(System.out::println);

/*

Info(id=2001, uid=1001, msg=测试信息, user=User(id=1001, name=ahzoo))

Info(id=2002, uid=1001, msg=第二条信息, user=User(id=1001, name=ahzoo))

*/

}

}

一对多

一个用户可以发送多条信息，查询用户下的所有信息。在User实体类中增加Info的List对象，用于返回查询到的多个Info对象：entity/User

import lombok.Data;

import java.util.List;

@Data

public class User{

private String id;

private String name;

// 接收一对多查询到的Info对象集合

private List<Info> infos;

}

持久层：mapper/UserMapper

import com.example.demo.entity.User;

import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper

public interface UserMapper {

List<User> findAllByMap();

}

映射文件：mapper/UserMapper.xml

<?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.example.demo.mapper.UserMapper">

<resultMap id="BaseResultMap" type="com.example.demo.entity.User">

<id property="id" column="id" jdbcType="VARCHAR"/>

<result property="name" column="name" jdbcType="VARCHAR"/>



<collection property="infos" ofType="com.example.demo.entity.Info">

<result property="id" column="info_id" jdbcType="VARCHAR"/>

<result property="uid" column="uid" jdbcType="VARCHAR"/>

<result property="msg" column="msg" jdbcType="VARCHAR"/>

</collection>

</resultMap>



<select id="findAllByMap" resultMap="BaseResultMap">

SELECT u.id,u.name,i.id AS info_id,i.uid,i.msg

FROM user u

LEFT OUTER JOIN info i

ON u.id = i.uid;

</select>

</mapper>

测试：

import com.example.demo.entity.User;

import com.example.demo.mapper.UserMapper;

import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest

public class UserTests {

@Autowired

UserMapper userMapper;

@Test

public void toGetAll(){

List<User> allByMap = userMapper.findAllByMap();

allByMap.forEach(System.out::println);

/*

User(id=1001, name=ahzoo, infos=[Info(id=2001, uid=1001, msg=测试信息, user=null), Info(id=2002, uid=1001, msg=第二条信息, user=null)])

*/

}

}

可以看到所有用户下的所有信息都被查询到

多对多

一个用户可以有多个角色，一个角色可以属于多个用户，查询用户对应的角色，及角色对应的用户。在User实体类中增加Role的List对象，用于返回查询到的多个Role对象：entity/User

import lombok.Data;

import java.util.List;

@Data

public class User{

private String id;

private String name;

private List<Role> roles;

}

同理：entity/Role

import lombok.Data;

import java.util.List;

@Data

public class Role {

private String id;

private String name;

private List<User> users;

}

持久层：mapper/RoleMapper

import com.example.demo.entity.Role;

import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper

public interface RoleMapper {

List<Role> getAllUser();

}

mapper/UserMapper

import com.example.demo.entity.User;

import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper

public interface UserMapper {

List<User> getAllRole();

}

映射文件：mapper/RoleMapper

<resultMap id="roleMap" type="com.example.demo.entity.Role">

<id property="id" column="id" jdbcType="VARCHAR"/>

<result property="name" column="name" jdbcType="VARCHAR"/>

<collection property="users" ofType="com.example.demo.entity.User">

<result property="id" column="role_id" jdbcType="VARCHAR"/>

<result property="name" column="name" jdbcType="VARCHAR"/>

</collection>

</resultMap>

<select id="getAllUser" resultMap="roleMap">

SELECT u.id,u.name,r.id AS role_id,r.name

FROM user u

LEFT OUTER JOIN user_role ur ON u.id = ur.user_id

LEFT OUTER JOIN role r ON r.id = ur.role_id

</select>

mapper/UserMapper

<resultMap id="userMap" type="com.example.demo.entity.User">

<id property="id" column="id" jdbcType="VARCHAR"/>

<result property="name" column="name" jdbcType="VARCHAR"/>

<collection property="roles" ofType="com.example.demo.entity.Role">

<result property="id" column="role_id" jdbcType="VARCHAR"/>

<result property="name" column="name" jdbcType="VARCHAR"/>

</collection>

</resultMap>

<select id="getAllRole" resultMap="userMap">

SELECT u.id,u.name,r.id AS role_id,r.name

FROM user u

LEFT OUTER JOIN user_role ur ON u.id = ur.user_id

LEFT OUTER JOIN role r ON r.id = ur.role_id

</select>

测试：

@Autowired

RoleMapper roleMapper;

@Autowired

UserMapper userMapper;

@Test

public void toGetAllUser(){

List<Role> allUser = roleMapper.getAllUser();

allUser.forEach(System.out::println);

/*

Role(id=1001, name=ahzoo, users=[User(id=3001, name=ahzoo, roles=null), User(id=3002, name=ahzoo, roles=null)])

Role(id=1002, name=ouo, users=[User(id=null, name=ouo, roles=null)])

*/

}

@Test

public void toGetAllRole(){

List<User> allRole = userMapper.getAllRole();

allRole.forEach(System.out::println);

/*

User(id=1001, name=ahzoo, roles=[Role(id=3001, name=ahzoo, users=null), Role(id=3002, name=ahzoo, users=null)])

User(id=1002, name=ouo, roles=[Role(id=null, name=ouo, users=null)])

*/

}