Mybatis中如何实现一对一，一对多的关联查询？

news/2024/5/19 19:14:35/文章来源:https://blog.csdn.net/m0_67942533/article/details/125382052

MyBatis实现一对一、一对多关联查询一般有两种方式：

方式一：sqlMapper配置文件

一对一：在resultMap标签中使用 association 标签

一对多：在resultMap 标签中使用collection 标签

方式二：注解

一对一：在@Results 注解中的@Result注解中使用@One注解

一对多：在@Results 注解中的@Result 注解中使用@Many注解

接下来，我们一起来看看这两种方式分别是怎么用代码实现的？

这里需要用到学生student和班级class两张表，如下图所示:

一、一对一查询（sqlMapper配置文件）

1. 需求

一个学生相对应一个专属班级，所以学生Student对班级Banji是一对一的关系；

查询需求：查询一个学生，与此同时查询出该学生所属的班级信息（用sqlMapper的方式）

2. 创建Student和Banji实体类

Student.java

public class Student {private int sid;private String sname;private Date birthday;private String ssex;private int classid;// 外部属性private Banji bj;// 省略无参、有参、get、set、toString方法}

Banji.java

public class Banji {private int classid;private String classname;//  省略无参、有参、get、set、toString方法}

3. 创建StudentMapper接口

public interface StudentMapper {//定义方法public List<Student> findAllStudent();	}

4. 配置 StudentMapper.xml

PS:查询的返回值类型使用的resultMap，和上面resultMap标签中的id名必须保持一致

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ape.mapper.StudentMapper"><!-- 手动映射column：数据表的字段名property：java中实体类的属性--><resultMap type="Student" id="Student_Class_Map"><result column="sid"  property="sid"/><result column="sname"  property="sname"/><result column="birthday"  property="birthday"/><result column="ssex"  property="ssex"/><result column="classid"  property="classid"/><association property="bj"><result column="classid"  property="classid"/><result column="classname"  property="classname"/></association>		</resultMap><!--一对一查询--><select id="findAllStudent" resultMap="Student_Class_Map">select * from studentinner join class on student.classid=class.classid</select></mapper>

5. 测试 Test

public class Test01 {public static void main(String[] args) {SqlSession sqlSession = DaoUtil.getSqlSession();StudentMapper sm = sqlSession.getMapper(StudentMapper.class);//一对一查询List<Student> slist = sm.findAllStudent();for (Student s:slist) {System.out.println(s);}//释放资源DaoUtil.closeSource(sqlSession);}

输出结果为：

二、一对一查询（注解）

1. 需求

一个学生相对应一个专属班级，所以学生Student对班级Banji是一对一的关系；

查询需求：查询一个学生，与此同时查询出该学生所属的班级信息（用注解的方式）

2. 创建Student和Banji实体类

Student.java

public class Student {private int sid;private String sname;private Date birthday;private String ssex;private int classid;// 外部属性private Banji bj;// 省略无参、有参、get、set、toString方法}

Banji.java

public class Banji {private int classid;private String classname;//  省略无参、有参、get、set、toString方法}

3. 创建StudentMapper接口

public interface StudentMapper {//学生对班级一对一，做映射
@Results({@Result(column = "classid",property = "classid"),@Result(column = "classid",property = "bj",one=@One(select ="com.ape.mapper.BanjiMapper.findBanjiByClassid" ))	
})@Select("select * from student")
public List<Student> findAllStudent();}

4. 创建BanjiMapper接口

public interface BanjiMapper {//学生对班级，找班级对象@Select("select * from class where classid=#{v}")public Banji findBanjiByClassid(int classid);}

5. 测试 Test

public class Test01 {public static void main(String[] args) {SqlSession sqlSession = DaoUtil.getSqlSession();StudentMapper sm = sqlSession.getMapper(StudentMapper.class);List<Student> slist = sm.findAllStudent();for(Student s:slist) {System.out.println(s);}DaoUtil.closeSource(sqlSession);}
}

输出结果为：

三、一对多查询（sqlMapper配置文件）

1. 需求

一个班级对应多个学生，所以班级Banji对学生Student是一对多的关系；

查询需求：查询一个班级，与此同时查询出该班级所有的学生信息（用sqlMapper的方式）

2. 创建Banji和Student实体类

Banji.java

public class Banji {private int classid;private String classname;//外部属性private List<Student> slist;//  省略无参、有参、get、set、toString方法}

Student.java

public class Student {private int sid;private String sname;private Date birthday;private String ssex;private int classid;// 省略无参、有参、get、set、toString方法}

3. 创建BanjiMapper接口

public interface BanjiMapper {//班级对学生，一对多public List<Banji> findAllBanji();}

4. 配置BanjiMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.ape.mapper.BanjiMapper"><resultMap type="Banji" id="Class_Student_Map"><result column="classid"  property="classid"/><result column="classname"  property="classname"/><collection property="slist" ofType="Student"><result column="sid"  property="sid"/><result column="sname"  property="sname"/><result column="birthday"  property="birthday"/><result column="ssex"  property="ssex"/><result column="classid"  property="classid"/></collection></resultMap><select id="findAllBanji" resultMap="Class_Student_Map">select * from classleft join student on class.classid=student.classidorder by class.classid</select></mapper>

5. 测试 Test

public class Test01 {public static void main(String[] args) {SqlSession sqlSession = DaoUtil.getSqlSession();BanjiMapper bm = sqlSession.getMapper(BanjiMapper.class);List<Banji> blist = bm.findAllBanji();for (Banji b:blist) {System.out.println("班级编号"+b.getClassid()+","+"班级名称"+b.getClassname());for(Student s:b.getSlist()) {System.out.println(s);}System.out.println("====================================");}//释放资源DaoUtil.closeSource(sqlSession);}
}

输出结果为：

四、一对多查询（注解）

1. 需求

一个班级对应多个学生，所以班级Banji对学生Student是一对多的关系；

查询需求：查询一个班级，与此同时查询出该班级所有的学生信息（用注解的方式）

2. 创建Banji和Student实体类

Banji.java

public class Banji {private int classid;private String classname;//外部属性private List<Student> slist;//  省略无参、有参、get、set、toString方法}

Student.java

public class Student {private int sid;private String sname;private Date birthday;private String ssex;private int classid;// 省略无参、有参、get、set、toString方法}

3. 创建BanjiMapper接口

public interface BanjiMapper {//班级对学生一对多，做映射@Results({@Result(column = "classid",property = "classid"),@Result(column = "classid",property = "slist",many=@Many(select="com.ape.mapper.StudentMapper.findStudentByClassid"))		})@Select("select * from class")public List<Banji> findAllBanji();
}

4. 创建StudentMapper接口

public interface StudentMapper {//班级对学生一对多，找学生对象@Select("select * from student where classid=#{v}")public Student findStudentByClassid(int classid);}

5. 测试 Test

public class Test01 {public static void main(String[] args) {SqlSession sqlSession = DaoUtil.getSqlSession();BanjiMapper bm = sqlSession.getMapper(BanjiMapper.class);List<Banji> blist = bm.findAllBanji();for (Banji b:blist) {System.out.println("班级编号"+b.getClassid()+","+"班级名称"+b.getClassname());for(Student s:b.getSlist()) {System.out.println(s);}System.out.println("====================================");}//释放资源DaoUtil.closeSource(sqlSession);}
}

输出结果为：