mybatis实现级联查询多对一

素颜马尾好姑娘i 2024-03-24 14:59 45阅读 0赞

下面是bean对象 一个宠物对应一个主人 而一个主人对应多个宠物

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;
        }
    
    
    //    @Override
    //    public String toString() {
    //        return "Pet{" +
    //                "id=" + id +
    //                ", nickname='" + nickname + '\'' +
    //                ", user=" + user +
    //                '}';
    //    }
    }

/**
     * CREATE TABLE mybatis_user
     * (id INT PRIMARY KEY AUTO_INCREMENT,
     * NAME VARCHAR(32) NOT NULL DEFAULT ''
     * )CHARSET=utf8 ;
     *
     */
    public class User {
        private Integer id;
        private String name;
        //因为一个User可以养多个宠物，mybatis体现关系。
        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;
        }
    
    
    //    @Override
    //    //会带来一些麻烦 java.lang.StackOverflowError 栈溢出 因为调用pets 会调用user  循环
    //    public String toString() {
    //        return "User{" +
    //                "id=" + id +
    //                ", name='" + name + '\'' +
    //                ", pets=" + pets +
    //                '}';
    //    }
    }

下面是mapper

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

下面是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.hspedu.mapper.PetMapper">
    
        <resultMap id="PetResultMap" type="Pet">
            <id property="id" column="id"/>
            <result property="nickname" column="nickname"/>
            <association property="user" column="user_id"
                         select="com.hspedu.mapper.UserMapper.getUserById"/>
        </resultMap>
        <select id="getPetByUserId" parameterType="Integer" resultMap="PetResultMap">
    
             SELECT * FROM mybatis_pet WHERE `user_id` = #{userId};
    
        </select>
    
    
    
    
    
        <select id="getPetById" parameterType="Integer" resultMap="PetResultMap">
    
             SELECT * FROM mybatis_pet WHERE `id` = #{id};
    
        </select>
    
    
    </mapper>

下面是一个user主人对应多个多个宠物

package com.hspedu.mapper;
    
    import com.hspedu.entity.User;
    /**
     * 映射关系多对一
     */
    public interface UserMapper {
    
        //通过 id 获取 User 对象
        public User getUserById(Integer id);
    }

下面是它对应的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.hspedu.mapper.UserMapper">
    
    
    
    
        <resultMap id="UserResultMap" type="User">
    
           <id property="id" column="id"/>
            <result property="name" column="name"/>
    
            <collection property="pets" column="id" ofType="Pet"
                        select="com.hspedu.mapper.PetMapper.getPetByUserId"/>
    
        </resultMap>
        <select id="getUserById" parameterType="Integer" resultMap="UserResultMap">
             SELECT * FROM mybatis_user  WHERE `id` = #{id};
        </select>
    
    
    
    
    </mapper>