"上一篇文章介绍了基于 Mybatis 对数据库的增、删、改、查。这一篇介绍下关联查询（join query）。三张表：user article blog 表的存储 sql 文件： [图片][图片] /* Navicat MySQL Data Transfer Source Server : loc ...."

someone1
AI专业者开发社区 1 号成员
AI开发者社区 • 0 回帖 • 1 浏览 • 5 个月前

Mybatis关联查询（嵌套查询）

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

三张表：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 <span style="color: rgba(255, 0, 255, 1)">user</span><span style="color: rgba(0, 0, 0, 1)">;
CREATE TABLE <span style="color: rgba(255, 0, 255, 1)">user</span><span style="color: rgba(0, 0, 0, 1)"> (
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 <span style="color: rgba(255, 0, 255, 1)">user</span> VALUES ('1', 'summer', '30', 'shanghai');
INSERT INTO <span style="color: rgba(255, 0, 255, 1)">user</span> VALUES ('2', 'test1', '22', 'suzhou');
INSERT INTO <span style="color: rgba(255, 0, 255, 1)">user</span> VALUES ('3', 'test1', '29', 'some place');
INSERT INTO <span style="color: rgba(255, 0, 255, 1)">user</span> VALUES ('4', 'lu', '28', 'some place');
INSERT INTO <span style="color: rgba(255, 0, 255, 1)">user</span> 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');

View Code

配置文件 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>
    <!-- mybatis 别名定义 -->
    <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> 
<span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">environments </span><span style="color: rgba(255, 0, 0, 1)">default</span><span style="color: rgba(0, 0, 255, 1)">="development"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">environment </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="development"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">transactionManager </span><span style="color: rgba(255, 0, 0, 1)">type</span><span style="color: rgba(0, 0, 255, 1)">="JDBC"</span><span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
        <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">dataSource </span><span style="color: rgba(255, 0, 0, 1)">type</span><span style="color: rgba(0, 0, 255, 1)">="POOLED"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
        <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">property </span><span style="color: rgba(255, 0, 0, 1)">name</span><span style="color: rgba(0, 0, 255, 1)">="driver"</span><span style="color: rgba(255, 0, 0, 1)"> value</span><span style="color: rgba(0, 0, 255, 1)">="com.mysql.jdbc.Driver"</span><span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
        <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">property </span><span style="color: rgba(255, 0, 0, 1)">name</span><span style="color: rgba(0, 0, 255, 1)">="url"</span><span style="color: rgba(255, 0, 0, 1)"> value</span><span style="color: rgba(0, 0, 255, 1)">="jdbc:mysql://127.0.0.1:3306/mybatis"</span> <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
        <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">property </span><span style="color: rgba(255, 0, 0, 1)">name</span><span style="color: rgba(0, 0, 255, 1)">="username"</span><span style="color: rgba(255, 0, 0, 1)"> value</span><span style="color: rgba(0, 0, 255, 1)">="root"</span><span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
        <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">property </span><span style="color: rgba(255, 0, 0, 1)">name</span><span style="color: rgba(0, 0, 255, 1)">="password"</span><span style="color: rgba(255, 0, 0, 1)"> value</span><span style="color: rgba(0, 0, 255, 1)">="admin"</span><span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
        <span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">dataSource</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">environment</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
<span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">environments</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>

<span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)"> mybatis的mapper文件，每个xml配置文件对应一个接口 </span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>
<span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">mappers</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">mapper </span><span style="color: rgba(255, 0, 0, 1)">resource</span><span style="color: rgba(0, 0, 255, 1)">="com/mybatis/test/User.xml"</span><span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">mapper </span><span style="color: rgba(255, 0, 0, 1)">resource</span><span style="color: rgba(0, 0, 255, 1)">="com/mybatis/test/Article.xml"</span><span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">mapper </span><span style="color: rgba(255, 0, 0, 1)">resource</span><span style="color: rgba(0, 0, 255, 1)">="com/mybatis/test/Blog.xml"</span><span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
<span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">mappers</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>

</configuration>

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

Article 类定义：

