Mybatis 关联查询（嵌套查询）

会员服务 ·

Mybatis 关联查询（嵌套查询）

2018 年 6 月 18 日 ImportNew

（点击上方公众号，可快速关注）

来源：阿凡卢，

www.cnblogs.com/luxiaoxun/p/4035276.html

上一篇文章《Mybatis实现数据的增删改查（CRUD）》介绍了基于Mybatis对数据库的增、删、改、查。这一篇介绍下关联查询（join query）。

http://www.importnew.com/24591.html

三张表：user article blog

表的存储sql文件：

/*

Navicat MySQL Data Transfer

Source Server : localhost

Source Server Version : 50620

Source Host : localhost:3306

Source Database : mybatis

Target Server Type : MYSQL

Target Server Version : 50620

File Encoding : 65001

Date: 2014-10-19 18:27:31

*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for `user`

-- ----------------------------

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`userName` varchar(50) DEFAULT NULL,

`userAge` int(11) DEFAULT NULL,

`userAddress` varchar(200) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of user

-- ----------------------------

INSERT INTO `user` VALUES ('1', 'summer', '30', 'shanghai');

INSERT INTO `user` VALUES ('2', 'test1', '22', 'suzhou');

INSERT INTO `user` VALUES ('3', 'test1', '29', 'some place');

INSERT INTO `user` VALUES ('4', 'lu', '28', 'some place');

INSERT INTO `user` VALUES ('5', 'xiaoxun', '27', 'nanjing');

-- ----------------------------

-- Table structure for `article`

-- ----------------------------

DROP TABLE IF EXISTS `article`;

CREATE TABLE `article` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`userid` int(11) NOT NULL,

`title` varchar(100) DEFAULT NULL,

`content` text,

`blogid` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of article

-- ----------------------------

INSERT INTO `article` VALUES ('1', '1', 'test_title_1', 'test_content_1', '1');

INSERT INTO `article` VALUES ('2', '1', 'test_title_2', 'test_content_2', '1');

INSERT INTO `article` VALUES ('3', '1', 'test_title_3', 'test_content_3', '2');

INSERT INTO `article` VALUES ('4', '1', 'test_title_4', 'test_content_4', '2');

INSERT INTO `article` VALUES ('5', '2', 'test_title_5', 'test_content_5', '2');

-- ----------------------------

-- Table structure for `blog`

-- ----------------------------

DROP TABLE IF EXISTS `blog`;

CREATE TABLE `blog` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`title` varchar(200) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of blog

-- ----------------------------

INSERT INTO `blog` VALUES ('1', 'xiaoxun_blog');

INSERT INTO `blog` VALUES ('2', 'zhang_blog');

配置文件Configuration.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 alias="User" type="com.mybatis.test.User"/>

<typeAlias alias="Article" type="com.mybatis.test.Article"/>

<typeAlias alias="Blog" type="com.mybatis.test.Blog"/>

</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://127.0.0.1:3306/mybatis" />

<property name="username" value="root"/>

<property name="password" value="admin"/>

</dataSource>

</environment>

</environments>



<mappers>

<mapper resource="com/mybatis/test/User.xml"/>

<mapper resource="com/mybatis/test/Article.xml"/>

<mapper resource="com/mybatis/test/Blog.xml"/>

</mappers>

</configuration>

User类的定义和User.xml的配置见上一文章。

Article类定义：

package com.mybatis.test;

public class Article {

private int id;

private User user;

private String title;

private String content;

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public User getUser() {

return user;

}

public void setUser(User user) {

this.user = user;

}

public String getTitle() {

return title;

}

public void setTitle(String title) {

this.title = title;

}

public String getContent() {

return content;

}

public void setContent(String content) {

this.content = content;

}

}

Article类中有一个User类。

Article.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.mybatis.test.IArticleOperation">

<resultMap type="User" id="userResultMap">



<id property="id" column="user_id" />

<result property="userName" column="user_userName" />

<result property="userAge" column="user_userAge" />

<result property="userAddress" column="user_userAddress" />

</resultMap>



<resultMap id="articleResultMap" type="Article">

<id property="id" column="article_id" />

<result property="title" column="article_title" />

<result property="content" column="article_content" />



<association property="user" javaType="User" resultMap="userResultMap"/>

</resultMap>



<select id="getUserArticles" parameterType="int" resultMap="articleResultMap">

select user.id user_id,user.userName user_userName,user.userAddress user_userAddress,

article.id article_id,article.title article_title,article.content article_content

from user,article

where user.id=article.userid and user.id=#{id}

</select>





</mapper>

Blog类定义：

package com.mybatis.test;

import java.util.List;

public class Blog {

private int id;

private String title;

private List<Article> articles;

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getTitle() {

return title;

}

public void setTitle(String title) {

this.title = title;

}

public List<Article> getArticles() {

return articles;

}

public void setArticles(List<Article> articles) {

this.articles = articles;

}

}

Blog类中有一个List<Article>。

Blog.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.mybatis.test.IBlogOperation">

<resultMap id="userResultMap" type="User">

<id property="id" column="user_id" />

<result property="userName" column="user_userName" />

<result property="userAge" column="user_userAge" />

<result property="userAddress" column="user_userAddress" />

</resultMap>

<resultMap id="articleResultMap" type="Article">

<id property="id" column="article_id" />

<result property="title" column="article_title" />

<result property="content" column="article_content" />

<association property="user" javaType="User" resultMap="userResultMap"/>

</resultMap>

<resultMap id="blogResultMap" type="Blog">

<id property="id" column="blog_id" />

<result property="title" column="blog_title" />



<collection property="articles" ofType="Article" resultMap="articleResultMap"/>

</resultMap>



<select id="getBlogByID" parameterType="int" resultMap="blogResultMap">

select user.id user_id,user.userName user_userName,user.userAddress user_userAddress,

article.id article_id,article.title article_title,article.content article_content,

blog.id blog_id, blog.title blog_title

from user,article,blog

where user.id=article.userid and blog.id=article.blogid and blog.id=#{id}

</select>

</mapper>

IArticleOperation定义：

package com.mybatis.test;

import java.util.List;

public interface IArticleOperation {

public List<Article> getUserArticles(int userID);

}

IBlogOperation定义：

package com.mybatis.test;

public interface IBlogOperation {

Blog getBlogByID(int id);

}

Test类：

package com.mybatis.test;

import java.io.Reader;

import java.util.List;

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 Test {

private static SqlSessionFactory sqlSessionFactory;

private static Reader reader;

static {

try {

//通过配置文件初始化sqlSessionFactory

reader = Resources.getResourceAsReader("Configuration.xml");

sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);

} catch (Exception e) {

e.printStackTrace();

}

}

public static SqlSessionFactory getSession() {

return sqlSessionFactory;

}

public void getUserByID(int userID) {

SqlSession session = sqlSessionFactory.openSession();

try {

IUserOperation userOperation = session

.getMapper(IUserOperation.class);

User user = userOperation.selectUserByID(userID);

if (user != null) {

System.out.println(user.getId() + ":" + user.getUserName()

+ ":" + user.getUserAddress());

}

} finally {

session.close();

}

}

public void getUserList(String userName) {

SqlSession session = sqlSessionFactory.openSession();

try {

IUserOperation userOperation = session

.getMapper(IUserOperation.class);

List<User> users = userOperation.selectUsersByName(userName);

for (User user : users) {

System.out.println(user.getId() + ":" + user.getUserName()

+ ":" + user.getUserAddress());

}

} finally {

session.close();

}

}

/**

* 增加后要commit

*/

