MyBatis研习录(08)——自定义结果集标签resultMap


C语言自学完备手册(33篇)

Android多分辨率适配框架

JavaWeb核心技术系列教程

HTML5前端开发实战系列教程

MySQL数据库实操教程(35篇图文版)

推翻自己和过往——自定义View系列教程(10篇)

走出思维困境,踏上精进之路——Android开发进阶精华录

讲给Android程序员看的前端系列教程(40集免费视频教程+源码)


版权声明

  • 本文原创作者:谷哥的小弟
  • 作者博客地址:http://blog.csdn.net/lfdfhl

概述

在之前的学习中,我们使用resultType将查询到的结果集转换成为简单的JavaBean对象(pojo)。假若,我们要将查询结果转换为复杂的JavaBean;此时,resultType难以胜任,需要使用resultMap来完成。

数据准备

DROP DATABASE IF EXISTS mybatisDatabase;
CREATE DATABASE mybatisDatabase;
USE mybatisDatabase;
CREATE TABLE enterprise(
  id INT PRIMARY KEY auto_increment,
  enterpriseName VARCHAR(50)
);

CREATE TABLE founder(
  id INT primary key auto_increment,
  founderName VARCHAR(50),
  enterpriseID INT,
  FOREIGN KEY(enterpriseID) REFERENCES enterprise(id)
);

INSERT INTO enterprise(enterpriseName) VALUES('淘宝');
INSERT INTO enterprise(enterpriseName) VALUES('华为');
INSERT INTO enterprise(enterpriseName) VALUES('腾讯');
INSERT INTO enterprise(enterpriseName) VALUES('百度');
INSERT INTO enterprise(enterpriseName) VALUES('京东');

INSERT INTO founder(founderName,enterpriseID) VALUES('马云',1);
INSERT INTO founder(founderName,enterpriseID) VALUES('任正非',2);
INSERT INTO founder(founderName,enterpriseID) VALUES('马化腾',3);
INSERT INTO founder(founderName,enterpriseID) VALUES('李彦宏',4);
INSERT INTO founder(founderName,enterpriseID) VALUES('刘强东',5);

SELECT * FROM enterprise;

SELECT * FROM founder;

搭建开发环境

创建普通的Java工程,结构如下:
在这里插入图片描述

Enterprise

package cn.com.pojo;

public class Enterprise {
	private Integer id;
	private String enterpriseName;
	public Enterprise() {
		super();
	}
	public Enterprise(Integer id, String enterpriseName) {
		super();
		this.id = id;
		this.enterpriseName = enterpriseName;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getEnterpriseName() {
		return enterpriseName;
	}
	public void setEnterpriseName(String enterpriseName) {
		this.enterpriseName = enterpriseName;
	}
	@Override
	public String toString() {
		return "Enterprise [id=" + id + ", enterpriseName=" + enterpriseName + "]";
	}
	
}

Founder

package cn.com.pojo;

public class Founder {
	private Integer id;
	private String founderName;
	private Enterprise enterprise;
	public Founder() {
		super();
	}
	public Founder(Integer id, String founderName, Enterprise enterprise) {
		super();
		this.id = id;
		this.founderName = founderName;
		this.enterprise = enterprise;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getFounderName() {
		return founderName;
	}
	public void setFounderName(String founderName) {
		this.founderName = founderName;
	}
	public Enterprise getEnterprise() {
		return enterprise;
	}
	public void setEnterprise(Enterprise enterprise) {
		this.enterprise = enterprise;
	}
	@Override
	public String toString() {
		return "Founder [id=" + id + ", founderName=" + founderName + ", enterprise=" + enterprise + "]";
	}
	
}

FounderMapper.java

package cn.com.mapper;

import cn.com.pojo.Founder;

public interface FounderMapper {
	
	//利用resultType映射复杂pojo
	public Founder queryFounderById(Integer id);
	
	//利用resultMap映射复杂pojo
	public Founder queryFounderByIdUseResultMap(Integer id);
	
	//利用resultMap结合association映射复杂pojo
	public Founder queryFounderByIdUseResultMapAndAssociation(Integer id);
	
	//利用resultMap结合association的分步查询映射复杂pojo
	public Founder queryFounderByIdUseResultMapAndAssociationByTwoStep(Integer id);
	
}

EnterpriseMapper.java

package cn.com.mapper;

import cn.com.pojo.Enterprise;
/**
 * 本文作者:谷哥的小弟 
 * 博客地址:http://blog.csdn.net/lfdfhl
 */
public interface EnterpriseMapper {
	
