Mybatis-高级映射:一对一查询

Mybatis:一对一查询获取结果有两种方式:resultType和resultMap
现在有这样一条select语句

SELECT orders.* ,`user`.username,`user`.sex,`user`.address 
		from orders,`user` 
		where 
		orders.user_id = `user`.id 

可以看出这条语句需要连接两张表进行查询,分别从数据表’orderas’,'user’中查询’orders’表的全部信息,'user’表中的username,sex,address.
现在有两张表 orders , user
orders表

user表

两个实体类 User , Orders
User.java

package mybatis.com.entity;

import java.sql.Date;

public class User {
    private int id;
    private String username;
    private Date birthday;
    private String   sex;
    private String address;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
}

Orders.java

package mybatis.com.entity;

import java.sql.Date;

public class Orders {
    private int id;
    private int user_id;
    private String num;
    private Date createtime;
    private String note;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getUser_id() {
        return user_id;
    }
    public void setUser_id(int user_id) {
        this.user_id = user_id;
    }
    public String getNum() {
        return num;
    }
    public void setNum(String num) {
        this.num = num;
    }
    public Date getCreatetime() {
        return createtime;
    }
    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }
    public String getNote() {
        return note;
    }
    public void setNote(String note) {
        this.note = note;
    }
    public User getUser() {
        return user;
    }
  
    
}

使用resultType实现

OrdersMapperCustom.xml中select语句如下:

<select id="findOrderUser" resultType="">
	SELECT orders.* ,`user`.username,`user`.sex,`user`.address 
		from orders,`user` 
		where 
		orders.user_id = `user`.id 
</select>

现在我们需要确定resultType(输出映射)的类型,我们现在有两个实体类User和ordes,但是和我们所需要的resultType的类型都不符合,所以要使用resultType,需要重新创建一个实体类,这个实体类需要包含orders的全部属性,user的部分属性.
对此,可以新建一个类OrdersCustom继承orders,并新增user的属性.
OrdersCustom.java

package mybatis.com.entity.pojo;

import java.sql.Date;

import mybatis.com.entity.Orders;

public class OrdersCustom extends Orders{
	//user属性
    private String username;
    private String   sex;
    private String address;
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
}

OrdersCustom类符合我们所需要输出映射类型
resultType=“mybatis.com.entity.pojo.OrdersCustom”

<select id="findOrderUser" resultType="mybatis.com.entity.pojo.OrdersCustom">
		SELECT orders.* ,`user`.username,`user`.sex,`user`.address 
		from orders,`user` 
		where 
		orders.user_id = `user`.id 
	</select>

测试

mapper接口

public interface OrdersMapperCustom {
    
    public List<OrdersCustom> findOrderUser();
    
}

@Test
    public void findOrderUser() throws IOException{
	String resource = "SqlMapConfig.xml";
	InputStream inputStream = Resources.getResourceAsStream(resource);
	SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
	SqlSession sqlSession = sqlSessionFactory.openSession();
	OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
	List<OrdersCustom> ordersCustoms = ordersMapperCustom.findOrderUser();
	System.out.println(ordersCustoms);
	sqlSession.close();
    }

使用resultType,需要针对每一种查询结果都要创建一个新的类来接收查询结果,接下来说一说第二种方法,resultMap

resultMap

<!--
type:class类的全限定名
comlumn:列名
property:type类的属性名
resultMap建立 类属性和表列名之间的映射关系
--!>
<resultMap type="" id="">
		<id column="" property=""/>
		<result column="" property=""/>
	</resultMap>

使用resultMap可以不用重新创建一个新的子类,不过需要修改原有的类,
在orders.java新增一个属性user,再在resultMap中建立查询返回结果中和user有关的列与user属性之间的映射关系.
orders.java

package mybatis.com.entity;

import java.sql.Date;

public class Orders {
    private int id;
    private int user_id;
    private String num;
    private Date createtime;
    private String note;
    //新增user属性
    private User user;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getUser_id() {
        return user_id;
    }
    public void setUser_id(int user_id) {
        this.user_id = user_id;
    }
    public String getNum() {
        return num;
    }
    public void setNum(String num) {
        this.num = num;
    }
    public Date getCreatetime() {
        return createtime;
    }
    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }
    public String getNote() {
        return note;
    }
    public void setNote(String note) {
        this.note = note;
    }
    public User getUser() {
        return user;
    }
    public void setUser(User user) {
        this.user = user;
    }
}

resultMap

<!-- 
		订单查询用户的resultMap,将查询结果映射到orders
	 -->
	<resultMap type="mybatis.com.entity.Orders" id="OrderUserResultMap">
		<!-- 配置映射的订单信息 -->
		<id column="id" property="id"/>
		<result column="user_id" property="user_id"/>
		<result column="num" property="num"/>
		<result column="createtime" property="createtime"/>
		<result column="note" property="note"/>
		<!-- 配置映射的用户信息 -->
		<!-- association用于映射单个对象的信息
			property:要将查询到的用户信息映射到orders中哪个属性
		 -->
		<association property="user" javaType="mybatis.com.entity.User">
			<id column="user_id" property="id"/>
			<result column="username" property="username"/>
			<result column="sex" property="sex"/>
			<result column="address" property="address"/>
		</association>
	</resultMap>

实现一对一查询:
resultType:使用resultType比较简单,如果实体类中没有对应的属性,只需要增加列名对应的属性,即可完成映射,如果对查询结果没有特殊要求建议用resultType.
resultMap:需要单独定义resultMap,实现有点麻烦,如果对查询结果有特殊要求可以使用resultMap.
resultMap可以实现延迟加载,resultType无法实现延迟加载.