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.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%m%n
log4j.logger.java.sql.ResultSet=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.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; 

	@Before
	public void init(){
		try {
			reader  = Resources.getResourceAsReader("mybatis-config.xml");
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public 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() 
					);
			else
				System.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: 1
title: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