mybatis利用resultMap方式实现数据库表一对一查询

由于 Order 订单表和 User 用户表是一对一关系，因此实现一对一查询

思路：用 mybatis 就少不了写 sql，在 sql 中有要查询的字段，这些字段要被映射到一个 pojo 中，因此想到要创建 pojo 类 

sqlMapConfig.xml 文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"  
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="OrdersMapperCustom.xml"/>
    </mappers>
</configuration>

OrdersMapperCustom.xml 文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"  
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mx.resultMap.OrderMapperCustom">

    <!-- 定义一个 resultMap
        这里的 id 要和下面的 statement 的 resultMap 值一样，type 就是要映射的类别，column 就是在sql 语句中要查询的字段
        property:是Orders 类里面对应于查询字段的属性
     -->
    <resultMap type="com.mx.resultMap.Orders" id="OrderUserResultMap">
        <id column="id" property="id"/>
        <result column="user_id" property="user_id"/>
        <result column="number" property="number"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>
        
        <!-- 配置关联用户信息
            association:用于映射关联查询单个对象信息
            property:将关联查询用户信息映射到 Orders 中的哪个属性
         -->
        <association property="user" javaType="com.mx.resultMap.User">
            <result column="username" property="username"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>
        </association>
    </resultMap>
    
    <!-- resultMap 映射方式 -->
    <select id="findUserresultMap" resultMap="OrderUserResultMap">
        select orders.*,
                user.username,
                user.sex,
                user.address
        from orders,user
        where orders.user_id=user.id;
    </select>
</mapper>

User 类：

package com.mx.resultMap;

public class 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;
	}
}

Orders 类：

package com.mx.resultMap;
import java.util.Date;

public class Orders {

	private int id;
	private String user_id;
	private String number;
	private Date createtime;
	private String note;
	
	//定义一个 User 把关联查询的结果映射到该对象中
	private User user;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUser_id() {
		return user_id;
	}
	public void setUser_id(String user_id) {
		this.user_id = user_id;
	}
	public String getNumber() {
		return number;
	}
	public void setNumber(String number) {
		this.number = number;
	}
	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;
	}
	
}

OrderMapperCustom 接口：

package com.mx.resultMap;

import java.util.List;

public interface OrderMapperCustom {

	public List<Orders> findUserresultMap();
}

Test 测试类：

package com.mx.resultMap;

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;

public class Test {

	public static void main(String[]args) throws IOException{
		String resource="sqlMapConfig.xml";
		InputStream is = Resources.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		OrderMapperCustom omc = sqlSession.getMapper(OrderMapperCustom.class);
		List<Orders> list = omc.findUserresultMap();
		System.out.println(list);
	}
}