首页 > 代码库 > myBatis系列之四：关联数据的查询

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

2024-08-23 17:03:56   221人阅读

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>

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


联系
我们