mybatis复杂查询例子
最近用到了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>
页:
[1]