MyBatis一对一，一对多，多对一关联查询

水深无声 2023-10-17 18:39 115阅读 0赞

## 1、一对一关联查询的案例 ##

### （1）需求 ###

根据班级id查询班级信息（带老师信息）

### （2）创建表和数据 ###

创建一张教师表和班级表，这里我们假设一个老师只负责教一个班，那么老师和班级之间的关系就是一种一对一的关系。

CREATE TABLE teacher(  
        t_id INT PRIMARY KEY AUTO_INCREMENT,   
        t_name VARCHAR(20)  
    );  
    CREATE TABLE class(  
        c_id INT PRIMARY KEY AUTO_INCREMENT,   
        c_name VARCHAR(20),   
        teacher_id INT  
    );  
    ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id);      
      
    INSERT INTO teacher(t_name) VALUES('teacher1');  
    INSERT INTO teacher(t_name) VALUES('teacher2');  
      
    INSERT INTO class(c_name, teacher_id) VALUES('class_a', 1);  
    INSERT INTO class(c_name, teacher_id) VALUES('class_b', 2);

### （3）构建实体 ###

老师：

public class Teacher {  
      
        // 定义实体类的属性，与teacher表中的字段对应  
        private int id; // id===>t_id  
        private String name; // name===>t_name  
      
        public int getId() {  
            return id;  
        }  
      
        public void setId(int id) {  
            this.id = id;  
        }  
      
        public String getName() {  
            return name;  
        }  
      
        public void setName(String name) {  
            this.name = name;  
        }  
      
        @Override  
        public String toString() {  
            return "Teacher [id=" + id + ", name=" + name + "]";  
        }  
    }

班级：

public class Classes {  
      
        // 定义实体类的属性，与class表中的字段对应  
        private int id; // id===>c_id  
        private String name; // name===>c_name  
      
        /** 
         * class表中有一个teacher_id字段，所以在Classes类中定义一个teacher属性， 
         * 用于维护teacher和class之间的一对一关系，通过这个teacher属性就可以知道这个班级是由哪个老师负责的 
         */  
        private Teacher teacher;  
      
        public int getId() {  
            return id;  
        }  
      
        public void setId(int id) {  
            this.id = id;  
        }  
      
        public String getName() {  
            return name;  
        }  
      
        public void setName(String name) {  
            this.name = name;  
        }  
      
        public Teacher getTeacher() {  
            return teacher;  
        }  
      
        public void setTeacher(Teacher teacher) {  
            this.teacher = teacher;  
        }  
      
        @Override  
        public String toString() {  
            return "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher  
                    + "]";  
        }  
    }

#### （4）定义Mapper对象 ####

public interface ClassMapper {  
        /** 
         * 用于测试，非懒加载的one2one的实现 
         *  
         * @param id 
         * @return 
         */  
        public Classes getClass1(int id);  
      
        /** 
         * 用于测试，懒加载的one2one的实现 
         *  
         * @param id 
         * @return 
         */  
        public Classes getClass2(int id);  
    }

（5）定义sql映射文件classMapper.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.baowei.mapper.ClassMapper">  
      
          
      
           
        <select id="getClass1" parameterType="int" resultMap="ClassResultMap">  
            select *  
            from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}  
        </select>  
          
        <resultMap type="com.baowei.entity.Classes" id="ClassResultMap">  
            <id property="id" column="c_id" />  
            <result property="name" column="c_name" />  
            <association property="teacher" javaType="com.baowei.entity.Teacher">  
                <id property="id" column="t_id" />  
                <result property="name" column="t_name" />  
            </association>  
        </resultMap>  
      
          
        <select id="getClass2" parameterType="int" resultMap="ClassResultMap2">  
            select *  
            from class where c_id=#{id}  
        </select>  
          
        <resultMap type="com.baowei.entity.Classes" id="ClassResultMap2">  
            <id property="id" column="c_id" />  
            <result property="name" column="c_name" />  
            <association property="teacher" column="teacher_id"  
                select="getTeacher" />  
        </resultMap>  
        <select id="getTeacher" parameterType="int"  
            resultType="com.baowei.entity.Teacher">  
            SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}  
        </select>  
      
    </mapper>

### (6)SqlMapConfig.xml配置 ###

