Mybatis高级查询

最新推荐文章于 2024-06-02 12:37:41 发布

时空恋旅人

最新推荐文章于 2024-06-02 12:37:41 发布

阅读量1.8k

点赞数 3

分类专栏： Mybatis

本文链接：https://blog.csdn.net/weixin_37650458/article/details/96587906

版权

Mybatis 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

知识点总结

1、resultMap与resultType：

resultType：将查询结果按照sql列名pojo属性名一致性映射到pojo中。

场合：常见一些明细记录的展示，比如用户购买商品明细，将关联查询信息全部展示在页面时，此时可直接使用resultType将每一条记录映射到pojo中，在前端页面遍历list（list中是pojo）即可。

resultMap：使用association和collection完成一对一和一对多高级映射（对结果有特殊的映射要求）。

2、association

作用：将关联查询信息映射到一个pojo对象中。（一个对象）

场合：为了方便查询关联信息可以使用association将关联订单信息映射为用户对象的pojo属性中，比如：查询订单及关联用户信息。使用resultType无法将查询结果映射到pojo对象的pojo属性中，根据对结果集查询遍历的需要选择使用resultType还是resultMap。

3、collection

作用：将关联查询信息映射到一个list集合中。

场合：为了方便查询遍历关联信息可以使用collection将关联信息映射到list集合中，比如：查询用户权限范围模块及模块下的菜单，可使用collection将模块映射到模块list中，将菜单列表映射到模块对象的菜单list属性中，这样的作的目的也是方便对查询结果集进行遍历查询。

1.表

四张表,用户表，订单表，订单明细表，商品表

CREATE DATABASE mybatis DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
CREATE TABLE `items` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL COMMENT '商品名称',
  `price` FLOAT(10,1) NOT NULL COMMENT '商品定价',
  `detail` TEXT COMMENT '商品描述',
  `pic` VARCHAR(64) DEFAULT NULL COMMENT '商品图片',
  `createtime` DATETIME NOT NULL COMMENT '生产日期',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `orderdetail` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `orders_id` INT(11) NOT NULL COMMENT '订单id',
  `items_id` INT(11) NOT NULL COMMENT '商品id',
  `items_num` INT(11) DEFAULT NULL COMMENT '商品购买数量',
  PRIMARY KEY (`id`),
  KEY `FK_orderdetail_1` (`orders_id`),
  KEY `FK_orderdetail_2` (`items_id`),
  CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`items_id`) REFERENCES `items` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `orders` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL COMMENT '下单用户id',
  `number` VARCHAR(32) NOT NULL COMMENT '订单号',
  `createtime` DATETIME NOT NULL COMMENT '创建订单时间',
  `note` VARCHAR(100) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `FK_orders_1` (`user_id`),
  CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(32) NOT NULL COMMENT '用户名称',
  `birthday` DATE DEFAULT NULL COMMENT '生日',
  `sex` CHAR(1) DEFAULT NULL COMMENT '性别',
  `address` VARCHAR(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

关系如图：一个用户有多个订单，一个对应多个订单明细，一个订单明细对应一个商品信息。

插入数据：

INSERT  INTO `items`(`id`,`name`,`price`,`detail`,`pic`,`createtime`) VALUES (1,'台式机',3000.0,'该电脑质量非常好！！！！',NULL,'2015-02-03 13:22:53'),(2,'笔记本',6000.0,'笔记本性能好，质量好！！！！！',NULL,'2015-02-09 13:22:57'),(3,'背包',200.0,'名牌背包，容量大质量好！！！！',NULL,'2015-02-06 13:23:02');

INSERT  INTO `orderdetail`(`id`,`orders_id`,`items_id`,`items_num`) VALUES (1,3,1,1),(2,3,2,3),(3,4,3,4),(4,4,2,3);

INSERT  INTO `orders`(`id`,`user_id`,`number`,`createtime`,`note`) VALUES (3,1,'1000010','2015-02-04 13:22:35',NULL),(4,1,'1000011','2015-02-03 13:22:41',NULL),(5,10,'1000012','2015-02-12 16:13:23',NULL);

INSERT  INTO `user`(`id`,`username`,`birthday`,`sex`,`address`) VALUES (1,'王五',NULL,'2',NULL),(10,'张三','2014-07-10','1','北京市'),(16,'张小明',NULL,'1','河南郑州'),(22,'陈小明',NULL,'1','河南郑州'),(24,'张三丰',NULL,'1','河南郑州'),(25,'陈小明',NULL,'1','河南郑州'),(26,'王五',NULL,NULL,NULL);

2.实体类

实体类Orders

package com.wx.domain;

import java.util.Date;
import java.util.List;

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

实体类User:

package com.wx.domain;

import java.util.Date;
import java.util.List;

public class User {
    //属性名和数据库表的字段对应
    private int id;
    private String username;// 用户姓名
    private String sex;// 性别
    private Date birthday;// 生日
    private String address;// 地址

    //用户创建的订单列表
    private List<Orders> ordersList;


    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    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 Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", sex=" + sex
                + ", birthday=" + birthday + ", address=" + address + "]";
    }
    public List<Orders> getOrdersList() {
        return ordersList;
    }
    public void setOrdersList(List<Orders> ordersList) {
        this.ordersList = ordersList;
    }

}

