MyBatis高级映射（一对一查询）

1、需求

查询所有用户创建的订单信息，用户与订单为一对一关系

2、表结构

user用户表

orders订单表

orders订单表外键

3、SQL语句

4、开始编码

4.1、定义POJO

User

package org.mybatis.demo.po;

import java.util.Date;

public class User {
	
	//属性名和数据库表的字段对应
	private int id;
	private String username;// 用户姓名
	private String sex;// 性别
	private Date birthday;// 生日
	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 String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", sex=" + sex
				+ ", birthday=" + birthday + ", address=" + address + "]";
	}

}

Orders

package org.mybatis.demo.po;

import java.util.Date;
import java.util.List;

public class Orders {
	
    private Integer id;
    private Integer userId;
    private String number;
    private Date createtime;
    private String note;
    //用户信息
    private User user;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number == null ? null : number.trim();
    }

    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 == null ? null : note.trim();
    }

	public User getUser() {
		return user;
	}

	public void setUser(User user) {
		this.user = user;
	}

}

4.2、定义Mapper.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">
<mapper namespace="org.mybatis.demo.mapper.OrdersMapperCustom">
	
	<!-- 查询订单关联查询用户信息，使用resultmap -->
	<select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
		SELECT
		orders.*,
		USER.username,
		USER.sex,
		USER.address
		FROM
		orders,
		USER
		WHERE orders.user_id = user.id
	</select>
	
	<!-- 订单查询关联用户的resultMap
	将整个查询的结果映射到cn.itcast.mybatis.po.Orders中
	 -->
	<resultMap type="org.mybatis.demo.po.Orders" id="OrdersUserResultMap">
		<!-- 配置映射的订单信息 -->
		<!-- id：指定查询列中的唯 一标识，订单信息的中的唯 一标识，如果有多个列组成唯一标识，配置多个id
			column：订单信息的唯 一标识 列
			property：订单信息的唯 一标识 列所映射到Orders中哪个属性
		  -->
		<id column="id" property="id"/>
		<result column="user_id" property="userId"/>
		<result column="number" property="number"/>
		<result column="createtime" property="createtime"/>
		<result column="note" property="note"/>
		
		<!-- 配置映射的关联的用户信息 -->
		<!-- association：用于映射关联查询单个对象的信息
		property：要将关联查询的用户信息映射到Orders中哪个属性
		 -->
		<association property="user"  javaType="org.mybatis.demo.po.User">
			<!-- id：关联查询用户的唯 一标识
			column：指定唯 一标识用户信息的列
			javaType：映射到user的哪个属性
			 -->
			<id column="user_id" property="id"/>
			<result column="username" property="username"/>
			<result column="sex" property="sex"/>
			<result column="address" property="address"/>
		
		</association>
	</resultMap>
	
</mapper>

4.3、Mapper接口

package org.mybatis.demo.mapper;

import java.util.List;

import org.mybatis.demo.po.Orders;

public interface OrdersMapperCustom {

	public List<Orders> findOrdersUserResultMap();
}

4.4、单元测试

package org.mybatis.demo.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 org.junit.Before;
import org.junit.Test;
import org.mybatis.demo.mapper.OrdersMapperCustom;
import org.mybatis.demo.po.Orders;
import org.mybatis.demo.po.OrdersCustom;

public class TestMyBatis {

	private SqlSessionFactory sessionFactory;

	@Before
	public void createSqlSessionFactory() throws IOException {
		//引入配置文件
		String resource = "SqlMapConfig.xml";
		InputStream ins = Resources.getResourceAsStream(resource);
		
		//创建SqlSession对象
		sessionFactory = new SqlSessionFactoryBuilder().build(ins);
	}
	
	@Test
	public void test1() {
		SqlSession session = null;
		try {
			session = sessionFactory.openSession();
			OrdersMapperCustom mapper = session.getMapper(OrdersMapperCustom.class);
			List<Orders> list = mapper.findOrdersUserResultMap();
			for(Orders order : list){
				System.out.println(order.getUser().getUsername());
			}
		} finally {
			if(session != null){
				session.close();
			}
		}
	}

}

4.5、源代码

MyBatis高级映射（一对一查询）