Mybatis关联查询（嵌套查询）

1
public class Users {

1
2
3
4
5
	private int id;
	private String userName;
	private int userAge;
	private String userAddress;
}

1
2
3
4
5
6
public class Article {
	 private int id;
	 private Users user;//外键
	 private String title;
	 private String content;
}

1
2
3
4
5
public class Blog {
	private int id;
	private String title;
	private List<Article> articles;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE `users` (
  `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;

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;

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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?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.mook.locker.misc.domain.Users"/> 
        <typeAlias alias="Article" type="com.mook.locker.misc.domain.Article"/> 
        <typeAlias alias="Blog" type="com.mook.locker.misc.domain.Blog"/>
    </typeAliases> 
	
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="UNPOOLED">
				<property name="driver" value="com.mysql.jdbc.Driver" />
				<property name="url" value="jdbc:mysql://localhost:3306/mybatis" />
				<property name="username" value="root" />
				<property name="password" value="123456" />
			</dataSource>
		</environment>
	</environments>
	
	<mappers>
		<mapper resource="mapper/BlogMapper.xml" />
	</mappers>
	
</configuration>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
<?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.mook.locker.misc.mapper.BlogMapper">
	
	<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>
    
    <!-- User join Article进行联合查询  (一对一)-->    
    <resultMap id="articleResultMap" type="Article">
        <id property="id" column="article_id" />
        <result property="title" column="article_title" />
        <result property="content" column="article_content" />
        <!-- 将article的user属性映射到userResultMap -->
        <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" />
        <!-- 将article list属性映射到collection -->
        <collection property="articles" ofType="Article" resultMap="articleResultMap"/>
    </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 users user,article 
       where user.id=article.userid and user.id=#{id}
    </select>
    
  	<!-- select语句 -->
    <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 users user,article,blog 
       where user.id=article.userid and blog.id=article.blogid and blog.id=#{id}
    </select>
    
</mapper>

1
2
3
4
public interface BlogMapper {
	public List<Article> getUserArticles(int userId);
	public Blog getBlogByID(int blogId);
}