本课程对应视频教程：

1、高级查询

MyBatis作为一个ORM框架，也对sql的高级查询做了支持，这里以用户，订单，订单详情，商品为例讲解

案例说明：此案例的业务关系是用户、订单、订单详情、商品之间的关系、其中

一个订单只能属于一个人

一个订单可以有多个订单详情

一个订单详情中包含一个商品信息

他们的关系是：

订单和人是一对一的关系

订单和订单详情是一对多的关系

订单和商品是多对多的关系

1.1、数据模型分析

1.2、需求分析

一对一查询：查询订单，并且查询出下单人信息

一对多查询：查询订单，查询出下单人信息并且查询出订单详情

多对多查询：查询订单，查询出订单人信息并且查询出订单详情中的商品数据

1.3、一对一查询

1.3.1、方式一

将查询出来字段封装到一个新的类中，让这个类的属性包含所有查询出来的字段，

它的弊端在于，我的pojo类会急剧增加

先定义一个实体类

package cn.org.kingdom.pojo;public class OrderUser extends User {    private int oid;    private int userId;    private String orderNum;    public int getOid() {        return oid;    }    public void setOid(int oid) {        this.oid = oid;    }    public int getUserId() {        return userId;    }    public void setUserId(int userId) {        this.userId = userId;    }    public String getOrderNum() {        return orderNum;    }    public void setOrderNum(String orderNum) {        this.orderNum = orderNum;    }    @Override    public String toString() {        return "OrderUser [oid=" + oid + ", userId=" + userId + ", orderNum="                + orderNum + ", userid=" + userid + ", userName=" + userName                + ", pwd=" + pwd + ", age=" + age + ", sex=" + sex                + ", birthday=" + birthday + "]";    }}

定义数据接口

public List
    
      one2one(@Param("orderNum")String orderNum);
    

配置mapper.xml文件（OrderMapper.xml）

        select tb_user.*,tb_order.* from tb_user,tb_order        where tb_user.userid = tb_order.user_id and order_number=#{orderNum}  

测试类

package cn.org.kingdom.test;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.After;import org.junit.Before;import org.junit.Test;import cn.org.kingdom.mapper.OrderMapper;import cn.org.kingdom.pojo.OrderUser;import cn.org.kingdom.pojo.User;public class MyBatisTest03 {    SqlSessionFactory sqlSessionFactory = null ;    SqlSession sqlSession = null ;     OrderMapper  orderMapper = null ;    @Before    public void setUp() throws Exception {        //加载资源        String resource = "mybatis-config.xml";        InputStream inputStream = Resources.getResourceAsStream(resource);        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);        sqlSession  = sqlSessionFactory.openSession();        orderMapper = sqlSession.getMapper(OrderMapper.class);    }    @After    public void tearDown() throws Exception {        //关闭        sqlSession.close();    }    @Test    public void testone2one()throws Exception{        List
    
      list = orderMapper.one2one("20180810001");        for (OrderUser u : list) {            System.out.println(u);        }    }}
    

生成的日志

DEBUG - Opening JDBC ConnectionDEBUG - Created connection 665964512.DEBUG - Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@27b1cfe0]DEBUG - ==>  Preparing: select tb_user.*,tb_order.* from tb_user,tb_order where tb_user.userid = tb_order.user_id and order_number=? DEBUG - ==> Parameters: 20180810001(String)DEBUG - <==      Total: 1OrderUser [oid=1, userId=0, orderNumber=20180810001, userid=2, userName=阿柯, pwd=123456, age=10, sex=女, birthday=Tue Aug 14 13:45:34 CST 2018]DEBUG - Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@27b1cfe0]DEBUG - Closing JDBC Connection [oracle.jdbc.driver.T4CConnection@27b1cfe0]DEBUG - Returned connection 665964512 to pool.

1.3.2、采用面向对象的思维

让一个类持有另外一个类的属性

定义一个Order类