package com.mybatis.test;
public class Article {
</span><span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> id;
</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> User user;
</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String title;
</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String content;

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> getId() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> id;
}
</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> setId(<span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> id) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.id =<span style="color: rgba(0, 0, 0, 1)"> id;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> User getUser() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> user;
}
</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setUser(User user) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.user =<span style="color: rgba(0, 0, 0, 1)"> user;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getTitle() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> title;
}
</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setTitle(String title) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.title =<span style="color: rgba(0, 0, 0, 1)"> title;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getContent() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> content;
}
</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setContent(String content) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.content =<span style="color: rgba(0, 0, 0, 1)"> content;
}

}

View Code

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">
<span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">resultMap </span><span style="color: rgba(255, 0, 0, 1)">type</span><span style="color: rgba(0, 0, 255, 1)">="User"</span><span style="color: rgba(255, 0, 0, 1)"> id</span><span style="color: rgba(0, 0, 255, 1)">="userResultMap"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
    <span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)"> 属性名和数据库列名映射 </span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">id </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="user_id"</span>  <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="userName"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="user_userName"</span>  <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="userAge"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="user_userAge"</span>  <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="userAddress"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="user_userAddress"</span>  <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
<span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">resultMap</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>

<span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)"> User join Article进行联合查询  (一对一)</span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>    
<span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">resultMap </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="articleResultMap"</span><span style="color: rgba(255, 0, 0, 1)"> type</span><span style="color: rgba(0, 0, 255, 1)">="Article"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">id </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="article_id"</span> <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="title"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="article_title"</span> <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="content"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="article_content"</span> <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)"> 将article的user属性映射到userResultMap </span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">association </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="user"</span><span style="color: rgba(255, 0, 0, 1)"> javaType</span><span style="color: rgba(0, 0, 255, 1)">="User"</span><span style="color: rgba(255, 0, 0, 1)"> resultMap</span><span style="color: rgba(0, 0, 255, 1)">="userResultMap"</span><span style="color: rgba(0, 0, 255, 1)">/&gt;</span>  
<span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">resultMap</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>

<span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)"> 使用别名来映射匹配 </span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>
<span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">select </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="getUserArticles"</span><span style="color: rgba(255, 0, 0, 1)"> parameterType</span><span style="color: rgba(0, 0, 255, 1)">="int"</span><span style="color: rgba(255, 0, 0, 1)"> resultMap</span><span style="color: rgba(0, 0, 255, 1)">="articleResultMap"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span><span style="color: rgba(0, 0, 0, 1)">
   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}
</span><span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">select</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>

<span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)"> 另一种联合查询  (一对一)的实现，但是这种方式有“N+1”的问题 </span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>

<!--  <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" column="userid" select="selectUser"/>
</resultMap>
&lt;select id="selectUser" parameterType="int" resultType="User"&gt;
    select * from user where id = #{id}
&lt;/select&gt; </span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>

</mapper>

Blog 类定义：

package com.mybatis.test;
import java.util.List;
public class Blog {
</span><span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> id;
</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String title;
</span><span style="color: rgba(0, 0, 255, 1)">private</span> List&lt;Article&gt;<span style="color: rgba(0, 0, 0, 1)"> articles;

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> getId() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> id;
}
</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> setId(<span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> id) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.id =<span style="color: rgba(0, 0, 0, 1)"> id;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getTitle() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> title;
}
</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setTitle(String title) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.title =<span style="color: rgba(0, 0, 0, 1)"> title;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> List&lt;Article&gt;<span style="color: rgba(0, 0, 0, 1)"> getArticles() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> articles;
}
</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> setArticles(List&lt;Article&gt;<span style="color: rgba(0, 0, 0, 1)"> articles) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.articles =<span style="color: rgba(0, 0, 0, 1)"> articles;
}

}

View Code

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">
<span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">resultMap </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="userResultMap"</span><span style="color: rgba(255, 0, 0, 1)"> type</span><span style="color: rgba(0, 0, 255, 1)">="User"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">id </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="user_id"</span>  <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="userName"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="user_userName"</span>  <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="userAge"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="user_userAge"</span>  <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="userAddress"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="user_userAddress"</span>  <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
<span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">resultMap</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>

<span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">resultMap </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="articleResultMap"</span><span style="color: rgba(255, 0, 0, 1)"> type</span><span style="color: rgba(0, 0, 255, 1)">="Article"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">id </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="article_id"</span> <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="title"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="article_title"</span> <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="content"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="article_content"</span> <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">association </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="user"</span><span style="color: rgba(255, 0, 0, 1)"> javaType</span><span style="color: rgba(0, 0, 255, 1)">="User"</span><span style="color: rgba(255, 0, 0, 1)"> resultMap</span><span style="color: rgba(0, 0, 255, 1)">="userResultMap"</span><span style="color: rgba(0, 0, 255, 1)">/&gt;</span>  
<span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">resultMap</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>