关联查询结果类：OrdersCustom

package com.wx.domain;

//通过此类映射订单和用户查询的结果，让此类继承包括字段较多的pojo类
public class OrdersCustom extends Orders {
    //添加用户属性
    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;
    }

}

实体类Orderdetail：

package com.wx.domain;

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 + "]";
    }
}

实体类Items：

package com.wx.domain;

import java.util.Date;

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 + "]";
    }
}

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.wx.dao.OrdersMapper">
    <!--设置domain类和数据库中表的字段一一对应，注意数据库字段和domain类中的字段名称不致，此处一定要！ -->
    <resultMap type="com.wx.domain.Orders" id="OrdersUserResultMap">
       <!-- 查询订单关联查询用户信息,订单对用户是一个一对一的关系 使用association-->
        <!--订单表的信息-->
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="number" property="number"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>
        <!--关联到用户的信息-->
        <association property="user" javaType="com.wx.domain.User">
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>
        </association>
    </resultMap>

    <select id="findOrdersUser" resultType="com.wx.domain.OrdersCustom" parameterType="com.wx.domain.Orders">
        SELECT orders.*, USER.username,USER.sex,USER.address FROM orders,USER WHERE
        orders.user_id = user.id AND orders.id =#{id}
    </select>
    <!--返回值使用resultMap-->
    <select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap" parameterType="com.wx.domain.Orders">
        SELECT orders.*, USER.username,USER.sex,USER.address FROM orders,USER WHERE
        orders.user_id = user.id AND orders.id =#{id}
    </select>
    <!--resultMap还可以继承，一个订单包括多个订单明细，一对多使用collection-->
    <resultMap type="com.wx.domain.Orders" id="OrdersAndOrderDetailResultMap" extends="OrdersUserResultMap" >

        <collection property="orderdetails" ofType="com.wx.domain.Orderdetail">
            <id column="orderdetail_id" property="id"/>
            <result column="items_id" property="itemsId"/>
            <result column="items_num" property="itemsNum"/>
            <result column="orders_id" property="ordersId"/>
        </collection>
    </resultMap>
    <!--查询订单关联查询用户及订单明细，使用resultmap-->
    <select id="findOrdersAndOrderDetailResultMap" resultMap="OrdersAndOrderDetailResultMap" parameterType="com.wx.domain.Orders" >
        SELECT
        orders.*,
        USER.username,
        USER.sex,
        USER.address,
        orderdetail.id orderdetail_id,
        orderdetail.items_id,
        orderdetail.items_num,
        orderdetail.orders_id
        FROM
        orders,
        USER,
        orderdetail
        WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id
        AND orders.id=#{id}
    </select>
  <!--  查询用户及购买的商品-->
    <resultMap type="com.wx.domain.User" id="UserAndItemsResultMap">
        <!-- 用户信息 -->
        <id column="user_id" property="id"/>
        <result column="username" property="username"/>
        <result column="sex" property="sex"/>
        <result column="address" property="address"/>
        <!-- 订单信息一个用户对应多个订单，使用collection映射-->
        <collection property="ordersList" ofType="com.wx.domain.Orders">
            <id column="id" property="id"/>
            <result column="user_id" property="userId"/>
            <result column="number" property="number"/>
            <result column="createtime" property="createtime"/>
            <result column="note" property="note"/>
            <!-- 订单明细 一个订单包括 多个明细-->
            <collection property="orderdetails" ofType="com.wx.domain.Orderdetail">
                <id column="orderdetail_id" property="id"/>
                <result column="items_id" property="itemsId"/>
                <result column="items_num" property="itemsNum"/>
                <result column="orders_id" property="ordersId"/>
                <!-- 商品信息一个订单明细对应一个商品-->
                <association property="items" javaType="com.wx.domain.Items">
                    <id column="items_id" property="id"/>
                    <result column="items_name" property="name"/>
                    <result column="items_detail" property="detail"/>
                    <result column="items_price" property="price"/>
                </association>
            </collection>
        </collection>
    </resultMap>

    <!-- 查询用户及购买的商品信息，使用resultmap -->
    <select id="findUserAndItemsResultMap" resultMap="UserAndItemsResultMap">
        SELECT
        orders.*,
        USER.username,
        USER.sex,
        USER.address,
        orderdetail.id orderdetail_id,
        orderdetail.items_id,
        orderdetail.items_num,
        orderdetail.orders_id,
        items.name items_name,
        items.detail items_detail,
        items.price items_price
        FROM
        orders,
        USER,
        orderdetail,
        items
        WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id AND orderdetail.items_id = items.id
    </select>

