mybatis的关联查询

Myth丶恋晨 2022-09-26 03:53 153阅读 0赞

三张表：user article blog

表的存储sql文件：

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

/*
    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');

![复制代码][copycode.gif]

配置文件Configuration.xml

![复制代码][copycode.gif]

<?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.mybatis.test.User"/> 
            <typeAlias alias="Article" type="com.mybatis.test.Article"/> 
            <typeAlias alias="Blog" type="com.mybatis.test.Blog"/>
        </typeAliases> 
    
        <environments default="development">
            <environment id="development">
            <transactionManager type="JDBC"/>
                <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis" />
                <property name="username" value="root"/>
                <property name="password" value="admin"/>
                </dataSource>
            </environment>
        </environments>
        
        
        <mappers>
            <mapper resource="com/mybatis/test/User.xml"/>
            <mapper resource="com/mybatis/test/Article.xml"/>
            <mapper resource="com/mybatis/test/Blog.xml"/>
        </mappers>
    </configuration>

![复制代码][copycode.gif]

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

Article类定义：

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

Article类中有一个User类。

Article.xml的配置：

![复制代码][copycode.gif]

<?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">
    
        <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>
        
            
        <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>
        
        
        <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 user,article 
           where user.id=article.userid and user.id=#{id}
        </select>
        
        
       
        
    </mapper>

![复制代码][copycode.gif]

Blog类定义：

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

Blog类中有一个List<Article>。

Blog.xml配置：

![复制代码][copycode.gif]

<?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>

![复制代码][copycode.gif]

IArticleOperation定义：

![复制代码][copycode.gif]

package com.mybatis.test;
    
    import java.util.List;
    
    public interface IArticleOperation {
    
        public List<Article> getUserArticles(int userID);
    }

![复制代码][copycode.gif]

IBlogOperation定义：

![复制代码][copycode.gif]

package com.mybatis.test;
    
    public interface IBlogOperation {
        
        Blog getBlogByID(int id);
        
    }

![复制代码][copycode.gif]

Test类：

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

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 {
    
        private static SqlSessionFactory sqlSessionFactory;
        private static Reader reader;
    
        static {
            try {
                //通过配置文件初始化sqlSessionFactory
                reader = Resources.getResourceAsReader("Configuration.xml");
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
        public static SqlSessionFactory getSession() {
            return sqlSessionFactory;
        }
    
        public void getUserByID(int userID) {
            SqlSession session = sqlSessionFactory.openSession();
            try {
                IUserOperation userOperation = session
                        .getMapper(IUserOperation.class);
                User user = userOperation.selectUserByID(userID);
                if (user != null) {
                    System.out.println(user.getId() + ":" + user.getUserName()
                            + ":" + user.getUserAddress());
                }
    
            } finally {
                session.close();
            }
        }
    
        public void getUserList(String userName) {
            SqlSession session = sqlSessionFactory.openSession();
            try {
                IUserOperation userOperation = session
                        .getMapper(IUserOperation.class);
                List<User> users = userOperation.selectUsersByName(userName);
                for (User user : users) {
                    System.out.println(user.getId() + ":" + user.getUserName()
                            + ":" + user.getUserAddress());
                }
    
            } finally {
                session.close();
            }
        }
    
        /**
         * 增加后要commit
         */
        public void addUser() {
            User user = new User();
            user.setUserAddress("place");
            user.setUserName("test_add");
            user.setUserAge(30);
            SqlSession session = sqlSessionFactory.openSession();
            try {
                IUserOperation userOperation = session
                        .getMapper(IUserOperation.class);
                userOperation.addUser(user);
                session.commit();
                System.out.println("新增用户ID：" + user.getId());
            } finally {
                session.close();
            }
        }
    
        /**
         * 修改后要commit
         */
        public void updateUser() {
            SqlSession session = sqlSessionFactory.openSession();
            try {
                IUserOperation userOperation = session
                        .getMapper(IUserOperation.class);
                User user = userOperation.selectUserByID(1);
                if (user != null) {
                    user.setUserAddress("A new place");
                    userOperation.updateUser(user);
                    session.commit();
                }
            } finally {
                session.close();
            }
        }
    
        /**
         * 删除后要commit.
         * 
         * @param id
         */
        public void deleteUser(int id) {
            SqlSession session = sqlSessionFactory.openSession();
            try {
                IUserOperation userOperation = session
                        .getMapper(IUserOperation.class);
                userOperation.deleteUser(id);
                session.commit();
            } finally {
                session.close();
            }
        }
    
        public void getUserArticles(int userid) {
            SqlSession session = sqlSessionFactory.openSession();
            try {
                IArticleOperation articleOperation = session
                        .getMapper(IArticleOperation.class);
                List<Article> articles = articleOperation.getUserArticles(userid);
                for (Article article : articles) {
                    System.out.println(article.getTitle() + ":"
                            + article.getContent() + "用户名："
                            + article.getUser().getUserName() + "用户地址："
                            + article.getUser().getUserAddress());
                }
            } finally {
                session.close();
            }
        }
    
        public void getBlogArticles(int blogid) {
            SqlSession session = sqlSessionFactory.openSession();
            try {
                IBlogOperation blogOperation = session
                        .getMapper(IBlogOperation.class);
                Blog blog = blogOperation.getBlogByID(blogid);
                System.out.println(blog.getTitle() + ":");
                List<Article> articles = blog.getArticles();
                for (Article article : articles) {
                    System.out.println(article.getTitle() + ":"
                            + article.getContent() + "用户名："
                            + article.getUser().getUserName() + "用户地址："
                            + article.getUser().getUserAddress());
                    /*System.out.println(article.getTitle() + ":"
                            + article.getContent());*/
                }
            } finally {
                session.close();
            }
        }
    
        public static void main(String[] args) {
            try {
                Test test = new Test();
                // test.getUserByID(1);
                // test.getUserList("test1");
                // test.addUser();
                // test.updateUser();
                // test.deleteUser(6);
    
                //test.getUserArticles(1);
    
                test.getBlogArticles(1);
    
            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
        }
    
    }

