1.N+1方式
(1) N+1查询方式,先查询出某个表的全部信息。根据这个表的信息查询另一个表的信息。
(2)与业务装配的区别:在service里面写的代码,由mybatis完成装配。
实现步骤:
- 实体类
【注意】在Student实现类中包含一个Teacher对象。
package com.ouc.pojo;
public class Student {
private int sid;
private String sname;
private int sage;
private int tid;
private Teacher teacher;
public Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
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 int getSage() {
return sage;
}
public void setSage(int sage) {
this.sage = sage;
}
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
@Override
public String toString() {
return "Student [sid=" + sid + ", sname=" + sname + ", sage=" + sage + ", tid=" + tid + ", teacher=" + teacher
+ "]";
}
}
- 在TeacherMapper.xml中提供一个查询
<mapper namespace="com.ouc.mapper.TeacherMapper">
<select id="selById" parameterType="int" resultType="teacher">
select * from teacher where tid = #{0}
</select>
</mapper>
- 在StudentMapper.xml中
①<association>
装配一个对象时使用
② property 对象在类中的属性名
③ select:通过那个查询查询出这个对象的信息
④ column:把当前表的那个列的值作为参考传递给另一个查询
⑤使用N+1查询方式时,如果列名和属性名相同可以不配置,使用Auto Mapping 特性,但是Mybatis默认只会给列专配一次。
<mapper namespace="com.ouc.mapper.StudentMapper">
<resultMap type="student" id="stuMap">
<id property="sid" column="sid"/>
<result property="sname" column="sname"/>
<result property="sage" column="sage"/>
<result property="tid" column="tid"/>
<association property="teacher" select="com.ouc.mapper.TeacherMapper.selById" column="tid"></association>
</resultMap>
<select id="selAll" resultMap="stuMap">
select * from student
</select>
</mapper>
- 测试类
package com.ouc.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.ouc.pojo.Student;
public class Test {
public static void main(String[] args) throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession session = factory.openSession();
List<Student> list = session.selectList("com.ouc.mapper.StudentMapper.selAll");
System.out.println(list);
session.close();
}
}
- 结果
2.联合查询方式
只需要编写一个SQL,在StudentMapper.xml中添加下面效果
(1)<association/>
只要专配一个对象就用这个标签。
(2)此时可以把<association/>
当做小的<resultMap>
看待
(3)javaType属性:<association/>
专配完成后返回一个什么类型的对象。取值是一个类(或类的别名)
实现步骤:
- 实体类
【注意】在Student实现类中包含一个Teacher对象。
package com.ouc.pojo;
public class Student {
private int sid;
private String sname;
private int sage;
private int tid;
private Teacher teacher;
public Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
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 int getSage() {
return sage;
}
public void setSage(int sage) {
this.sage = sage;
}
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
@Override
public String toString() {
return "Student [sid=" + sid + ", sname=" + sname + ", sage=" + sage + ", tid=" + tid + ", teacher=" + teacher
+ "]";
}
}
- 在StudentMapper.xml中
<mapper namespace="com.ouc.mapper.StudentMapper">
<resultMap type="student" id="stuMap">
<id property="sid" column="sid"/>
<result property="sname" column="sname"/>
<result property="sage" column="sage"/>
<result property="tid" column="tid"/>
<association property="teacher" javaType="Teacher">
<id property="tid" column="tid"/>
<result property="tname" column="tname"/>
</association>
</resultMap>
<select id="selAll" resultMap="stuMap">
select sid,sname,sage,t.tid tid, tname
from student s left outer join teacher t
on s.tid=t.tid
</select>
</mapper>
- 测试类
package com.ouc.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.ouc.pojo.Student;
public class Test {
public static void main(String[] args) throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession session = factory.openSession();
List<Student> list = session.selectList("com.ouc.mapper.StudentMapper.selAll");
System.out.println(list);
session.close();
}
}
- 结果
-org.apache.ibatis.logging.jdbc.BaseJdbcLogger => Preparing: select sid,sname,sage,t.tid tid, tname from student s left outer join teacher t on s.tid=t.tid
-org.apache.ibatis.logging.jdbc.BaseJdbcLogger => Parameters:
-org.apache.ibatis.logging.jdbc.BaseJdbcLogger <= Total: 10
[Student [sid=1, sname=学生1, sage=1, tid=1, teacher=Teacher [tid=1, tname=老师1]], Student [sid=2, sname=学生2, sage=2, tid=1, teacher=Teacher [tid=1, tname=老师1]], Student [sid=3, sname=学生3, sage=3, tid=1, teacher=Teacher [tid=1, tname=老师1]], Student [sid=4, sname=学生4, sage=4, tid=1, teacher=Teacher [tid=1, tname=老师1]], Student [sid=5, sname=学生5, sage=5, tid=1, teacher=Teacher [tid=1, tname=老师1]], Student [sid=6, sname=学生6, sage=6, tid=1, teacher=Teacher [tid=1, tname=老师1]], Student [sid=7, sname=学生7, sage=7, tid=1, teacher=Teacher [tid=1, tname=老师1]], Student [sid=8, sname=学生8, sage=8, tid=2, teacher=Teacher [tid=2, tname=老师2]], Student [sid=9, sname=学生9, sage=9, tid=2, teacher=Teacher [tid=2, tname=老师2]], Student [sid=10, sname=学生10, sage=10, tid=2, teacher=Teacher [tid=2, tname=老师2]]]
3. N+1查询方式和联合查询方式比较
(1)N+1:需求不确定时
(2)联合查询:需求中明确查询时两个表都要查
4. N+1名字由来
(1)举例:学生中有3 条数据
(2) 需求:查询所有学生信息级授课老师信息
(3) 需要执行的SQL 命令
(4)查询全部学生信息:select * from 学生
(5)执行3 遍select * from 老师 where id=学生的外键
(6)使用多条SQl 命令查询两表数据时,如果希望把需要的数据都
查询出来,需要执行N+1 条SQl 才能把所有数据库查询出来.
5. N+1优缺点
缺点: 效率低(使用联合查询可以解决这个问题)
优点: 如果有的时候不需要查询学生的同时查询老师.只需要
执行一个select * from student
;
适用场景:
(1)有的时候需要查询学生同时查询老师
(2)有的时候只需要查询学生.