</mapper>

Mapper:

package com.wx.dao;

import com.wx.domain.Orders;
import com.wx.domain.OrdersCustom;
import com.wx.domain.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface OrdersMapper {
    /***
     * 查询订单关联查询用户信息,订单对用户是一个一对一的关系
     * @return
     * @throws Exception
     */
    public List<OrdersCustom> findOrdersUser(Orders orders)throws Exception;

    /***
     * 查询订单关联查询用户使用resultMap
     * @return
     * @throws Exception
     */
    public List<Orders> findOrdersUserResultMap(Orders orders)throws Exception;

    /***
     * 查询订单(关联用户)及订单明细
     * @return
     * @throws Exception
     */
    public List<Orders>  findOrdersAndOrderDetailResultMap(Orders orders)throws Exception;

    /**
     * 查询用户购买商品信息
     * @return
     * @throws Exception
     */
    public List<User>  findUserAndItemsResultMap()throws Exception;

    /***
     * 查询订单关联查询用户，用户信息是延迟加载
     * @return
     * @throws Exception
     */
    public List<Orders> findOrdersUserLazyLoading()throws Exception;

}

查询：一对一

package com.wx.controller;

import com.google.gson.Gson;
import com.wx.dao.OrdersMapper;
import com.wx.domain.Orders;
import com.wx.domain.OrdersCustom;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.List;

@Controller
public class SeniorQueryController {
    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    @RequestMapping(value = "/orderanduser",produces = "text/json;charset=UTF-8")
    @ResponseBody
    public String  query(Orders orders) throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
        List<OrdersCustom> ordersUser = mapper.findOrdersUser(orders);
        Gson gson=new Gson();
        String json = gson.toJson(ordersUser);
        return json;
    }
}

结果：

使用resultMap作为返回值，才能真正关联到用户的信息。

<!--返回值使用resultMap-->
    <select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap" parameterType="com.wx.domain.Orders">
        SELECT orders.*, USER.username,USER.sex,USER.address FROM orders,USER WHERE
        orders.user_id = user.id AND orders.id =#{id}
    </select>

一对一对多，查询订单关联查询用户及订单明细，一个订单包括多个订单明细

<?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.wx.dao.OrdersMapper">
    <!--设置domain类和数据库中表的字段一一对应，注意数据库字段和domain类中的字段名称不致，此处一定要！ -->
    <resultMap type="com.wx.domain.Orders" id="OrdersUserResultMap">
       <!-- 查询订单关联查询用户信息,订单对用户是一个一对一的关系-->
        <!--订单表的信息-->
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="number" property="number"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>
        <!--关联到用户的信息-->
        <association property="user" javaType="com.wx.domain.User">
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>
        </association>
    </resultMap>

    <select id="findOrdersUser" resultType="com.wx.domain.OrdersCustom" parameterType="com.wx.domain.Orders">
        SELECT orders.*, USER.username,USER.sex,USER.address FROM orders,USER WHERE
        orders.user_id = user.id AND orders.id =#{id}
    </select>
    <!--返回值使用resultMap-->
    <select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap" parameterType="com.wx.domain.Orders">
        SELECT orders.*, USER.username,USER.sex,USER.address FROM orders,USER WHERE
        orders.user_id = user.id AND orders.id =#{id}
    </select>
    <!--resultMap还可以继承-->
    <resultMap type="com.wx.domain.Orders" id="OrdersAndOrderDetailResultMap" extends="OrdersUserResultMap" >

        <collection property="orderdetails" ofType="com.wx.domain.Orderdetail">
            <id column="orderdetail_id" property="id"/>
            <result column="items_id" property="itemsId"/>
            <result column="items_num" property="itemsNum"/>
            <result column="orders_id" property="ordersId"/>
        </collection>
    </resultMap>
    <!--查询订单关联查询用户及订单明细，使用resultmap-->
    <select id="findOrdersAndOrderDetailResultMap" resultMap="OrdersAndOrderDetailResultMap" parameterType="com.wx.domain.Orders" >
        SELECT
        orders.*,
        USER.username,
        USER.sex,
        USER.address,
        orderdetail.id orderdetail_id,
        orderdetail.items_id,
        orderdetail.items_num,
        orderdetail.orders_id
        FROM
        orders,
        USER,
        orderdetail
        WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id
        AND orders.id=#{id}
    </select>