public void addUser() {

User user = new User();

user.setUserAddress("place");

user.setUserName("test_add");

user.setUserAge(30);

SqlSession session = sqlSessionFactory.openSession();

try {

IUserOperation userOperation = session

.getMapper(IUserOperation.class);

userOperation.addUser(user);

session.commit();

System.out.println("新增用户ID：" + user.getId());

} finally {

session.close();

}

}

/**

* 修改后要commit

*/

public void updateUser() {

SqlSession session = sqlSessionFactory.openSession();

try {

IUserOperation userOperation = session

.getMapper(IUserOperation.class);

User user = userOperation.selectUserByID(1);

if (user != null) {

user.setUserAddress("A new place");

userOperation.updateUser(user);

session.commit();

}

} finally {

session.close();

}

}

/**

* 删除后要commit.

*

* @param id

*/

public void deleteUser(int id) {

SqlSession session = sqlSessionFactory.openSession();

try {

IUserOperation userOperation = session

.getMapper(IUserOperation.class);

userOperation.deleteUser(id);

session.commit();

} finally {

session.close();

}

}

public void getUserArticles(int userid) {

SqlSession session = sqlSessionFactory.openSession();

try {

IArticleOperation articleOperation = session

.getMapper(IArticleOperation.class);

List<Article> articles = articleOperation.getUserArticles(userid);

for (Article article : articles) {

System.out.println(article.getTitle() + ":"

+ article.getContent() + "用户名："

+ article.getUser().getUserName() + "用户地址："

+ article.getUser().getUserAddress());

}

} finally {

session.close();

}

}

