mybatis学习(49):嵌套查询--一对多

数据库表

数据库叫blog_gp1701

author表

数据

blog表

数据

comment表

数据

post表

数据

目录结构

jar包导入

先给对应的jar包导入

建立一个junit单元测试

配置文件

log4j.properties

### \u914D\u7F6E\u6839 ###
log4j.rootLogger = debug,console ,fileAppender,dailyRollingFile,ROLLING_FILE,MAIL,DATABASE
 
### \u8BBE\u7F6E\u8F93\u51FAsql\u7684\u7EA7\u522B\uFF0C\u5176\u4E2Dlogger\u540E\u9762\u7684\u5185\u5BB9\u5168\u90E8\u4E3Ajar\u5305\u4E2D\u6240\u5305\u542B\u7684\u5305\u540D ###
log4j.logger.org.apache=dubug
log4j.logger.java.sql.Connection=dubug
log4j.logger.java.sql.Statement=dubug
log4j.logger.java.sql.PreparedStatement=dubug
log4j.logger.java.sql.ResultSet=dubug
 
### \u914D\u7F6E\u8F93\u51FA\u5230\u63A7\u5236\u53F0 ###
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern =  %d{ABSOLUTE} %5p %c{1}:%L - %m%n
mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
    <setting name="useGeneratedKeys" value="true"/>
</settings>
<typeAliases>
    <typeAlias type="com.geyao.mybatis.pojo.Blog" alias="Blog"/>
    <typeAlias type="com.geyao.mybatis.pojo.Author" alias="Author"/>
    <typeAlias type="com.geyao.mybatis.pojo.Comment" alias="Comment"/>
    <typeAlias type="com.geyao.mybatis.pojo.Post" alias="Post"/>
</typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <!-- 配置数据库连接信息 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/blog_gp1701?serverTimezone=GMT%2B8" />
                <property name="username" value="root" />
                <property name="password" value="123" />
            </dataSource>
        </environment>
    </environments>
      <mappers>
        <!-- 注册userMapper.xml文件， 
         userMapper.xml位于me.gacl.mapping这个包下，所以resource写成me/gacl/mapping/userMapper.xml-->
         <mapper resource="com/geyao/mybatis/mapper/BlogMapper.xml"/>
          <mapper resource="com/geyao/mybatis/mapper/AuthorMapper.xml"/>
          <mapper resource="com/geyao/mybatis/mapper/PostMapper.xml"/>
          <mapper resource="com/geyao/mybatis/mapper/CommentMapper.xml"/>
     </mappers>
</configuration>
com.geyao.mybatis.mapper

AuthorMapper

package com.geyao.mybatis.mapper;
 
import com.geyao.mybatis.pojo.Author;
public interface AuthorMapper {
    Author selectAuthorById(Integer id);
}
BlogMapper

package com.geyao.mybatis.mapper;
 
 
import java.util.List;
 
import com.geyao.mybatis.pojo.Blog;
 
public interface BlogMapper {
    Blog selectBlogById(Integer id);
    
    List<Blog> selectBlogList();
}
Comment.Maoper

package com.geyao.mybatis.mapper;
 
import java.util.List;
 
import com.geyao.mybatis.pojo.Comment;
 
public interface CommentMapper {
List<Comment> selectCommentByPostId(Integer id);
}
PostMapper

package com.geyao.mybatis.mapper;
 
import com.geyao.mybatis.pojo.Post;
 
public interface PostMapper {
    Post selectPostById(Integer id);
}
AuthorMapper.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，namespace的值习惯上设置成包名+sql映射文件名，这样就能够保证namespace的值是唯一的
例如namespace="me.gacl.mapping.userMapper"就是me.gacl.mapping(包名)+userMapper(userMapper.xml文件去除后缀)
 -->
<mapper namespace="com.geyao.mybatis.mapper.AuthorMapper">
   <resultMap type="Author" id="authorResultMap">
   <id column="id" property="id" jdbcType="INTEGER"></id>
 
   </resultMap>
   <select id="selectAuthorById" parameterType="int" resultMap="authorResultMap">
   select * from author where id=#{id}
   </select>
