Mybatis(9 多表查询)

@Data
public class UserBook {
    private Long userId;
    private String userName;
    private String bookName;
    private Date updateTime;
}

   //根据用户id获取该用户所借的书及更新时间
    List queryUserBookByUserId(Long userId);

 
        select a.user_id,a.username,b.book_name,b.update_time
        from user_info a join book b on a.user_id=b.user_id
        where a.user_id=#{id}
     
public static void main( String[] args ) throws Exception{
       SqlSession sqlSession=getSqlSession();
       //根据用户id获取该用户所借的书及更新时间
        UserInfoMapper mapper=sqlSession.getMapper(UserInfoMapper.class);
        List userBooks=mapper.queryUserBookByUserId(1L);
        userBooks.forEach(e-> System.out.println(e));
        sqlSession.close();
    }
 
一对多 
如果用上述方法，一个用户借了多本书，结果则为：
 
 
 用户id和用户名字段重复，如想不重复显示需进行改造
 
@Data
public class UserBook {
    private String bookName;
    private Date updateTime;
}
 
@Data
public class UserBooks {
    private Long userId;
    private String userName;
    private List userBooks;
}
 
//根据用户id获取该用户所借的书及更新时间
    UserBooks queryUserBooksByUserId(Long userId);
 
 
        select a.user_id,a.username,b.book_name,b.update_time
        from user_info a join book b on a.user_id=b.user_id
        where a.user_id=#{id}
    

配置对应resultMap

结果

public static void main( String[] args ) throws Exception{
       SqlSession sqlSession=getSqlSession();
       //根据用户id获取该用户所借的书及更新时间
        UserInfoMapper mapper=sqlSession.getMapper(UserInfoMapper.class);
        UserBooks userBooks=mapper.queryUserBooksByUserId(1L);
        System.out.println(userBooks);
        sqlSession.close();
    }

UserBooks(userId=1, userName=zhangsan, bookList=[UserBook(bookName=白夜行, updateTime=Sun Jan 23 16:22:14 CST 2022), UserBook(bookName=新参者, updateTime=Sun Jan 23 16:52:33 CST 2022)])

基本和一对多一样

  //获取所有用户所借的书及更新时间
    List queryUserBooks();

 
        select a.user_id,a.username,b.book_name,b.update_time
        from user_info a join book b on a.user_id=b.user_id
    

public static void main( String[] args ) throws Exception{
       SqlSession sqlSession=getSqlSession();
       //根据用户id获取该用户所借的书及更新时间
        UserInfoMapper mapper=sqlSession.getMapper(UserInfoMapper.class);
        List userBooksList=mapper.queryUserBooks();
        userBooksList.forEach(e-> System.out.println(e));
        sqlSession.close();
    }

但是在实际应用中，多表查询通常使用java通过多个单表查询实现

@Data
public class UserBooks {
    private Long userId;
    private String userName;
    private List bookList;
}

 public static void main( String[] args ) throws Exception{
       SqlSession sqlSession=getSqlSession();
        UserInfoMapper userInfoMapper=sqlSession.getMapper(UserInfoMapper.class);
        //获取到所有用户
        List userInfos=userInfoMapper.selectAllUsers();
        //获取用户id到用户名的映射
        Map userInfoMap=userInfos.stream().collect(Collectors.toMap(UserInfo::getUserId,UserInfo::getUserName));
        BookDao bookMapper=sqlSession.getMapper(BookDao.class);
        //获取到所有用户id
        List userIdList=userInfos.stream().map(UserInfo::getUserId).collect(Collectors.toList());
        //根据所有用户id获取到所有书
        List books=bookMapper.queryBookByUserIds(userIdList);
        //根据用户id对书分组
        Map> bookMap=books.stream().collect(Collectors.groupingBy(Book::getUserId));

        List userBooksList=new ArrayList<>();
        userIdList.forEach(e->{
            UserBooks userBooks=new UserBooks();
            userBooks.setUserId(e);
            userBooks.setUserName(userInfoMap.get(e));
            userBooks.setBookList(bookMap.get(e));
            userBooksList.add(userBooks);
        });
        userBooksList.forEach(e-> System.out.println(e));
    }