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" />
- <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>