MyBatis--一对一关联查询（级联查询）的三种方式

ゝ一世哀愁。 2021-08-13 18:27 1015阅读 0赞

#### 一对一关联查询 ####

在 MyBatis 中，通过 元素的子元素 处理这种一对一级联关系。

在 元素中通常使用以下属性。

*  property：指定映射到实体类的对象属性。
 *  column：指定表中对应的字段（即查询返回的列名）
 *  javaType：指定映射到实体对象属性的类型。
 *  select：指定引入嵌套查询的子 SQL 语句，该属性用于关联映射中的嵌套查询。

#### 示例 ####

下面以个人与身份证之间的关系为例演示一对一级联查询的处理过程。

**建表及准备数据：**

CREATE TABLE idcard (
        id tinyint(2) NOT NULL AUTO_INCREMENT,
        code varchar(18) COLLATE utf8_unicode_ci DEFAULT NULL,
        PRIMARY KEY (id)
    );
    
    CREATE TABLE person(
        id tinyint(2) NOT NULL,
        name varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
        age int(11) DEFAULT NULL,
        idcard_id tinyint(2) DEFAULT NULL,
        PRIMARY KEY (id),
        KEY idcard_id(idcard_id),
        CONSTRAINT idcard_id FOREIGN KEY (idcard_id) REFERENCES idcard(id)
    );

![在这里插入图片描述][20191028180410669.png]![在这里插入图片描述][2019102818042556.png]  
**创建工程并导入相关JAR包：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]  
**创建持久化类Idcard 、Person：**

package pers.zhang.po;
    
    
    public class Idcard { 
        private Integer id;
        private String code;
    
        
        public Integer getId() { 
    		return id;
    	}
    
    
    	public void setId(Integer id) { 
    		this.id = id;
    	}
    
    
    	public String getCode() { 
    		return code;
    	}
    
    
    	public void setCode(String code) { 
    		this.code = code;
    	}
    
    
    	@Override
        public String toString() { 
            return "Idcard [id=" + id + ",code=" + code + "]";
        }
    }

package pers.zhang.po;
    
    public class Person { 
        private Integer id;
        private String name;
        private Integer age;
    
        // 个人身份证关联
        private Idcard card;
    
        
        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 Integer getAge() { 
    		return age;
    	}
    
    
    	public void setAge(Integer age) { 
    		this.age = age;
    	}
    
    
    	public Idcard getCard() { 
    		return card;
    	}
    
    
    	public void setCard(Idcard card) { 
    		this.card = card;
    	}
    
    
    	@Override
        public String toString() { 
            return "Person[id=" + id + ",name=" + name + ",age=" + age + ",card="
                    + card + "]";
        }
    }

**创建映射文件：**  
jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
    jdbc.url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8
    jdbc.username=root
    jdbc.password=123456

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="jdbc.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:mysql://localhost:3306/mybatis?characterEncoding=utf-8" />
    				<property name="username" value="root" />
    				<property name="password" value="123456" />
    			</dataSource>
    		</environment>
    	</environments>
    	
    	<mappers>
    		<package name="pers.zhang.mapper"/>
    	</mappers>
    </configuration>

IdCardMapper.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="pers.zhang.mapper.IdCardMapper">
        <select id="selectCodeById" parameterType="Integer" resultType= "pers.zhang.po.Idcard">
            select * from idcard where id=#{id}
        </select>
    </mapper>

PersonMapper.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="pers.zhang.mapper.PersonMapper">
        
        <resultMap type="pers.zhang.po.Person" id="cardAndPerson1">
            <id property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="age" column="age"/>
            
            <association property="card" column="idcard_id" javaType="pers.zhang.po.Person" select="pers.zhang.mapper.IdCardMapper.selectCodeById"/>
        </resultMap>
        <select id="selectPersonById1" parameterType="Integer" resultMap=
        "cardAndPerson1">
            select * from person where id=#{id}
        </select>
        
        
        
        
        <resultMap type="pers.zhang.po.Person" id="cardAndPerson2">
            <id property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="age" column="age"/>
            
            <association property="card" javaType="pers.zhang.po.Idcard">
                <id property="id" column="idcard_id"/>
                <result property="code" column="code"/>
            </association>
        </resultMap>
    
        <select id="selectPersonById2" parameterType="Integer" resultMap= "cardAndPerson2">
            select p.*,ic.code
            from person p, idcard ic
            where p.idcard_id=ic.id and p.id=#{id}
        </select>
        
        
    
        
        <select id="selectPersonById3" parameterType="Integer" resultType= "pers.zhang.po.SelectPersonById">
            select p.*,ic.code
            from person p, idcard ic
            where p.idcard_id = ic.id and p.id=#{id}
        </select>
    </mapper>