	public Enterprise queryEnterpriseById(Integer id);
	
}

FounderMapper.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.com.mapper.FounderMapper">

  <!-- 利用resultType映射复杂pojo -->
  <select id="queryFounderById" resultType="cn.com.pojo.Founder">
		SELECT 
			founder.*,enterprise.enterpriseName
		FROM 
			founder LEFT JOIN enterprise
		ON 
			founder.enterpriseID = enterprise.id
		WHERE 
			founder.id = #{id}
  </select>

  <!-- 利用resultMap映射复杂pojo -->
  <select id="queryFounderByIdUseResultMap" resultMap="queryFounderResultMap">
		SELECT 
			founder.*,enterprise.enterpriseName
		FROM 
			founder LEFT JOIN enterprise
		ON 
			founder.enterpriseID = enterprise.id
		WHERE 
			founder.id = #{id}
  </select>
  
  <resultMap id="queryFounderResultMap" type="cn.com.pojo.Founder">
      <id column="id" property="id"/>
      <result column="founderName" property="founderName"/>
      <result column="enterpriseID" property="enterprise.id"/>
      <result column="enterpriseName" property="enterprise.enterpriseName"/>
  </resultMap>	
  	

  <!-- 利用resultMap结合association映射复杂pojo -->
  <select id="queryFounderByIdUseResultMapAndAssociation" resultMap="queryFounderResultMapAndAssociation">
		SELECT 
			founder.*,enterprise.enterpriseName
		FROM 
			founder LEFT JOIN enterprise
		ON 
			founder.enterpriseID = enterprise.id
		WHERE 
			founder.id = #{id}
  </select>
  
  <resultMap id="queryFounderResultMapAndAssociation" type="cn.com.pojo.Founder">
      <id column="id" property="id"/>
      <result column="founderName" property="founderName"/>
      <association property="enterprise" javaType="cn.com.pojo.Enterprise">
	      <id column="enterpriseID" property="id"/>
	      <result column="enterpriseName" property="enterpriseName"/>
      </association>
  </resultMap>
  
  
  <!-- 利用resultMap结合association的分步查询映射复杂pojo -->
  <select id="queryFounderByIdUseResultMapAndAssociationByTwoStep" resultMap="queryFounderResultMapAndAssociationTwoStep">
		select id,founderName,enterpriseID from founder where id=#{id}
  </select>
  
  <resultMap id="queryFounderResultMapAndAssociationTwoStep" type="cn.com.pojo.Founder">
      <id column="id" property="id"/>
      <result column="founderName" property="founderName"/>
      <association property="enterprise" select="cn.com.mapper.EnterpriseMapper.queryEnterpriseById" column="enterpriseID"/>
  </resultMap>
  
  
</mapper>

EnterpriseMapper.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.com.mapper.EnterpriseMapper">
  
  <select id="queryEnterpriseById" resultType="cn.com.pojo.Enterprise">
		select id,enterpriseName from enterprise where id=#{id}
  </select>
  
</mapper>

MybatisTest

package cn.com.test;

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;
import org.junit.Test;
import cn.com.mapper.FounderMapper;
import cn.com.pojo.Enterprise;
import cn.com.pojo.Founder;
/**
 * 本文作者:谷哥的小弟 
 * 博客地址:http://blog.csdn.net/lfdfhl
 */
public class MybatisTest {

	static SqlSessionFactory sqlSessionFactory = null;

	public static SqlSessionFactory getSqlSessionFactory() {
		try {
			if (sqlSessionFactory == null) {
				InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
				SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
				sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
			}
			return sqlSessionFactory;
		} catch (Exception e) {
			
		} finally {

		}
		return null;
	}
	
	@Test
	public void testQueryFounderById() {
		//获取SqlSession
		SqlSession sqlSession=getSqlSessionFactory().openSession();
		//利用SqlSession得到Mapper接口
		FounderMapper founderMapper = sqlSession.getMapper(FounderMapper.class);
		//利用SqlSession执行数据操作
		Founder founder = founderMapper.queryFounderById(2);
		System.out.println(founder);
		//关闭SqlSession
		sqlSession.close();
	}
	

	@Test
	public void testQueryFounderByIdUseResultMap() {
		//获取SqlSession
		SqlSession sqlSession=getSqlSessionFactory().openSession();
		//利用SqlSession得到Mapper接口
		FounderMapper founderMapper = sqlSession.getMapper(FounderMapper.class);
		//利用SqlSession执行数据操作
		Founder founder = founderMapper.queryFounderByIdUseResultMap(2);
		System.out.println(founder);
		//关闭SqlSession
		sqlSession.close();
	}
	