</mapper>

查询用户及购买的商品信息，使用resultmap

<?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.wx.dao.OrdersMapper">
    <!--设置domain类和数据库中表的字段一一对应，注意数据库字段和domain类中的字段名称不致，此处一定要！ -->
    <resultMap type="com.wx.domain.Orders" id="OrdersUserResultMap">
       <!-- 查询订单关联查询用户信息,订单对用户是一个一对一的关系 使用association-->
        <!--订单表的信息-->
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="number" property="number"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>
        <!--关联到用户的信息-->
        <association property="user" javaType="com.wx.domain.User">
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>
        </association>
    </resultMap>

    <select id="findOrdersUser" resultType="com.wx.domain.OrdersCustom" parameterType="com.wx.domain.Orders">
        SELECT orders.*, USER.username,USER.sex,USER.address FROM orders,USER WHERE
        orders.user_id = user.id AND orders.id =#{id}
    </select>
    <!--返回值使用resultMap-->
    <select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap" parameterType="com.wx.domain.Orders">
        SELECT orders.*, USER.username,USER.sex,USER.address FROM orders,USER WHERE
        orders.user_id = user.id AND orders.id =#{id}
    </select>
    <!--resultMap还可以继承，一个订单包括多个订单明细，一对多使用collection-->
    <resultMap type="com.wx.domain.Orders" id="OrdersAndOrderDetailResultMap" extends="OrdersUserResultMap" >

        <collection property="orderdetails" ofType="com.wx.domain.Orderdetail">
            <id column="orderdetail_id" property="id"/>
            <result column="items_id" property="itemsId"/>
            <result column="items_num" property="itemsNum"/>
            <result column="orders_id" property="ordersId"/>
        </collection>
    </resultMap>
    <!--查询订单关联查询用户及订单明细，使用resultmap-->
    <select id="findOrdersAndOrderDetailResultMap" resultMap="OrdersAndOrderDetailResultMap" parameterType="com.wx.domain.Orders" >
        SELECT
        orders.*,
        USER.username,
        USER.sex,
        USER.address,
        orderdetail.id orderdetail_id,
        orderdetail.items_id,
        orderdetail.items_num,
        orderdetail.orders_id
        FROM
        orders,
        USER,
        orderdetail
        WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id
        AND orders.id=#{id}
    </select>
  <!--  查询用户及购买的商品-->
    <resultMap type="com.wx.domain.User" id="UserAndItemsResultMap">
        <!-- 用户信息 -->
        <id column="user_id" property="id"/>
        <result column="username" property="username"/>
        <result column="sex" property="sex"/>
        <result column="address" property="address"/>
        <!-- 订单信息一个用户对应多个订单，使用collection映射-->
        <collection property="ordersList" ofType="com.wx.domain.Orders">
            <id column="id" property="id"/>
            <result column="user_id" property="userId"/>
            <result column="number" property="number"/>
            <result column="createtime" property="createtime"/>
            <result column="note" property="note"/>
            <!-- 订单明细 一个订单包括 多个明细-->
            <collection property="orderdetails" ofType="com.wx.domain.Orderdetail">
                <id column="orderdetail_id" property="id"/>
                <result column="items_id" property="itemsId"/>
                <result column="items_num" property="itemsNum"/>
                <result column="orders_id" property="ordersId"/>
                <!-- 商品信息一个订单明细对应一个商品-->
                <association property="items" javaType="com.wx.domain.Items">
                    <id column="items_id" property="id"/>
                    <result column="items_name" property="name"/>
                    <result column="items_detail" property="detail"/>
                    <result column="items_price" property="price"/>
                </association>
            </collection>
        </collection>
    </resultMap>

    <!-- 查询用户及购买的商品信息，使用resultmap -->
    <select id="findUserAndItemsResultMap" resultMap="UserAndItemsResultMap">
        SELECT
        orders.*,
        USER.username,
        USER.sex,
        USER.address,
        orderdetail.id orderdetail_id,
        orderdetail.items_id,
        orderdetail.items_num,
        orderdetail.orders_id,
        items.name items_name,
        items.detail items_detail,
        items.price items_price
        FROM
        orders,
        USER,
        orderdetail,
        items
        WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id AND orderdetail.items_id = items.id
    </select>

