mybatis复杂查询例子

xiuxuan

最近用到了mybatis框架，对于多表联合查询将结果集转化为复杂java对象。
现在写下这个例子，基本包括了多对多，一对多，多对一的复杂情况。
 

• 表结构和javabean
  

　　5张表：博客表blog，作者表author，文章表post，标签表tag，文章标签关联表post_tag。
　　每个博客对应一个作者，和多个文章。每个文章对应一个作者。文章和标签是多对多的关系。
　　建表sql语句

CREATE TABLE `blog` (
`blog_id` int(11) NOT NULL AUTO_INCREMENT,
`blog_title` varchar(100) DEFAULT NULL,
`blog_author_id` int(11) DEFAULT NULL,
PRIMARY KEY (`blog_id`)
)
CREATE TABLE `author` (
`author_id` int(11) NOT NULL AUTO_INCREMENT,
`author_username` varchar(100) DEFAULT NULL,
`author_password` varchar(100) DEFAULT NULL,
`author_email` varchar(100) DEFAULT NULL,
`author_bio` varchar(100) DEFAULT NULL,
`author_favourite_section` varchar(100) DEFAULT NULL,
PRIMARY KEY (`author_id`)
)
CREATE TABLE `post` (
`post_id` int(11) NOT NULL AUTO_INCREMENT,
`post_subject` varchar(100) DEFAULT NULL,
`post_body` varchar(100) DEFAULT NULL,
`blog_id` int(11) DEFAULT NULL,
`post_author_id` int(11) DEFAULT NULL,
PRIMARY KEY (`post_id`)
)
CREATE TABLE `tag` (
`tag_id` int(11) NOT NULL AUTO_INCREMENT,
`tag_name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`tag_id`)
)
CREATE TABLE `post_tag` (
`post_id` int(11) NOT NULL,
`tag_id` int(11) NOT NULL,
PRIMARY KEY (`post_id`,`tag_id`)
)
　　实体类，省略了所有get和set方法

public class Blog {
private int id;
private String title;
private Author author;
private ArrayList<Post> posts;
public Blog(Integer id){
this.id=id;
}
}

public class Author {
private int id;
private String username;
private String password;
private String email;
private String bio;
private String favouriteSection;
}

public class Post {
private int id;
private String subject;
private String body;
private Author author;
private ArrayList<Tag> tags;
}

public class Tag {
private int id;
private String name;
}

• mapper接口和配置文件
  

　　mapper接口定义

public interface BlogMapper {
public Blog findById(int id);
}
　　BlogMapper.xml定义

<?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="org.myBatis.mapper.BlogMapper">
<select id="findById" parameterType="int" resultMap="detailedBlogResultMap">
SELECT
b.blog_id,
b.blog_title,
a.author_id,
a.author_username,
a.author_password,
a.author_email,
a.author_bio,
a.author_favourite_section,
p.post_id,
p.post_subject,
p.post_body,
t.tag_id,
t.tag_name
FROM
blog b
LEFT JOIN author a ON b.blog_author_id = a.author_id
LEFT JOIN post p ON b.blog_id = p.blog_id
LEFT JOIN post_tag pt ON pt.post_id=p.post_id
LEFT JOIN tag t ON pt.tag_id=t.tag_id
WHERE b.blog_id = #{id}
</select>
<resultMap id="detailedBlogResultMap" type="Blog">
<constructor>
<idArg column="blog_id" javaType="int" />
</constructor>
<result property="title" column="blog_title" />
<association property="author" column="blog_author_id"
javaType="Author">
<id property="id" column="author_id" />
<result property="username" column="author_username" />
<result property="password" column="author_password" />
<result property="email" column="author_email" />
<result property="bio" column="author_bio" />
<result property="favouriteSection" column="author_favourite_section" />
</association>
<collection property="posts" ofType="Post">
<id property="id" column="post_id" />
<result property="subject" column="post_subject" />
<result property="body" column="post_body" />
<association property="author" column="post_author_id"
javaType="Author">
<id property="id" column="author_id" />
<result property="username" column="author_username" />
<result property="password" column="author_password" />
<result property="email" column="author_email" />
<result property="bio" column="author_bio" />
<result property="favouriteSection" column="author_favourite_section" />
</association>
<collection property="tags" column="post_id" ofType="Tag">
<id property="id" column="tag_id" />
<id property="name" column="tag_name" />
</collection>
</collection>
</resultMap>
</mapper>  
　　根据id返回唯一的blog
　　id为detailedBlogResultMap的resultMap将sql语句返回的结果集转换为blog对象。
　　constructor标签：定义了Blog类的构造方法
　　result标签：根据返回的结果集列对应blog对象的属性
　　association标签：一个blog对应一个author，定义了author的映射关系
　　collection标签：一个blog有多个post，定义了post的映射关系，该标签下面可以继续嵌套association和collection

• 测试类MyBatisTest
  

public class MyBatisTest {
public static void main(String[] args) {
SqlSessionFactory sessionFactory = null;  
String resource = "configuration.xml";  
try {  
sessionFactory = new SqlSessionFactoryBuilder().build(Resources  
.getResourceAsReader(resource));  
} catch (IOException e) {  
e.printStackTrace();  
}  
SqlSession session = sessionFactory.openSession();  
BlogMapper mapper = session.getMapper(BlogMapper.class);  
Blog blog = mapper.findById(1);
session.close();
}
}

• BlogMapper.xml结构简化
  

　　可以将author、post、tag等对象单独定义成resultMap

<?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="org.myBatis.mapper.BlogMapper">
<select id="findById" parameterType="int" resultMap="detailedBlogResultMap">
SELECT
b.blog_id,
b.blog_title,
a.author_id,
a.author_username,
a.author_password,
a.author_email,
a.author_bio,
a.author_favourite_section,
p.post_id,
p.post_subject,
p.post_body,
t.tag_id,
t.tag_name
FROM
blog b
LEFT JOIN author a ON b.blog_author_id =
a.author_id
LEFT JOIN
post p ON b.blog_id = p.blog_id
LEFT JOIN post_tag
pt ON
pt.post_id=p.post_id
LEFT JOIN tag t ON pt.tag_id=t.tag_id
WHERE
b.blog_id = #{id}
</select>
<resultMap id="authorResultMap" type="Author">
<id property="id" column="author_id" />
<result property="username" column="author_username" />
<result property="password" column="author_password" />
<result property="email" column="author_email" />
<result property="bio" column="author_bio" />
<result property="favouriteSection" column="author_favourite_section" />
</resultMap>
<resultMap id="tagResultMap" type="Tag">
<id property="id" column="tag_id" />
<id property="name" column="tag_name" />
</resultMap>
<resultMap id="postResultMap" type="Post">
<id property="id" column="post_id" />
<result property="subject" column="post_subject" />
<result property="body" column="post_body" />
<association property="author" column="post_author_id"
javaType="Author" resultMap="authorResultMap" />
<collection property="tags" column="post_id" ofType="Tag"
resultMap="tagResultMap" />
</resultMap>
<resultMap id="detailedBlogResultMap" type="Blog">
<constructor>
<idArg column="blog_id" javaType="int" />
</constructor>
<result property="title" column="blog_title" />
<association property="author" column="blog_author_id"
javaType="Author" resultMap="authorResultMap" />
<collection property="posts" ofType="Post" resultMap="postResultMap" />
</resultMap>
</mapper>