Mybatis一对一与一对多查询

关联查询

  • 多表关系:

一对一、一对多、多对多。
在这里插入图片描述

关联查询:一对一(1)

resultType 指定一个自定义javaBean类(通常要再编写一个新的javaBean类)
resultMap 指定映射关系(不用编写新的javaBean类 推荐使用)

  • 测试类Test01.java
@Test
public void test01(){
        SqlSession session = MySessionUtils.getSession();
        OrderDao orderDao = session.getMapper(OrderDao.class);
        List<OrderUer> list = orderDao.findAllOrderUser();
        System.out.println(list);
        session.commit();
        session.close();
  • 创建新类OrderUser.java
public class OrderUer {
    //订单的属性
    private int oid;
    private Integer user_id;
    private String number;
    private Date createtime;
    private String note;
    //用户的属性
    private int uid;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    }
  • 在OderDao.java定义接口方法
List<OrderUer> findAllOrderUser();
  • OrderDao.xml里resultType配置映射
<select id="findAllOrderUser" resultType="com.syy.bean.OrderUer">
    select o.id as oid,
    o.user_id ,
    o.number,
    o.createtime,
    o.note,
    u.id as uid,
    u.username,
    u.birthday,
    u.sex,
    u.address
    from  `order` o left join `user` u
    on o.user_id=u.id;
    </select>
  • 重新定义javaBean类
public class OrderUser {
    // 订单id
    private int id;
    // 用户id
    private Integer userId;
    // 订单号
    private String number;
    // 订单创建时间
    private Date createtime;
    // 备注
    private String note;

    private int uid;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    }

关联查询:一对一(2)

association 标签 用于 成员变量的类型为自定义实体类型
添加属性:autoMapping=“true”,自动映射

  • 测试类
@Test
    public void test01(){
        SqlSession session = MySessionUtils.getSession();
        OrderDao orderDao =  session.getMapper(OrderDao.class);
        List<Order> list=orderDao.findAllOrder2();
        System.out.println(list);
        //关闭资源
        session.commit();
        session.close();

    }
  • 修改Order类Oder.java
public class Order {
    // 订单id
    private int id;
    // 用户id
    private Integer userId;
    // 订单号
    private String number;
    // 订单创建时间
    private Date createtime;
    // 备注
    private String note;
    //增加User成员变量来接收一条记录的用户的查询字段。
    private User user;
  • OrderDao.java定义接口
List<Order> findAllOrder2();

resultMap与association 配置映射

映射配置的重点是 Order类中的 User user 成员变量,它的类型User是自定义实现类的类型

autoMapping:表示如果字段名和属性名一致,则自动映射

<!--2:查询每个订单属于哪个用户  一对一  方式2-->
    <!--
      如果是关联查询,则必须添加属性:autoMapping="true",自动映射
    -->
    <resultMap id="findAllOrder2Map" type="com.wzx.bean.Order" autoMapping="true">
        <!--先指定Order类属性和字段之间的映射关系-->
        <id column="id" property="id"></id>
        <result column="user_id" property="userId"></result>
        <association property="user" javaType="com.wzx.bean.User" autoMapping="true" >
            <id column="uid" property="id"></id>
        </association>
    </resultMap>
    <select id="findAllOrder2" resultMap="findAllOrder2Map">
   select o.id as id,
	    o.user_id as userId,
	    o.number,
	    o.createtime,
	    o.note,
	    u.id as uid,
	    u.username,
	    u.birthday,
	    u.sex,
	    u.address
    from  `order` o left join `user` u
    on o.user_id=u.id;
    </select>

关联查询:一对多

查询结果使用collection标签 映射List<元素>

  • 编写测试类
 @Test
    public void test01(){
        //ResultMap=>原来的User类
        SqlSession session = MySessionUtils.getSession();
        UserDao userDao =  session.getMapper(UserDao.class);
        List<User> list=userDao.findAllUsers();
        //获取用户,然后调用用户的getOrders()
        System.out.println(list);
        session.commit();
        session.close();
    }
  • 改造User类
public class User {

    private int id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    //变量类型是集合List
    private List<Order> orders;
    }
  • 定义接口方法
List<User> findAllUsers();
  • ResultMap与collection配置映射
 <!-- autoMapping="true" 如果字段名与变量名一样,则不需要映射
    如果一个类成员变量出现集合类型,List,则需要使用collection标签了映射,字段与变量的关系
    ofType指定的集合List<元素> 元素的类型,不要写成javaType
    -->
    <resultMap id="findAllUsersMap" type="com.wzx.bean.User" autoMapping="true">
            <id property="id" column="uid"></id>
            <collection property="orders" ofType="com.wzx.bean.Order" autoMapping="true">
                <id property="id" column="oid"></id>
            </collection>
    </resultMap>
    <select id="findAllUsers" resultMap="findAllUsersMap">
	     select u.id as uid,
	        u.username,
	        u.birthday,
	        u.sex,
	        u.address,
	        o.id as oid,
	        o.user_id as userId,
	        o.number,
	        o.createtime,
	        o.note
	    from `user` u left join `order` o
	    on u.id=o.user_id;
    </select>
©️2020 CSDN 皮肤主题: 游动-白 设计师:上身试试 返回首页