Java--Mybatis关联映射之关联单个对象(即一对一)；关联多个对象(即一对多)

待我称王封你为后i 2023-10-03 13:42 33阅读 0赞

使用Mybatis框架，在业务需要对数据库进行关联查询的时候需使用关联映射  
而关联映射分为关联单个对象与关联多个对象，即一对一、一对多的情况，且每种关联方式都可以有两种处理情况，即使用1条sql进行处理与使用2条sql进行处理

已有表关系如下(表的字段名与实体类中的属性名一致)：  
user表：主键为user\_Id  
book表：主键为book\_id

Java实体类对象如下：  
1、domain.User.java

package domain;
    import java.util.List;
    
    public class User {
        private String user_id;//用户id，主键
        private String user_name;//用户姓名
        private String user_password;//用户密码
        private List<Book> books;//用户拥有的书籍信息，用户与书籍是一对多的关系
    
        public List<Book> getBooks() {
            return books;
        }
        public void setBooks(List<Book> books) {
            this.books = books;
        }
        public String getUser_id() {
            return user_id;
        }
        public void setUser_id(String user_id) {
            this.user_id = user_id;
        }
        public String getUser_name() {
            return user_name;
        }
        public void setUser_name(String user_name) {
            this.user_name = user_name;
        }
        public String getUser_password() {
            return user_password;
        }
        public void setUser_password(String user_password) {
            this.user_password = user_password;
        }
        @Override
        public String toString() {
            return "User [user_id=" + user_id + ", user_name=" + user_name + ", user_password="
                    + user_password + ", books=" + books + "]";
        }
    }

2、domain.Book.java

package domain;
    
    public class Book {
        private String book_id;//书籍id，主键
        private String user_id;//所属用户id，外键参照于用户表的主键
        private String book_name;//书籍名称
        private User user;//书籍所属用户关系，书籍与用户为一对一
    
        public String getBook_id() {
            return book_id;
        }
        public void setBook_id(String book_id) {
            this.book_id = book_id;
        }
        public String getUser_id() {
            return user_id;
        }
        public void setUser_id(String user_id) {
            this.user_id = user_id;
        }
        public String getBook_name() {
            return book_name;
        }
        public void setBook_name(String book_name) {
            this.book_name = book_name;
        }
        public User getUser() {
            return user;
        }
        public void setUser(User user) {
            this.user = user;
        }
        @Override
        public String toString() {
            return "Book [book_id=" + book_id + ", user_id=" + user_id + ", book_name=" + book_name
                    + ", user=" + user + "]";
        }
    }

3、mapper.BookAndUserMapper(Mapper映射器)

package mapper;
    import java.util.List;
    import domain.Book;
    
    public interface UserMapper {
    	//关联单个对象
    	public List<Book> findBookAndUser1();//一条语句执行sql
    	public List<Book> findBookAndUser2();//两条语句执行sql
    
    
        //关联多个对象
    	public User findUserById1(String userId);//一条语句执行sql
    	public User findUserById2(String userId);//两条语句执行sql
    }

4、UserMapper.xml

### 一、一对一，<association>标签 ###

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
            "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
    
    
    <mapper namespace="mapper.UserMapper">
        
        <select id="findBookAndUser1" resultMap="bookMap1" >
            SELECT * FROM book 
            LEFT JOIN user ON book.user_id = user.user_id
        </select>
        
        <resultMap id="bookMap1" type="domain.Book">
            
            <id property="book_id" column="book_id"></id>
            <result property="user_id" column="user_id"></result>
            <result property="book_name" column="book_name"></result>
            
            <association property="user" javaType="domain.User">
                <id property="user_id" column="user_id"></id>
                <result property="user_name" column="user_name"></result>
                <result property="user_password" column="user_password"></result>
            </association>
        </resultMap>
    
    
        
        <select id="findBookAndUser2" resultMap="bookMap2">
            select *from book
        </select>
        <resultMap id="bookMap2" type="domain.Book">
            <association property="user" javaType="domain.User" select="findUser"
                         column="user_id"></association>
        </resultMap>
        
        <select id="findUser" parameterType="String" resultType="domain.User">
            select *from user where user_id = #{userId}
        </select>
    </mapper>

### 二、一对多，<collection>标签 ###

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
            "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
    
    
    <mapper namespace="mapper.UserMapper">
        
        <select id="findUserById1" parameterType="String" resultMap="userMap1">
            select * from user u 
            left join book b on (u.user_id = b.user_id)
            where u.user_id = #{userId}
        </select>
        <resultMap id="userMap1" type="domain.User">
            
            <id property="user_id" column="user_id" />
            <result property="user_name" column="user_name" />
            <result property="user_password" column="user_password" />
            <collection property="books" javaType="java.util.List" ofType="domain.Book">
                <id property="book_id" column="book_id" />
                <result property="user_id" column="user_id" />
                <result property="book_name" column="book_name" />
            </collection>
        </resultMap>
    
    
        
        <select id="findUserById2" parameterType="String" resultMap="userMap2">
            select * from user 
            where user_id = #{userId}
        </select>
        <resultMap id="userMap2" type="domain.User">
            
            <id property="user_id" column="user_id"/>
            
            <result property="user_name" column="user_name"/>
            
            <collection property="books" javaType="java.util.List" ofType="domain.Book"
                        select="findBooks" column="user_id" >
            </collection>
        </resultMap>
        <select id="findBooks" parameterType="String" resultType="domain.Book">
            select * from book 
            where user_id = #{userId}
        </select>
    </mapper>

《阿里巴巴JAVA开发手册》里面写超过三张表禁止join，原文如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70][]

此规范是针对MySQL系数据库的，模拟一些场景多造些数据去查询对比不难发现：每增加1张表的JOIN，查询性能就会显著下降

因此我们还是要根据业务场景去灵活运用

针对阿里为何这样规定，以及如何解决，可参考文章

[《阿里巴巴JAVA开发手册》超过三张表禁止join][JAVA_join]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210815212437106.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu,size_16,color_FFFFFF,t_70
[JAVA_join]: https://blog.csdn.net/MinggeQingchun/article/details/119720505