</mapper>
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="com.geyao.mybatis.mapper.BlogMapper">
   <resultMap type="Blog" id="blogResultMap">
   <id column="id" property="id" jdbcType="INTEGER"></id>                                     
    <association property="author" column="author" javaType="Author"
         select="com.geyao.mybatis.mapper.AuthorMapper.selectAuthorById">
   </association>
   </resultMap>
   
   <select id="selectBlogById" parameterType="int" resultMap="blogResultMap">
   select * from Blog where id=#{id}
   </select>
   
    <select id="selectBlogList"  resultMap="blogResultMap">
   select * from Blog 
   </select>
</mapper>
CommentMapper.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，namespace的值习惯上设置成包名+sql映射文件名，这样就能够保证namespace的值是唯一的
例如namespace="me.gacl.mapping.userMapper"就是me.gacl.mapping(包名)+userMapper(userMapper.xml文件去除后缀)
 -->
<mapper namespace="com.geyao.mybatis.mapper.CommentMapper">
   <resultMap type="Comment" id="commentResultMap">
   <id column="id" property="id" jdbcType="INTEGER"></id>
 
   </resultMap>
    <select id="selectCommentByPostId" parameterType="int" resultMap="commentResultMap">
   select * from comment where post_id=#{postId}
   </select>
   <select id="selectCommentById" parameterType="int" resultMap="commentResultMap">
   select * from comment where id=#{id}
   </select>
</mapper>
PostMapper.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，namespace的值习惯上设置成包名+sql映射文件名，这样就能够保证namespace的值是唯一的
例如namespace="me.gacl.mapping.userMapper"就是me.gacl.mapping(包名)+userMapper(userMapper.xml文件去除后缀)
 -->
<mapper namespace="com.geyao.mybatis.mapper.PostMapper">
   <resultMap type="Post" id="postResultMap">
   <id column="id" property="id" jdbcType="INTEGER"></id>
 <collection property="commentList" column="id" javaType="ArrayList" ofType="Comment"
 select="com.geyao.mybatis.mapper.CommentMapper.selectCommentByPostId"></collection>
   </resultMap>
   <select id="selectPostById" parameterType="int" resultMap="postResultMap">
   select * from post where id=#{id}
   </select>
</mapper>
com.geyao.mybatis.pojo

Author

package com.geyao.mybatis.pojo;
 
public class Author {
    private Integer id;
    private String username;
    private String password;
    private String email;
    private String bio;
    private String favouriteSection;
    private String nickname;
    private String realname;
    public Author() {
        super();
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public String getBio() {
        return bio;
    }
    public void setBio(String bio) {
        this.bio = bio;
    }
    public String getFavouriteSection() {
        return favouriteSection;
    }
    public void setFavouriteSection(String favouriteSection) {
        this.favouriteSection = favouriteSection;
    }
    public String getNickname() {
        return nickname;
    }
    public void setNickname(String nickname) {
        this.nickname = nickname;
    }
    public String getRealname() {
        return realname;
    }
    public void setRealname(String realname) {
        this.realname = realname;
    }
    public Author(Integer id, String username, String password, String email, String bio, String favouriteSection,
            String nickname, String realname) {
        super();
        this.id = id;
        this.username = username;
        this.password = password;
        this.email = email;
        this.bio = bio;
        this.favouriteSection = favouriteSection;
        this.nickname = nickname;
        this.realname = realname;
    }
    @Override
    public String toString() {
        return "Author [id=" + id + ", username=" + username + ", password=" + password + ", email=" + email + ", bio="
                + bio + ", favouriteSection=" + favouriteSection + ", nickname=" + nickname + ", realname=" + realname
                + "]";
    }
    
    
    
}

Blog

package com.geyao.mybatis.pojo;
 
import java.io.Serializable;
 
public class Blog implements Serializable {
 
    private static final long serialVersionUID = 1L;
    private Integer id;
    private String title;
    private Author author;
    private String state;
    private boolean featured;
    private String style;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public Author getAuthor() {
        return author;
    }
    public void setAuthor(Author author) {
        this.author = author;
    }
    public String getState() {
        return state;
    }
    public void setState(String state) {
        this.state = state;
    }
    public boolean isFeatured() {
        return featured;
    }
    public void setFeatured(boolean featured) {
        this.featured = featured;
    }
    public String getStyle() {
        return style;
    }
    public void setStyle(String style) {
        this.style = style;
    }
    public static long getSerialversionuid() {
        return serialVersionUID;
    }
    @Override
    public String toString() {
        return "Blog [id=" + id + ", title=" + title + ", author=" + author + ", state=" + state + ", featured="
                + featured + ", style=" + style + "]\n";
    }
    
    
    
 
}
Comment

package com.geyao.mybatis.pojo;
 
import java.util.Date;
 
public class Comment {
    private Integer id;
    private String name;
    private String comment;
    private Date createOn;
    private Post post;
    private Author author;
    public Comment() {
        
    }
    