<span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">resultMap </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="blogResultMap"</span><span style="color: rgba(255, 0, 0, 1)"> type</span><span style="color: rgba(0, 0, 255, 1)">="Blog"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">id </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="blog_id"</span> <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="title"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="blog_title"</span> <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)"> 将article list属性映射到collection </span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">collection </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="articles"</span><span style="color: rgba(255, 0, 0, 1)"> ofType</span><span style="color: rgba(0, 0, 255, 1)">="Article"</span><span style="color: rgba(255, 0, 0, 1)"> resultMap</span><span style="color: rgba(0, 0, 255, 1)">="articleResultMap"</span><span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
<span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">resultMap</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>

<span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)"> select语句 </span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>
<span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">select </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="getBlogByID"</span><span style="color: rgba(255, 0, 0, 1)"> parameterType</span><span style="color: rgba(0, 0, 255, 1)">="int"</span><span style="color: rgba(255, 0, 0, 1)"> resultMap</span><span style="color: rgba(0, 0, 255, 1)">="blogResultMap"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span><span style="color: rgba(0, 0, 0, 1)">
   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}
</span><span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">select</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>

</mapper>

IArticleOperation 定义：

package com.mybatis.test;
import java.util.List;
public interface IArticleOperation {
</span><span style="color: rgba(0, 0, 255, 1)">public</span> List&lt;Article&gt; getUserArticles(<span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> userID);

}

IBlogOperation 定义：

