mybatis进阶（2）--一对一查询（使用resultType）

本文链接：https://blog.csdn.net/fromddd/article/details/75292726

1.需求分析：

SELECT 
            * 
          FROM
            orderdetail 
            SELECT 
              orders.*,
              user.username,
              user.sex,
              user.address 
            FROM
              orders,
              USER 
            WHERE orders.user_id = user.id

对于这样的查询，我们首先需要创建相关的pojo

package com.ddd.mybatis.pojo;

import java.util.Date;
/**
 * 商品信息
 * @author Dan
 *
 */
public class Items {
    private Integer id;

    private String name;

    private Float price;

    private String pic;

    private Date createtime;

    private String detail;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name == null ? null : name.trim();
    }

    public Float getPrice() {
        return price;
    }

    public void setPrice(Float price) {
        this.price = price;
    }

    public String getPic() {
        return pic;
    }

    public void setPic(String pic) {
        this.pic = pic == null ? null : pic.trim();
    }

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    public String getDetail() {
        return detail;
    }

    public void setDetail(String detail) {
        this.detail = detail == null ? null : detail.trim();
    }

    @Override
    public String toString() {
        return "Items [id=" + id + ", name=" + name + ", price=" + price
                + ", pic=" + pic + ", createtime=" + createtime + ", detail="
                + detail + "]";
    }

}

package com.ddd.mybatis.pojo;

import java.util.Date;
import java.util.List;
/**
 * 订单信息
 * @author Dan
 *
 */
public class Orders {


    private Integer id;

    private Integer userId;

    private String number;

    private Date createtime;

    private String note;

    //用户信息
    private User user;

    //订单明细
    private List<Orderdetail> orderdetails;

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

    public List<Orderdetail> getOrderdetails() {
        return orderdetails;
    }

    public void setOrderdetails(List<Orderdetail> orderdetails) {
        this.orderdetails = orderdetails;
    }

     @Override
        public String toString() {
            return "Orders [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
                    + ", note=" + note + ", user=" + user + ", orderdetails=" + orderdetails + "]";
        }


}

package com.ddd.mybatis.pojo;
/**
 * 订单明细信息
 * @author Dan
 *
 */
public class Orderdetail {
    private Integer id;

    private Integer ordersId;

    private Integer itemsId;

    private Integer itemsNum;

    //明细对应的商品信息
    private Items items;


    public Integer getId() {
        return id;
    }

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

    public Integer getOrdersId() {
        return ordersId;
    }

    public void setOrdersId(Integer ordersId) {
        this.ordersId = ordersId;
    }

    public Integer getItemsId() {
        return itemsId;
    }

    public void setItemsId(Integer itemsId) {
        this.itemsId = itemsId;
    }

    public Integer getItemsNum() {
        return itemsNum;
    }

    public void setItemsNum(Integer itemsNum) {
        this.itemsNum = itemsNum;
    }

    public Items getItems() {
        return items;
    }

    public void setItems(Items items) {
        this.items = items;
    }

    @Override
    public String toString() {
        return "Orderdetail [id=" + id + ", ordersId=" + ordersId
                + ", itemsId=" + itemsId + ", itemsNum=" + itemsNum + "]";
    }

}

但是我们要查询的是多表查询，所以需要这样一个包装类，将我们要查询的全部字段都能包含，使用继承的方式

package com.ddd.mybatis.pojo;

/**
 * 订单的扩展类
 * @author Dan
 *
 */
//通过此类映射订单和用户查询的结果，让此类继承包括 字段较多的pojo类
public class OrdersCustom extends Orders{

    //添加用户属性
    /*USER.username,
      USER.sex,
      USER.address */

    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;
    }
    @Override
    public String toString() {
        return "OrdersCustom [username=" + username + ", sex=" + sex + ", address=" + address + "]";
    }



}

接下来就是写mapper.xml了
根据sql分析
我们需要创建一个OrdersMapperCustom.xml
查询语句：

<!-- 查询订单关联查询用户信息 -->

    <select id="findOrdersUser" resultType="cn.itcast.mybatis.po.OrdersCustom">
        SELECT
        orders.*,
        USER.username,
        USER.sex,
        USER.address
        FROM
        orders,
        USER
        WHERE orders.user_id = user.id
    </select>

mapper.java接口方法

//查询订单关联查询用户信息
    public List<OrdersCustom> findOrdersUser()throws Exception;

测试代码：

package com.ddd.mybatis.mapperTest;

import static org.junit.Assert.*;

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 com.ddd.mybatis.mapper.OrdersMapperCustom;
import com.ddd.mybatis.pojo.OrdersCustom;
import com.sun.jmx.remote.util.OrderClassLoaders;

public class OrdersMapperCustomTest {
    private SqlSessionFactory sqlSessionFactory;
    @Before
    public void setUp() throws IOException{
        String fileSource="SqlMapConfig.xml";
        //获取文件流
        InputStream inputStream=Resources.getResourceAsStream(fileSource);
        //根据加载的配置文件信息创建SqlSessionFactory
        sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
    }
    @Test
    public void testFindOrdersUser() throws Exception {
        SqlSession sqlSession=sqlSessionFactory.openSession();
        //获取代理对象
        OrdersMapperCustom ordersMapperCustom=sqlSession.getMapper(OrdersMapperCustom.class);
        List<OrdersCustom> list=ordersMapperCustom.findOrdersUser();
        System.out.println(list);
        for (OrdersCustom ordersCustom : list) {
            System.out.println(ordersCustom);
        }
        sqlSession.close();
    }

}

结果：

Preparing: SELECT orders.*, USER.username, USER.sex, USER.address FROM orders, USER WHERE orders.user_id = user.id 
2017-07-18 12:39:24,787 [main] [com.ddd.mybatis.mapper.OrdersMapperCustom.findOrdersUser]-[DEBUG] ==> Parameters: 
2017-07-18 12:39:24,817 [main] [com.ddd.mybatis.mapper.OrdersMapperCustom.findOrdersUser]-[DEBUG] <==      Total: 5
[OrdersCustom [username=小明, sex=男, address=北京市海淀区], OrdersCustom [username=huauhua, sex=女, address=河南郑州], OrdersCustom [username=杰克, sex=男, address=美国纽约], OrdersCustom [username=小明星, sex=男, address=北京市海淀区4], OrdersCustom [username=大张伟, sex=男, address=广州佛山]]