</mapper>

查询订单关联查询用户，用户信息是延迟加载

延迟加载其实就是将数据加载时机推迟，比如推迟嵌套查询的执行时机。在Mybatis中经常用到关联查询，但是并不是任何时候都需要立即返回关联查询结果。比如查询订单信息，并不一定需要及时返回订单对应的产品信息，查询商品分类信息并不一定要及时返回该类别下有哪些产品，这种情况一下需要一种机制，当需要查看时，再执行查询，返回需要的结果集，这种需求在Mybatis中可以使用延迟加载机制来实现。延迟加载可以实现先查询主表，按需实时做关联查询，返回关联表结果集，一定程度上提高了效率。

配置懒加载：

<?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>
    <settings>
        <!-- 启用延迟加载特性，不配置默认关闭该特性-->
        <setting name="lazyLoadingEnabled" value="true"></setting>
        <!-- 按需加载: false:使用关联属性，及时加载;    true,加载对象，则加载所有属性， -->
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>

    <!-- 配置分页插件 -->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageHelper">
            <!-- 设置数据库类型 Oracle,Mysql,MariaDB,SQLite,Hsqldb,PostgreSQL六种数据库-->
            <property name="dialect" value="mysql"/>
        </plugin>
    </plugins>

</configuration>

写上sql:

<?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.wx.dao.LazyLoadMapper">
    <!--设置domain类和数据库中表的字段一一对应，注意数据库字段和domain类中的字段名称不致，此处一定要！ -->
    <resultMap type="com.wx.domain.Orders" id="OrdersUserResultMap">
        <!-- 查询订单关联查询用户信息,订单对用户是一个一对一的关系 使用association-->
        <!--订单表的信息-->
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="number" property="number"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>
        <!--关联到用户的信息-->
        <association property="user"
                     javaType="com.wx.domain.User"
                     column="user_id"
                     select="findUserByUserId">
        </association>
    </resultMap>

    <!--懒加载,先查询订单的信息-->
    <select id="findOrdersUserLazyLoading" resultMap="OrdersUserResultMap" parameterType="com.wx.domain.Orders">
        SELECT orders.* FROM orders WHERE id=#{id}
    </select>
    <select id="findUserByUserId" resultType="com.wx.domain.User">
        SELECT user.*  FROM user WHERE id=#{user_id}
    </select>

</mapper>

Mapper:

package com.wx.dao;

import com.wx.domain.Orders;
import com.wx.domain.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface LazyLoadMapper {
    /***
     * 查询订单关联查询用户，用户信息是延迟加载
     * @return
     * @throws Exception
     */
    public List<Orders> findOrdersUserLazyLoading(Orders orders)throws Exception;

    User findUserByUserId(Integer user_id);

}

controller:

@RequestMapping(value = "/lazyload",produces = "text/json;charset=UTF-8")
    @ResponseBody
    public String  lazyload(Orders orders) throws Exception {
        System.out.println(orders.getId());
        SqlSession sqlSession = sqlSessionFactory.openSession();
        LazyLoadMapper mapper = sqlSession.getMapper(LazyLoadMapper.class);
        List<Orders> resultMap = mapper.findOrdersUserLazyLoading(orders);
        System.out.println("______________");
        for (Orders orders1 : resultMap) {
            System.out.println(orders1.getUserId());
        }
        String s = JSON.toJSONString(resultMap);
        return s;
    }

当查询order的时候只执行了一条语句：

取user属性的时候，才会继续执行下一条语句：

一对多懒加载时需要使用collection 返回集合

时空恋旅人

关注

3
点赞
踩
15

收藏

觉得还不错? 一键收藏
打赏
0
评论
Mybatis高级查询

知识点总结1、resultMap与resultType：resultType：将查询结果按照sql列名pojo属性名一致性映射到pojo中。场合：常见一些明细记录的展示，比如用户购买商品明细，将关联查询信息全部展示在页面时，此时可直接使用resultType将每一条记录映射到pojo中，在前端页面遍历list（list中是pojo）即可。resultMap：使用associati...
复制链接

扫一扫