**创建Mapper接口**  
IdCardMapper.java

package pers.zhang.mapper;
    
    import pers.zhang.po.Idcard;
    
    public interface IdCardMapper { 
    
    	public Idcard selectCodeById(Integer i);
    }

PersonMapper.java

package pers.zhang.mapper;
    
    import pers.zhang.po.Idcard;
    import pers.zhang.po.Person;
    import pers.zhang.po.SelectPersonById;
    
    public interface PersonMapper { 
    
        public Person selectPersonById1(Integer id);
        public Person selectPersonById2(Integer id);
        public SelectPersonById selectPersonById3(Integer id);
    }

**创建pojo：**

package pers.zhang.po;
    
    public class SelectPersonById { 
        private Integer id;
        private String name;
        private Integer age;
        private String code;
        
        
        
        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 Integer getAge() { 
    		return age;
    	}
    
    
    
    	public void setAge(Integer age) { 
    		this.age = age;
    	}
    
    
    
    	public String getCode() { 
    		return code;
    	}
    
    
    
    	public void setCode(String code) { 
    		this.code = code;
    	}
    
    
    
    	@Override
        public String toString() { 
            return "Person [id=" +id+",name=" +name+ ",age=" +age+ ",code=" +code+ "]";
        }
    }

#### 测试 ####

**第一种方式：嵌套查询，执行两个SQL语句**

@Test
    public void test2() throws Exception{ 
    	//加载核心配置文件
    	String resource = "sqlMapConfig.xml";
    	InputStream in = Resources.getResourceAsStream(resource);
    	//创建SqlSessionFactory
    	SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
    	//获得sqlSession
    	SqlSession sqlSession = sqlSessionFactory.openSession();
    	//获得Mapper
    	PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
    	Integer id = 2;
    	Person person = mapper.selectPersonById1(id);
    	System.out.println(person);
    }

控制台输出：

DEBUG [main] - ==>  Preparing: select * from person where id=? 
    DEBUG [main] - ==> Parameters: 2(Integer)
    DEBUG [main] - <==      Total: 1
    DEBUG [main] - ==>  Preparing: select * from idcard where id=? 
    DEBUG [main] - ==> Parameters: 2(Integer)
    DEBUG [main] - <==      Total: 1
    Person[id=2,name=李四,age=28,card=Idcard [id=2,code=2222]]

**第二种方式：连接查询，结果映射到持久化类中**

@Test
    public void test3() throws Exception{ 
    	//加载核心配置文件
    	String resource = "sqlMapConfig.xml";
    	InputStream in = Resources.getResourceAsStream(resource);
    	//创建SqlSessionFactory
    	SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
    	//获得sqlSession
    	SqlSession sqlSession = sqlSessionFactory.openSession();
    	//获得Mapper
    	PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
    	Integer id = 1;
    	Person person = mapper.selectPersonById2(id);
    	System.out.println(person);
    }

控制台打印：

DEBUG [main] - ==>  Preparing: select p.*,ic.code from person p, idcard ic where p.idcard_id=ic.id and p.id=? 
    DEBUG [main] - ==> Parameters: 1(Integer)
    DEBUG [main] - <==      Total: 1
    Person[id=1,name=张三,age=18,card=Idcard [id=1,code=1111]]

**第三种方式：连接查询，结果映射到POJO中**

@Test
    public void test4() throws Exception{ 
    	//加载核心配置文件
    	String resource = "sqlMapConfig.xml";
    	InputStream in = Resources.getResourceAsStream(resource);
    	//创建SqlSessionFactory
    	SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
    	//获得sqlSession
    	SqlSession sqlSession = sqlSessionFactory.openSession();
    	//获得Mapper
    	PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
    	Integer id = 1;
    	SelectPersonById spb = mapper.selectPersonById3(id);
    	System.out.println(spb);
    }

控制台打印：

DEBUG [main] - ==>  Preparing: select p.*,ic.code from person p, idcard ic where p.idcard_id = ic.id and p.id=? 
    DEBUG [main] - ==> Parameters: 1(Integer)
    DEBUG [main] - <==      Total: 1
    Person [id=1,name=张三,age=18,code=1111]

[20191028180410669.png]: https://img-blog.csdnimg.cn/20191028180410669.png
[2019102818042556.png]: /images/20210813/2a238cc1fc52458dbe3fe20ccf6acdb2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]: /images/20210813/d3edf9519dca4bceb77a09e72f532f14.png