mybatis一对一查询

下图为简单的用户购物系统数据模型

表简介：

user：用户信息

orders：订单信息

ordertail：订单明细

items：商品信息

 

需求

查询订单用户信息，一个订单对应一个用户

可以采用两种方式实现一对一查询

第一种：resultType

第二种：resultMap

使用resultType实现一对一查询

第一步创建pojo

User.java

public class User extends BaseDo {
    private int id;
    private String username;
    private Date birthday;
    private String address;
//省略getset方法
}

Orders.java

public class Orders extends BaseDo{
    private int id;
    private int userId;
    private String number;
    private String note;
    private Date createtime;
//省略getset方法
}

BaseDo.java

import org.apache.commons.lang3.builder.ToStringBuilder;

import java.io.Serializable;

/**
 * author:zh
 * created time:2019/05/17
 */
public class BaseDo implements Serializable {

    @Override
    public String toString() {
        return ToStringBuilder.reflectionToString(this);
    }
}

创建订单用户模型OrdersCustomer.java

public class OrdersCustomer extends Orders {
    private String username;
    private String address;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

第二步创建OrdersMapper.java接口

import com.cloud.mybaits.pojo.OrdersCustomer;

/**
 * author:zh
 * created time:2019/05/18
 */
public interface OrdersMapper {
//    查询订单用户信息
    OrdersCustomer findOrderUserResultType();

}

创建映射文件OrdersMapper.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="com.cloud.mybaits.mapper.OrdersMapper">
    <sql id="Base_Column_List">
        orders.id,orders.user_id as userId,orders.number,orders.note,orders.createtime,user.username,user.address
    </sql>
    <!--id:OrdersMapper接口中的方法名称；resultType：查询结果集对应的实体类，查询字段名称需要和实体类属性名保持一致-->
  <select id="findOrderUserResultType" resultType="com.cloud.mybaits.pojo.OrdersCustomer">
      select
      <include refid="Base_Column_List"/>
      from orders,user where orders.user_id=user.id limit 1
  </select>
</mapper>

添加测试方法进行测试

package com.cloud.mybaits.mapper;

import com.cloud.mybaits.pojo.OrdersCustomer;
import com.cloud.mybaits.pojo.UserCustomer;
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 java.io.IOException;
import java.io.InputStream;
import java.util.List;

import static org.junit.Assert.*;

/**
 * author:zh
 * created time:2019/05/18
 */
public class OrdersMapperTest {
    private SqlSessionFactory sqlSessionFactory;
    @Before
    public void before() throws IOException {
        String resurce="mybatis-config.xml";
        InputStream inputStream=Resources.getResourceAsStream(resurce);
        sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
    }

//    查询订单用户信息
    @Test
    public void findOrderForUser() {
        SqlSession session=sqlSessionFactory.openSession();
        OrdersMapper ordersMapper=session.getMapper(OrdersMapper.class);
        OrdersCustomer ordersCustomer =ordersMapper.findOrderUserResultType();
        System.out.println(ordersCustomer);
        session.close();
    }

}

 

 使用resultMap实现一对一查询

OrdersMapper接口添加方法

public interface OrdersMapper {
//    查询订单用户信息
    OrdersCustomer findOrderUserResultType();

    OrdersCustomer findOrderUserResultMap();
}

修改OrdersCustomer实体类（也可以不修改）

package com.cloud.mybaits.pojo;

/**
 * author:zh
 * created time:2019/05/18
 */
public class OrdersCustomer extends Orders {
    private User user;

    public User getUser() {
        return user;
    }

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

OrdersMapper.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="com.cloud.mybaits.mapper.OrdersMapper">
    <sql id="Base_Column_List">
        orders.id,orders.user_id as userId,orders.number,orders.note,orders.createtime,user.username,user.address
    </sql>
    <resultMap id="orderUserResultMap" type="com.cloud.mybaits.pojo.OrdersCustomer">
        <!--column：orders表的主键；property：实体类属性-->
        <id property="id" column="id"></id>
        <result property="number" column="number"></result>
        <result property="createtime" column="createtime"></result>
        <association property="user" javaType="com.cloud.mybaits.pojo.User">
            <id property="id" column="userId"></id>
            <result property="username" column="username"></result>
            <result property="address" column="address"></result>
        </association>
    </resultMap>
    <!--id:OrdersMapper接口中的方法名称；resultType：查询结果集对应的实体类，查询字段名称需要和实体类属性名保持一致-->
  <select id="findOrderUserResultType" resultType="com.cloud.mybaits.pojo.OrdersCustomer">
      select
      <include refid="Base_Column_List"/>
      from orders,user where orders.user_id=user.id limit 1
  </select>
    <!--resultMap：上面resultMap的id-->
    <select id="findOrderUserResultMap" resultMap="orderUserResultMap">
        select
        <include refid="Base_Column_List"></include>
        from orders,user where orders.user_id=user.id limit 1
    </select>
</mapper>

添加测试方法

import com.cloud.mybaits.pojo.OrdersCustomer;
import com.cloud.mybaits.pojo.UserCustomer;
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 java.io.IOException;
import java.io.InputStream;
import java.util.List;

import static org.junit.Assert.*;

/**
 * author:zh
 * created time:2019/05/18
 */
public class OrdersMapperTest {
    private SqlSessionFactory sqlSessionFactory;
    @Before
    public void before() throws IOException {
        String resurce="mybatis-config.xml";
        InputStream inputStream=Resources.getResourceAsStream(resurce);
        sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
    }

//    查询订单用户信息
    @Test
    public void findOrderForUser() {
        SqlSession session=sqlSessionFactory.openSession();
        OrdersMapper ordersMapper=session.getMapper(OrdersMapper.class);
//        OrdersCustomer ordersCustomer =ordersMapper.findOrderUserResultType();
        OrdersCustomer ordersCustomer =ordersMapper.findOrderUserResultMap();
        System.out.println(ordersCustomer);
        session.close();
    }

}

测试结果

总结

resultMap比resultType更加灵活，resultMap不仅可以实现一对一，还可以实现一对多，多对多查询，后期我会编写一对多和多对多的例子 。