跳至主要內容

映射关系多对一

LiAngJava2023年11月16日MyBatisJava大约 10 分钟约 1997 字

官方文档

https://mybatis.org/mybatis-3/zh/sqlmap-xml.html

基本介绍

​ 项目中多对 1 的关系是一个基本的映射关系，多对 1，也可以理解成是 1 对多。

​ (1)User --- Pet： 一个用户可以养多只宠物；(2)Dep ---Emp：一个部门可以有多个员工

注意细节

​ 在实际的项目开发中，要求会使用双向的多对一的映射关系。

​ 什么是双向的多对一的关系：比如通过 User 可以查询到对应的 Pet，反过来，通过 Pet 也可以级联查询到对应的 User 信息。

​ 多对多的关系，是在多对一的基础上扩展即可。

映射方式

配置 Mapper.xml方式实现

​ 实现级联查询，通过 user 的 id 可以查询到用户信息，并可以查询到关联的 pet 信息，反过来，通过 Pet 的 id 可以查询到 Pet 的信息，并且可以级联查询到它的主人 User 对象信息。

创建 mybatis_user 和 mybatis_pet 表

CREATE TABLE mybatis_user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(32) NOT NULL DEFAULT '' 
)CHARSET=utf8;


CREATE TABLE mybatis_pet (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    nickname VARCHAR(32) NOT NULL DEFAULT '',
    user_id INT , 
    FOREIGN KEY (user_id) REFERENCES mybatis_user(id) 
)CHARSET=utf8 ;

INSERT INTO mybatis_user VALUES(NULL,'宋江'),(NULL,'张飞'); 
INSERT INTO mybatis_pet VALUES(1,'黑背',1),(2,'小哈',1); 
INSERT INTO mybatis_pet VALUES(3,'波斯猫',2),(4,'贵妃猫',2);

创建 entity

package com.lzw.entity;

/**
 * @author LiAng
 */
public class Pet {
    private Integer id;
    private String nickname;
    //一个pet对应一个主人 User对象
    private User user;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getNickname() {
        return nickname;
    }

    public void setNickname(String nickname) {
        this.nickname = nickname;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }
}

//==================================================================

package com.lzw.entity;

import java.util.List;

/**
 * @author LiAng
 */
public class User {
    private Integer id;
    private String name;
    //因为一个user可以养多个宠物,mybatis 使用集合List<Pet>体现这个关系
    private List<Pet> pets;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Pet> getPets() {
        return pets;
    }

    public void setPets(List<Pet> pets) {
        this.pets = pets;
    }
}

创建 PetMapper.java

package com.lzw.mapper;

import com.lzw.entity.Pet;

import java.util.List;

/**
 * @author LiAng
 */
public interface PetMapper {
    //通过 User 的 id 来获取 pet 对象，可能有多个，因此使用 List 接收
    public List<Pet> getPetByUserId(Integer userId);
    //通过 pet 的 id 获取 Pet 对象
    public Pet getPetById(Integer id);
}

创建 UserMapper.java

package com.lzw.mapper;

import com.lzw.entity.User;

/**
 * @author LiAng
 */
public interface UserMapper {
    //通过 id 获取 User 对象
    public User getUserById(Integer id)
}

创建 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.lzw.mapper.UserMapper">

    <!--
    1、一定要想一想我们前面1-1是如何实现
    2、配置/实现 public User getUserById(Integer id);
    3、思路(1) 先通过user-id 查询得到user信息 (2) 再根据user-id查询对应的pet信息
      并映射到User-List<Pet> pets
    -->
    <resultMap id="UserResultMap" type="User">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <!--因为pets属性是集合，因此这里需要是collection标签来处理
           1. ofType="Pet" 指定返回的集合中存放的数据类型Pet
           2. collection 表示 pets 是一个集合
           3. property="pets" 是返回的user对象的属性 pets
           4. column="id" SELECT * FROM `mybatis_user` WHERE `id` = #{id} 返回的id字段对应的值
        -->
        <collection property="pets" column="id" ofType="Pet" select="com.lzw.mapper.PetMapper.getPetByUserId"/>
    </resultMap>
    <select id="getUserById" parameterType="Integer" resultMap="UserResultMap">
        select * from `mybatis_user` where `id` = #{id}
    </select>

</mapper>

创建 Mapper.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.lzw.mapper.UserMapper">

    <!--
        1、通过User的id来获取pet对象，可能有多个，因此使用List接收
        2、public List<Pet> getPetByUserId(Integer userId);
        3. 完成的思路和前面大体相同.
    -->
    <resultMap id="PetResultMap" type="Pet">
        <id property="id" column="id"/>
        <result property="nickname" column="nickname"/>
        <association property="user" column="user_id" select="com.lzw.mapper.UserMapper.getUserById"/>
    </resultMap>
    <select id="getPetByUserId" parameterType="Integer" resultMap="PetResultMap">
        select * from `mybatis_pet` where `user_id` = #{user_id}
    </select>

