目录
一、使用sqlmapper配置文件
1、一对一关系
我们这里有学生表和班级表,学生和班级就是一对一关系(一个学生只有一个班级)
当我们需要查找学生和对应班级时就需要多表联查
首先我们来看学生实体类该怎么写(这里重写toString是为了后面测试结果明显)
public class Student {
private int sid;
private String sname;
private Date birthday;
private String ssex;
private int classid;
private BanJi bj;
public Student(int sid, String sname, Date birthday, String ssex, int classid, BanJi bj) {
super();
this.sid = sid;
this.sname = sname;
this.birthday = birthday;
this.ssex = ssex;
this.classid = classid;
this.bj = bj;
}
public Student() {
super();
}
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSsex() {
return ssex;
}
public void setSsex(String ssex) {
this.ssex = ssex;
}
public int getClassid() {
return classid;
}
public void setClassid(int classid) {
this.classid = classid;
}
public BanJi getBj() {
return bj;
}
public void setBj(BanJi bj) {
this.bj = bj;
}
@Override
public String toString() {
return "Student [sid=" + sid + ", sname=" + sname + ", birthday=" + birthday + ", ssex=" + ssex + ", classid="
+ classid + ", bj=" + bj + "]";
}
}
然后是StudentMapper.xml
<resultMap type="Student" id="stu_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="findStudentAndClass" resultMap="stu_class_Map" >
select * from student inner join class on student.classid= class.classid
</select>
这里在ResultMap标签中使用association
标签来建立一对一的映射关系
结果:
2、一对多关系
同样还是学生表和班级表,班级和学生就是一对多的关系(一个班级可以有多个学生)
当我们查询每个或某个班级所对应的班级名和学生时
先建立实体类(这里重写toString是为了后面测试结果明显)
public class BanJi {
private int classid;
private String classname;
private List<Student> list;
public BanJi() {
super();
}
public BanJi(int classid, String classname, List<Student> list) {
super();
this.classid = classid;
this.classname = classname;
this.list = list;
}
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;
}
public List<Student> getList() {
return list;
}
public void setList(List<Student> list) {
this.list = list;
}
@Override
public String toString() {
return "Banji [classid=" + classid + ", classname=" + classname + ", list=" + list + "]";
}
}
然后是Banji的xml文件
<resultMap type="BanJi" id="class_stu">
<result column="classid" property="classid"/>
<result column="classname" property="classname"/>
<collection property="list" ofType="Student">
<result column="sid" property="sid"/>
<result column="birthday" property="birthday"/>
<result column="sname" property="sname"/>
<result column="ssex" property="ssex"/>
<result column="classid" property="classid"/>
</collection>
</resultMap>
<select id="findClassOfStudent" resultMap="class_stu">
select * from class left join student on class.classid= student.classid
</select>
这里在ResultMap标签中使用collection
标签来建立一对多的映射关系
结果:
二、使用注解
1、一对一关系
查看所有的学生和对应班级信息(学生和班级就是一对一关系)
实体类与上面相同
@Results(
{@Result(column="classid", property="classid"),
@Result(column="classid", property="bj",
one=@One(select="com.ape.mapper.BanJiMapper.findBanjiByid"))
})
@Select("select * from student")
public List<Student> findAllStudnetAndC();
这里我们使用@One注解,我们通过@One里的select属性去调用BanjiMapper(接口)的findBanjiByid方法,入口参数为字段classid,将返回值赋给bj。
2、一对多关系
查看班级对应的学生
@Results({
@Result(column="classid",property="classid"),
@Result(column="classid",property="list",
many=@Many(select="com.ape.mapper.StudentMapper.findAllStudentBycid"))
})
@Select("select * from class where classid=#{v}")
public BanJi findBanjiByCid(int classid);
这里我们使用@Many标签,通过select属性调用StudentMapper下的findAllStudentByid方法
入口参数为字段classid,并将返回值赋给list。
总结一下:使用xml文件确实是进行了多表联查,但使用注解其实是分多次的单表查询。