package com.mybatis.test;
public interface IBlogOperation {
Blog getBlogByID(</span><span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> 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 {
</span><span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">static</span><span style="color: rgba(0, 0, 0, 1)"> SqlSessionFactory sqlSessionFactory;
</span><span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">static</span><span style="color: rgba(0, 0, 0, 1)"> Reader reader;

</span><span style="color: rgba(0, 0, 255, 1)">static</span><span style="color: rgba(0, 0, 0, 1)"> {
    </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">通过配置文件初始化sqlSessionFactory</span>
        reader = Resources.getResourceAsReader("Configuration.xml"<span style="color: rgba(0, 0, 0, 1)">);
        sqlSessionFactory </span>= <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> SqlSessionFactoryBuilder().build(reader);
    } </span><span style="color: rgba(0, 0, 255, 1)">catch</span><span style="color: rgba(0, 0, 0, 1)"> (Exception e) {
        e.printStackTrace();
    }
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">static</span><span style="color: rgba(0, 0, 0, 1)"> SqlSessionFactory getSession() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> sqlSessionFactory;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> getUserByID(<span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> userID) {
    SqlSession session </span>=<span style="color: rgba(0, 0, 0, 1)"> sqlSessionFactory.openSession();
    </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
        IUserOperation userOperation </span>=<span style="color: rgba(0, 0, 0, 1)"> session
                .getMapper(IUserOperation.</span><span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)">);
        User user </span>=<span style="color: rgba(0, 0, 0, 1)"> userOperation.selectUserByID(userID);
        </span><span style="color: rgba(0, 0, 255, 1)">if</span> (user != <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">) {
            System.out.println(user.getId() </span>+ ":" +<span style="color: rgba(0, 0, 0, 1)"> user.getUserName()
                    </span>+ ":" +<span style="color: rgba(0, 0, 0, 1)"> user.getUserAddress());
        }

    } </span><span style="color: rgba(0, 0, 255, 1)">finally</span><span style="color: rgba(0, 0, 0, 1)"> {
        session.close();
    }
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> getUserList(String userName) {
    SqlSession session </span>=<span style="color: rgba(0, 0, 0, 1)"> sqlSessionFactory.openSession();
    </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
        IUserOperation userOperation </span>=<span style="color: rgba(0, 0, 0, 1)"> session
                .getMapper(IUserOperation.</span><span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)">);
        List</span>&lt;User&gt; users =<span style="color: rgba(0, 0, 0, 1)"> userOperation.selectUsersByName(userName);
        </span><span style="color: rgba(0, 0, 255, 1)">for</span><span style="color: rgba(0, 0, 0, 1)"> (User user : users) {
            System.out.println(user.getId() </span>+ ":" +<span style="color: rgba(0, 0, 0, 1)"> user.getUserName()
                    </span>+ ":" +<span style="color: rgba(0, 0, 0, 1)"> user.getUserAddress());
        }

    } </span><span style="color: rgba(0, 0, 255, 1)">finally</span><span style="color: rgba(0, 0, 0, 1)"> {
        session.close();
    }
}

</span><span style="color: rgba(0, 128, 0, 1)">/**</span><span style="color: rgba(0, 128, 0, 1)">
 * 增加后要commit
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> addUser() {
    User user </span>= <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> User();
    user.setUserAddress(</span>"place"<span style="color: rgba(0, 0, 0, 1)">);
    user.setUserName(</span>"test_add"<span style="color: rgba(0, 0, 0, 1)">);
    user.setUserAge(</span>30<span style="color: rgba(0, 0, 0, 1)">);
    SqlSession session </span>=<span style="color: rgba(0, 0, 0, 1)"> sqlSessionFactory.openSession();
    </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
        IUserOperation userOperation </span>=<span style="color: rgba(0, 0, 0, 1)"> session
                .getMapper(IUserOperation.</span><span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)">);
        userOperation.addUser(user);
        session.commit();
        System.out.println(</span>"新增用户ID：" +<span style="color: rgba(0, 0, 0, 1)"> user.getId());
    } </span><span style="color: rgba(0, 0, 255, 1)">finally</span><span style="color: rgba(0, 0, 0, 1)"> {
        session.close();
    }
}

</span><span style="color: rgba(0, 128, 0, 1)">/**</span><span style="color: rgba(0, 128, 0, 1)">
 * 修改后要commit
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> updateUser() {
    SqlSession session </span>=<span style="color: rgba(0, 0, 0, 1)"> sqlSessionFactory.openSession();
    </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
        IUserOperation userOperation </span>=<span style="color: rgba(0, 0, 0, 1)"> session
                .getMapper(IUserOperation.</span><span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)">);
        User user </span>= userOperation.selectUserByID(1<span style="color: rgba(0, 0, 0, 1)">);
        </span><span style="color: rgba(0, 0, 255, 1)">if</span> (user != <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">) {
            user.setUserAddress(</span>"A new place"<span style="color: rgba(0, 0, 0, 1)">);
            userOperation.updateUser(user);
            session.commit();
        }
    } </span><span style="color: rgba(0, 0, 255, 1)">finally</span><span style="color: rgba(0, 0, 0, 1)"> {
        session.close();
    }
}

</span><span style="color: rgba(0, 128, 0, 1)">/**</span><span style="color: rgba(0, 128, 0, 1)">
 * 删除后要commit.
 * 
 * </span><span style="color: rgba(128, 128, 128, 1)">@param</span><span style="color: rgba(0, 128, 0, 1)"> id
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> deleteUser(<span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> id) {
    SqlSession session </span>=<span style="color: rgba(0, 0, 0, 1)"> sqlSessionFactory.openSession();
    </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
        IUserOperation userOperation </span>=<span style="color: rgba(0, 0, 0, 1)"> session
                .getMapper(IUserOperation.</span><span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)">);
        userOperation.deleteUser(id);
        session.commit();
    } </span><span style="color: rgba(0, 0, 255, 1)">finally</span><span style="color: rgba(0, 0, 0, 1)"> {
        session.close();
    }
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> getUserArticles(<span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> userid) {
    SqlSession session </span>=<span style="color: rgba(0, 0, 0, 1)"> sqlSessionFactory.openSession();
    </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
        IArticleOperation articleOperation </span>=<span style="color: rgba(0, 0, 0, 1)"> session
                .getMapper(IArticleOperation.</span><span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)">);
        List</span>&lt;Article&gt; articles =<span style="color: rgba(0, 0, 0, 1)"> articleOperation.getUserArticles(userid);
        </span><span style="color: rgba(0, 0, 255, 1)">for</span><span style="color: rgba(0, 0, 0, 1)"> (Article article : articles) {
            System.out.println(article.getTitle() </span>+ ":"
                    + article.getContent() + "用户名："
                    + article.getUser().getUserName() + "用户地址："
                    +<span style="color: rgba(0, 0, 0, 1)"> article.getUser().getUserAddress());
        }
    } </span><span style="color: rgba(0, 0, 255, 1)">finally</span><span style="color: rgba(0, 0, 0, 1)"> {
        session.close();
    }
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> getBlogArticles(<span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> blogid) {
    SqlSession session </span>=<span style="color: rgba(0, 0, 0, 1)"> sqlSessionFactory.openSession();
    </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
        IBlogOperation blogOperation </span>=<span style="color: rgba(0, 0, 0, 1)"> session
                .getMapper(IBlogOperation.</span><span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)">);
        Blog blog </span>=<span style="color: rgba(0, 0, 0, 1)"> blogOperation.getBlogByID(blogid);
        System.out.println(blog.getTitle() </span>+ ":"<span style="color: rgba(0, 0, 0, 1)">);
        List</span>&lt;Article&gt; articles =<span style="color: rgba(0, 0, 0, 1)"> blog.getArticles();
        </span><span style="color: rgba(0, 0, 255, 1)">for</span><span style="color: rgba(0, 0, 0, 1)"> (Article article : articles) {
            System.out.println(article.getTitle() </span>+ ":"
                    + article.getContent() + "用户名："
                    + article.getUser().getUserName() + "用户地址："
                    +<span style="color: rgba(0, 0, 0, 1)"> article.getUser().getUserAddress());
            </span><span style="color: rgba(0, 128, 0, 1)">/*</span><span style="color: rgba(0, 128, 0, 1)">System.out.println(article.getTitle() + ":"
                    + article.getContent());</span><span style="color: rgba(0, 128, 0, 1)">*/</span><span style="color: rgba(0, 0, 0, 1)">
        }
    } </span><span style="color: rgba(0, 0, 255, 1)">finally</span><span style="color: rgba(0, 0, 0, 1)"> {
        session.close();
    }
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> main(String[] args) {
    </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
        Test test </span>= <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> Test();
        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> test.getUserByID(1);
        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> test.getUserList("test1");
        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> test.addUser();
        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> test.updateUser();
        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> test.deleteUser(6);

        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">test.getUserArticles(1);</span>


            test.getBlogArticles(1);
    } </span><span style="color: rgba(0, 0, 255, 1)">catch</span><span style="color: rgba(0, 0, 0, 1)"> (Exception e) {
        System.out.println(e.getMessage());
    }
}

}

