目录
多对一:
编写VO类 (因为一个儿子对应一个父亲,所以儿子的VO类中有父亲的信息)
@Data
@NoArgsConstructor
@AllArgsConstructor
public class SonVo implements Serializable {
private static final long serialVersionUID = -203988976020008362L;
/**
* 儿子ID
*/
private long sId;
/**
* 儿子姓名
*/
private String sName;
/**
* 该儿子父亲ID
*/
private long fId;
/**
* 该儿子父亲信息
*/
private Father father;
}
1.使用关联查询实现
编写接口(返回一个SonVo对象)
public interface SonMapper {
/**
* 根据儿子ID查询儿子和该儿子父亲的信息
*
* @param sid
* @return
*/
SonVo selectSonVoBySid(@Param("sid") long sid);
}
编写映射配置文件
<?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.lihaozhe.mapper.SonMapper">
<!--根据儿子ID查询儿子和该儿子父亲的信息-->
<resultMap id="SonVoMap" type="sonVo">
<id property="sId" column="s_id"/>
<result property="sName" column="s_name"/>
<result property="fId" column="fid"/>
<association property="father">
<id property="fId" column="fid"/>
<result property="fName" column="f_name"/>
</association>
</resultMap>
<select id="selectSonVoBySid" resultMap="SonVoMap">
SELECT s_id, s_name, s.f_id AS fid, f_name
FROM son s
INNER JOIN father f ON s.f_id = f.f_id AND s_id = #{sid}
</select>
</mapper>
编写测试类
2.使用子查询实现
编写接口(这时需要先查询父亲的信息,以便后期的使用)
public interface FatherMapper {
/**
* 根据父亲ID获取父亲信息
*
* @param fid
* @return
*/
Father selectFatherById(@Param("fid") long fid);
}
public interface SonMapper {
/**
* 根据儿子ID查询儿子和该儿子父亲的信息
*
* @param sid
* @return
*/
SonVo selectSonVoById(@Param("sid") long sid);
}
编写映射配置文件(第一份映射父亲信息,第二份映射儿子信息)
<?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.lihaozhe.mapper.FatherMapper">
<!--根据父亲ID获取父亲信息-->
<select id="selectFatherById" resultType="father">
SELECT *
FROM `father`
WHERE `f_id` = #{fid}
</select>
</mapper>
注意:<association>标签中会走一遍查询父亲信息的子查询
<?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.lihaozhe.mapper.SonMapper">
<!--根据儿子ID查询儿子和该儿子父亲的信息-->
<resultMap id="SonVoMapper" type="sonVo">
<id property="sId" column="s_id"/>
<result property="sName" column="s_name"/>
<result property="fId" column="f_id"/>
<association property="father" column="f_id" select="com.lihaozhe.mapper.FatherMapper.selectFatherById"/>
</resultMap>
<select id="selectSonVoById" resultMap="SonVoMapper">
SELECT *
FROM `son`
WHERE `s_id` = #{sid}
</select>
</mapper>
编写测试类
(第一份测试是查询父亲信息的)
public class FatherMapperTest {
@Test
public void selectFatherById(){
SqlSession sqlSession = MyBatisUtil.openSqlSession();
FatherMapper mapper = sqlSession.getMapper(FatherMapper.class);
Father father = mapper.selectFatherById(1L);
MyBatisUtil.close();
System.out.println(father);
}
}
(第二份才是总的测试类)
public class SonMapperTest {
@Test
public void selectSonVoById() {
SqlSession sqlSession = MyBatisUtil.openSqlSession();
SonMapper mapper = sqlSession.getMapper(SonMapper.class);
SonVo sonVo = mapper.selectSonVoById(3L);
MyBatisUtil.close();
System.out.println(sonVo);
}
}
SQL解析:
==> Preparing: SELECT s_id, s_name,f_id FROM son WHERE s_id = ?
==> Parameters: 1(Long)
<== Columns: s_id, s_name, f_id
<== Row: 1, 李金吒, 1
====> Preparing: SELECT f_id, f_name FROM father WHERE f_id = ?
====> Parameters: 1(Integer)
<==== Columns: f_id, f_name
<==== Row: 1, 李靖
<==== Total: 1
<== Total: 1
一对多:(同样使用联合查询与子查询实现)
编写VO类(因为一个父亲可以对应多个儿子,所以父亲信息中要有儿子的集合)
@Data
@NoArgsConstructor
@AllArgsConstructor
public class FatherVo implements Serializable {
private static final long serialVersionUID = -1335512987974717220L;
/**
* 父亲ID
*/
private long fId;
/**
* 父亲姓名
*/
private String fName;
/**
* 该父亲的儿子们
*/
private List<Son> sons;
}
1.使用子查询实现
编写接口(这时需要先查询儿子们的信息,以便后期的使用)
public interface SonMapper {
/**
* 根据父亲ID获取该父亲的儿子列表
* @param fid
* @return
*/
List<Son> selectAllByFId(@Param("fid") long fid);
}
public interface FatherMapper {
/**
* 根据父亲ID获取父亲及其儿子们的信息
*
* @param fid
* @return
*/
FatherVo selectFatherVoById(@Param("fid") long fid);
}
编写映射配置文件(第一份映射儿子信息,第二份映射父亲信息)
<?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.lihaozhe.mapper.SonMapper">
<!--根据父亲ID获取该父亲的儿子列表-->
<select id="selectAllByFId" resultType="son">
select *
from son
where f_id = #{fid};
</select>
</mapper>
注意:<collection>标签中会走一遍查询儿子们信息的子查询
<?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.lihaozhe.mapper.FatherMapper">
<!--根据父亲ID获取父亲及其儿子们的信息-->
<resultMap id="FatherVoMap" type="fatherVo">
<id property="fId" column="f_id"/>
<result property="fName" column="f_name"/>
<collection property="sons" ofType="son" column="f_id" select="com.lihaozhe.mapper.SonMapper.selectAllByFId"/>
</resultMap>
<select id="selectFatherVoById" resultMap="FatherVoMap">
select *
from father
where f_id = #{fid};
</select>
</mapper>
编写测试类
(第一份测试是查询儿子们信息的)
public class SonMapperTest {
@Test
public void selectAllByFId(){
SqlSession sqlSession = MyBatisUtil.openSqlSession();
SonMapper mapper = sqlSession.getMapper(SonMapper.class);
List<Son> sons = mapper.selectAllByFId(1L);
MyBatisUtil.close();
sons.forEach(System.out::println);
}
}
(第二份才是总的测试类)
public class FatherMapperTest {
@Test
public void selectFatherVoById(){
SqlSession sqlSession = MyBatisUtil.openSqlSession();
FatherMapper mapper = sqlSession.getMapper(FatherMapper.class);
FatherVo fatherVo = mapper.selectFatherVoById(1L);
MyBatisUtil.close();
System.out.println(fatherVo);
}
}
SQL解析:
==> Preparing: SELECT f_id, f_name FROM father WHERE f_id = ?
==> Parameters: 1(Long)
<== Columns: f_id, f_name
<== Row: 1, 李靖
====> Preparing: SELECT s_id, s_name,f_id FROM son WHERE f_id = ?
====> Parameters: 1(Integer)
<==== Columns: s_id, s_name, f_id
<==== Row: 1, 李金吒, 1
<==== Row: 2, 李木吒, 1
<==== Row: 3, 李哪吒, 1
<==== Total: 3
<== Total: 1
2.使用联合查询实现
编写接口(返回一个FatherVo对象)
public interface FatherMapper {
/**
* 根据父亲ID获取父亲及其儿子们的信息
*
* @param fid
* @return
*/
FatherVo selectFatherVoBySid(@Param("fid") long fid);
}
编写映射配置文件
<?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.lihaozhe.mapper.FatherMapper">
<!--根据父亲ID获取父亲及其儿子们的信息-->
<resultMap id="FatherVoMapper" type="fatherVo">
<id property="fId" column="fid"/>
<result property="fName" column="f_name"/>
<collection property="sons" ofType="son">
<id property="sId" column="s_id"/>
<result property="sName" column="s_name"/>
<result property="fId" column="fid"/>
</collection>
</resultMap>
<select id="selectFatherVoBySid" resultMap="FatherVoMapper">
select f.f_id as fid, f_name, s_id, s_name
from father f
inner join son s on f.f_id = s.f_id and f.f_id = #{fid}
</select>
</mapper>
编写测试类
public class FatherMapperTest {
@Test
public void selectFatherVoBySid(){
SqlSession sqlSession = MyBatisUtil.openSqlSession();
FatherMapper mapper = sqlSession.getMapper(FatherMapper.class);
FatherVo fatherVo = mapper.selectFatherVoBySid(1L);
MyBatisUtil.close();
System.out.println(fatherVo);
}
}
多对多:
1.直接多对多(需求:查找某图书类目下的所有图书)
编写VO类(包含图书这个集合)
@Data
@NoArgsConstructor
@AllArgsConstructor
public class CategoryVo implements Serializable {
private static final long serialVersionUID = 5565769402064534797L;
/**
* 分类ID
*/
private long cid;
/**
* 分类名称
*/
private String cname;
/**
* 某分类的图书列表
*/
private List<Book> books;
}
编写接口(返回图书类目对象)
public interface CategoryMapper {
/**
* 根据分类ID获取分类及其分类下的图书列表
*
* @param cid
* @return
*/
CategoryVo selectCategoryVoByCid(@Param("cid") long cid);
}
编写映射配置文件
<?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.lihaozhe.mapper.CategoryMapper">
<!--根据分类ID获取分类及其分类下的图书列表-->
<resultMap id="CategoryVoMap" type="categoryVo">
<id property="cid" column="cid"/>
<id property="cname" column="cname"/>
<collection property="books" ofType="book">
<id property="bid" column="bid"/>
<result property="bname" column="bname"/>
</collection>
</resultMap>
<select id="selectCategoryVoByCid" resultMap="CategoryVoMap">
select cid, cname, bid, bname
from category
inner join book_category
inner join book
on cid = m_cid and bid = m_bid and cid = #{cid}
</select>
</mapper>
编写测试类
public class CategoryMapperTest {
@Test
public void selectCategoryVoByCid(){
SqlSession sqlSession = MyBatisUtil.openSqlSession();
CategoryMapper mapper = sqlSession.getMapper(CategoryMapper.class);
CategoryVo categoryVo = mapper.selectCategoryVoByCid(3L);
MyBatisUtil.close();
System.out.println(categoryVo);
}
}
2.直接多对多(需求:查找某图书对应的所有图书类目)
编写VO类(包含图书类目这个集合)
@Data
@NoArgsConstructor
@AllArgsConstructor
public class BookVo implements Serializable {
private static final long serialVersionUID = 6785352134653400094L;
/**
* 书的ID
*/
private long bid;
/**
* 书名
*/
private String bname;
/**
* 某图书的分类信息列表
*/
private List<Category> categories;
}
编写接口(返回图书对象)
public interface BookMapper {
/**
* 根据图书ID查询图书信息包括该图书的分类信息
*
* @param bid
* @return
*/
BookVo selectBookVoByBid(@Param("bid") long bid);
}
编写映射配置文件
<?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.lihaozhe.mapper.BookMapper">
<!--根据分类ID获取分类及其分类下的图书列表-->
<resultMap id="BookVoMap" type="bookVo">
<id property="bid" column="bid"/>
<result property="bname" column="bname"/>
<collection property="categories" ofType="category">
<id property="cid" column="cid"/>
<id property="cname" column="cname"/>
</collection>
</resultMap>
<select id="selectBookVoByBid" resultMap="BookVoMap">
select bid, bname, cid, cname
from book
inner join book_category
inner join category
on bid = m_bid and cid = m_cid and bid = #{bid}
</select>
</mapper>
编写测试类
public class BookMapperTest {
@Test
public void test(){
SqlSession sqlSession = MyBatisUtil.openSqlSession();
BookMapper mapper = sqlSession.getMapper(BookMapper.class);
BookVo bookVo = mapper.selectBookVoByBid(3L);
MyBatisUtil.close();
System.out.println(bookVo);
}
}
3.间接多对多(将多对多拆成两个一对多 或 两个多对一)
(需求:查找某课程下的所有学生信息)
编写中间表映射类(包括学生信息 和 课程信息)
@Data
@NoArgsConstructor
@AllArgsConstructor
public class StudentCourse implements Serializable {
private static final long serialVersionUID = 7294808647412902387L;
/**
* 学生ID
*/
private long sId;
/**
* 课程ID
*/
private long cId;
/**
* 学生信息
*/
private Student student;
/**
* 课程信息
*/
private Course course;
}
编写接口
public interface CourseMapper {
/**
* 根据课程ID查找课程
*
* @param cid
* @return
*/
Course selectCourseByCid(@Param("cid") long cid);
}
编写映射配置文件
<mapper namespace="com.lihaozhe.mapper.CourseMapper">
<!--根据课程ID查找课程-->
<select id="selectCourseByCid" resultType="course">
SELECT *
FROM `course`
WHERE `c_id` = #{cid}
</select>
</mapper>
编写测试类
public class CourseMapperTest {
@Test
public void selectCourseByCid(){
SqlSession sqlSession = MyBatisUtil.openSqlSession();
CourseMapper mapper = sqlSession.getMapper(CourseMapper.class);
Course course = mapper.selectCourseByCid(3L);
MyBatisUtil.close();
System.out.println(course);
}
}
编写接口
public interface StudentMapper {
/**
* 根据学生ID 查询学生信息 包含所需课程的ID列表
*
* @param sid
* @return
*/
StudentVo selectStudentVoBySid(@Param("sid") long sid);
}
编写映射配置文件
<?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.lihaozhe.mapper.StudentMapper">
<!--根据学生ID 查询学生信息 包含所需课程的ID列表-->
<resultMap id="StudentVoMap" type="studentVo">
<id property="sId" column="sid"/>
<result property="sName" column="s_name"/>
<collection property="studentCourses" ofType="studentCourse">
<result property="sId" column="sid"/>
<result property="cId" column="c_id"/>
<association property="course" column="c_id" select="com.lihaozhe.mapper.CourseMapper.selectCourseByCid"/>
</collection>
</resultMap>
<select id="selectStudentVoBySid" resultMap="StudentVoMap">
select stu.s_id as sid, s_name, c_id
from student as stu
inner join student_course sc on stu.s_id = sc.s_id and stu.s_id = #{sid}
</select>
</mapper>
编写测试类
public class StudentMapperTest {
@Test
public void selectStudentVoBySid() {
SqlSession sqlSession = MyBatisUtil.openSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
StudentVo studentVo = mapper.selectStudentVoBySid(3L);
System.out.println(studentVo);
System.out.println(studentVo.getSName());
studentVo.getStudentCourses().forEach(studentCourse -> System.out.println(studentCourse.getCourse()));
}
}
SQL解析
==> Preparing: SELECT s.s_id, s_name, c_id FROM `student` s INNER JOIN `student_course` sc ON s.s_id = sc.s_id AND s.s_id = ?
==> Parameters: 1(Long)
<== Columns: s_id, s_name, c_id
<== Row: 1, stu01, 1
====> Preparing: SELECT * FROM `course` WHERE `c_id` = ?
====> Parameters: 1(Integer)
<==== Columns: c_id, c_name
<==== Row: 1, java
<==== Total: 1
<== Row: 1, stu01, 2
====> Preparing: SELECT * FROM `course` WHERE `c_id` = ?
====> Parameters: 2(Integer)
<==== Columns: c_id, c_name
<==== Row: 2, scala
<==== Total: 1
<== Total: 2
4.间接多对多(将多对多拆成两个一对多 或 两个多对一)
(需求:查找某学生对应的所有课程信息)
编写接口
public interface StudentMapper {
/**
* 根据学生ID 查询学生信息
*
* @param sid
* @return
*/
Student selectStudentBySid(@Param("sid") long sid);
}
编写映射配置文件
<?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.lihaozhe.mapper.StudentMapper">
<!--根据学生ID 查询学生信息-->
<select id="selectStudentBySid" resultType="student">
SELECT * FROM `student` WHERE `s_id` = #{sid}
</select>
</mapper>
编写测试类
public class StudentMapperTest {
@Test
public void selectStudentBySid() {
SqlSession sqlSession = MyBatisUtil.openSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Student student = mapper.selectStudentBySid(3L);
System.out.println(student);
}
}
编写VO类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class CourseVo implements Serializable {
private static final long serialVersionUID = -2649136027724661116L;
/**
* 课程ID
*/
private long cId;
/**
* 课程名称
*/
private String cName;
/**
* 课程关联信息
*/
private List<StudentCourse> studentCourses;
}
编写接口
public interface CourseMapper {
/**
* 根据课程ID查找课程 包含所需课程的ID列表
*
* @param cid
* @return
*/
CourseVo selectCourseVoByCid(@Param("cid") long cid);
}
<?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.lihaozhe.mapper.CourseMapper">
<!--根据课程ID查找课程 包含所需课程的ID列表-->
<resultMap id="CourseVoMap" type="courseVo">
<id property="cId" column="cid"/>
<result property="cName" column="c_name"/>
<collection property="studentCourses" ofType="studentCourse">
<result property="sId" column="sid"/>
<result property="cId" column="c_id"/>
<association property="student" column="s_id" select="com.lihaozhe.mapper.StudentMapper.selectStudentBySid"/>
</collection>
</resultMap>
<select id="selectCourseVoByCid" resultMap="CourseVoMap">
select c.c_id as cid, c_name, s_id
from course as c
inner join student_course sc on c.c_id = sc.c_id and c.c_id = #{cid}
</select>
</mapper>
编写测试类
public class CourseMapperTest {
@Test
public void selectCourseVoByCid(){
SqlSession sqlSession = MyBatisUtil.openSqlSession();
CourseMapper mapper = sqlSession.getMapper(CourseMapper.class);
CourseVo courseVo = mapper.selectCourseVoByCid(3L);
System.out.println(courseVo);
System.out.println(courseVo.getCName());
courseVo.getStudentCourses().forEach(studentCourse -> System.out.println(studentCourse.getStudent()));
}
}