• myBatis系列之四:关联数据的查询


    myBatis系列之三:增删改查是基于单表的查询,如果联表查询,返回的是复合对象,需要用association关键字来处理。 
    如User发表Article,每个用户可以发表多个Article,他们之间是一对多的关系。

    1. 创建Article表,并插入测试数据:

    -- Drop the table if exists  
    DROP TABLE IF EXISTS `Article`;  
      
    -- Create a table named 'Article'  
    CREATE TABLE `Article` (  
        `id` int NOT NULL AUTO_INCREMENT,  
        `user_id` int NOT NULL,  
        `title` varchar(100) NOT NULL,  
        `content` text NOT NULL,  
        PRIMARY KEY (`id`)  
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;  
      
    -- Add several test records  
    INSERT INTO `article`  
    VALUES  
    ('1', '1', 'title1', 'content1'),  
    ('2', '1', 'title2', 'content2'),  
    ('3', '1', 'title3', 'content3'),  
    ('4', '1', 'title4', 'content4');

    2. com.john.hbatis.model.Article类: 

    public class Article {  
        private int id;  
        private User user;  
        private String title;  
        private String content;  
        // Getters and setters are omitted  
    }  

    3. 在IUserMapper中添加:

    List<Article> getArticlesByUserId(int id); 

    4. 在User.xml中添加:

    <resultMap type="com.john.hbatis.model.Article" id="articleList">  
        <id column="a_id" property="id" />  
        <result column="title" property="title" />  
        <result column="content" property="content" />  
          
        <association property="user" javaType="User"><!-- user属性映射到User类 -->  
            <id column="id" property="id" />  
            <result column="name" property="name" />  
            <result column="address" property="address" />  
        </association>  
    </resultMap>  
      
    <select id="getArticlesByUserId" parameterType="int" resultMap="articleList">  
        select u.id, u.name, u.age, u.address, a.id a_id, a.title, a.content  
        from article a  
        inner join user u  
        on a.user_id=u.id and u.id=#{id}  
    </select>

    5. 测试方法:

    @Test  
    public void getArticlesByUserIdTest() {  
        SqlSession session = sqlSessionFactory.openSession();  
        try {  
            IUserMapper mapper = session.getMapper(IUserMapper.class);  
            List<Article> articles = mapper.getArticlesByUserId(1);  
            for (Article article : articles) {  
                log.info("{} - {}, author: {}", article.getTitle(), article.getContent(), article.getUser().getName());  
            }  
        } finally {  
            session.close();  
        }  
    }  

    附:除了在association标签内定义字段和属性的映射外,还可以重用User的resultMap: 

    <association property="user" javaType="User" resultMap="userList" />
    1. <resultMap type="com.john.hbatis.model.Article" id="articleList">  
    2.     <id column="a_id" property="id" />  
    3.     <result column="title" property="title" />  
    4.     <result column="content" property="content" />  
    5.       
    6.     <association property="user" javaType="User"><!-- user属性映射到User类 -->  
    7.         <id column="id" property="id" />  
    8.         <result column="name" property="name" />  
    9.         <result column="address" property="address" />  
    10.     </association>  
    11. </resultMap>  
    12.   
    13. <select id="getArticlesByUserId" parameterType="int" resultMap="articleList">  
    14.     select u.id, u.name, u.age, u.address, a.id a_id, a.title, a.content  
    15.     from article a  
    16.     inner join user u  
    17.     on a.user_id=u.id and u.id=#{id}  
    18. </select>
  • 相关阅读:
    高内聚,低偶合
    Infinite Scrolling in UIScrollView
    ios5开发UITableView开启编辑功能
    一种自动的将自定义类序列化为JSON的方法
    Beini Compatible Hardware List
    BizTalk开发小工具批量更新发送端口订阅配置参数
    IOS5开发http get/post调用mvc4 webapi互操作(图片上传)
    IOS5开发控件位置适应屏幕旋转代码
    打开WHRHPG54AP的HP(High Power)功能
    xcode uiscrollview with paging and zoom
  • 原文地址:https://www.cnblogs.com/lingbing/p/6107353.html
Copyright © 2020-2023  润新知