Java之Mybatis多层嵌套查询

三张表: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');

实体类

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

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" />
        <!-- 将article list属性映射到collection -->
        <collection property="articles" ofType="Article" resultMap="articleResultMap"/>
    </resultMap>
    
    <!-- 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 user,article,blog 
       where user.id=article.userid and blog.id=article.blogid and blog.id=#{id}
    </select>
    
</mapper>

本人测试通过,这是原文地址:Mybatis关联查询(嵌套查询) - javaobject - 博客园

  • 2
    点赞
  • 20
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

文子阳

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值