</mapper>

创建 UserMapperTest.java

package com.lzw.mapper;

import com.lzw.entity.Pet;
import com.lzw.entity.User;
import com.lzw.util.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Before;
import org.junit.Test;

import java.util.List;

/**
 * @author LiAng
 */
public class UserMapperTest {
    private SqlSession sqlSession;
    private UserMapper userMapper;

    //初始化
    @Before
    public void init() {
        sqlSession = MyBatisUtils.getSqlSession();
        userMapper = sqlSession.getMapper(UserMapper.class);
    }

    @Test
    public void getUserById(){
        User user = userMapper.getUserById(1);

        System.out.println("user信息-" + user.getId() + "-" + user.getName());

        List<Pet> pets = user.getPets();
        for (Pet pet : pets) {
            System.out.println("养的宠物信息-" + pet.getId() + "-" + pet.getNickname());
        }

        if(sqlSession != null){
            sqlSession.close();
        }
    }
}
image-20220707085926038
image-20220707085926038

创建 PetMapperTest.java

package com.lzw.mapper;

import com.lzw.entity.Pet;
import com.lzw.entity.User;
import com.lzw.util.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Before;
import org.junit.Test;

import java.util.List;

/**
 * @author LiAng
 */
public class PetMapperTest {
    private SqlSession sqlSession;
    private PetMapper petMapper;

    //初始化
    @Before
    public void init() {
        sqlSession = MyBatisUtils.getSqlSession();
        petMapper = sqlSession.getMapper(PetMapper.class);
    }

    @Test
    public void getUserById(){
        List<Pet> pets = petMapper.getPetByUserId(2);
        for (Pet pet : pets) {
            System.out.println("pet信息-" + pet.getId() + "-" + pet.getNickname());
            User user = pet.getUser();
            System.out.println("user信息 name-" + user.getName());
        }

        if(sqlSession != null){
            sqlSession.close();
        }
    }
}
image-20220707090254664
image-20220707090254664

修改 PetMapper.xml

	<!--
        1. 注意体会resultMap带来好处， 直接复用
        2. 实现/配置public Pet getPetById(Integer id);
        3. 通过pet的id获取Pet对象
    -->
    <select id="getPetById" parameterType="Integer" resultMap="PetResultMap">
        select * from `mybatis_pet` where `id` = #{id}
    </select>

修改 PetMapperTest.java

	@Test
    public void getPetById(){
        Pet pet = petMapper.getPetById(2);
        System.out.println("pet信息-" + pet.getId() + "-" + pet.getNickname());
        User user = pet.getUser();
        System.out.println("user信息-" + user.getId() + "-" + user.getName());
        if(sqlSession != null) {
            sqlSession.close();
        }
    }
image-20220707090549735
image-20220707090549735

注解方式实现

​ 通过注解的方式来实现下面的多对 1 的映射关系，实现级联查询,完成前面完成 的任务，通过 User-->Pet 也可 Pet->User ， 在实际开发中推荐使用配置方式来做。

创建 UserMapperAnnotation.java

package com.lzw.mapper;

import com.lzw.entity.User;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

/**
 * @author LiAng
 * 以注解的方式来配置多对一
 */
public interface UserMapperAnnotation {

    //通过 id 获取 User 对象
    /**
     * 1. 注解的配置就是对应的Mapper.xml文件配置的，改写
     * 2.
     *     1、一定要想一想我们前面1-1是如何实现
     *     2、配置/实现 public User getUserById(Integer id);
     *     3、思路(1) 先通过user-id 查询得到user信息 (2) 再根据user-id查询对应的pet信息
     *       并映射到User-List<Pet> pets
     *     <resultMap id="UserResultMap" type="User">
     *         <id property="id" column="id"/>
     *         <result property="name" column="name"/>
     *         1. ofType="Pet" 指定返回的集合中存放的数据类型Pet
     *         2. collection 表示 pets 是一个集合
     *         3. property="pets" 是返回的user对象的属性 pets
     *         4. column="id" SELECT * FROM `mybatis_user` WHERE `id` = #{id} 返回的id字段对应的值
     *         -->
     *         <collection property="pets" column="id" ofType="Pet"
     *                     select="com.lzw.mapper.PetMapper.getPetByUserId"/>
     *     </resultMap>
     *     <select id="getUserById" parameterType="Integer" resultMap="UserResultMap">
     *         SELECT * FROM `mybatis_user` WHERE `id` = #{id}
     *     </select>
     */
    @Select("SELECT * FROM `mybatis_user` WHERE `id` = #{id}")
    @Results({
            @Result(id = true, property = "id", column = "id"),
            @Result(property = "name", column = "name"),
            //这里请注意，pets属性对应的是集合
            @Result(property = "pets", column = "id", 
                    many = @Many(select = "com.lzw.mapper.PetMapperAnnotation.getPetByUserId"))
    })
    public User getUserById(Integer id);
}