	@Test
	public void testQueryFounderByIdUseResultMapAndAssociation () {
		//获取SqlSession
		SqlSession sqlSession=getSqlSessionFactory().openSession();
		//利用SqlSession得到Mapper接口
		FounderMapper founderMapper = sqlSession.getMapper(FounderMapper.class);
		//利用SqlSession执行数据操作
		Founder founder = founderMapper.queryFounderByIdUseResultMapAndAssociation(2);
		System.out.println(founder);
		//关闭SqlSession
		sqlSession.close();
	}
	
	@Test
	public void testQueryFounderResultMapAndAssociationTwoStep () {
		//获取SqlSession
		SqlSession sqlSession=getSqlSessionFactory().openSession();
		//利用SqlSession得到Mapper接口
		FounderMapper founderMapper = sqlSession.getMapper(FounderMapper.class);
		//利用SqlSession执行数据操作
		Founder founder = founderMapper.queryFounderByIdUseResultMapAndAssociationByTwoStep(2);
		
		//第一次测试
		//String founderName = founder.getFounderName();
		//System.out.println(founderName);
		
		//第二次测试
		Enterprise enterprise = founder.getEnterprise();
		System.out.println(enterprise);
		
		//关闭SqlSession
		sqlSession.close();
	}
	
	
}

log4j.properties

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

mybatis-config.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>
  <!-- 配置数据源 -->
  <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:3306/mybatisDatabase"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
      </dataSource>
    </environment>
  </environments>
  
