最近用到了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>