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

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=广州佛山]]

与数据库比较：

这是我们完成的第一个订单查询