Mybatis入门之级联关系
Mybatis入门之级联关系
1.环境搭建
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.一对多
根据查询小说，查询对应的章节
- 子查询
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查询对应的章节
- 通过结果嵌套查询
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.子查询跟通过结果嵌套查询优缺点
-
子查询代码简单，关系麻烦，难理解
-
通过结果嵌套查询,sql语句麻烦，容易理解，操作好上手
（建议使用通过结果嵌套查询）
