关联查询（高级映射）

在MyBatis中映射是指：将数据库中表的字段和javaBean的属性对应在一起。
默认的MyBatis在处理表字段和对象属性之间的映射关系时，必须保持名称一致。如果两者名称不一致，将会导致得不到查询结果。

resultMap

当数据库的表字段和对象属性名称不一致时，可以使用resultMap指定数据库表字段和对象属性值的映射。假设我们把user表的每个字段后添加“_1”，则此时数据库的表字段和对象属性名称就不一致。那么此时我们就要在UserDao.xml添加resultMap元素：

    <resultMap id="NewUser" type="com.luhui.bean.User">
        <id column="id_1" property="id" />
        <result column="username_1" property="username" />
        <result column="password_1" property="password" />
        <result column="age_1" property="age" />
        <result column="sex_1" property="sex" />
    </resultMap>

这个时候，返回值的类型就要填resultMap=“NewUser”

<select id="selectAllUser" resultMap="NewUser">
   select * from user
</select>

当数据库的表字段和对象属性名称不一致时，使用MyBatis进行查询操作时可以采用的两种办法：
1）通过在sql语句中使用别名，让别名和对象属性名称一致；

<select id="selectAllUser" resultType="user">
    select id_1 id, username_1 username,
    password_1 password, age_1 age, sex_1 sex  from user
</select>

2）通过resultMap来映射表字段和对象属性名称。

Mybatis多表查询之一对一

需求：查询订单，并且查出订单的用户信息

JavaBean
public class Orders {
    private int id;
    private int userId;
    private String number;
    private String createTime;
    private String note;
}
public class User {
    private int id;
    private String username;
    private String birthday;
    private String sex;
    private String address;
}

实现方式一：

PO类：
public class OrdersCustom extends Orders{
    private String username;
    private String sex;
    private String address;
}

OrderDao.xml

<mapper namespace="com.luhui.mapper.OrderDao">
    <select id="findOrdersUser" resultType="com.luhui.bean.OrdersCustom">
        SELECT t1.*,
        t2.username,
        t2.sex,
        t2.address
        FROM
        orders t1,
        t_user t2
        WHERE t1.user_id=t2.id
    </select>
</mapper>

OrderDao.Java

public List<OrdersCustom> findOrdersUser();

测试代码：

@Test
public void test13() {
SqlSession sqlSession = SqlSessionFactoryUtils.openSession();
    OrderDao orderDao = sqlSession.getMapper(OrderDao.class);
    List<OrdersCustom> ordersUsers = orderDao.findOrdersUser();
    System.out.println(ordersUsers);
    sqlSession.commit();
    sqlSession.close();
}

实现方式二：
在Order.Java添加属性：

private User user;			// 用户信息

OrderDao.xml

<resultMap id="findOrdersUserMap" type="com.luhui.bean.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" />
    <!-- 配置映射的关联用户信息 -->
    <!--association:用于映射关联查询单个对象的信息
        property:要将关联查询的用户信息映射到Orders中那个属性
      -->
    <association property="user" javaType="com.luhui.bean.User">
        <!-- id:关联查询用户的唯一标识
            column:指定唯一标识用户信息的列
            property:映射到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="findOrdersUserByMap" resultMap="findOrdersUserMap">
    SELECT t1.*,
      t2.username,
      t2.sex,
      t2.address
    FROM
      orders t1,
      t_user t2
    WHERE t1.user_id=t2.id
</select>

OrderDao.Java

public List<Orders> findOrdersUserByMap();

测试代码：

@Test
public void test14(){
 SqlSession sqlSession = SqlSessionFactoryUtils.openSession();
    OrderDao orderDao = sqlSession.getMapper(OrderDao.class);
    List<Orders> ordersUsers = orderDao.findOrdersUserByMap();
    System.out.println(ordersUsers);
    sqlSession.commit();
    sqlSession.close();
}

Mybatis多表查询之一对多

需求：查询订单(关联用户)及订单明细

JavaBean
public class Orderdetail {
    private int id;
    private int ordersId;
    private int itemsId;
    private int itemsNum;
}

在订单类Orders中添加订单明细OrderDetail。由于一个订单类对应多个订单详细，所以添加的具体属性为List：

private List<Orderdetail> orderdetail;

OrderDao.xml