<?xml version="1.0" encoding="UTF-8" ?>  
    <!DOCTYPE configuration  
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"  
    "http://mybatis.org/dtd/mybatis-3-config.dtd">  
    <configuration>  
      
          
        <properties resource="db.properties" />  
          
        <settings>  
              
            <setting name="lazyLoadingEnabled" value="true" />  
              
            <setting name="aggressiveLazyLoading" value="false" />  
        </settings>  
          
        <environments default="development">  
            <environment id="development">  
                  
                <transactionManager type="JDBC" />  
                  
                <dataSource type="POOLED">  
                    <property name="driver" value="${jdbc.driver}" />  
                    <property name="url" value="${jdbc.url}" />  
                    <property name="username" value="${jdbc.username}" />  
                    <property name="password" value="${jdbc.password}" />  
                </dataSource>  
            </environment>  
        </environments>  
          
        <mappers>  
            <package name="com.baowei.mapper" />  
        </mappers>  
      
    </configuration>

### （7）.MyBatisUtil.java工具 ###

import java.io.IOException;  
    import java.io.InputStream;  
    import org.apache.ibatis.io.Resources;  
    import org.apache.ibatis.session.SqlSession;  
    import org.apache.ibatis.session.SqlSessionFactory;  
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;  
      
    public class MyBatisUtil {  
      
        /** 
         * 获取SqlSessionFactory 
         *  
         * @return SqlSessionFactory 
         */  
        public static SqlSessionFactory getSqlSessionFactory() {  
            String resource = "SqlMapConfig.xml";  
            InputStream inputStream = null;  
            SqlSessionFactory sqlSessionFactory = null;  
            try {  
                inputStream = Resources.getResourceAsStream(resource);  
                sqlSessionFactory = new SqlSessionFactoryBuilder()  
                        .build(inputStream);  
      
            } catch (IOException e) {  
                e.printStackTrace();  
            }  
            return sqlSessionFactory;  
        }  
      
        /** 
         * 获取SqlSession 
         *  
         * @return SqlSession 
         */  
        public static SqlSession getSqlSession() {  
            return getSqlSessionFactory().openSession();  
        }  
      
        /** 
         * 获取SqlSession 
         *  
         * @param isAutoCommit 
         *            true 表示创建的SqlSession对象在执行完SQL之后会自动提交事务 false 
         *            表示创建的SqlSession对象在执行完SQL之后不会自动提交事务 
         *            ，这时就需要我们手动调用sqlSession.commit()提交事务 
         * @return SqlSession 
         */  
        public static SqlSession getSqlSession(boolean isAutoCommit) {  
            return getSqlSessionFactory().openSession(isAutoCommit);  
        }  
      
        public static void main(String[] args) {  
            System.out.println(getSqlSessionFactory());  
        }  
    }

### （8）测试代码 ###

import org.apache.ibatis.session.SqlSession;  
    import org.junit.Test;  
      
    import com.baowei.entity.Classes;  
    import com.baowei.mapper.ClassMapper;  
    import com.baowei.utils.MyBatisUtil;  
      
    public class TestOne2One {  
      
        /** 
         * 非懒加载的one2one的测试 
         */  
        @Test  
        public void testGetClass() {  
            SqlSession sqlSession = MyBatisUtil.getSqlSession();  
      
            ClassMapper mapper = sqlSession.getMapper(ClassMapper.class);  
            Classes clazz = mapper.getClass1(1);  
      
            // 使用SqlSession执行完SQL之后需要关闭SqlSession  
            sqlSession.close();  
            System.out.println(clazz.getId());  
        }  
      
        /** 
         * 懒加载的one2one的测试 
         */  
        @Test  
        public void testGetClass2() {  
            SqlSession sqlSession = MyBatisUtil.getSqlSession();  
      
            ClassMapper mapper = sqlSession.getMapper(ClassMapper.class);  
            Classes clazz = mapper.getClass2(1);  
      
            // 使用SqlSession执行完SQL之后需要关闭SqlSession  
            sqlSession.close();  
            System.out.println(clazz.getId());  
            // 可用于测试懒加载(查看日志的sql输出结果，就可以发现使用了懒加载)  
            //System.out.println(clazz.getTeacher());  
      
        }  
    }

## 2.一对多/多对一查询案例 ##

### （1）需求 ###

本实例使用顾客和订单的例子做说明: 一个顾客可以有多个订单, 一个订单只对应一个顾客。

### （2）创建表和数据 ###

CREATE DATABASE test;    
      
    USE test;  
      
    CREATE TABLE person(  
      personId VARCHAR(36) PRIMARY KEY,  
      personName VARCHAR(64),  
      personAddress VARCHAR(128),  
      personTel VARCHAR(11)  
    );  
      
    CREATE TABLE orders(  
      orderId VARCHAR(36) PRIMARY KEY,  
      orderNumber VARCHAR(20),  
      orderPrice INT,  
      pid VARCHAR(36)  
    );  
      
    INSERT INTO person VALUES('001', 'Jack', 'Wuhan', '1234567');  
    INSERT INTO orders VALUES('O_00001', '00001', 100, '001');  
    INSERT INTO orders VALUES('O_00002', '00002', 200, '001');  
      
    SELECT p.*, o.*  
    FROM person p   
      JOIN orders o ON (p.personId=o.pid)  
    WHERE p.personId = '001'