  <!-- 配置mapper -->
  <mappers>
    <mapper resource="cn/com/pojo/FounderMapper.xml"/>
    <mapper resource="cn/com/pojo/EnterpriseMapper.xml"/>
  </mappers>
  
</configuration>

实验过程及其分析

在此,我们以示例结合试验的方式学习resultMap

利用resultType映射复杂pojo

FounderMapper.java

//利用resultType映射复杂pojo
public Founder queryFounderById(Integer id);

FounderMapper.xml

<!-- 利用resultType映射复杂pojo -->
<select id="queryFounderById" resultType="cn.com.pojo.Founder">
	SELECT 
		founder.*,enterprise.enterpriseName
	FROM 
		founder LEFT JOIN enterprise
	ON 
		founder.enterpriseID = enterprise.id
	WHERE 
		founder.id = #{id}
</select>

MybatisTest.java

@Test
public void testQueryFounderById() {
	//获取SqlSession
	SqlSession sqlSession=getSqlSessionFactory().openSession();
	//利用SqlSession得到Mapper接口
	FounderMapper founderMapper = sqlSession.getMapper(FounderMapper.class);
	//利用SqlSession执行数据操作
	Founder founder = founderMapper.queryFounderById(2);
	System.out.println(founder);
	//关闭SqlSession
	sqlSession.close();
}

测试结果
在这里插入图片描述
利用resultType可映射出了Founder的基本属性id和founderName,但是不能映射出Founder中的cn.com.pojo.Enterprise类型的enterprise。

利用resultMap映射复杂pojo

FounderMapper.java

//利用resultMap映射复杂pojo
public Founder queryFounderByIdUseResultMap(Integer id);

FounderMapper.xml

<!-- 利用resultMap映射复杂pojo -->
<select id="queryFounderByIdUseResultMap" resultMap="queryFounderResultMap">
	SELECT 
		founder.*,enterprise.enterpriseName
	FROM 
		founder LEFT JOIN enterprise
	ON 
		founder.enterpriseID = enterprise.id
	WHERE 
		founder.id = #{id}
</select>

<resultMap id="queryFounderResultMap" type="cn.com.pojo.Founder">
    <id column="id" property="id"/>
    <result column="founderName" property="founderName"/>
    <result column="enterpriseID" property="enterprise.id"/>
    <result column="enterpriseName" property="enterprise.enterpriseName"/>
</resultMap>	
  • 1、在select标签中将查询结果交给resultMap标签处理。所以,select标签中resultMap属性的值与resultMap标签中id属性的值相同。
  • 2、resultMap标签中type属性的值表示将查询结果转换JavaBean的类型。
  • 3、resultMap标签中的id子标签用于将查询结果的主键列转换到JavaBean对象对应的属性。其中,column属性表示查询结果的列名,property属性表示JavaBean对象对应的属性
  • 4、resultMap标签中的result 子标签用于将查询结果的非主键列转换到JavaBean对象对应的属性。其中,column属性表示查询结果的列名,property属性表示JavaBean对象对应的属性

MybatisTest.java

@Test
public void testQueryFounderByIdUseResultMap() {
	//获取SqlSession
	SqlSession sqlSession=getSqlSessionFactory().openSession();
	//利用SqlSession得到Mapper接口
	FounderMapper founderMapper = sqlSession.getMapper(FounderMapper.class);
	//利用SqlSession执行数据操作
	Founder founder = founderMapper.queryFounderByIdUseResultMap(2);
	System.out.println(founder);
	//关闭SqlSession
	sqlSession.close();
}

测试结果
在这里插入图片描述
利用resultMap不但可映射出了Founder的基本属性id和founderName,还可以映射出Founder中的cn.com.pojo.Enterprise类型的enterprise。

利用resultMap结合association映射复杂pojo

FounderMapper.java

//利用resultMap结合association映射复杂pojo
public Founder queryFounderByIdUseResultMapAndAssociation(Integer id);

FounderMapper.xml

<!-- 利用resultMap结合association映射复杂pojo -->
<select id="queryFounderByIdUseResultMapAndAssociation" resultMap="queryFounderResultMapAndAssociation">
	SELECT 
		founder.*,enterprise.enterpriseName
	FROM 
		founder LEFT JOIN enterprise
	ON 
		founder.enterpriseID = enterprise.id
	WHERE 
		founder.id = #{id}
</select>

<resultMap id="queryFounderResultMapAndAssociation" type="cn.com.pojo.Founder">
    <id column="id" property="id"/>
    <result column="founderName" property="founderName"/>
    <association property="enterprise" javaType="cn.com.pojo.Enterprise">
     <id column="enterpriseID" property="id"/>
     <result column="enterpriseName" property="enterpriseName"/>
    </association>
</resultMap>
  • 1、在select标签中将查询结果交给resultMap标签处理。所以,select标签中resultMap属性的值与resultMap标签中id属性的值相同。
  • 2、resultMap标签中type属性的值表示将查询结果转换JavaBean的类型。
  • 3、resultMap标签中的id子标签用于将查询结果的主键列转换到JavaBean对象对应的属性。其中,column属性表示查询结果的列名,property属性表示JavaBean对象对应的属性
  • 4、resultMap标签中的result 子标签用于将查询结果的菲主键列转换到JavaBean对象对应的属性。其中,column属性表示查询结果的列名,property属性表示JavaBean对象对应的属性
  • 5、在处理完Founder的简单属性id和founderName之后再使用association标签处理Founder中的cn.com.pojo.Enterprise类型的enterprise,它也常被称为Founder的子对象。association标签的property属性表示子对象的名称,association标签的javaType属性表示子对象的全路径(类型)。association标签中的id子标签用于将查询结果的主键列转换到子对象对应的属性;其中,column属性表示查询结果的列名,property属性表示子对象对应的属性。类似地,association标签中的result子标签用于将查询结果的非主键列转换到子对象对应的属性;其中,column属性表示查询结果的列名,property属性表示子对象对应的属性。

MybatisTest.java

@Test
public void testQueryFounderByIdUseResultMapAndAssociation () {
	//获取SqlSession
	SqlSession sqlSession=getSqlSessionFactory().openSession();
	//利用SqlSession得到Mapper接口
	FounderMapper founderMapper = sqlSession.getMapper(FounderMapper.class);
	//利用SqlSession执行数据操作
	Founder founder = founderMapper.queryFounderByIdUseResultMapAndAssociation(2);
	System.out.println(founder);
	//关闭SqlSession
	sqlSession.close();
}

测试结果
在这里插入图片描述
利用resultMap不但可映射出了Founder的基本属性id和founderName,还可以映射出Founder中的cn.com.pojo.Enterprise类型的enterprise。

利用resultMap结合association的分步立即查询映射复杂pojo

FounderMapper.java

//利用resultMap结合association的分步立即查询映射复杂pojo
public Founder queryFounderByIdUseResultMapAndAssociationByTwoStep(Integer id);

FounderMapper.xml

<!-- 利用resultMap结合association的分步立即查询映射复杂pojo -->
<select id="queryFounderByIdUseResultMapAndAssociationByTwoStep" resultMap="queryFounderResultMapAndAssociationTwoStep">
     select id,founderName,enterpriseID from founder where id=#{id}
</select>

<resultMap id="queryFounderResultMapAndAssociationTwoStep" type="cn.com.pojo.Founder">
    <id column="id" property="id"/>
    <result column="founderName" property="founderName"/>
    <association property="enterprise" select="cn.com.mapper.EnterpriseMapper.queryEnterpriseById" column="enterpriseID"/>
</resultMap>

该示例为resultMap结合association的分步立即查询。我们先来说:什么是分步查询?

该示例中:
Founder类有三个属性:Integer id、String founderName、Enterprise enterprise
founder表有三个字段:id、founderName、enterpriseID
Enterprise类有两个属性:Integer id、String enterpriseName
enterprise表有两个字段:id、enterpriseName

假若,我们需要获取Founder对象中的enterprise,那么就可从founder表中查询出enterpriseID再依据该enterpriseID从enterprise查询出enterpriseName。

代码分析:

