MyBatis的关联映射（多对一查询）

一：搭建mybatis框架

二：创建数据库

三：创建查询映射 与 代码补齐

        1：创建Orders实体

        2：创建mapper

        3：创建映射

        4：写测试类

        5：运行结果

二：创建数据库

USE mybatis;
# 创建一个名称为tb_user的表
CREATE TABLE tb_user (
  id int(32) PRIMARY KEY AUTO_INCREMENT,
  username varchar(32),
  address varchar(256)
 );
# 插入3条数据
INSERT INTO tb_user VALUES ('1', '小明', '北京');
INSERT INTO tb_user VALUES ('2', '李华', '上海');
INSERT INTO tb_user VALUES ('3', '李刚', '上海');
# 创建一个名称为tb_orders的表
CREATE TABLE tb_orders (
  id int(32) PRIMARY KEY AUTO_INCREMENT,
  number varchar(32) NOT NULL,
  user_id int(32) NOT NULL,
  FOREIGN KEY(user_id) REFERENCES tb_user(id)
);
# 插入3条数据
INSERT INTO tb_orders VALUES ('1', '1000011', '1');
INSERT INTO tb_orders VALUES ('2', '1000012', '1');
INSERT INTO tb_orders VALUES ('3', '1000013', '2');

三：创建查询映射 与 代码补齐

        1：创建Orders、Users实体

package com.biem.pojo;
 
import lombok.*;
 
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Orders {
    private Integer id;
    private String number;
    private Integer userId;
    private Users users;
}
 
 

 users

package com.biem.pojo;
 
import lombok.*;
 
import java.util.List;
 
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Users {
    private Integer id;
    private String username;
    private String address;
    private List<Orders> ordersList;
}

        2：创建mapper

package com.biem.mapper;
 
import com.biem.pojo.Orders;
 
import java.util.List;
 
public interface OrdersMapper {
    public List<Orders> getOrders1(Integer userId);
 
 
}

package com.biem.mapper;
 
import com.biem.pojo.Users;
 
public interface UsersMapper {
 
    public Users getUsersById(Integer id);
 
 
}

       3：创建映射

<?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为映射的根节点-->
<!-- mapper为映射的根节点，namespace指定Dao接口的完整类名
mybatis会依据这个接口动态创建一个实现类去实现这个接口，
而这个实现类是一个Mapper对象-->
<mapper namespace="com.biem.mapper.OrdersMapper">
 
 
        <select id="getOrders1" resultMap="ordersResult1">
        select * from tb_orders where user_id = #{userId}
    </select>
    <resultMap id="ordersResult1" type="orders">
        <result column="id" property="id"/>
        <result column="number" property="number"/>
        <result column="user_id" property="userId"/>        <!--复杂的属性，我们需要单独处理
            对象：使用association
            集合：collection-->
        <association column="id" property="users" javaType="users" select="com.biem.mapper.UsersMapper.getUsersById"/>
    </resultMap>
 
</mapper>

<?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为映射的根节点-->
<!-- mapper为映射的根节点，namespace指定Dao接口的完整类名
mybatis会依据这个接口动态创建一个实现类去实现这个接口，
而这个实现类是一个Mapper对象-->
<mapper namespace="com.biem.mapper.UsersMapper">
 
 
        <select id="getUsersById" resultType="users">
        select * from tb_user where `id` = #{id}
    </select>
 
</mapper>

4：写测试类

package com.biem.test;
 
import com.biem.mapper.OrdersMapper;
import com.biem.pojo.Orders;
import com.biem.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
 
import java.util.List;
 
 
public class OrdersTest {
 
    @Test
    public void testGetOrders1(){
        SqlSession session = MybatisUtil.openSession();
        OrdersMapper mapper = session.getMapper(OrdersMapper.class);
        List<Orders> ordersList = mapper.getOrders1(1);
        System.out.println("ordersList = " + ordersList);
    }
}
 

        5：运行结果、

 嵌套结果方式

OrderMapper.java 添加

public List<Orders> getOrders1ByNestedResult(Integer userId);

OrderMapper.xml添加

<!-- public List<Orders> getOrders1ByNestedResult(Integer userId); -->
    <select id="getOrders1ByNestedResult" resultMap="ordersResult2">
        <!-- id为接口中实现的方法名，resultMap为返回值类型，由于返回值包含一个Teacher对象，所以不能用resultType-->
        select o.id oid, o.number number, o.user_id uid , u.username username, u.address address
        from tb_orders o,tb_user u
        where o.user_id=u.id and o.user_id = #{userId}
    </select>
    <resultMap id="ordersResult2" type="orders">
        <result column="oid" property="id"/>
        <result column="number" property="number"/>
        <result column="uid" property="userId"/>
        <association property="users" javaType="users">
            <!--property是实体类中的字段属性，复杂的属性，使用了javaType描述的是属性的类型，它是一个Teacher对象-->
            <result property="id" column="uid"/>
            <result property="username" column="username"/>
            <result property="address" column="address"/>
        </association>
    </resultMap>

OrderTest.java 添加

    @Test
    public void testGetOrders2(){
        SqlSession session = MyBatisUtil.openSession();
        OrdersMapper mapper = session.getMapper(OrdersMapper.class);
        List<Orders> ordersList = mapper.getOrders1ByNestedResult(1);
        System.out.println("ordersList = " + ordersList);
    }

测试结果