### （3）构建实体 ###

客户：

public class Person {  
      
        private String id;  
        private String name;  
        private String address;  
        private String tel;  
          
        private List<Order> orders;  
          
        @Override  
        public String toString() {  
            return "{id: " + id + ", name: " + name + ", address: " + address + ", tel: " + tel + "}";  
        }  
    }

订单：

public class Order {  
      
        private String id;  
        private String number;  
        private int price;  
          
        private Person person;   
          
        @Override  
        public String toString() {  
            return "{id: " + id + ", number: " + number + ", price: " + price + "}";  
        }  
      
    }

### （4）一对多实体配置: Person.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.zdp.domain.Person">  
      
        <resultMap type="Person" id="personBean">  
            <id column="personId" property="id"/>  
            <result column="personName" property="name"/>  
            <result column="personAddress" property="address"/>  
            <result column="personTel" property="tel"/>  
              
              
              
            <collection property="orders" ofType="Order">  
                <id column="orderId" property="id"/>  
                <result column="orderNumber" property="number"/>  
                <result column="orderPrice" property="price"/>  
            </collection>  
        </resultMap>  
          
          
        <select id="selectPersonById" parameterType="string" resultMap="personBean">  
            select p.*, o.* from person p, orders o where p.personId  = o.pid and p.personId = #{id}  
        </select>  
          
    </mapper>

### （5）多对一实体配置:order.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.zdp.domain.Order">  
        <resultMap type="Order" id="orderBean">  
            <id column="orderId" property="id"/>  
            <result column="orderNumber" property="number"/>  
            <result column="orderPrice" property="price"/>  
              
              
              
            <association property="person" javaType="Person">  
                <id column="personId" property="id"/>  
                <result column="personName" property="name"/>  
                <result column="personAddress" property="address"/>  
                <result column="personTel" property="tel"/>  
            </association>  
        </resultMap>  
          
          
        <select id="selectOrderById" parameterType="string" resultMap="orderBean">  
            select p.*, o.* from person p, orders o where p.personId  = o.pid and o.orderId = #{id}  
        </select>  
          
    </mapper>

### (6)SqlMapConfig.xml配置 ###

<?xml version="1.0" encoding="UTF-8" ?>  
    <!DOCTYPE configuration  
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"  
    "http://mybatis.org/dtd/mybatis-3-config.dtd">  
    <configuration>  
        <typeAliases>  
            <typeAlias type="com.zdp.domain.Person" alias="Person"/>    
            <typeAlias type="com.zdp.domain.Order" alias="Order"/>    
        </typeAliases>  
        <environments default="development">  
            <environment id="development">  
                <transactionManager type="JDBC" />  
                <dataSource type="POOLED">  
                    <property name="driver" value="com.mysql.jdbc.Driver" />  
                    <property name="url" value="jdbc:mysql://localhost/test" />  
                    <property name="username" value="root" />  
                    <property name="password" value="root" />  
                </dataSource>  
            </environment>  
        </environments>  
        <mappers>  
              
            <mapper resource="com/zdp/domain/Person.xml" />  
            <mapper resource="com/zdp/domain/Order.xml" />  
        </mappers>  
    </configuration>

### (7)测试代码 ###

/** 
     * 测试一对多和多对一 
     */  
    public class MybatisTest {  
      
        private SqlSessionFactory ssf;   
      
        @Before  
        public void initSF() throws Exception {  
            String resource = "sqlMapConfig.xml";  
            InputStream inputStream = Resources.getResourceAsStream(resource);  
            ssf = new SqlSessionFactoryBuilder().build(inputStream);  
        }  
      
        @Test//一对多关联查询  
        public void selectPersonById()throws Exception{  
            SqlSession session = ssf.openSession();  
            Person person = session.selectOne("com.zdp.domain.Person.selectPersonById", "001");  
            System.out.println(person.getOrders());  
        }  
          
        @Test//多对一关联查询  
        public void selectOrderById()throws Exception{  
            SqlSession session = ssf.openSession();  
            Order order = session.selectOne("com.zdp.domain.Order.selectOrderById", "O_00001");  
            System.out.println(order.getPerson().getName());  
        }  
    }

## 3.参考博文 ##

(1)http://www.cnblogs.com/xdp-gacl/p/4264440.html

(2)http://blog.csdn.net/jkxiaoxing/article/details/52199386