mybatis学习笔记（二） 多pojo，复杂映射

现在在数据库增加两张表blog与comment ，即博客与评论表。

CREATE TABLE `blog` (  `id` int(11) NOT NULL default '0',  `title` varchar(255) default NULL,  `content` text,  `pub_time` datetime default NULL,  `user_id` int(11) default NULL,  PRIMARY KEY  (`id`),  KEY `title` (`title`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `comment` (  `id` int(11) NOT NULL default '0',  `content` text,  `pub_time` datetime default NULL,  `blog_id` int(11) default NULL,  `user_id` int(11) default NULL,  PRIMARY KEY  (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

增加pojo对象

package com.zf.mybatis.pojo;import java.util.Date;import java.util.List;public class Blog {private int id ;private String title ;private String content ;private Date pubTime ;private User author ;private List<Comment> comments ;public int getId() {return id;}public void setId(int id) {this.id = id;}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;}public Date getPubTime() {return pubTime;}public void setPubTime(Date pubTime) {this.pubTime = pubTime;}public User getAuthor() {return author;}public void setAuthor(User author) {this.author = author;}public List<Comment> getComments() {return comments;}public void setComments(List<Comment> comments) {this.comments = comments;}}

package com.zf.mybatis.pojo;import java.util.Date;public class Comment {private int id ;private String content ;private Date pubTime;private Blog blog ;private User author ;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getContent() {return content;}public void setContent(String content) {this.content = content;}public Date getPubTime() {return pubTime;}public void setPubTime(Date pubTime) {this.pubTime = pubTime;}public Blog getBlog() {return blog;}public void setBlog(Blog blog) {this.blog = blog;}public User getAuthor() {return author;}public void setAuthor(User author) {this.author = author;}}

编写映射文件

Blog.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="com.zf.mybatis.pojo.BlogMapper"><sql id="queryFields">  id , title , content , pub_time as pubTime , user_id as User</sql><resultMap type="Blog" id="detailBlogResultMap"><id property="id" column="blog_id" /><result property="title" column="blog_title" /><result property="content" column="blog_content" /><result property="pubTime" column="blog_pub_time" /><association property="author" column="blog_user_id" javaType="User" ><id property="id" column="user_id" /><result property="title" column="title" /><result property="password" column="user_password" /><result property="userName" column="user_user_name" /><result property="userAge" column="user_user_age" /><result property="userAddress" column="user_user_address" /></association><collection property="comments" column="comment_blog_id" ofType="Comment"  ><id property="id" column="comment_id" /><result property="content" column="comment_content" /></collection></resultMap>    <select id="selectByID" parameterType="int" resultMap="detailBlogResultMap">        select        B.id as blog_id ,        B.title as blog_title ,        B.content as blog_content ,        B.pub_time as blog_pub_time ,        B.user_id as blog_user_id ,        U.id as user_id,        U.password as user_password,        U.user_name as user_user_name ,        U.user_age as user_user_age ,        U.user_address as user_user_address ,        C.id as comment_id ,        C.blog_id as comment_blog_id ,        C.content as comment_content        from `blog` B         left outer join `user` U on B.user_id = U.id        left outer join `comment` C on B.id = C.blog_id                where B.id = #{id}    </select>    </mapper>

按照上面的配置，会将blog的所有字段全部查询出来 ，author的所有字段查询出来，comment的id与content字段查询出来。  如果要查询出其他的字段，可以在resutMap中进行配置，并在sql中加入要查询的字段。

然后在mybatis-config.xml文件中加入pojo的别名，并将Blog.xml映射文件注册到mybatis-config.xml文件中。

之后就可以进行测试了。 看能否查询出想要的数据。 

注意：可以通过配置log4j来打印sql的输出。 首先在pom.xml中加入log4j的依赖，然后加入log4j的配置文件，如下：

log4j.rootCategory=DEBUG, stdout log4j.appender.stdout=org.apache.log4j.ConsoleAppenderlog4j.appender.stdout.layout=org.apache.log4j.PatternLayoutlog4j.appender.stdout.layout.ConversionPattern=%m%nlog4j.logger.java.sql.ResultSet=INFOlog4j.logger.org.apache=INFOlog4j.logger.java.sql.Connection=DEBUGlog4j.logger.java.sql.Statement=DEBUGlog4j.logger.java.sql.PreparedStatement=DEBUG 

现在编写测试类来测试：

package com.zf.mybatis;import java.io.IOException;import java.io.Reader;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import org.junit.Before;import org.junit.Test;import com.zf.mybatis.pojo.Blog;public class TestMyBatis02 {private  SqlSessionFactory sqlSessionFactory;private  Reader reader; @Beforepublic void init(){try {reader  = Resources.getResourceAsReader("mybatis-config.xml");sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);} catch (IOException e) {e.printStackTrace();}}@Testpublic void testQueryBlog(){SqlSession session = sqlSessionFactory.openSession();try {Blog blog = (Blog) session.selectOne("com.zf.mybatis.pojo.BlogMapper.selectByID" ,1);if(blog != null)System.out.printf("title:%s , content:%s , pubTime:%tT , authorName:%s ,authorAge:%d , authorPass:%s , authorAddress:%s" ,blog.getTitle(), blog.getContent() ,blog.getPubTime() ,blog.getAuthor().getUserName(),blog.getAuthor().getUserAge() ,blog.getAuthor().getPassword() ,blog.getAuthor().getUserAddress() );elseSystem.out.println("没有查询到数据");} finally {session.close();}}}

执行testQueryBlog方法打印结果如下：

ooo Using Connection [com.mysql.jdbc.JDBC4Connection@1f66cff]==>  Preparing: select B.id as blog_id , B.title as blog_title , B.content as blog_content , B.pub_time as blog_pub_time , B.user_id as blog_user_id , U.id as user_id, U.password as user_password, U.user_name as user_user_name , U.user_age as user_user_age , U.user_address as user_user_address from `blog` B left outer join `user` U on B.user_id = U.id where B.id = ? ==> Parameters: 1(Integer)<==      Total: 1title:testtitle , content:test content , pubTime:23:14:31 , authorName:summer ,authorAge:100 , authorPass:123131 , authorAddress:shanghai,pudong

可以看到 ， 正确的查询除了数据。 （在执行方法之前首先在数据库blog表中插入一条测试数据），上面的方法同时会将该blog的所有comment也查询出来，但是要实现在comment表中插入一些测试数据。

上面的resultMap写起来相当的庞大，且不利于复用。 所以还有另外一种方式来配置resultMap ，如下：

     <resultMap type="Blog" id="detailBlogResultMap02"><id property="id" column="blog_id" /><result property="title" column="blog_title" /><result property="content" column="blog_content" /><result property="pubTime" column="blog_pub_time" /><association property="author" column="blog_user_id" javaType="User" select="com.zf.mybatis.pojo.UserMapper.selectByID" /><collection property="comments" column="blog_id"  javaType="ArrayList" ofType="Comment" select="com.zf.mybatis.pojo.CommentMapper.selectByBlogID" /></resultMap>    <select id="selectByID02" parameterType="int" resultMap="detailBlogResultMap02">        select        B.id as blog_id ,        B.title as blog_title ,        B.content as blog_content ,        B.pub_time as blog_pub_time ,        B.user_id as blog_user_id        from `blog` B        where B.id = #{id}    </select>

注意 association节点中的select属性，他的值指向了User.xml中的selectByID方法，所以他会使用该方法进行查询， 这样做的好处是提高了代码的复用率 ，也减小了resultMap的复杂性。 但是这样会存在一个N+1的问题。

注意collection中的select指向的是Comment.xml中的selectByBlogID方法，它会将sql中查询出来列名为blog_id的值传递给该方法作为参数。

Comment.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="com.zf.mybatis.pojo.CommentMapper"><sql id="queryFields">  id , content , pub_time , blog_id , user_id</sql>    <select id="selectByID" parameterType="int" resultType="Comment">        select         <include refid="queryFields"/>         from `comment` where id = #{id}    </select>        <select id="selectByBlogID" parameterType="int" resultType="Comment">    select    <include refid="queryFields"/>    from `comment` where blog_id = #{id}    </select>        </mapper>

另外一种提高代码复用率的方法是将resultMap提取出来， 同时也减小了resultMap的复杂度。 代码如下：

<resultMap type="Blog" id="detailBlogResultMap03"><id property="id" column="blog_id" /><result property="title" column="blog_title" /><result property="content" column="blog_content" /><result property="pubTime" column="blog_pub_time" /><association property="author" column="blog_user_id" javaType="User" resultMap="authorResultMap03" /><collection property="comments" column="comment_blog_id" ofType="Comment"  resultMap="commentResultMap03" /></resultMap><resultMap type="User" id="authorResultMap03"><id property="id" column="user_id" /><result property="title" column="title" /><result property="password" column="user_password" /><result property="userName" column="user_user_name" /><result property="userAge" column="user_user_age" /><result property="userAddress" column="user_user_address" /></resultMap><resultMap type="Comment" id="commentResultMap03"><id property="id" column="comment_id" /><result property="content" column="comment_content" /></resultMap>    <select id="selectByID03" parameterType="int" resultMap="detailBlogResultMap">        select        B.id as blog_id ,        B.title as blog_title ,        B.content as blog_content ,        B.pub_time as blog_pub_time ,        B.user_id as blog_user_id ,        U.id as user_id,        U.password as user_password,        U.user_name as user_user_name ,        U.user_age as user_user_age ,        U.user_address as user_user_address ,        C.id as comment_id ,        C.blog_id as comment_blog_id ,        C.content as comment_content        from `blog` B         left outer join `user` U on B.user_id = U.id        left outer join `comment` C on B.id = C.blog_id        where B.id = #{id}    </select>        

项目文件下载 mybatis-study-02.zip