    public Comment(Integer id, String name, String comment, Date createOn, Post post, Author author) {
        super();
        this.id = id;
        this.name = name;
        this.comment = comment;
        this.createOn = createOn;
        this.post = post;
        this.author = author;
    }
 
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getComment() {
        return comment;
    }
    public void setComment(String comment) {
        this.comment = comment;
    }
    public Date getCreateOn() {
        return createOn;
    }
    public void setCreateOn(Date createOn) {
        this.createOn = createOn;
    }
    public Post getPost() {
        return post;
    }
    public void setPost(Post post) {
        this.post = post;
    }
    public Author getAuthor() {
        return author;
    }
    public void setAuthor(Author author) {
        this.author = author;
    }
    @Override
    public String toString() {
        return "Comment [id=" + id + ", name=" + name + ", comment=" + comment + ", createOn=" + createOn + ", post="
                + post + ", author=" + author + "]";
    }
    
    
}
Post

package com.geyao.mybatis.pojo;
 
import java.util.Date;
import java.util.List;
 
public class Post {
    private Integer id;
    private Author author;
    private Blog blog;
    private Date createOn;
    private String section;
    private String subject;
    private String draft;
    private String body;
    private Integer visit;
    private List<Comment> commentList;
    
    public Post(Integer id, Author author, Blog blog, Date createOn, String section, String subject, String draft,
            String body, Integer visit, List<Comment> commentList) {
        super();
        this.id = id;
        this.author = author;
        this.blog = blog;
        this.createOn = createOn;
        this.section = section;
        this.subject = subject;
        this.draft = draft;
        this.body = body;
        this.visit = visit;
        this.commentList = commentList;
    }
    public Post() {
        super();
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public Author getAuthor() {
        return author;
    }
    public void setAuthor(Author author) {
        this.author = author;
    }
    public Blog getBlog() {
        return blog;
    }
    public void setBlog(Blog blog) {
        this.blog = blog;
    }
    public Date getCreateOn() {
        return createOn;
    }
    public void setCreateOn(Date createOn) {
        this.createOn = createOn;
    }
    public String getSection() {
        return section;
    }
    public void setSection(String section) {
        this.section = section;
    }
    public String getSubject() {
        return subject;
    }
    public void setSubject(String subject) {
        this.subject = subject;
    }
    public String getDraft() {
        return draft;
    }
    public void setDraft(String draft) {
        this.draft = draft;
    }
    public String getBody() {
        return body;
    }
    public void setBody(String body) {
        this.body = body;
    }
    public Integer getVisit() {
        return visit;
    }
    public void setVisit(Integer visit) {
        this.visit = visit;
    }
    public List<Comment> getCommentList() {
        return commentList;
    }
    public void setCommentList(List<Comment> commentList) {
        this.commentList = commentList;
    }
    @Override
    public String toString() {
        return "Post [id=" + id + ", author=" + author + ", blog=" + blog + ", createOn=" + createOn + ", section="
                + section + ", subject=" + subject + ", draft=" + draft + ", body=" + body + ", visit=" + visit + "]";
    }
    
}
com.geyao.mybatis.util

MybatisUtil

package com.geyao.mybatis.util;
 
import java.io.InputStream;
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;
 
public class MyBatisUtil {
    private static SqlSessionFactory sqlSessionFactory =null;
    static {
        try {
            InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    private MyBatisUtil() {}
    
    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }
}
单元测试

com.geyao.mybatis.mapper

PostMannerTest

package com.geyao.mybatis.mapper;
 
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
 
import com.geyao.mybatis.pojo.Blog;
import com.geyao.mybatis.pojo.Post;
import com.geyao.mybatis.util.MyBatisUtil;
 
public class PostMapperTest {
    @Test
    public void testSelectPost() {
        SqlSession session =MyBatisUtil.getSqlSession();
        PostMapper postMapper =session.getMapper(PostMapper.class);
        
        Post post = postMapper.selectPostById(1);
        session.close();
        System.out.println(post);
    }
}
运行结果