package cn.org.kingdom.pojo;import java.io.Serializable;public class Order implements Serializable {    private int oid;    private int userId;    private String orderNumber;    private User user ;    public int getOid() {        return oid;    }    public void setOid(int oid) {        this.oid = oid;    }    public int getUserId() {        return userId;    }    public void setUserId(int userId) {        this.userId = userId;    }    public String getOrderNumber() {        return orderNumber;    }    public void setOrderNumber(String orderNumber) {        this.orderNumber = orderNumber;    }    public User getUser() {        return user;    }    public void setUser(User user) {        this.user = user;    }    @Override    public String toString() {        return "Order [oid=" + oid + ", userId=" + userId + ", orderNumber="                + orderNumber + ", user=" + user + "]";    } }

mapper.xml文件

            
             
             
                 
              
       
              select tb_user.*,tb_order.* from tb_user,tb_order        where tb_user.userid = tb_order.user_id and order_number=#{orderNum}  

测试类

@Test    public void testone2one2()throws Exception{         List
    
      list = orderMapper.one2one2("20180810001");        for (Order u : list) {            System.out.println(u);        }    }
    

日志：

DEBUG - ==>  Preparing: select tb_user.*,tb_order.* from tb_user,tb_order where tb_user.userid = tb_order.user_id and order_number=? DEBUG - ==> Parameters: 20180810001(String)DEBUG - <==      Total: 1Order [oid=1, userId=2, orderNumber=20180810001, user=User [userid=2, userName=阿柯, pwd=123456, age=10, sex=女, birthday=Tue Aug 14 13:45:34 CST 2018]]DEBUG - Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@5bad7476]DEBUG - Closing JDBC Connection [oracle.jdbc.driver.T4CConnection@5bad7476]DEBUG - Returned connection 1538094198 to pool.

1.4、一对多查询

一对多查询：查询订单，查询出下单人信息并且查询出订单详情

sql

select tb_user.*,tb_order.*,tb_orderdetail.* from tb_user,tb_order,tb_orderdetailwhere tb_user.userid = tb_order.user_id and tb_order.oid = tb_orderdetail.order_idand order_number ='20180810001'

创建pojo类

package cn.org.kingdom.pojo;import java.io.Serializable;public class Detail  implements Serializable{    private int detailId;    private int orderId;    private int productId;    private double price;    private String status;    public Detail() {        super();    }    public int getDetailId() {        return detailId;    }    public void setDetailId(int detailId) {        this.detailId = detailId;    }    public int getOrderId() {        return orderId;    }    public void setOrderId(int orderId) {        this.orderId = orderId;    }    public int getProductId() {        return productId;    }    public void setProductId(int productId) {        this.productId = productId;    }    public double getPrice() {        return price;    }    public void setPrice(double price) {        this.price = price;    }    public String getStatus() {        return status;    }    public void setStatus(String status) {        this.status = status;    }    @Override    public String toString() {        return "Detail [detailId=" + detailId + ", orderId=" + orderId                + ", productId=" + productId + ", price=" + price + ", status="                + status + "]";    }}

然后在Order中加入多的一方的引用

private List
    
      details ;     public List
     
       getDetails() {        return details;    }    public void setDetails(List
      
        details) {        this.details = details;    }
      
     
    

mapper.xml文件

            
             
             
                 
              
             
             
                 
              
       
              select tb_user.*,tb_order.*,tb_orderdetail.*         from tb_user,tb_order,tb_orderdetail        where tb_user.userid = tb_order.user_id         and tb_order.oid = tb_orderdetail.order_id        and order_number =#{orderNum}  

测试类

public void testone2many()throws Exception{        List
    
      list = orderMapper.one2many("20180810001");        for (Order u : list) {            System.out.println(u);        }}
    

日志信息

