mybatis学习(50):嵌套查询
数据库表
数据库叫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);
}
}
运行结果
