MyBatis学习笔记（四）---关联数据的查询

在实际项目中，经常是关联表的查询，比如最常见到的多对一，一对多等。这些查询是如何处理的呢，这一讲就讲这个问题。我们首先创建一个Article 这个表，并初始化数据：

Drop TABLE IF EXISTS `article`;
Create TABLE `article` (
  `id` int(11) NOT NULL auto_increment,
  `userid` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

然后插入几条数据：

Insert INTO `article` VALUES ('1', '1', 'test_title', 'test_content');
Insert INTO `article` VALUES ('2', '1', 'test_title_2', 'test_content_2');
Insert INTO `article` VALUES ('3', '1', 'test_title_3', 'test_content_3');
Insert INTO `article` VALUES ('4', '1', 'test_title_4', 'test_content_4');

增加一个 Article 的class，注意一下，文章的用户是怎么定义的，是直接定义的一个User对象。而不是int类型。

package com.mybatis.model;

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;
    }


}

场景:在读取某个用户发表的所有文章。当然还是需要在User.xml 里面配置 select 语句, 但重点是这个 select 的resultMap 对应什么样的数据呢。这是重点，这里要引入 association 看定义如下:

    <resultMap type="User" id="resultListUser">
        <id column="id" property="id"/>
        <result column="userName" property="userName"/>
        <result column="userAge" property="userAge"/>
        <result column="userAddress" property="userAddress"/>
    </resultMap>    

    <resultMap type="Article" id="resultUserArticleList">
        <id column="aid" property="id"/>
        <result column="title" property="title"/>
        <result column="content" property="content"/>
        <association property="user" javaType="User" resultMap="resultListUser"/>
    </resultMap>

 <select id="queryUserArticle" parameterType="int" resultMap="resultUserArticleList">
        select user.id, user.username,user.useraddress, 
            article.id aid, article.title, article.content from
            user, article
            where user.id = article.userid and user.id = #{id} 
 </select>

同时在configuration.xml添加下面的article：

    <typeAliases> 
        <typeAlias alias="User" type="com.mybatis.model.User"/> 
        <typeAlias alias="Article" type="com.mybatis.model.Article"/>
    </typeAliases>

接口中添加：

public List<Article> queryUserArticle(int id);

下面添加测试程序：

    @Test
    public void queryUserArticle() {
        IUserOperation userOperation = session.getMapper(IUserOperation.class);
        List<Article> list = userOperation.queryUserArticle(1);

        for(Article article : list) {
            System.out.println(article.getUser().getId()+"  " + article.getUser().getUserName() +" " 
             + article.getTitle());
        }
    }

运行结果如下：

log4j:WARN No appenders could be found for logger (org.apache.ibatis.logging.LogFactory).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
1  gary test_title
1  gary test_title_2
1  gary test_title_3
1  gary test_title_4