Mybatis Series 4: Query of associated data

Mybatis Series 3: add, delete, modify, and query are based on single-table queries. If a join Table query returns a composite object, you need to use the association keyword for processing.
For example, if a user publishes an article, each user can post multiple articles. The relationship between them is one-to-many.

1. Create an article table and insert test data:

-- 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 class:

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

3. Add the following in iusermapper:

List<Article> getArticlesByUserId(int id); 

4. Add the following in 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 property ing to user class --> <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,. id a_id,. title,. content from article a inner join user U on. user_id = u. ID and U. id = # {ID} </SELECT>

5. Test method:

@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();      }  }  

Note: In addition to defining the ing between fields and attributes in the association label, you can also reuse the user's 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"/>
6. <Association property = "user" javatype = "user"> <! -- Ing the user property to the user class -->
7. <ID column = "ID" property = "ID"/>
8. <Result column = "name" property = "name"/>
9. <Result column = "Address" property = "Address"/>
10. </Association>
11. </Resultmap>
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
16. Inner join user u
17. On a. user_id = U. ID and U. ID = # {ID}
18. </SELECT>

Mybatis Series 4: Query of associated data