mybatis中的高级映射（一）

首先创建需要的关系表

-- 用户表
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 user(username,birthday,sex,address) values('Janes','1998-06-05','女','美国某个州'),
('James','1888-08-08','男','美国堕落里拉州'),('eluoyi','1990-02-10','女','英国某个市某个县'),('ailisi','1889-09-15','女','加拿大加布加多州');

-- 订单表
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;

insert into orders(number,createtime,user_id) values('X123456789','2020-04-01 12:10:20',27),('X123456790','2020-04-01 12:13:20',27),('X123456793','2020-04-03 11:13:20',29),('X123456820','2020-04-02 12:20:20',28),('X123456850','2020-04-05 14:13:20',30);

--商品表
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;

insert into items(name,price,detail,createtime) values('卫龙辣条','2.5','辣出感觉，辣出高度','2020-01-10'),('雪花酥','20','好吃不油腻，错过后悔一辈子','2020-01-10'),('巧乐兹','3','真巧遇见你，快乐兹出油','2020-01-10'),('藤椒火腿肠','9','火腿肠也可以有味道，因为与众不同','2020-01-10'),('呀!土豆','2.5','呀!有本事单挑，吃我算什么','2020-01-10');

--订单详情
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;

 insert into orderdetail(orders_id,items_id) values(6,6),(10,5),(10,6),(8,7),(9,7),(7,8);

1.别名映射
需求：查询指定编号的用户信息

<!--
        resultMap：指定映射关系
            id：映射关系的唯一标识
            type：指定映射关系所在的实体类
    -->
<resultMap id="queryUserByIdMap" type="user">
  <id property="id" column="uid"/>
  <result property="userName" column="uname"/>
</resultMap>

<!--带有别名的字段-->
<select id="queryUserById" parameterType="int" resultMap="queryUserByIdMap">
  select id uid,username uname,sex,birthday,address from user where id=#{id}
</select>

测试代码

@Test
public void queryUserByIdTest() throws IOException {
  //Resources.getResourceAsStream默认从双src路径下查找
  InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
  //获取SqlSessionFactory对象
  SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
  //创建SQLSession对象
  SqlSession sqlSession = factory.openSession();
  //通过SQLSession映射一个接口的实例对象
  IUserDao userDao = sqlSession.getMapper(IUserDao.class);
  //调用要执行的方法
  User user = userDao.queryUserById(28);
  System.out.println(user);
  sqlSession.close();
}

注意：指定映射关系时，如果只是指定了特殊的字段映射，其他的没有指定，Mybatis也会自动进行映射

2.一对一映射
需求：查询订单信息关联查询用户信息

<resultMap id="queryOrdersByIdMap" type="orders">
  <id column="orderid" 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：指定映射到类中的那个属性上（类的变量名）
                javaType：指定该属性的数据类型
        -->
  <association property="user" javaType="user">
    <id column="uid" property="id"/>
    <result column="username" property="userName"/>
    <result column="birthday" property="birthday"/>
    <result column="sex" property="sex"/>
    <result column="address" property="address"/>
  </association>
</resultMap>

<select id="queryOrdersById" parameterType="int" resultMap="queryOrdersByIdMap">
  select orders.id orderid,user_id,number,createtime,note,user.id uid,username,birthday,sex,address from orders
  join user on orders.user_id=user.id where orders.id=#{id}
</select>

测试代码

@Test
public void queryOrdersById() throws IOException {
  //Resources.getResourceAsStream默认从双src路径下查找
  InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
  //获取SqlSessionFactory对象
  SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
  //创建SQLSession对象
  SqlSession sqlSession = factory.openSession();
  //通过SQLSession映射一个接口的实例对象
  IOrdersDao ordersDao = sqlSession.getMapper(IOrdersDao.class);
  Orders orders = ordersDao.queryOrdersById(11);
  System.out.println(orders);
  sqlSession.close();
}

注意：映射过程中，如果未指定要映射的属性，Mybatis不会自动进行映射