mybatis高階結果對映

shaonianbz發表於2018-03-24

一、資料模型分析

1、分析

(1)明確每張表儲存的資訊

(2)明確每張表中關鍵欄位(主鍵、外來鍵、非空)

(3)明確資料庫中表與表之間的外來鍵關係

(4)明確業務中表與表的關係(建立在具體的業務)

2、分析圖解

(1)圖解:
這裡寫圖片描述

二、一對一對映

1、需求分析

(1)需求:查詢訂單資訊,關聯查詢使用者資訊

(2)sql:主資訊:orders;從資訊:user

SELECT
  orders.id,
  orders.user_id,
  orders.number,
  user.username,
  user.sex
FROM orders,
  USER
WHERE orders.user_id = user.id

2、resultType

(1)建立擴充套件類

public class OrderExt extends Orders{
    private String username;
    private String sex;
}

(2)對映檔案

<!-- 一對一對映之resultType -->
    <select id="findOrdersAndUser" resultType="com.san.model.OrderExt">
        SELECT
          orders.id,
          orders.user_id,
          orders.number,
          user.username,
          user.sex
        FROM orders,
          USER
        WHERE orders.user_id = user.id
    </select>

(3)mapper介面

//一對一之ResultType
public List<OrderExt> findOrdersAndUser();

(4)測試

@Test
    //一對一resultType
    public void Test01() throws IOException{
        String resource="SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //建立mapper物件
        OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
        //執行查詢語句
        List<OrderExt> orders=ordersMapper.findOrdersAndUser();
        System.out.println(orders);
        //關閉資源
        sqlSession.close();
    }

(5)小結
使用resultType來進行一對一結果對映,查詢出的列的個數和對映的屬性的個數要一致。而且對映的屬性要存在與一個大的物件中,它是一種平鋪式的對映,即資料庫查詢出多少條記錄,則對映成多少個物件。

3、resultMap

(1)修改擴充套件類
這裡寫圖片描述

(2)對映檔案

<!-- OrdersAndUserResMap -->
    <resultMap type="com.san.model.OrderExt" id="OrdersAndUserResMap">
        <!-- 訂單資訊 -->
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="number" property="number"/>

        <!-- 使用者資訊(一對一) -->
        <!-- association:一對一關聯對映 -->
        <!-- property:關聯資訊查詢的結果將要對映的擴充套件類中的物件屬性名稱 -->
        <!-- id標籤:建議在關聯查詢時必須寫上,不寫不會報錯,但是會影響效能 -->
        <association property="user" javaType="com.san.model.User">
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
            <result column="sex" property="sex"/>
        </association>
    </resultMap>
    <!-- 一對一對映之resultMap -->
    <select id="findOrdersAndUserResMap" resultMap="OrdersAndUserResMap">
        SELECT
          orders.id,
          orders.user_id,
          orders.number,
          user.username,
          user.sex
        FROM orders,
          USER
        WHERE orders.user_id = user.id
    </select>

(3)mapper介面

    //一對一之ResultMap
    public List<OrderExt> findOrdersAndUserResMap();

(4)測試

@Test
    //一對一resultMap
    public void Test02() throws IOException{
        String resource="SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //建立mapper物件
        OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
        //執行查詢語句
        List<OrderExt> orders=ordersMapper.findOrdersAndUserResMap();
        System.out.println(orders);
        //關閉資源
        sqlSession.close();
    }

(5)小結
在一對一結果對映時,使用resultType更加簡單方便,如果有特殊要求(物件巢狀物件)時,需要使用resultMap進行對映,比如:查詢訂單列表,然後在點選列表中的檢視訂單明細按鈕,這個時候就需要使用resultMap進行結果對映。而resultType更適應於查詢明細資訊,比如,查詢訂單明細列表。

三、一對多對映

1、需求分析

(1)需求:查詢訂單資訊,關聯查詢訂單明細資訊及使用者資訊

(2)sql:主資訊:orders;從資訊:orderdetail、user

SELECT
  orders.id,
  orders.user_id,
  orders.number,
  user.username,
  user.sex,
  orderdetail.id detailId,
  orderdetail.items_id,
  orderdetail.items_num
FROM orders,
  USER,
  orderdetail
WHERE orders.user_id = user.id
    AND orders.id = orderdetail.orders_id

2、修改擴充套件類

這裡寫圖片描述

3、對映檔案

