• 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语句麻烦,容易理解,操作好上手

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

  • 相关阅读:
    ubuntu下eclipse打开win下的代码中文出现乱码
    ubuntu设置ip和dns
    堆和栈的区别
    分析与利用
    C语言ASM汇编内嵌语法
    vnc无法显示桌面
    草稿本(1)总结
    系统虚拟化:原理与实现
    错误
    ab(http)与abs(https)压测工具
  • 原文地址:https://www.cnblogs.com/sky1023/p/13062467.html
Copyright © 2020-2023  润新知