Java之Mybatis多层嵌套查询

喜欢ヅ旅行 2022-03-07 08:12 260阅读 0赞

三张表：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" />
            
            <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>

本人测试通过，这是原文地址：[https://www.cnblogs.com/a8457013/p/7987964.html][https_www.cnblogs.com_a8457013_p_7987964.html]

[https_www.cnblogs.com_a8457013_p_7987964.html]: https://www.cnblogs.com/a8457013/p/7987964.html