问题：查询时，实体属性和列名不一致

用别名(容易)
用resultMap（麻烦、有时候必须用它，如关联关系时）
用注解

MyBatis关联关系映射(注解)


一个国家一个首都	一个省多个城市	学生与课程

一对一：在任意一方引入对方主键作为外键

一对多：在“多”的一方，添加“一”的一方的主键作为外键

多对多：产生的中间表，引入了至少两张表的主键作为外键。

实体间的表现关系如下：

一对一

一对多

多对多

class A {

B b;

}

class A {

List<B> b = new ArrayList<>();

}

class A {

List<B> b = new ArrayList<>();

}

class B {

A a;

}

class B {

A a;

}

class B {

List<A> a = new ArrayList<>();

}

课堂案例：

主要关注两个单词association(javaType)和collection (ofType)

数据库生成表的语句

Navicat Premium Data Transfer

Source Server : mysql

Source Server Type : MySQL

Source Server Version : 50717

Source Host : localhost:3306

Source Schema : mybatis

Target Server Type : MySQL

Target Server Version : 50717

File Encoding : 65001

Date: 25/05/2020 13:14:56

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for items

-- ----------------------------

DROP TABLE IF EXISTS `items`;

CREATE TABLE `items` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品名称',

`price` float(10, 1) NOT NULL COMMENT '商品定价',

`detail` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品描述',

`pic` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品图片',

`createtime` datetime(0) NOT NULL COMMENT '生产日期',

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of items

-- ----------------------------

INSERT INTO `items` VALUES (1, '台式机', 3000.0, '该电脑质量非常好！！！！', NULL, '2020-02-03 13:22:53');

INSERT INTO `items` VALUES (2, '笔记本', 6000.0, '笔记本性能好，质量好！！！！！', NULL, '2020-02-09 13:22:57');

INSERT INTO `items` VALUES (3, '背包', 200.0, '名牌背包，容量大质量好！！！！', NULL, '2020-02-06 13:23:02');

-- ----------------------------

-- Table structure for orderdetail

-- ----------------------------

DROP TABLE IF EXISTS `orderdetail`;

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) NULL DEFAULT NULL COMMENT '商品购买数量',

PRIMARY KEY (`id`) USING BTREE,

INDEX `FK_orderdetail_1`(`orders_id`) USING BTREE,

INDEX `FK_orderdetail_2`(`items_id`) USING BTREE,

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 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of orderdetail

-- ----------------------------

INSERT INTO `orderdetail` VALUES (1, 3, 1, 1);

INSERT INTO `orderdetail` VALUES (2, 3, 2, 3);

INSERT INTO `orderdetail` VALUES (3, 4, 3, 4);

INSERT INTO `orderdetail` VALUES (4, 4, 2, 3);

-- ----------------------------

-- Table structure for orders

-- ----------------------------

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`user_id` int(11) NOT NULL COMMENT '下单用户id',

`number` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单号',

`createtime` datetime(0) NOT NULL COMMENT '创建订单时间',

`note` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',

PRIMARY KEY (`id`) USING BTREE,

INDEX `FK_orders_1`(`user_id`) USING BTREE,

CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of orders

-- ----------------------------

INSERT INTO `orders` VALUES (3, 1, '1000010', '2020-02-04 13:22:35', NULL);

INSERT INTO `orders` VALUES (4, 1, '1000011', '2020-02-03 13:22:41', NULL);

INSERT INTO `orders` VALUES (5, 10, '1000012', '2020-02-12 16:13:23', NULL);

-- ----------------------------

-- Table structure for user

-- ----------------------------

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名称',

`birthday` date NULL DEFAULT NULL COMMENT '生日',

`sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',

`address` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地址',

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 27 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of user

-- ----------------------------

INSERT INTO `user` VALUES (1, '孙司空', '2000-08-10', '1', '娄底');

INSERT INTO `user` VALUES (2, '白骨精', '2016-10-10', '1', '邵阳');

INSERT INTO `user` VALUES (3, '沙河尚', '2004-01-10', '1', '邵阳');

INSERT INTO `user` VALUES (4, '猪八戒', '2002-02-09', '1', '岳阳');

INSERT INTO `user` VALUES (5, '观音', '2015-09-09', '1', '株洲');

INSERT INTO `user` VALUES (6, '如来', '2019-01-01', '1', '永州');

INSERT INTO `user` VALUES (10, '唐僧', '2013-07-10', '2', '宁乡');

SET FOREIGN_KEY_CHECKS = 1;

四张表

user	用户表
orders	订单表
orderdetail	订单明细表
items	商品表

关系分析

表与表之间的关系
user和orders表	一个用户可以创建多个订单，一对多一个订单只由一个用户创建，一对一
orders表和orderdetail表	一个订单可以包含多个订单明细一个订单明细只能属于一个订单
orderdetail和items表	一个订单明细只能对应一个商品一个商品可以出现在多个订单明细中

操作步骤

1.分析需求，找出关联表，写出sql语句。

2.写实体之间的关系。

3.写Mapper接口和Mapper.xml 或写Mapper接口和注解

4.测试

一对一应用

任务：查询订单对应的用户信息。

思路：

先写出sql语句（需要哪些字段就查哪些）

select o.id,o.number,u.username
from orders o inner join user u on o.user_id=u.id

2.实体

Orders	User
private User user;	private Orders orders;

3.OrdersMapper.xml

<select id="findOrderAndUser" resultMap="map_find_order_User">
select o.id,o.number,u.username
from orders o inner join user u on o.user_id=u.id
</select>
<resultMap id="map_find_order_User" type="com.hr.entity.Orders">

<id property="id" column="id"/>
<result property="number" column="number"/>

<association property="user" javaType="com.hr.entity.User">
<id property="id" column="user.id"/>
<result property="username" column="username"/>
</association>

</resultMap>

4.测试，期望和数据查询结果一致。

public class TestOrders {
    SqlSessionFactory factory;//工厂  和数据对话
    SqlSession sqlSession;//会话
    OrdersMapper mapper ;
    @Before
    public void init() throws Exception{
        //1.加载配置文件
        String xml = "mybatis.xml";
        Reader reader = Resources.getResourceAsReader(xml);
        factory = new SqlSessionFactoryBuilder().build(reader);
        //2.创建会话
        sqlSession=factory.openSession();
        mapper =  sqlSession.getMapper(OrdersMapper.class);

    }

List<Orders> orders = mapper.findOrderAndUser();
for (Orders o : orders) {
  System.out.println(o.getId()+","+o.getNumber()+","+o.getUser().getUsername());

}

@After
public void destroy(){
    sqlSession.close();
}

resultMap可以进行高级结果映射

resultType：使用resultType实现较为简单，如果pojo中没有包括查询出来的列名，需要增加列名对应的属性，即可完成映射。

如果没有查询结果的特殊要求建议使用resultType。

resultMap：进行高级结果映射，单独定义resultMap，使用resultMap可以完成将实体到表的映射（尤其属性名和表中列表有不同的时候）

resultMap可以实现延迟加载，resultType无法实现延迟加载。

一对多应用

任务：查询订单下的订单明细。

sql:select o.id,o.number,od.items_id,od.items_num from orders o inner join orderdetail od on o.id = od.orders_id

实体

Orders	OrderDetail
private List<Orderdetail> orderdetails =new ArrayList<>();	private Orders orders;

Mpper接口 List<Orders> findOrdersAndOrderDetail();

Mapper.xml

<select id="findOrdersAndOrderDetail" resultMap="mapFindOrdersAndOrderDetail">
select o.id,o.number,od.items_id,od.items_num
from orders o inner join orderdetail od
on o.id = od.orders_id
</select>
<resultMap id="mapFindOrdersAndOrderDetail" type="com.hr.entity.Orders">

<id property="id" column="id"/>
<result property="number" column="number"/>

<collection property="orderdetails" ofType="com.hr.entity.Orderdetail">
<id property="id" column="orerdetails.id"/>
<result property="itemsId" column="items_id"/>
<result property="itemsNum" column="items_num"/>
</collection>
</resultMap>

测试

@Test
public void findOrdersAndOrderDetail(){
    List<Orders> orders = mapper.findOrdersAndOrderDetail();
    for (Orders o : orders) {
        System.out.println(o.getId()+","+o.getNumber());
        List<Orderdetail> orderdetails = o.getOrderdetails();
        for (Orderdetail orderdetail : orderdetails) {
            System.out.println(orderdetail.getItemsId()+","+orderdetail.getItemsNum());
        }
        System.out.println("------------------------");
    }

}

多对多应用

任务：查询用户订了什么商品。

sql:

select u.id,u.username,i.`name`
from user u inner join orders o on u.id=o.user_id
inner join orderdetail od on o.id = od.orders_id
inner join items i on od.items_id=i.id

实体

User	private List<Orders> myOrders = new ArrayList<>();
Orders	private List<Orderdetail> orderdetails =new ArrayList<>();
OrderDetail	private Items items;

Mapper接口 List<User> findUserOrdersOrderdetailItems();

Mapper.xml

<select id="findUserOrdersOrderdetailItems" resultMap="mapFindUserOrdersOrderdetailItems">
select u.id,u.username,i.`name`
from user u inner join orders o on u.id=o.user_id
inner join orderdetail od on o.id = od.orders_id
inner join items i on od.items_id=i.id
</select>

测试

@Test
public void findUserOrdersOrderdetailItems(){
    List<User> users = mapper.findUserOrdersOrderdetailItems();
    for (User user : users) {
        System.out.println(user.getId()+","+user.getUsername());
        List<Orders> myOrders = user.getMyOrders();
        for (Orders myOrder : myOrders) {
            List<Orderdetail> orderdetails = myOrder.getOrderdetails();
            for (Orderdetail orderdetail : orderdetails) {
                String name = orderdetail.getItems().getName();
                System.out.println(name);
            }
        }
        System.out.println("----------------------");
    }

}

配套视频在B站

MyBatis关联关系

MyBatis关联关系

问题：查询时，实体属性和列名不一致

MyBatis关联关系映射(注解)

一对一应用

一对多应用

多对多应用

配套视频在B站

相关推荐