View Code

Mybatis 的“N+1 查询问题”

示例：

    <resultMap id=”blogResult” type=”Blog”>
        <association property="author" column="blog_author_id"
            javaType="Author" select=”selectAuthor” />
    </resultMap>
<span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">select </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">=”selectBlog” </span><span style="color: rgba(255, 0, 0, 1)">parameterType</span><span style="color: rgba(0, 0, 255, 1)">=”int” </span><span style="color: rgba(255, 0, 0, 1)">resultMap</span><span style="color: rgba(0, 0, 255, 1)">=”blogResult”&gt;
    </span><span style="color: rgba(255, 0, 0, 1)">SELECT * FROM BLOG WHERE ID </span><span style="color: rgba(0, 0, 255, 1)">= #{id}
</span><span style="color: rgba(255, 0, 0, 1)">&lt;/select</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>

<span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">select </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">=”selectAuthor” </span><span style="color: rgba(255, 0, 0, 1)">parameterType</span><span style="color: rgba(0, 0, 255, 1)">=”int” </span><span style="color: rgba(255, 0, 0, 1)">resultType</span><span style="color: rgba(0, 0, 255, 1)">="Author"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span><span style="color: rgba(0, 0, 0, 1)">
    SELECT * FROM AUTHOR WHERE ID = #{id}
</span><span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">select</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span></pre>

有两个查询语句：一个来加载博客，另外一个来加载作者，而且博客的结果映射描述了“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>
<span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">resultMap </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="authorResult"</span><span style="color: rgba(255, 0, 0, 1)"> type</span><span style="color: rgba(0, 0, 255, 1)">="Author"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">id </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="author_id"</span> <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="username"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="author_username"</span> <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="password"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="author_password"</span> <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="email"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="author_email"</span> <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="bio"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="author_bio"</span> <span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
<span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">resultMap</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span></pre>

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

代码下载：http://download.csdn.net/detail/luxiaoxun/8056559

参考：

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

Mybatis关联查询（嵌套查询）

相关帖子

随便看看

Mybatis关联查询（嵌套查询）