1. 一对一查询
建立好两个表student和card,其中student表中主键为id,外键为cardid,指向card表中的主键id。
建立一个Card类与表card对应
package com.santiago.entity;
public class Card {
private int id;
private String info;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getInfo() {
return info;
}
public void setInfo(String info) {
this.info = info;
}
}
1.1 使用扩展业务类来实现一对一查询
建立StudentBusiness类,使该类继承自Student,然后将Card类中的属性和方法加入到StudentBusiness中,这样,StudentBusiness类就会同时有Student和Card类的所有属性和方法。
package com.santiago.entity;
public class StudentBusiness extends Student {
private int id;
private String info;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getInfo() {
return info;
}
public void setInfo(String info) {
this.info = info;
}
@Override
public String toString() {
return super.toString()
+ "StudentBusiness{" +
"id=" + id +
", info='" + info + '\'' +
'}';
}
}
SQL映射文件
<select id="queryWith1vs1" parameterType="int" resultType="StudentBusiness">
select s.*, c.* from student s inner join card c
on s.cardid = c.id
where s.stuno = #{stuno}
</select>
测试类
public static void queryWith1vs1(){
InputStream stream = Student.class.getClassLoader().getResourceAsStream("conf.xml");
SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(stream);
SqlSession session=ssf.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
StudentBusiness studentBusiness= studentMapper.queryWith1vs1(2);
session.commit();
System.out.println(studentBusiness);
session.close();
}
运行结果
1.2 使用resultMap来实现一对一查询
使用业务扩展类时,需要新建一个类StudentBusiness,使其继承自Student,然后添加Card类所有的属性和方法,于是StudentBusiness类则同时具有Student和Card所有的属性。
而使用resultMap时,只需在Student类中添加一个Card属性,则Student类就具有了Card类中的属性和方法。
package com.santiago.entity;
public class Student {
private int stuNo;
private String stuName;
private int stuAge;
private Address address;
private Card card;
public Card getCard() {
return card;
}
public void setCard(Card card) {
this.card = card;
}
public Student() {
}
public int getStuNo() {
return stuNo;
}
public void setStuNo(int stuNo) {
this.stuNo = stuNo;
}
public String getStuName() {
return stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public int getStuAge() {
return stuAge;
}
public void setStuAge(int stuAge) {
this.stuAge = stuAge;
}
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
@Override
public String toString() {
return "Student{" +
"stuNo=" + stuNo +
", stuName='" + stuName + '\'' +
", cardId=" + this.card.getId() +
", cardInfo=" + this.card.getInfo() +
'}';
}
}
SQL映射文件
<select id="queryWithMap1vs1" parameterType="int" resultMap="Student_card_map">
select s.*, c.* from student s inner join card c
on s.cardid = c.id
where s.stuno = #{stuno}
</select>
<resultMap id="Student_card_map" type="Student">
<!--学生信息-->
<id property="stuNo" column="stuNo"/>
<result property="stuName" column="stuName"/>
<result property="stuAge" column="stuAge"/>
<!--学生卡信息-->
<!--一对一映射用association,一对多映射用collection-->
<association property="card" javaType="Card">
<id property="id" column="id"/>
<result property="info" column="info"/>
</association>
</resultMap>
注意:主键使用< id>标签,非主键使用< result>标签,对于类属性,一对一映射用association,一对多映射用collection
测试类
public static void queryWithMap1vs1(){
InputStream stream = Student.class.getClassLoader().getResourceAsStream("conf.xml");
SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(stream);
SqlSession session=ssf.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student= studentMapper.queryWithMap1vs1(2);
session.commit();
System.out.println(student);
session.close();
}
运行结果
嵌套查询和分步查询
一对一嵌套查询和分步查询:https://blog.csdn.net/qq_36544760/article/details/79502206
2. 一对多查询
一对多以一个班级对应多个学生为例。
首先,新建一个班级表,添加classid字段和classname字段,在student表中添加一个classid字段,在class表中添加外键与student表进行关联。
新建一个StuClass类,在类中添加List< Student> 属性使其与Student类进行关联
public class StuClass {
private int classId;
private String className;
private List<Student> students;
public List<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
public int getClassId() {
return classId;
}
public void setClassId(int classId) {
this.classId = classId;
}
public String getClassName() {
return className;
}
public void setClassName(String className) {
this.className = className;
}
@Override
public String toString() {
return "Class{" +
"classId=" + classId +
", className='" + className + '\'' +
", students=" + students +
'}';
}
}
SQL映射文件
<select id="queryWith1vsN" parameterType="int" resultMap="class_student_map">
select s.*, c.* from student s
inner join class c
on c.classid = s.classid
where c.classid = #{classId}
</select>
<resultMap id="class_student_map" type="StuClass">
<!--因为Class是主类,所以先配置Class的属性-->
<id property="classId" column="classid"/>
<result property="className" column="classname"/>
<!--配置成员属性学生-->
<collection property="students" ofType="Student">
<id property="stuNo" column="stuno"/>
<result property="stuName" column="stuname"/>
<result property="stuAge" column="stuage"/>
</collection>
</resultMap>
注意:<collection property="students" ofType="Student">
中使用到了oftype
,JavaType和ofType都是用来指定对象类型的,但是JavaType是用来指定pojo中属性的类型,而ofType指定的是映射到list集合属性中pojo的类型。
测试类
首先在接口中添加方法StuClass queryWith1vsN(int id);
然后在测试类中添加具体的测试方法
public static void queryWith1vsN(){
InputStream stream = Student.class.getClassLoader().getResourceAsStream("conf.xml");
SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(stream);
SqlSession session=ssf.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
StuClass clazz = studentMapper.queryWith1vsN(1);
session.commit();
System.out.println(clazz);
session.close();
}
测试结果