千峰商城-springboot项目搭建-14-关联查询实现

1.添加实体类Orders.java

package com.qfedu.tkmapperdemo.beans;

import lombok.Data;

@Data
public class Orders {
    private String orderId;
    private Integer userId;
    private String receiverName;
    private String receiverMobile;
    private String receiverAddress;
}

2.添加OrderDAO.java:

package com.qfedu.tkmapperdemo.dao;

import com.qfedu.tkmapperdemo.beans.Orders;
import com.qfedu.tkmapperdemo.general.GeneralDAO;

public interface OrderDAO extends GeneralDAO {
}

3.编写UserDAOTest.java:

package com.qfedu.tkmapperdemo.dao;

import com.qfedu.tkmapperdemo.TkmapperDemoApplication;
import com.qfedu.tkmapperdemo.beans.Orders;
import com.qfedu.tkmapperdemo.beans.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import tk.mybatis.mapper.entity.Example;

import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = TkmapperDemoApplication.class)
public class UserDAOTest {
    @Autowired
    private UserDAO userDAO;
    @Autowired
    private OrderDAO orderDAO;

    @Test
    public void testSelect(){
        //查询用户的同时查询订单
        Example example = new Example(User.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andEqualTo("username","zhangsan");

        //根据用户名查询用户
        //1.先根据用户名查询用户信息
        List users = userDAO.selectByExample(example);
        User user = users.get(0);

        //2.再根据用户id到订单表查询订单信息
        Example example1 = new Example(Orders.class);
        Example.Criteria criteria1 = example1.createCriteria();
        criteria1.andEqualTo("userId",user.getUserId());

        List ordersList = orderDAO.selectByExample(example1);
        //3.将查询到的订单集合设置到user中
        user.setOrdersList(ordersList);


        System.out.println(user);
    }

}

4.在User.java中的Id上加上@Id注解。

package com.qfedu.tkmapperdemo.beans;

import io.swagger.annotations.ApiModel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.Id;
import javax.persistence.Table;
import java.util.Date;
import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(value = "User对象",description = "买家信息")
@Table(name = "users")
public class User {

    @Id
private Integer userId;
    private String username;
    private String password;
    private String nickname;
    private String realname;
    private String userImg;
    private String userMobile;
    private String userEmail;
    private String userSex;
    private Date userBirth;
    private Date userRegtime;
    private Date userModtime;

    private List ordersList;

}

方法二：

<?xml version="1.0" encoding="UTF-8" ?>
mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qfedu.tkmapperdemo.dao.UserDAO">

    <resultMap id="userMap" type="User">
        <id column="user_id" property="userId"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="nickname" property="nickname"/>
        <result column="realname" property="realname"/>
        <result column="user_img" property="userImg"/>
        <result column="user_mobile" property="userMobile"/>
        <result column="user_email" property="userEmail"/>
        <result column="user_sex" property="userSex"/>
        <result column="user_birth" property="userBirth"/>
        <result column="user_regtime" property="userRegtime"/>
        <result column="user_modtime" property="userModtime"/>
        <collection property="ordersList" ofType="Orders">
            <result column="order_id" property="orderId"/>
            <result column="receiver_name" property="receiverName"/>
            <result column="receiver_mobile" property="receiverMobile"/>
            <result column="receiver_address" property="receiverAddress"/>
        collection>
    resultMap>

    <select id="selectByUsername" parameterType="String" resultMap="userMap">
        select
               u.user_id,
               u.username,
               u.password,
               u.nickname,
               u.realname,
               u.user_img,
               u.user_mobile,
               u.user_email,
               u.user_sex,
               u.user_birth,
               u.user_regtime,
               u.user_modtime,

               o.order_id,
               o.receiver_name,
               o.receiver_mobile,
               o.receiver_address
        from users u inner join orders o
        on u.user_id = o.user_id
        where u.username = #{username}
    select>

mapper>

6.修改UserDAOTest.java:

package com.qfedu.tkmapperdemo.dao;

import com.qfedu.tkmapperdemo.TkmapperDemoApplication;
import com.qfedu.tkmapperdemo.beans.Orders;
import com.qfedu.tkmapperdemo.beans.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import tk.mybatis.mapper.entity.Example;

import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = TkmapperDemoApplication.class)
public class UserDAOTest {
    @Autowired
    private UserDAO userDAO;
    @Autowired
    private OrderDAO orderDAO;

    @Test
    public void testSelect(){
//        //查询用户的同时查询订单
//        Example example = new Example(User.class);
//        Example.Criteria criteria = example.createCriteria();
//        criteria.andEqualTo("username","zhangsan");
//
//        //根据用户名查询用户
//        //1.先根据用户名查询用户信息
//        List users = userDAO.selectByExample(example);
//        User user = users.get(0);
//
//        //2.再根据用户id到订单表查询订单信息
//        Example example1 = new Example(Orders.class);
//        Example.Criteria criteria1 = example1.createCriteria();
//        criteria1.andEqualTo("userId",user.getUserId());
//
//        List ordersList = orderDAO.selectByExample(example1);
//        //3.将查询到的订单集合设置到user中
//        user.setOrdersList(ordersList);
//
//
//        System.out.println(user);

        User user = userDAO.selectByUsername("zhangsan");
        System.out.println(user);

    }

}