创建 PetMapperAnnotation.java

package com.lzw.mapper;

import com.lzw.entity.Pet;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author LiAng
 */
public interface PetMapperAnnotation {

    //通过 User 的 id 来获取 pet 对象，可能有多个，因此使用 List 接收
    /**
     * 1、通过User的id来获取pet对象，可能有多个，因此使用List接收
     * 2、public List<Pet> getPetByUserId(Integer userId);
     * 3. 完成的思路和前面大体相同.
     * <resultMap id="PetResultMap" type="Pet">
     * <id property="id" column="id"/>
     * <result property="nickname" column="nickname"/>
     * <association property="user" column="user_id"
     * select="com.lzw.mapper.UserMapper.getUserById" />
     * </resultMap>
     * <select id="getPetByUserId" parameterType="Integer" resultMap="PetResultMap">
     * SELECT * FROM `mybatis_pet` WHERE `user_id` = #{userId}
     * </select>
     */
    @Select("SELECT * FROM `mybatis_pet` WHERE `user_id` = #{userId}")
    @Results({
            @Result(id = true, property = "id", column = "id"),
            @Result(property = "nickname", column = "nickname"),
            @Result(property = "user", column = "user_id",
                    one = @One(select = "com.lzw.mapper.UserMapperAnnotation.getUserById")),
    })
    public List<Pet> getPetByUserId(Integer userId);
}

创建 UserMapperAnnotationTest

package com.lzw.mapper;

import com.lzw.entity.Pet;
import com.lzw.entity.User;
import com.lzw.util.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Before;
import org.junit.Test;

import java.util.List;

/**
 * @author LiAng
 */
public class UserMapperAnnotationTest {
    private SqlSession sqlSession;
    private UserMapperAnnotation userMapperAnnotation;

    //初始化
    @Before
    public void init() {
        sqlSession = MyBatisUtils.getSqlSession();
        userMapperAnnotation = sqlSession.getMapper(UserMapperAnnotation.class);
    }
    
    @Test
    public void getUserById(){
        User user = userMapperAnnotation.getUserById(1);
        System.out.println("user信息-" + user.getId() + "，" + user.getName());
        List<Pet> pets = user.getPets();
        for (Pet pet : pets) {
            System.out.println("宠物信息-" + pet.getId() + "；" + pet.getNickname());
        }

        if(sqlSession != null){
            sqlSession.close();
        }
    } 
}
image-20220709095936204
image-20220709095936204

修改 PetMapperAnnotation.java

	@Select("SELECT * FROM `mybatis_pet` WHERE `user_id` = #{userId}")
    @Results(id = "PetResultMap",value = {
            @Result(id = true, property = "id", column = "id"),
            @Result(property = "nickname", column = "nickname"),
            @Result(property = "user", column = "user_id",
                    one = @One(select = "com.lzw.mapper.UserMapperAnnotation.getUserById")),
    })
    public List<Pet> getPetByUserId(Integer userId);

    //通过 pet 的 id 获取 Pet 对象
    /**
     * <select id="getPetById"
     * parameterType="Integer"
     * resultMap="PetResultMap">
     * SELECT * FROM `mybatis_pet` WHERE `id` = #{id}
     * </select>
     *
     * @ResultMap("PetResultMap") 使用/引用我们上面定义的 Results[ResultMap]
     */
    @Select("SELECT * FROM `mybatis_pet` WHERE `id` = #{id}")
    @ResultMap("PetResultMap")
    public Pet getPetById(Integer id);

创建 PetMapperAnnotationTest.java

package com.lzw.mapper;

import com.lzw.entity.Pet;
import com.lzw.entity.User;
import com.lzw.util.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Before;
import org.junit.Test;

import java.util.List;

/**
 * @author LiAng
 */
public class PetMapperAnnotationTest {
    private SqlSession sqlSession;
    private PetMapperAnnotation petMapperAnnotation;

    //初始化
    @Before
    public void init() {
        sqlSession = MyBatisUtils.getSqlSession();
        petMapperAnnotation = sqlSession.getMapper(PetMapperAnnotation.class);
    }

    @Test
    public void getPetByUserId() {
        List<Pet> pets = petMapperAnnotation.getPetByUserId(1);
        for (Pet pet : pets) {
            System.out.println("宠物信息-" + pet.getId() + "-" + pet.getNickname());
        }
        if(sqlSession != null) {
            sqlSession.close();
        }
    }

    @Test
    public void getPetById() {
        Pet pet = petMapperAnnotation.getPetById(1);
        System.out.println("pet信息-" + pet.getId() + "-" +pet.getNickname());
        User user = pet.getUser();
        System.out.println("user信息-" + user.getId() + "-" + user.getName());
        if(sqlSession != null) {
            sqlSession.close();
        }
    }

}
image-20220709100024462
image-20220709100024462
上次编辑于: 2023/11/17 23:19:42
贡献者: LiAng