DEBUG - Cache Hit Ratio [cn.org.kingdom.mapper.OrderMapper]: 0.0DEBUG - Opening JDBC ConnectionDEBUG - Created connection 15932635.DEBUG - Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@f31cdb]DEBUG - ==>  Preparing: select tb_user.*,tb_order.*,tb_orderdetail.* from tb_user,tb_order,tb_orderdetail where tb_user.userid = tb_order.user_id and tb_order.oid = tb_orderdetail.order_id and order_number =? DEBUG - ==> Parameters: 20180810001(String)DEBUG - <==      Total: 2Order [oid=1, userId=2, orderNumber=20180810001, user=User [userid=2, userName=阿柯, pwd=123456, age=10, sex=女, birthday=Tue Aug 14 13:45:34 CST 2018], details=[Detail [detailId=1, orderId=1, productId=1, price=8888.0, status=1], Detail [detailId=2, orderId=1, productId=2, price=188.0, status=1]]]DEBUG - Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@f31cdb]DEBUG - Closing JDBC Connection [oracle.jdbc.driver.T4CConnection@f31cdb]DEBUG - Returned connection 15932635 to pool.

1.5、多对多

多对多查询：查询订单，查询出订单人信息并且查询出订单详情中的商品数据

sql语句分析

select tb_user.*,tb_order.*,tb_orderdetail.*,tb_product.*from tb_user,tb_order,tb_orderdetail,tb_productwhere tb_user.userid = tb_order.user_id and tb_order.oid = tb_orderdetail.order_idand tb_orderdetail.product_id = tb_product.pidand order_number ='20180810001'

接口方法定义

public List
    
      many2many(@Param("orderNum")String orderNum);
    

创建一个pojo类(Product)

package cn.org.kingdom.pojo;public class Product implements Serializable {    private int pid;    private String pname ;     private double price ;     private String proDetail;    public int getPid() {        return pid;    }    public void setPid(int pid) {        this.pid = pid;    }    public String getPname() {        return pname;    }    public void setPname(String pname) {        this.pname = pname;    }    public double getPrice() {        return price;    }    public void setPrice(double price) {        this.price = price;    }    public String getProDetail() {        return proDetail;    }    public void setProDetail(String proDetail) {        this.proDetail = proDetail;    }    @Override    public String toString() {        return "Product [pid=" + pid + ", pname=" + pname + ", price=" + price                + ", proDetail=" + proDetail + "]";    }}

在detail类中加入一个Product类的引用

private Product product;public Product getProduct() {    return product;}public void setProduct(Product product) {    this.product = product;}

mapper.xml文件

            
             
             
                 
              
             
             
                 
                  
                      
                   
              
       
              select tb_user.*,tb_order.*,tb_orderdetail.*,tb_product.*        from tb_user,tb_order,tb_orderdetail,tb_product        where tb_user.userid = tb_order.user_id         and tb_order.oid = tb_orderdetail.order_id        and tb_orderdetail.product_id = tb_product.pid        and order_number =#{orderNum}  

测试类

@Test    public void testmany2many()throws Exception{        List
    
      list = orderMapper.many2many("20180810001");        for (Order u : list) {            System.out.println(u);        }    }
    

日志

DEBUG - Opening JDBC ConnectionDEBUG - Created connection 462907404.DEBUG - Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@1b97680c]DEBUG - ==>  Preparing: select tb_user.*,tb_order.*,tb_orderdetail.*,tb_product.* from tb_user,tb_order,tb_orderdetail,tb_product where tb_user.userid = tb_order.user_id and tb_order.oid = tb_orderdetail.order_id and tb_orderdetail.product_id = tb_product.pid and order_number =? DEBUG - ==> Parameters: 20180810001(String)DEBUG - <==      Total: 2Order [oid=1, userId=2, orderNumber=20180810001, user=User [userid=2, userName=阿柯, pwd=123456, age=10, sex=女, birthday=Tue Aug 14 13:45:34 CST 2018], details=[Detail [detailId=1, orderId=1, productId=1, price=8888.0, status=1, product=Product [pid=1, pname=iphone8, price=8888.0, proDetail=苹果公司最新产品]], Detail [detailId=2, orderId=1, productId=2, price=188.0, status=1, product=Product [pid=2, pname=冰峰战神, price=188.0, proDetail=关羽骚气皮肤]]]]DEBUG - Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@1b97680c]DEBUG - Closing JDBC Connection [oracle.jdbc.driver.T4CConnection@1b97680c]DEBUG - Returned connection 462907404 to pool.

1.6、resultMap的继承

resultMap元素可以通过继承机制来减少冗余

然后测试，运行ok