mybatis的一对多查询

最新推荐文章于 2025-08-14 09:11:10 发布

weixin_34417814

最新推荐文章于 2025-08-14 09:11:10 发布

阅读量72

点赞数

CC 4.0 BY-SA版权

文章标签： java python

原文链接：https://my.oschina.net/lcyanxi/blog/785576

2019独角兽企业重金招聘Python工程师标准>>>

Mapper.xml

<?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 namespace="com.yanxi.dao.OrderCustomDao">
  <resultMap id="userAndItemsResultMap" type="User">
        <!--用户信息-->
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="sex" property="sex"/>
        <result column="birthday" property="birthday"/>
        <result column="address" property="address"/>
        <!--订单信息
        一个用户对应多个订单-->
        <collection property="ordersList" ofType="Orders">
            <id column="id" property="id"/>
            <result column="userId" property="userId"/>
            <result column="number" property="number"/>
            <result column="createTime" property="createTime"/>
            <result column="note" property="note"/>
            <!--订单明细信息
            一个订单对应多个明细-->
            <collection property="orderDetailList" ofType="OrderDetail">
                <id column="id" property="id"/>
                <result column="orderId" property="orderId"/>
                <result column="itemsId" property="itemsId"/>
                <result column="itemsNum" property="itemsNum"/>
                <!--商品信息
                一个明细对应一个商品信息-->
                <association property="items" javaType="Items">
                    <id column="id" property="id"/>
                    <result column="itemsName" property="itemsName"/>
                    <result column="price" property="price"/>
                    <result column="detail" property="detail"/>
                    <result column="picture" property="picture"/>
                    <result column="createTime" property="createTime"/>
                </association>
            </collection>
        </collection>
    </resultMap>
    <select id="findUserAndItemsResultMap" resultMap="userAndItemsResultMap">
        SELECT t1.*,t2.*,t3.itemsNum,t4.*
        FROM user t1,orders t2,orderdetail t3,items t4
        WHERE t1.id=t2.userId AND t2.id=t3.orderId AND t3.itemsId=t4.id
    </select>

    <!--findUserByNameAndAddress多个参数查询方法-->
    <select id="excutsql"  resultType="map">
        ${SQL}
    </select>
</mapper>

OrderCustomDao

  public interface OrderCustomDao {
   List<User> findUserAndItemsResultMap();
    List<Map> excutsql(Map map);
}

Test

 @Test
    public void findUserAndItemsResultMapTest(){
        SqlSession sqlSession=getSqlSessionFactory().openSession();
        OrderCustomDao orderCustomDao=sqlSession.getMapper(OrderCustomDao.class);
        List<User> userList=orderCustomDao.findUserAndItemsResultMap();
        System.out.println(userList);
        for (User user:userList){
            System.out.println(user.getName()+
                    user.getOrdersList().get(0).getId()+
                    user.getOrdersList().get(0).getOrderDetailList().get(0).getItems().getDetail());
        }
        sqlSession.close();

    }

    @Test
    public void findUserByNameAndAddressTest(){
        SqlSession sqlSession=getSqlSessionFactory().openSession();
        OrderCustomDao orderCustomDao =sqlSession.getMapper(OrderCustomDao.class);
        Map map=new HashMap();
        map.put("SQL","select * from user where name=#{name} and address=#{password}");
        map.put("name","kangkang");
        map.put("password","123456");
        //接口调用方式
        List<Map> list=orderCustomDao.excutsql(map);
        System.out.println(list);
    }

转载于:https://my.oschina.net/lcyanxi/blog/785576