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

最新推荐文章于 2024-05-03 22:28:10 发布

吴声子夜歌

最新推荐文章于 2024-05-03 22:28:10 发布

阅读量3.3k

点赞数 3

分类专栏： Mybatis 文章标签： MyBatis 一对一级联查询

本文链接：https://blog.csdn.net/cold___play/article/details/102785791

版权

Mybatis 专栏收录该内容

35 篇文章 3 订阅

订阅专栏

一对一关联查询

在 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)
);

在这里插入图片描述
创建工程并导入相关JAR包：

创建持久化类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"/>
	
	<!--在使用MyBatis嵌套查询方式进行关联查询时，使用MyBatis的延迟加载可以在一定程度上提高查询效率-->
	<settings>
    <!--打开延迟加载的开关-->
    	<setting name= "lazyLoadingEnabled" value= "true"/>
    <!--将积极加载改为按需加载-->
    	<setting name="aggressiveLazyLoading" value="false"/>
	</settings>
	
	<environments default="development">
		<environment id="development">
			<!-- 使用jdbc事务管理 -->
			<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">
    <!-- 一对一根据id查询个人信息：级联查询的第一种方法（嵌套查询，执行两个SQL语句）-->
    <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>
    
    
    
    <!--对一根据id查询个人信息：级联查询的第二种方法（嵌套结果，执行一个SQL语句）-->
    <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>
    
    

    <!-- 一对一根据id查询个人信息：连接查询（使用POJO存储结果）-->
    <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]

吴声子夜歌

关注

3
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
MyBatis--一对一关联查询（级联查询）的三种方式

一对一关联查询在 MyBatis 中，通过元素的子元素处理这种一对一级联关系。在元素中通常使用以下属性。property：指定映射到实体类的对象属性。column：指定表中对应的字段（即查询返回的列名）javaType：指定映射到实体对象属性的类型。select：指定引入嵌套查询的子 SQL 语句，该属性用于关联映射中的嵌套查询。示例下面以个人与身份证之间的关系为例...
复制链接

扫一扫