<!-- OrdersAndDetailRstMap -->
    <!-- extends:可以繼承一個已有的resultMap,指定resultMap的唯一標識(返回型別要一致,才能繼承) -->
    <resultMap type="com.san.model.OrderExt" id="OrdersAndDetailRstMap" extends="OrdersAndUserResMap">
        <!-- collection:對映一對多關係 -->
        <!-- 多對一時:使用ofType,不使用javaType -->
        <collection property="detailList" ofType="com.san.model.Orderdetail">
            <id column="detailId" property="id"/>
            <result column="items_id" property="itemsId"/>
            <result column="items_num" property="itemsNum"/>
        </collection>
    </resultMap>
    <!-- 一對多對映 -->
    <select id="findOrdersAndDetailRstMap" resultMap="OrdersAndDetailRstMap">
        SELECT
          orders.id,
          orders.user_id,
          orders.number,
          user.username,
          user.sex,
          orderdetail.id detailId,
          orderdetail.items_id,
          orderdetail.items_num
        FROM orders,
          USER,
          orderdetail
        WHERE orders.user_id = user.id
            AND orders.id = orderdetail.orders_id
    </select>

4、mapper介面

    //一對多之ResultMap
    public List<OrderExt> findOrdersAndDetailRstMap();

5、測試

@Test
    //一對多resultMap
    public void Test03() throws IOException{
        String resource="SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //建立mapper物件
        OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
        //執行查詢語句
        List<OrderExt> orders=ordersMapper.findOrdersAndDetailRstMap();
        System.out.println(orders);
        //關閉資源
        sqlSession.close();
    }

四、多對多對映

1、需求分析

(1)需求:查詢使用者資訊,關聯查詢該使用者購買的商品資訊

(2)sql:主資訊:user;從資訊:items、orders、orderdetail

SELECT
  orders.id,
  orders.user_id,
  orders.number,
  user.username,
  user.sex,
  orderdetail.id           detailId,
  orderdetail.items_id,
  orderdetail.items_num,
  items.name,
  items.price
FROM orders,
  USER,
  orderdetail,
  items
WHERE orders.user_id = user.id
    AND orders.id = orderdetail.orders_id
    AND orderdetail.items_id = items.id

2、修改擴充套件類

注意:為了顯示方便,泛型加了“”
(1)在User類中新增List<“Orders”> orders
這裡寫圖片描述
(2)在Orders類中新增List<“Orderdetail”> detailList
這裡寫圖片描述
(3)在Orderdetail中新增Items items;
這裡寫圖片描述

3、對映檔案

<!-- UserAndItemsRstMap -->
    <resultMap type="com.san.model.User" id="UserAndItemsRstMap">
        <!-- 使用者資訊 -->
        <id column="user_id" property="id"/>
        <result column="username" property="username"/>
        <result  column="sex" property="sex"/>
        <!-- 訂單資訊(一對多) -->
        <collection property="orders" ofType="com.san.model.Orders">
            <id column="id" property="id"/>
            <result column="user_id" property="userId"/>
            <result column="number" property="number"/>
            <!-- 訂單明細資訊(一對多) -->
            <collection property="detailList" ofType="com.san.model.Orderdetail">
                <id column="detailId" property="id"/>
                <result column="items_id" property="itemsId"/>
                <result column="items_num" property="itemsNum"/>
                <!-- 商品資訊(一對一) -->
                <association property="items" javaType="com.san.model.Items">
                    <id column="items_id" property="id"/>
                    <result column="name" property="name"/>
                    <result column="price" property="price"/>
                </association>
            </collection>
        </collection>

    </resultMap>
    <!-- 多對多對映 -->
    <select id="findUserAndItemsRstMap" resultMap="UserAndItemsRstMap">
        SELECT
          orders.id,
          orders.user_id,
          orders.number,
          user.username,
          user.sex,
          orderdetail.id           detailId,
          orderdetail.items_id,
          orderdetail.items_num,
          items.name,
          items.price
        FROM orders,
          USER,
          orderdetail,
          items
        WHERE orders.user_id = user.id
            AND orders.id = orderdetail.orders_id
            AND orderdetail.items_id = items.id
    </select>

4、mapper介面

    //多對多之ResultMap
    public List<User> findUserAndItemsRstMap();

5、測試

@Test
    //多對多resultMap
    public void Test04() throws IOException{
        String resource="SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //建立mapper物件
        OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
        //執行查詢語句
        List<User> userList=ordersMapper.findUserAndItemsRstMap();
        System.out.println(userList);
        //關閉資源
        sqlSession.close();
    }

相關文章