mybatis高级结果映射

一、数据模型分析

1、分析

(1)明确每张表存储的信息

(2)明确每张表中关键字段(主键、外键、非空)

(3)明确数据库中表与表之间的外键关系

(4)明确业务中表与表的关系(建立在具体的业务)

2、分析图解

(1)图解: 

 

mybatis高级结果映射

二、一对一映射

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)修改扩展类 
mybatis高级结果映射

(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、修改扩展类

mybatis高级结果映射

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 
mybatis高级结果映射 
(2)在Orders类中添加List<“Orderdetail”> detailList 

mybatis高级结果映射

 

(3)在Orderdetail中添加Items items; 
mybatis高级结果映射

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();
    }

 


 

上一篇:装饰器模式-实现电商的优惠活动价格计算


下一篇:Java Bean Copy 问题发现