  • 1、在select标签中将查询结果交给resultMap标签处理。所以,select标签中resultMap属性的值与resultMap标签中id属性的值相同。
  • 2、resultMap标签中type属性的值表示将查询结果转换JavaBean的类型。
  • 3、resultMap标签中的id子标签用于将查询结果的主键列转换到JavaBean对象对应的属性。其中,column属性表示查询结果的列名,property属性表示JavaBean对象对应的属性
  • 4、resultMap标签中的result 子标签用于将查询结果的菲主键列转换到JavaBean对象对应的属性。其中,column属性表示查询结果的列名,property属性表示JavaBean对象对应的属性
  • 5、在处理完Founder的简单属性id和founderName之后再使用association标签处理Founder中的cn.com.pojo.Enterprise类型的enterprise,它也常被称为Founder的子对象。此时,我们需要使用select标签中查询得到的enterpriseID去enterprise表中查询enterpriseName。association中property属性表示将配置的子对象;select属性表示通过哪个子查询得到该对象 ,其表现形式为:xxxMapper.xml的命名空间.标签id;column属性表示将哪个列做为参数传递给子查询。

MybatisTest.java

@Test
public void testQueryFounderResultMapAndAssociationTwoStep () {
	//获取SqlSession
	SqlSession sqlSession=getSqlSessionFactory().openSession();
	//利用SqlSession得到Mapper接口
	FounderMapper founderMapper = sqlSession.getMapper(FounderMapper.class);
	//利用SqlSession执行数据操作
	Founder founder = founderMapper.queryFounderByIdUseResultMapAndAssociationByTwoStep(2);
	
	//第一次测试
	String founderName = founder.getFounderName();
	System.out.println(founderName);
	
	//第二次测试
	//Enterprise enterprise = founder.getEnterprise();
	//System.out.println(enterprise);
	
	//关闭SqlSession
	sqlSession.close();
}

测试结果

第一次测试

在这里插入图片描述
第二次测试

在这里插入图片描述
通过实验我们发现:可以通过两次分步查询获取数据。

在查询创始人公司时分了两步进行,这个无可厚非;但是,在查询创始人名字时居然也分了两步进行;而且,第二步是多余的,根本不需要的!为了解决类似的问题,提升查询效率需要使用延迟查询。

利用resultMap结合association的分步延迟查询映射复杂pojo

为实现延迟查询需在mybatis-config.xml中配置如下设置:

<!-- 配置延迟加载 -->
<settings>
   <!-- 打开延迟加载开关 -->  
   <setting name="lazyLoadingEnabled" value="true" />  
   <!-- 将积极加载改为消极加载 -->  
   <setting name="aggressiveLazyLoading" value="false"/> 
</settings>

修改后的mybatis-config.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>

  <!-- 配置延迟加载 -->
  <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="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/mybatisDatabase"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
      </dataSource>
    </environment>
  </environments>
  
  <!-- 配置mapper -->
  <mappers>
    <mapper resource="cn/com/pojo/FounderMapper.xml"/>
    <mapper resource="cn/com/pojo/EnterpriseMapper.xml"/>
  </mappers>
  
</configuration>

测试结果

在修改完毕mybatis-config.xml的配置后,我们再进行测试。

第一次测试
在这里插入图片描述
第二次测试
在这里插入图片描述
通过实验我们发现:在开启延时查询后查询创始人公司时分了两步进行,查询创始人名字时只进行了一次查询。

发布了1019 篇原创文章 · 获赞 1913 · 访问量 233万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 精致技术 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览