mybatis学习(50):嵌套查询

简介: mybatis学习(50):嵌套查询

image.pngimage.pngimage.pngimage.png

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.mapperAuthorMapper
package com.geyao.mybatis.mapper;
 import com.geyao.mybatis.pojo.Author;
 public interface AuthorMapper {
     Author selectAuthorById(Integer id);
 }
 BlogMapperpackage 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.Maoperpackage com.geyao.mybatis.mapper;
 import java.util.List;
 import com.geyao.mybatis.pojo.Comment;
 public interface CommentMapper {
 List<Comment> selectCommentByPostId(Integer id);
 }
 PostMapperpackage 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.pojoAuthor
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";
     }
 }
 Commentpackage 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 + "]";
     }
 }
 Postpackage 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.utilMybatisUtil
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);
     }
 }

image.png

目录
打赏
0
0
0
0
64
分享
相关文章
mybatis查询数据,返回的对象少了一个字段
mybatis查询数据,返回的对象少了一个字段
352 8
Mybatis一对一,一对多关联查询
## MyBatis一对一、一对多关联查询详解 MyBatis是一款优秀的持久层框架,提供了灵活的SQL映射功能,支持复杂的数据库操作。本文将详细介绍MyBatis中一对一和一对多关联查询的实现。 ### 一对一关联查询 一对一关联关系指的是一个表中的一条记录与另一个表中的一条记录相关联。例如,一个用户有一个地址信息。 #### 数据库表设计 假设有两个表:`user`和 `address`。 ``` CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE address
29 18
【潜意识Java】MyBatis中的动态SQL灵活、高效的数据库查询以及深度总结
本文详细介绍了MyBatis中的动态SQL功能,涵盖其背景、应用场景及实现方式。
54 6
Mybatis学习:Mybatis缓存配置
MyBatis缓存配置包括一级缓存(事务级)、二级缓存(应用级)和三级缓存(如Redis,跨JVM)。一级缓存自动启用,二级缓存需在`mybatis-config.xml`中开启并配置映射文件或注解。集成Redis缓存时,需添加依赖、配置Redis参数并在映射文件中指定缓存类型。适用于查询为主的场景,减少增删改操作,适合单表操作且表间关联较少的业务。
Mybatis实现RBAC权限模型查询
通过对RBAC权限模型的理解和MyBatis的灵活使用,我们可以高效地实现复杂的权限管理功能,为应用程序的安全性和可维护性提供有力支持。
67 5
spring和Mybatis的各种查询
Spring 和 MyBatis 的结合使得数据访问层的开发变得更加简洁和高效。通过以上各种查询操作的详细讲解,我们可以看到 MyBatis 在处理简单查询、条件查询、分页查询、联合查询和动态 SQL 查询方面的强大功能。熟练掌握这些操作,可以极大提升开发效率和代码质量。
115 3
|
2月前
|
MyBatis-Plus条件构造器:构建安全、高效的数据库查询
MyBatis-Plus 提供了一套强大的条件构造器(Wrapper),用于构建复杂的数据库查询条件。Wrapper 类允许开发者以链式调用的方式构造查询条件,无需编写繁琐的 SQL 语句,从而提高开发效率并减少 SQL 注入的风险。
55 1
MyBatis-Plus条件构造器:构建安全、高效的数据库查询
mybatis如何仅仅查询某个表的几个字段
【10月更文挑战第19天】mybatis如何仅仅查询某个表的几个字段
127 1
mybatis复习04高级查询 一对多,多对一的映射处理,collection和association标签的使用
文章介绍了MyBatis中高级查询的一对多和多对一映射处理,包括创建数据库表、抽象对应的实体类、使用resultMap中的association和collection标签进行映射处理,以及如何实现级联查询和分步查询。此外,还补充了延迟加载的设置和用法。
mybatis复习04高级查询 一对多,多对一的映射处理,collection和association标签的使用
springboot学习五:springboot整合Mybatis 连接 mysql数据库
这篇文章是关于如何使用Spring Boot整合MyBatis来连接MySQL数据库,并进行基本的增删改查操作的教程。
519 0
springboot学习五:springboot整合Mybatis 连接 mysql数据库