public void getBlogArticles(int blogid) {

SqlSession session = sqlSessionFactory.openSession();

try {

IBlogOperation blogOperation = session

.getMapper(IBlogOperation.class);

Blog blog = blogOperation.getBlogByID(blogid);

System.out.println(blog.getTitle() + ":");

List<Article> articles = blog.getArticles();

for (Article article : articles) {

System.out.println(article.getTitle() + ":"

+ article.getContent() + "用户名："

+ article.getUser().getUserName() + "用户地址："

+ article.getUser().getUserAddress());

/*System.out.println(article.getTitle() + ":"

+ article.getContent());*/

}

} finally {

session.close();

}

}

public static void main(String[] args) {

try {

Test test = new Test();

// test.getUserByID(1);

// test.getUserList("test1");

// test.addUser();

// test.updateUser();

// test.deleteUser(6);

//test.getUserArticles(1);

test.getBlogArticles(1);

} catch (Exception e) {

System.out.println(e.getMessage());

}

}

}

Mybatis的“N+1查询问题”

示例：

<resultMap id=”blogResult” type=”Blog”>

<association property="author" column="blog_author_id"

javaType="Author" select=”selectAuthor” />

</resultMap>

<select id=”selectBlog” parameterType=”int” resultMap=”blogResult”>

SELECT * FROM BLOG WHERE ID = #{id}

</select>

<select id=”selectAuthor” parameterType=”int” resultType="Author">

SELECT * FROM AUTHOR WHERE ID = #{id}

</select>

有两个查询语句：一个来加载博客，另外一个来加载作者，而且博客的结果映射描述了“selectAuthor”语句应该被用来加载它的 author 属性。

其他所有的属性将会被自动加载，假设它们的列和属性名相匹配。

这种方式很简单，但是对于大型数据集合和列表将不会表现很好。问题就是我们熟知的“N+1 查询问题”。概括地讲，N+1 查询问题可以是这样引起的：

你执行了一个单独的 SQL 语句来获取结果列表（就是“+1”）。

对返回的每条记录，你执行了一个查询语句来为每个加载细节（就是“N”）。

这个问题会导致成百上千的 SQL 语句被执行。这通常不是期望的。

可以采用关联的嵌套结果来解决这个问题：

<resultMap id="blogResult" type="Blog">

<id property=”id” column="blog_id" />

<result property="title" column="blog_title" />

<association property="author" column="blog_author_id"

javaType="Author" resultMap=”authorResult” />

</resultMap>

<resultMap id="authorResult" type="Author">

<id property="id" column="author_id" />

<result property="username" column="author_username" />

<result property="password" column="author_password" />

<result property="email" column="author_email" />

<result property="bio" column="author_bio" />

</resultMap>

resultMap 这是结果映射的 ID，可以映射关联的嵌套结果到一个合适的对象图中。这是一种替代方法来调用另外一个查询语句。

代码下载。

http://download.csdn.net/detail/luxiaoxun/8056559

参考

http://www.mybatis.org/mybatis-3/zh/sqlmap-xml.html

【关于投稿】

如果大家有原创好文投稿，请直接给公号发送留言。

① 留言格式：
【投稿】+《文章标题》+ 文章链接

② 示例：
【投稿】《不要自称是程序员，我十多年的 IT 职场总结》：http://blog.jobbole.com/94148/

③ 最后请附上您的个人简介哈~

看完本文有收获？请转发分享给更多人

关注「ImportNew」，提升Java技能

登录查看更多

相关内容

DTD

关注 37

文档类型定义(Document Type Definition)是一套为了进行程序间的数据交换而建立的关于标记符的语法规则。它是标准通用标记语言和可扩展标记语言1.0版规格的一部分，文档可根据某种DTD语法规则验证格式是否符合此规则。文档类型定义也可用做保证标准通用标记语言、可扩展标记语言文档格式的合法性，可通过比较文档和文档类型定义文件来检查文档是否符合规范，元素和标签使用是否正确。

【ICML2020】基于图感知逻辑回归和抢占式查询候选集生成的属性图上主动学习策略

专知会员服务

13+阅读 · 2020年7月9日

【UCLA】基于深度神经网络的工业大模型预测控制，36页ppt

专知会员服务

51+阅读 · 2020年5月23日

【资源】100+本免费数据科学书

专知会员服务

108+阅读 · 2020年3月17日