![复制代码][copycode.gif]

Mybatis的“N+1查询问题”

示例：

![复制代码][copycode.gif]

<resultMap id=”blogResult” type=”Blog”>
            <association property="author" column="blog_author_id"
                javaType="Author" select=”selectAuthor” />
        </resultMap>
        
        <select id=”selectBlog” parameterType=”int” resultMap=”blogResult”>
            SELECT * FROM BLOG WHERE ID = #{id}
        </select>
        
        <select id=”selectAuthor” parameterType=”int” resultType="Author">
            SELECT * FROM AUTHOR WHERE ID = #{id}
        </select>

![复制代码][copycode.gif]

有两个查询语句：一个来加载博客，另外一个来加载作者，而且博客的结果映射描述了“selectAuthor”语句应该被用来加载它的 author 属性。

其他所有的属性将会被自动加载，假设它们的列和属性名相匹配。

这种方式很简单，但是对于大型数据集合和列表将不会表现很好。问题就是我们熟知的“N+1 查询问题”。概括地讲，N+1 查询问题可以是这样引起的：

你执行了一个单独的 SQL 语句来获取结果列表（就是“+1”）。

对返回的每条记录，你执行了一个查询语句来为每个加载细节（就是“N”）。

这个问题会导致成百上千的 SQL 语句被执行。这通常不是期望的。

可以采用关联的嵌套结果来解决这个问题：

![复制代码][copycode.gif]

![复制代码][copycode.gif]

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

[ExpandedBlockStart.gif]: /images/20220717/3bbf80c69ba54036b354b046a06be5fa.png
[copycode.gif]: /images/20220717/afc57c9530134edb9ef7978dc730bcd5.png