Mybatis入门之级联关系

Mybatis入门之级联关系

1.环境搭建

参考Mybatis入门

2.创建存在有级联关系的两张表

小说表--章节表

3.代码准备工作

1.创建工具类

public class MybatisUntils {
  private static  SqlSessionFactory sqlSessionFactory =null;
//   1.加载sqlSessionFactory工程
    static{
        String resource ="mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory= new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
//2. 获取 SqlSession对象
    public static SqlSession getSqlSession(){
        /*sqlSessionFactory.openSession(true); 自动提交*/
        return sqlSessionFactory.openSession();
    }


//   3.测试
    public static void main(String[] args) {
        SqlSession sqlSession=MybatisUntils.getSqlSession();
        System.out.println("sqlSession="+sqlSession);
    }
}

2.创建对应的实体类

省略了构造，get,set.tostring方法

public class Chapter  {

   private Integer chapter_id;//章节id
   private String chapter_name;//章节名
   private String chapter_num;//第几章
   private Novel  novel;//对应的小说
}

public class Novel  {

   private Integer novel_id;//小说id
   private String novel_name;//小说书名
   private String novel_author;//小说作者
   private String novel_type;//小说类型
   private String novel_update;//小说更新时间
   private String novel_image_address;//小说图片地址
   private List<Chapter> chapters;
   }

3.配置实体类对应Mapper.xml

ChapterMapper.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">
<!--namespace绑定一个对应的Mapper接口-->
<mapper namespace="com.lgw.mapper.ChapterMapper">

</mapper>

NovelMapper.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">
<!--namespace绑定一个对应的Mapper接口-->
<mapper namespace="com.lgw.mapper.NovelMapper">
    
</mapper>

建议把Mapper.xml文件如图放置 与java目录下的mapper对应

在mybatis-config.xml加入映射

 <mappers>        
     <mapper resource="com/lgw/mapper/ChapterMapper.xml"></mapper>        
     <mapper resource="com/lgw/mapper/NovelMapper.xml"></mapper>
</mappers>

4.代码编写

1.多对一

根据查询章节，查询对应的小说

1.子查询

public interface ChapterMapper {
    /**
     * 子查询
     * @return 所有章节
     */
    List<Chapter>  QueryChapters2();
    @Select("select *from novel where novel_id=#{nid}")
    Novel QueryNovelById(@Param("nid") int id);

<!--**************xml配置***********子查询*******************************************-->
    <select id="QueryChapters2" resultMap="cap-nel">
         select *from chapter
    </select>
    <resultMap id="cap-nel" type="Chapter">
        <!--对象 使用 association-->
        <!--property 对应Chapter实体类 对应的 Novel的name 
column 对应Chapter表关联的外键名
 javaType  查询Novel对象返回类型
select 对应调用查询Novel表的方法-->
        <association property="novel" column="novel_id"  javaType="Novel" select="QueryNovelById"/>
    </resultMap>

先用select查询 chapter，如果 resultMap="Chapter"，查询出来的novel对应必然为null

因此就要设计结果映射

  • resultMap的id 对应 select的resultMap的值

  • resultMap的type 对应的实体类

  • 对象的话使用association标签

2.通过结果嵌套查询

public interface ChapterMapper {   
/**
     * 结果嵌套查询
     * @return  所有章节
     */
    List<Chapter>  QueryChapters();
}

<!--***********xml配置***********通过结果嵌套查询*************************************-->
    <select id="QueryChapters" resultMap="chapter_novel">
--              select c.chapter_id as chapter_id,c.chapter_name as chapter_name,
--                     c.chapter_num as chapter_num,n.novel_id as novel_id,
--                     n.novel_name as novel_name,n.novel_author as novel_author,
--                     n.novel_type as novel_type,n.novel_update as novel_update,
--                     n.novel_image_address as  novel_image_address from chapter c,
--                     novel n where c.novel_id=n.novel_id
select chapter.*,novel.* from chapter inner join novel on novel.novel_id=chapter.novel_id
        </select>
    <resultMap id="chapter_novel" type="Chapter">
        <id property="chapter_id" column="chapter_id"/>
        <result property="chapter_name" column="chapter_name"/>
        <result property="chapter_num" column="chapter_num"/>
    <association property="novel" javaType="Novel" column="novel_id">
        <id property="novel_id" column="novel_id"/>
        <result property="novel_name" column="novel_name"/>
        <result property="novel_author" column="novel_author"/>
        <result property="novel_type" column="novel_type"/>
        <result property="novel_update" column="novel_update"/>
        <result property="novel_image_address" column="novel_image_address"/>
    </association>
    </resultMap>

利用sql语句查询接触出来。在对接触进行映射处理

  • id标签 对应的是 表的主键
  • 普通的字段使用result标签
  • property对应实体类字段名称
  • column对应数据库字段名称

2.一对多

根据查询小说，查询对应的章节

  1. 子查询
public interface NovelMapper {

    @Select("select *from novel")
   List<Novel> QueryNovels();
    /**
     * @param id  小说编号
     * @return   小说
     */
  Novel QueryNovel_ChapterById2(@Param("nid") int id);
}

<!--子查询 xml-->
<select id="QueryNovel_ChapterById2"  resultMap="QueryNovel_ChapterById2">
    select *from novel where novel_id=#{nid}
</select>
    <resultMap id="QueryNovel_ChapterById2" type="Novel">
   <!--javaType=""指定属性类型
                  ofType=" " 集合的泛型 使用-->
     <!--集合 使用 collection-->
        <collection property="chapters" column="novel_id" javaType="ArrayList" ofType="Chapter" select="QueryChapters"/>
    </resultMap>
<select id="QueryChapters" resultMap="Chapter_Novel">
    select *from chapter where novel_id=#{nid}
</select>
    <resultMap id="Chapter_Novel" type="Chapter">
        <id property="chapter_id" column="chapter_id"></id>
        <result property="chapter_name" column="chapter_name"></result>
        <result property="chapter_num" column="chapter_num"></result>
    </resultMap>

先用Novel编号在Novel查询小说,在Novel编号在Chapter查询对应的章节

  1. 通过结果嵌套查询
public interface NovelMapper {

    /**
     * @param id  小说编号
     * @return   小说
     */
    Novel QueryNovel_ChapterById(@Param("nid") int id);
}

    <resultMap id="Novel_Chapter" type="Novel">
        <id property="novel_id" column="novel_id"/>
        <result property="novel_name" column="novel_name"/>
        <result property="novel_author" column="novel_author"/>
        <result property="novel_type" column="novel_type"/>
        <result property="novel_update" column="novel_update"/>
        <result property="novel_image_address" column="novel_image_address"/>
               <!--javaType=""指定属性类型
                  ofType=" " 集合的泛型 使用-->
        <collection property="chapters"  ofType="Chapter">
            <id property="chapter_id" column="chapter_id"/>
            <result property="chapter_name" column="chapter_name"/>
            <result property="chapter_num" column="chapter_num"/>
        </collection>
    </resultMap>

3.子查询跟通过结果嵌套查询优缺点

  1. 子查询代码简单，关系麻烦，难理解

  2. 通过结果嵌套查询,sql语句麻烦，容易理解，操作好上手

    （建议使用通过结果嵌套查询）

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/sky1023/p/13062467.html