在数据库设计中我们会都会涉及到表与表之间的关系,如:一对一,一对多,多对多的情况,那么在java中为了反映数据库中的表都会设计基本的POJO对象来进行数据的传递,而在POJO对象中为了反映数据库中的关联关系,通常都会在该对象加入其他表的对应对象:比如在一对一关系中,一个用户只能有一个博客,一个博客只对应一名用户。
工程目录:
mysql:
CREATE TABLE `blog` (
`blog_id` int(11) NOT NULL,
`blog_name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`blog_id`,`user_id`),
KEY `FK_blog` (`user_id`),
CONSTRAINT `FK_blog` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
)
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userName` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`password` varchar(20) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`)
)
POJO对象:
public class User {
private int id;
private String userName;
private String password;
private Blog blog;
public User() {
super();
}
public User(int id, String userName, String password, Blog blog) {
super();
this.id = id;
this.userName = userName;
this.password = password;
this.blog = blog;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Blog getBlog() {
return blog;
}
public void setBlog(Blog blog) {
this.blog = blog;
}
@Override
public String toString() {
return "User [id=" + id + ", userName=" + userName + ", password=" + password + ", blog=" + blog + "]";
}
}
public class Blog {
private int id;
private String blogName;
public Blog() {
super();
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getBlogName() {
return blogName;
}
public void setBlogName(String blogName) {
this.blogName = blogName;
}
@Override
public String toString() {
return "Blog [id=" + id + ", blogName=" + blogName + "]";
}
}
映射文件配置
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.UserMapper">
<resultMap type="User" id="UserMap">
<id property="id" column="user_id"/>
<result property="userName" column="userName"/>
<result property="password" column="PASSWORD"/>
</resultMap>
<select id="getUserList" resultMap="UserMap">
SELECT blog_id,blog_name,user_id AS blog_user_id,userName,PASSWORD,id AS user_id
FROM USER INNER JOIN blog ON blog.user_id = user.id
</select>
</mapper>
但是在mapper映射文件中如果使用不设置关联关系,查询结果的返回并不会对Blog对象赋值的,因此Blog对象为null。(当然也可写一个处理器来专门处理Blog类.)运行结果:
因此为了解决这种情况,便可以用到resultMap中的association标签来让Mybatis识别到blog对象并进行赋值:
映射文件设置:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.UserMapper">
<resultMap type="User" id="UserMap">
<id property="id" column="user_id"/>
<result property="userName" column="userName"/>
<result property="password" column="PASSWORD"/>
<association property="blog" javaType="bean.Blog" column="blog_user_id">
<id property="id" column="blog_id"></id>
<result property="blogName" column="blog_name"/>
</association>
</resultMap>
<select id="getUserList" resultMap="UserMap">
SELECT blog_id,blog_name,user_id AS blog_user_id,userName,PASSWORD,id AS user_id
FROM USER INNER JOIN blog ON blog.user_id = user.id
</select>
</mapper>
利用association标签来关联Blog类型从而让mybatis可以调用其set方法。结果
除了这种写法以外,association支持关联resultMap因此我们也可以这样写UserMap:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.UserMapper">
<resultMap type="User" id="UserMap">
<id property="id" column="user_id"/>
<result property="userName" column="userName"/>
<result property="password" column="PASSWORD"/>
<association property="blog" column="blog_user_id" resultMap="BlogMap"/>
</resultMap>
<resultMap type="bean.Blog" id="BlogMap">
<id property="id" column="blog_id"></id>
<result property="blogName" column="blog_name"/>
</resultMap>
<select id="getUserList" resultMap="UserMap">
SELECT blog_id,blog_name,user_id AS blog_user_id,userName,PASSWORD,id AS user_id
FROM USER INNER JOIN blog ON blog.user_id = user.id
</select>
</mapper>
仍然可以得到结果
最后还有一种是执行通过执行select语句对blog赋值:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.UserMapper">
<resultMap type="User" id="UserMap">
<id property="id" column="user_id"/>
<result property="userName" column="userName"/>
<result property="password" column="PASSWORD"/>
<association property="blog" column="blog_user_id" select="getBlogByUserId"/>
</resultMap>
<resultMap type="bean.Blog" id="BlogMap">
<id property="id" column="blog_id"></id>
<result property="blogName" column="blog_name"/>
</resultMap>
<select id="getBlogByUserId" parameterType="int" resultMap = "BlogMap">
select blog_id,blog_name from blog where user_id=#{id}
</select>
<select id="getUserList" resultMap="UserMap">
SELECT blog_id,blog_name,user_id AS blog_user_id,userName,PASSWORD,id AS user_id
FROM USER INNER JOIN blog ON blog.user_id = user.id
</select>
</mapper>
通过association中的关联列blog_user_id实现对sql语句getBlogByUserId传递值,但是会产生N+1现象,即随着查询出的blog_user_id数据越多执行的sql语句也在增多,因此不建议使用该方式