博客

最近，在学习MyBatis 关联数据查询时，遇到了问题。

User表在本例子中存储文章作者的相关信息。User表的详细设计如下：

Article表存储了文章的相关内容。Article表的详细设计如下：

对应的实体类：

package com.test.mybatis.pojo;

public class User {
	private int id;
	private String name;
	private int age;
	private String address;

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

}

package com.test.mybatis.pojo;

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

}

需要注意的是，Article持有的是User的引用，而不是userid。

MyBatis配置如下：

<resultMap type="Article" id="userArticleList">
	<id column="id" property="id"/>
	<result column="title" property="title"/>
	<result column="content" property="content"/>
	<association property="user" javaType="User">
		<id column="id" property="id" />
		<result column="name" property="name"/>
		<result column="age" property="age"/>
		<result column="address" property="address"/>
	</association>
</resultMap>
<select id="queryArticlesByUser" parameterType="int" resultMap="userArticleList">
	select user.id, user.name, user.age, user.address, article.id, article.title, article.content 
	from user, article where user.id = article.userid and user.id = #{id}
</select>

在Test中，传递User的id为1，只能查询到一条数据，而直接在命令行执行SQL语句，却能够查询到四条数据。

两张表中的id字段名相同，导致无法正常映射。解决办法是为列起别名。

<resultMap type="Article" id="userArticleList">
	<id column="id" property="id"/>
	<result column="title" property="title"/>
	<result column="content" property="content"/>
	<association property="user" javaType="User">
		<!-- 别名uid -->
		<id column="uid" property="id" />
		<result column="name" property="name"/>
		<result column="age" property="age"/>
		<result column="address" property="address"/>
	</association>
</resultMap>
<select id="queryArticlesByUser" parameterType="int" resultMap="userArticleList">
	select user.id uid, user.name, user.age, user.address, article.id, article.title, article.content 
	from user, article where user.id = article.userid and user.id = #{id}
</select>

这样便能正确查到四条数据。