<resultMap id="ordersAndOrderDetailResultMap"
 type="com.luhui.bean.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" />

    <association property="user" javaType="com.luhui.bean.User">
        <id column="user_id" property="id"/>
        <result column="username" property="username"/>
        <result column="sex" property="sex"/>
        <result column="address" property="address"/>
    </association>

    <!-- 关联订单明细信息
       一个订单关联查询出了多条订单明细,要使用collection映射
        collection:对关联查询到的多条记录映射到集合中
        property:将关联查询到的多条记录映射到orders类的那个属性
        ofType:指定映射的集合属性中pojo的类型
    -->
    <collection property="orderdetail" ofType="com.luhui.bean.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>
<select id="selectOrdersAndOrderDetail" resultMap="ordersAndOrderDetailResultMap">
    		SELECT
			t1.*,
			t2.username,
			t2.sex,
			t2.address,
			t3.id orderdetail_id,
			t3.items_id,
			t3.items_num,
			t3.orders_id
	FROM
			orders t1,
			t_user t2,
			orderdetail t3
	WHERE t1.user_id = t2.id AND t3.orders_id=t1.id
</select>

OrderDao.Java

public List<Orders> selectOrdersAndOrderDetail();

测试代码：

@Test
public void test15(){
    SqlSession sqlSession = SqlSessionFactoryUtils.openSqlSession();
    OrderDao orderDao = sqlSession.getMapper(OrderDao.class);
    List<Orders> ordersUsers = orderDao.selectOrdersAndOrderDetail();
    System.out.println(ordersUsers);
    sqlSession.commit();
    sqlSession.close();
}

Mybatis多表查询之多对多

需求：查询用户、用户的订单信息、用户订单的详细信息、商品信息。
思路：

1. 将用户信息映射到user中；
2. 在user类中添加订单列表属性List orderslist，将用户创建的订单映射到orderslist；
3. 在Orders中添加订单明细列表属性Listorderdetials，将订单的明细映射到orderdetials；
4. 在OrderDetail中添加Items属性，将订单明细所对应的商品映射到Item。

public class User {
private int id;
private String username;
private String birthday;
private String sex;
private String address;
private List orders;
}

public class Orders {
    private int id;
    private int userId;
    private String number;
    private String createTime;
    private String note;
    private List<Orderdetail> orderdetail;
}
public class Orderdetail {
    private int id;
    private int ordersId;
    private int itemsId;
    private int itemsNum;
    private Items items;
}
public class Items {
    private int id;
    private String itemsname;
    private float price;
    private String detail;
    private String pic;
    private String createTime;
}

OrderDao.xml

 <resultMap id="userResultMap" type="com.luhui.bean.User">
        <!-- 用户的基本信息 -->
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="birthday" property="birthday"/>
        <result column="sex" property="sex"/>
        <result column="address" property="address"/>
        <!-- 关联订单：一对多  -->
        <collection property="orders" ofType="com.luhui.bean.Orders" >
            <id column="orders_id" property="id" />
            <result column="orders_createTime" property="createTime"/>
            <result column="number" property="number"/>
            <!--关联订单详情：一对多  -->
            <collection property="orderdetail"  ofType="com.luhui.bean.Orderdetail" >
                <id column="orderdetail_id" property="id"/>
                <result column="items_num" property="itemsNum"/>
                <!--关联商品信息：一对一   -->
                <association property="items"  javaType="com.luhui.bean.Items" >
                    <id column="items_id" property="id"/>
                    <result column="itemsname" property="itemsname"/>
                    <result column="price" property="price"/>
                    <result column="items_createTime" property="createTime"/>
                    <result column="detail" property="detail"/>
                </association>
            </collection>
        </collection>
    </resultMap>

    <select id="userItemsResult" resultMap="userResultMap">
        SELECT
           t1.*,
           t2.id oid,
           t2.createtime as orders_createTime,
           t2.number,
           t3.id detail_id,
           t3.items_num,
           t4.id iid,
           t4.itemsname,
           t4.price,
           t4.createtime as items_createTime,
           t4.detail
         from t_user t1
        left join orders t2 on t1.id = t2.user_id
        left join orderdetail t3 on t2.id=t3.orders_id
        left join items t4 on t3.items_id=t4.id
    </select>

测试代码：

@Test
public void test16(){
    SqlSession sqlSession = SqlSessionFactoryUtils.openSqlSession();
    OrderDao orderDao = sqlSession.getMapper(OrderDao.class);
    List<User> users = orderDao.userItemsResult();
    for (User user : users){
        System.out.println(user);
    }
    System.out.println(users);
    sqlSession.commit();
    sqlSession.close();
}