一对一映射
Student和Address是一个【一对一】关系
drop table students;
drop table addresses;
如果需要可以使用 cascade constraints;
建表语言:
create table addresses(
addr_id number primary key,
street varchar2(50) not null,
city varchar2(50) not null,
state varchar2(50) not null,
zip varchar2(10),
country varchar2(50)
);
create table students(
stud_id number primary key,
name varchar2(50) not null,
email varchar2(50),
dob date ,
phone varchar2(15),
addr_id number references addresses(addr_id)
);
java类:
public class PhoneNumber {
private String countryCode;
private String stateCode;
private String number;
get/set
}
public class Address{
private Integer addrId;
private String street;
private String city;
private String state;
private String zip;
private String country;
get/set
}
public class Student {
private Integer studId;
private String name;
private String email;
private Date dob;
private PhoneNumber phone;
private Address address;
get/set
}
addresses 表的样例输入如下所示:
addr_id street city state zip country
1 redSt kunshan W 12345 china
2 blueST kunshan W 12345 china
insert into addresses(addr_id,street,city,state,zip,country) values(1,'redSt','kunshan','W','12345','china');
insert into addresses(addr_id,street,city,state,zip,country) values(2,'blueST','kunshan','W','12345','china');
students 表的样例数据如下所示:
stud_id name email phone addr_id
1 John john@gmail.com 123-456-7890 1
2 Paul paul@gmail.com 111-222-3333 2
insert into students(stud_id,name,email,phone,addr_id) values(1,'John','john@gmail.com','123-456-7890',1);
insert into students(stud_id,name,email,phone,addr_id) values(2,'Paul','paul@gmail.com','111-222-3333',2);
mapper XML:
<resultMap type="Student" id="StudentWithAddressResult">
<id property="studId" column="stud_id" />
<result property="name" column="name" />
<result property="email" column="email" />
<result property="phone" column="phone" />
<result property="address.addrId" column="addr_id" />
<result property="address.street" column="street" />
<result property="address.city" column="city" />
<result property="address.state" column="state" />
<result property="address.zip" column="zip" />
<result property="address.country" column="country" />
</resultMap>
<select id="selectStudentWithAddress" parameterType="int" resultMap="StudentWithAddressResult">
select stud_id, name, email,dob,phone, a.addr_id, street, city, state, zip, country
from students s left outer join addresses a
on s.addr_id=a.addr_id
where stud_id=#{id}
</select>
注意:可以使用(对象.属性名)的方式为内嵌的对象内的属性赋值。
//接口定义
public interface Student Mapper{
Student selectStudentWithAddress(int studId);
}
//方法调用
int studId = 1;
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student student = studentMapper.selectStudentWithAddress(studId);
System.out.println(“Student :” + student);
System.out.println(“Address :” + student.getAddress());
上面是一对一关联映射的一种方法。
注意,在使用这种方式映射的时候,如果address结果需要在其他的SELECT映射语句中单独封装成Address对象,那么就需要把同样的配置在重新写一遍。
因为当前是在封装Student对象的时候嵌入了Address对象的封装。这段对Address映射的配置不能在其他地方重复的单独使用。
针对于这种情况,MyBatis提供了更好地实现一对一关联映射的俩种方法:
【嵌套结果】ResultMap
【嵌套查询】select
3.4.1 使用【嵌套结果】ResultMap,实现一对一关系映射
使用嵌套结果ResultMap方式来获取Student及其Address信息,代码如下:
<!-- 独立的Address封装映射 -->
<resultMap type="Address" id="AddressResult">
<id property="addrId" column="addr_id" />
<result property="street" column="street" />
<result property="city" column="city" />
<result property="state" column="state" />
<result property="zip" column="zip" />
<result property="country" column="country" />
</resultMap>
<!-- Student封装映射,里面关联上Address的封装映射 -->
<resultMap type="Student" id="StudentWithAddressResult">
<id property="studId" column="stud_id" />
<result property="name" column="name" />
<result property="email" column="email" />
<result property="dob" column="dob" />
<result property="phone" column="phone" />
<association property="address" resultMap="AddressResult" />
</resultMap>
<select id="findStudentByIdWithAddress" parameterType="int" resultMap="StudentWithAddressResult">
select stud_id, name, email,dob,phone, a.addr_id, street, city, state, zip, country
from students s left outer join addresses a
on s.addr_id=a.addr_id
where stud_id=#{id}
</select>
注:<association>是关联的意思,常被用来表示(has-one)类型的关联。就是对象1里面关联另一个对象2
同时我们也可以使用 定义【内联】的resultMap,少用代码如下所示:
<!-- 相当于把Student映射和Address映射又合并在一起写了,还是使用<association>标签 -->
<resultMap type="Student" id="StudentWithAddressResult">
<id property="studId" column="stud_id" />
<result property="name" column="name" />
<result property="email" column="email" />
<association property="address" jdbcType="Address">
<id property="addrId" column="addr_id" />
<result property="street" column="street" />
<result property="city" column="city" />
<result property="state" column="state" />
<result property="zip" column="zip" />
<result property="country" column="country" />
</association>
</resultMap>
3.4.2 使用【嵌套查询】select,实现一对一关系映射
使用嵌套查询select来获取Student及其Address信息,代码如下:
<!-- Student封装映射,里面关联上查询address使用的select语句,并指定数据库表中的这个关联的外键列的名字,这里是addr_id -->
<!-- 独立的Address封装映射 -->
<resultMap type="Address" id="AddressResult">
<id property="addrId" column="addr_id" />
<result property="street" column="street" />
<result property="city" column="city" />
<result property="state" column="state" />
<result property="zip" column="zip" />
<result property="country" column="country" />
</resultMap>
<!-- 独立的select查询,专门查询Address -->
<select id="findAddressById" parameterType="int" resultMap="AddressResult">
select * from addresses
where addr_id=#{id}
</select>
<!-- Student封装映射,里面关联上查询address使用的select语句,并指定数据库表中的这个关联的外键列的名字,这里是addr_id -->
<resultMap type="Student" id="StudentWithAddressResult">
<id property="studId" column="stud_id" />
<result property="name" column="name" />
<result property="email" column="email" />
<result property="dob" column="dob" />
<result property="phone" column="phone" />
<!--student查出来的地址id,当作参数给查地址的sql语句 column="addr_id"为参数 , select="findAddressById"为查询结果,最后封装到property="address" -->
<association property="address" column="addr_id" select="findAddressById" />
</resultMap>
<!-- 查询Student的select语句,这里不用写多表查询,因为对于address的关联查询,已经在上边定义好了,并且在结果映射中关联进来了 -->
<select id="findStudentByIdWithAddress" parameterType="int" resultMap="StudentWithAddressResult">
select * from students
where stud_id=#{id}
</select>
在此方式中,元素的select属性被设置成了id为findAddressById的语句
两个分开的SQL语句将会在数据库中分别执行,第一个调用findStudentById加载student信息,而第二个调用findAddressById来加载address信息。addr_id列的值将会被作为输入参数传递给selectAddressById语句作为参数进行条件查询。
调用findStudentWithAddress方法测试:
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Student student = mapper.selectStudentWithAddress(1);
System.out.println(student);
System.out.println(student.getAddress());
区别:嵌套结果和嵌套查询
嵌套结果:
1.嵌套结果是一条多表sql语句,与数据库交互一次
2.查询出来封装的结果有多个,在第一个封装的结果中,在外键字段上嵌套另一个结果,通过 标签,指定另一个结果的映射。
嵌套查询:
1.嵌套查询是将多表SQL语句,拆分成多条sql语句,分别对应每张表进行单表查询,与数据库交互多次
2.把第一条sql语句查出来的数据,当作第二条sql语句查询的参数,后面也是直到执行到最后一条sql语句
3.效率低,因为要和数据库交互多次,而且如果第一条sql语句结果很多条的时候,后面的sql语句会根据每一条结果继续查询