Mybatis中一对一映射详解
Student和Address是一个【一对一】关系,我们进行对这类关系的操作
1、建表
对于地址信息,我们能够通过分析可知,有编号,街道,城市国家等,而对于教师信息,
drop table students;
drop table addresses;
如果需要,可以使用在上述语句后面追加 cascade constraints; 进行级联删除,然后根据下面语句进行创建表addresses和students
create table addresses(
addr_id number primary key,
street varchar2(20) not null,
city varchar2(20) not null,
state varchar2(20) not null,
zip varchar2(10),
country varchar2(20));
create table students(
stud_id number primary key,
name varchar2(20) not null,
email varchar2(20),
dob date,
phone varchar2(15),
addr_id number references addresses(addr_id));
创建后结果如下所示:
addresses 表的样例输入如下所示:
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 表的样例数据如下所示:
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);
2、创建pojo java类:
PhoneNumber类,是一个我们自己创建的类,主要用于对电话号码的转换
package com.mybatis.pojo;
public class PhoneNumber {
private String countryCode;
private String stateCode;
private String number;
public PhoneNumber() {}
public PhoneNumber(String phone) {
if(phone!=null)
{//进行分隔三段式电话号码
String [] strs=phone.split("[-]");
this.countryCode = strs[0];
this.stateCode = strs[1];
this.number = strs[2];
}
}
public PhoneNumber(String countryCode, String stateCode,String number) {
this.countryCode = countryCode;
this.stateCode = stateCode;
this.number = number;
}
@Override
public String toString() {
return countryCode+"-"+stateCode+"-"+number;
}
public String getCountryCode() {
return countryCode;
}
public void setCountryCode(String countryCode) {
this.countryCode = countryCode;
}
public String getStateCode() {
return stateCode;
}
public void setStateCode(String stateCode) {
this.stateCode = stateCode;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
}
Address类的实现:
package com.mybatis.pojo;
public class Address{
private Integer addrId;
private String street;
private String city;
private String state;
private String zip;
private String country;
public Address() {}
public Address(String street, String city, String state,String zip, String country) {
this.street = street;
this.city = city;
this.state = state;
this.zip = zip;
this.country = country;
}
public Address(Integer addrId, String street, String city,String state, String zip, String country) {
this.addrId = addrId;
this.street = street;
this.city = city;
this.state = state;
this.zip = zip;
this.country = country;
}
public Integer getAddrId() {
return addrId;
}
public void setAddrId(Integer addrId) {
this.addrId = addrId;
}
public String getStreet() {
return street;
}
public void setStreet(String street) {
this.street = street;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getState() {
return state;
}
public void setState(String state) {
this.state = state;
}
public String getZip() {
return zip;
}
public void setZip(String zip) {
this.zip = zip;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
@Override
public String toString() {
return "Address [addrId=" + addrId + ", street=" + street
+ ", city=" + city + ", state=" + state + ", zip="
+ zip + ", country=" + country + "]";
}
}
Student类的实现:
package com.mybatis.pojo;
import java.util.Date;
public class Student {
private Integer studId;
private String name;
private String email;
private Date dob;
private PhoneNumber phone;
private Address address;//注意实现的方式,在java中是对象,在数据库中是id
public Student() {}
public Student(String name, String email, Date dob,PhoneNumber phone, Address address) {
this.name = name;
this.email = email;
this.dob = dob;
this.phone = phone;
this.address = address;
}
public Student(Integer studId, String name, String email, Date dob,PhoneNumber phone, Address address) {
this.studId = studId;
this.name = name;
this.email = email;
this.dob = dob;
this.phone = phone;
this.address = address;
}
public Integer getStudId() {
return studId;
}
public void setStudId(Integer studId) {
this.studId = studId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getDob() {
return dob;
}
public void setDob(Date dob) {
this.dob = dob;
}
public PhoneNumber getPhone() {
return phone;
}
public void setPhone(PhoneNumber phone) {
this.phone = phone;
}
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
@Override
public String toString() {
return "Student [studId=" + studId + ", name=" + name
+ ", email=" + email + ", dob=" + dob + ", phone="
+ phone + ", address=" + address + "]";
}
}
当MyBatis将一个Java对象作为输入参数执行INSERT语句操作时,它会创建一个PreparedStatement对象,并且使用setXXX()方法对?号占位符 设置相应的参数值 。这里,XXX可以是int,String,Date 等 Java对象属性类型的任意一个。这里,参数对象的属性phone是 PhoneNumber类型。但是,MyBatis 并不知道该怎样来处理这个类型的对象。为了让MyBatis明白怎样处理这个自定义的Java对象类型,如PhoneNumber,我们可以创建一个自定义的类型处理器,MyBatis提供了抽象类BaseTypeHandler<T> ,我们可以继承此类创建自定义类型处理器。
新建包com.mybatis.handlers,编写代码如下所示:
package com.mybatis.handlers;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import com.mybatis.pojo.PhoneNumber;
public class PhoneNumberHandlers extends BaseTypeHandler<PhoneNumber> {
@Override
public void setNonNullParameter(PreparedStatement ps,int i, PhoneNumber phonenumber, JdbcType jdbctype)
throws SQLException {
ps.setString(i, phonenumber.toString());
}
@Override
public PhoneNumber getNullableResult(ResultSet rs,String column) throws SQLException {
return new PhoneNumber(rs.getString(column));
}
@Override
public PhoneNumber getNullableResult(ResultSet rs, int i)
throws SQLException {
return new PhoneNumber(rs.getString(i));
}
@Override
public PhoneNumber getNullableResult(CallableStatement paramCallableStatement, int paramInt)
throws SQLException {
return null;
}
}
这样,就能够进行处理PhoneNumber类的数据了
再有,为了简化对SqlSession的创建工作,我们创建com.mybatis.utils包对工厂类进行封装:
package com.mybatis.utils;
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MyBatisSqlSessionFactory {
private static SqlSessionFactory sqlSessionFactory;
public static SqlSessionFactory getSqlSessionFactory(){
if(sqlSessionFactory == null){
InputStream inputStream = null;
try {
inputStream = Resources.getResourceAsStream("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException(e.getCause());
}
}
return sqlSessionFactory;
}
public static SqlSession openSession() {
return openSession(false); //默认手动提交,故我们在调用的时候需要进行提交
}
public static SqlSession openSession(boolean autoCommit) {
return getSqlSessionFactory().openSession(autoCommit);
}
}
3、实现一对一映射
3.1 使用【对象.属性名】的方式映射
SqlMapper XML:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- com.mybatis.mappers.StudentMapper是我们定义接口的全限定名字 这样就可以使用接口调用映射的SQL语句了 这个名字一定要和接口对应上 -->
<mapper namespace="com.mybatis.mappers.StudentMapper">
<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" />
<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>
</mapper>
我们可以使用(对象.属性名)的方式为内嵌的对象的属性赋值。在上述的resultMap中,Student的address属性使用该方式被赋上了 address 对应列的值。同样地,我们可以访问【任意深度】的内嵌对象的属性。
//接口定义
package com.mybatis.mappers;
import com.mybatis.pojo.Student;
public interface StudentMapper {
Student selectStudentWithAddress(Integer id);
}
//方法调用
package com.mybatis.test;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.mybatis.mappers.StudentMapper;
import com.mybatis.pojo.Student;
import com.mybatis.utils.MyBatisSqlSessionFactory;
public class StudentMapperTest {
@Test
public void test_selectStudentWithAddress()
{
SqlSession session=null;
session = MyBatisSqlSessionFactory.openSession();//使用封装之后的类
// 使用sqlsession获得映射接口的实现类对象,接口的引用指向实现类的对象
StudentMapper mapper = session.getMapper(StudentMapper.class);
Student student = mapper.selectStudentWithAddress(1);
System.out.println(student);
System.out.println("执行完毕");
}
}
执行结果,能够根据学生信息查询到学生的住址信息:
上面展示了一对一关联映射的一种方法。然而,使用这种方式映射,如果address结果需要在其他的SELECT映射语句中映射成Address对象,我们需要为每一个语句重复这种映射关系。MyBatis提供了更好地实现一对一关联映射的方法:【嵌套结果】ResultMap和【嵌套查询】select语句。接下来,我们将讨论这两种方式。
3.2 使用嵌套结果ResultMap实现一对一关系映射我们可以使用一个嵌套结果ResultMap方式来获取Student及其Address信息,代码如下:
<mapper namespace="com.mybatis.mappers.StudentMapper">
<resultMap type="Address" id="AddressResult">
<id property="addrId" column="addr_id" />
<result property="dob" column="dob" />
<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>
<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="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>
</mapper>
注:association是关联的意思
元素<association>被用来导入“有一个”(has-one)类型的关联。在上述的例子中,我们使用了<association>元素引用了另外的在同一个XML文件中定义的<resultMap>。
同时我们也可以使用<association> 定义内联的resultMap,代码如下所示:
<mapper namespace="com.mybatis.mappers.StudentMapper">
<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" />
<association property="address" javaType="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>
<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>
</mapper>
结果与第一种查询方式相同
3.3 使用嵌套查询select实现一对一关系映射
我们可以通过使用嵌套select查询来获取Student及其Address信息,代码如下:<mapper namespace="com.mybatis.mappers.StudentMapper">
<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 id="findAddressById" parameterType="int" resultMap="AddressResult">
select * from addresses
where addr_id=#{id}
</select>
<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" column="addr_id" select="findAddressById" />
</resultMap>
<select id="selectStudentWithAddress" parameterType="int" resultMap="StudentWithAddressResult">
select * from students
where stud_id=#{id}
</select>
</mapper>
在此方式中,<association>元素的select属性被设置成了id为findAddressById的语句。这里,两个分开的SQL语句将会在数据库中分别执行,第一个调用findStudentById加载student信息,而第二个调用findAddressById来加载address信息。
addr_id列的值将会被作为输入参数传递给selectAddressById语句。
结果如下所示:
由结果可知,进行了两次查询,查询语句分别为 select * from students where stud_id=? 和Preparing: select * from addresses where addr_id=?,最终显示结果