MyBatis学习（五）——多表查询之一对一关系

本是古典何须时尚 2023-06-09 10:07 9阅读 0赞

## MyBatis的多表查询 ##

#### 一对一关系的实现 ####

**用例：人和它所住的地址是一对一关系，即一个人只能有一个地址。**

**第一步**：在数据库新建两个表，第一个是address表

CREATE TABLE address (
    	id BIGINT(32) NOT NULL,
    	NAME VARCHAR(35) DEFAULT NULL,
    	PRIMARY KEY (id)
    )ENGINE=INNODB DEFAULT CHARSET=utf8;

表中数据为：  
![在这里插入图片描述][20191021002335692.png]  
第二个是Person表

CREATE TABLE Person (
    	id BIGINT(32) NOT NULL AUTO_INCREMENT, -- 人的id
    	NAME VARCHAR(25) DEFAULT NULL, -- 人的姓名
    	sex VARCHAR(4) DEFAULT NULL, -- 人的性别
    	address_id BIGINT(32) DEFAULT NULL,
    	PRIMARY KEY (id),
    	KEY FK_address_id (address_id),
    	CONSTRAINT FK_address_id FOREIGN KEY (address_id)
    	REFERENCES address (id)
    )ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

表中数据为：  
![在这里插入图片描述][20191021002249982.png]  
由于两个表是一对一的关系，外键在Person表中添加  
**第二步**：建立实体类：  
Address.java

package cn.zhongger.domain;
    
    import java.io.Serializable;
    
    /** * 用户地址实体 */
    public class Address implements Serializable { 
        private Integer id;
        private String name;
    
        public Address(Integer id, String name) { 
            this.id = id;
            this.name = name;
        }
    
        public Address() { 
        }
    
        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;
        }
    
        @Override
        public String toString() { 
            return "Address{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    '}';
        }
    }

Person.java

package cn.zhongger.domain;
    
    import java.io.Serializable;
    
    /** * 用户实体 */
    public class Person implements Serializable { 
        private Integer id;
        private String name;
        private String sex;
        private Integer age;
        private Address address;//人和地址一一对应，即每个人只有一个地址
        public Person() { 
        }
    
        public Person(Integer id, String name, String sex, Integer age, Address address) { 
            this.id = id;
            this.name = name;
            this.sex = sex;
            this.age = age;
            this.address = address;
        }
    
        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 String getSex() { 
            return sex;
        }
    
        public void setSex(String sex) { 
            this.sex = sex;
        }
    
        public Integer getAge() { 
            return age;
        }
    
        public void setAge(Integer age) { 
            this.age = age;
        }
    
        public Address getAddress() { 
            return address;
        }
    
        public void setAddress(Address address) { 
            this.address = address;
        }
    
        @Override
        public String toString() { 
            return "Person{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", sex='" + sex + '\'' +
                    ", age=" + age +
                    ", address=" + address +
                    '}';
        }
    }

\*\*第三步：\*\*创建对应的DAO接口  
IAddressDao.java

package cn.zhongger.dao;
    
    import cn.zhongger.domain.Address;
    
    public interface IAddressDao { 
        /** * 根据id查找用户地址 * @param id * @return */
        Address findByid(Integer id);
    }

IPersonDao.java

package cn.zhongger.dao;
    
    import cn.zhongger.domain.Person;
    
    public interface IPersonDao { 
        /** * 根据id查找用户 * @param id * @return */
        Person findById(Integer id);
    }

**第四步**:编写映射文件  
AddressMap.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="cn.zhongger.dao.IAddressDao">
        <select id="findByid" parameterType="java.lang.Integer" resultType="cn.zhongger.domain.Address">
            select * from address where id=#{ id}
        </select>
    </mapper>

PersonMap.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="cn.zhongger.dao.IPersonDao">
        <resultMap id="PersonMap" type="cn.zhongger.domain.Person">
            <id property="id" column="id"></id>
            <result property="name" column="name"/>
            <result property="sex" column="sex"/>
            <association property="address" column="address_id" select="cn.zhongger.dao.IAddressDao.findByid"
                         javaType="cn.zhongger.domain.Address"></association>
        </resultMap>
        <select id="findById" parameterType="java.lang.Integer" resultMap="PersonMap">
            select * from Person where id=#{ id}
        </select>
    </mapper>

第五步：编写测试代码：

import cn.zhongger.dao.IAddressDao;
    import cn.zhongger.dao.IPersonDao;
    import cn.zhongger.domain.Address;
    import cn.zhongger.domain.Person;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    import org.junit.After;
    import org.junit.Before;
    import org.junit.Test;
    
    import java.io.IOException;
    import java.io.InputStream;
    
    public class testMyBatis { 
        private InputStream inputStream;
        private SqlSessionFactoryBuilder sqlSessionFactoryBuilder;
        private SqlSessionFactory sessionFactory;
        private SqlSession sqlSession;
        private IPersonDao mapper1;
        private IAddressDao mapper2;
        @Before
        public void init() throws IOException { 
            inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
            sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
            sessionFactory = sqlSessionFactoryBuilder.build(inputStream);
            sqlSession = sessionFactory.openSession();
            mapper1 = sqlSession.getMapper(IPersonDao.class);
            mapper2 = sqlSession.getMapper(IAddressDao.class);
        }
        @After
        public void destroy() throws IOException { 
            sqlSession.commit();
            sqlSession.close();
            inputStream.close();
        }
        @Test
        public void testfindById1(){ 
            Person person1 = mapper1.findById(1);
            Person person2 = mapper1.findById(2);
            Person person3 = mapper1.findById(3);
            Person person4 = mapper1.findById(4);
            System.out.println(person1);
            System.out.println(person2);
            System.out.println(person3);
            System.out.println(person4);
    
        }
        @Test
        public void testfindById2(){ 
            Address address1 = mapper2.findByid(1);
            Address address2 = mapper2.findByid(2);
            Address address3= mapper2.findByid(3);
            Address address4= mapper2.findByid(4);
            System.out.println(address1);
            System.out.println(address2);
            System.out.println(address3);
            System.out.println(address4);
        }
    }

输出结果如下：  
![在这里插入图片描述][2019102100244487.png]![在这里插入图片描述][20191021002518849.png]

#### 总结： ####

**要在MyBatis中实现一对一的多表查询，最重要的是SQL语句外键的设置和对应的DAO接口的映射文件的配置中的< association >属性**

[20191021002335692.png]: /images/20230531/d9da60a5e0d141be867c11bd33005cda.png
[20191021002249982.png]: /images/20230531/78f47cfda5134540b62a4f7a4732cbcf.png
[2019102100244487.png]: https://img-blog.csdnimg.cn/2019102100244487.png
[20191021002518849.png]: /images/20230531/9b0ac7bedeec405da010f4f9dbe92ee8.png