接上节案例，以下内连接SQL语句返回了学生视图。

select sid, sname,gender,birthday,school,major,s.cid ,cname from students s inner join class_room c where s.cid=c.cid

Mybatis如何实现查询如下视图呢？

实现步骤

1.定义学生视图类 在view包下创建StudentsView.java

package com.oracle.view;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.Column;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import java.io.Serializable;


@Data
@AllArgsConstructor
@NoArgsConstructor
public class StudentsView implements Serializable,Cloneable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY) //使用数据库默认的主键生成策略
    private int sid; //主键
    @Column(length = 20)  //规定学生的姓名不超过20个字符
    private String sname; //姓名，默认是占用255个字符。
    @Column(length = 2)
    private String gender; //性别
    @Column(length = 32)
    private String birthday; //出生日期
    private String school; //所在学校
    private String major; //所学的专业

    private String cid; //班级编号
    private String cname; //班级的名称
}

2.在StudentsMapper中定义返回视图的方法。

package com.oracle.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.oracle.entity.Students;
import com.oracle.view.StudentsView;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface StudentsMapper extends BaseMapper<Students> {


    @Select("select sid, sname,gender,birthday,school,major,s.cid ,cname from students s inner join class_room c where s.cid=c.cid")
    @Results(
            {
                    @Result(property = "sid", column = "sid"),
                    @Result(property = "sname", column = "sname"),
                    @Result(property = "gender", column = "gender"),
                    @Result(property = "birthday", column = "birthday"),
                    @Result(property = "school", column = "school"),
                    @Result(property = "major", column = "major"),
                    @Result(property = "cid", column = "cid"),
                    @Result(property = "cname", column = "cname")
            }
    )
    List<StudentsView> queryStudentsView();
}

3.在StudentsService和StudentsServiceImpl中实现该方法。

package com.oracle.service;

import com.oracle.entity.Students;
import com.oracle.mapper.StudentsMapper;
import com.oracle.repository.StudentsRepository;
import com.oracle.view.StudentsView;
import java.util.List;

public interface StudentsService extends BaseService<Students,Integer, StudentsRepository, StudentsMapper> {

    List<StudentsView> queryStudentsView();
}

package com.oracle.service.impl;

import com.oracle.entity.Students;
import com.oracle.mapper.StudentsMapper;
import com.oracle.repository.StudentsRepository;
import com.oracle.service.StudentsService;
import com.oracle.view.StudentsView;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service
public class StudentsServiceImpl extends BaseServiceImpl<Students,Integer, StudentsRepository, StudentsMapper> implements StudentsService {

    @Resource
    private StudentsMapper studentsMapper;

    @Override
    public List<StudentsView> queryStudentsView() {
        return studentsMapper.queryStudentsView();
    }
}

4.编写测试类

测试返回学生视图

package com.oracle.service;

import com.oracle.entity.ClassRoom;
import com.oracle.entity.Students;
import com.oracle.entity.StudentsTeachers;
import com.oracle.entity.Teachers;
import com.oracle.view.StudentsView;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest
public class StudentsServiceTest {

    @Resource
    private ClassRoomService classRoomService;
    @Resource
    private StudentsService studentsService;
    @Resource
    private TeachersService teachersService;
    @Resource
    private StudentsTeachersService studentsTeachersService;

    @Test
    public void testQueryStudentsView(){
        List<StudentsView> studentsViewList = studentsService.queryStudentsView();
        studentsViewList.forEach(System.out::println);
    }
}

5.带参数的视图查询

返回指定学生的视图，需要根据学号sid查询。

package com.oracle.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.oracle.entity.Students;
import com.oracle.view.StudentsView;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface StudentsMapper extends BaseMapper<Students> {


    @Select("select sid, sname,gender,birthday,school,major,s.cid ,cname from students s inner join class_room c where s.cid=c.cid and s.sid=#{sid}")
    @Results(
            {
                    @Result(property = "sid", column = "sid"),
                    @Result(property = "sname", column = "sname"),
                    @Result(property = "gender", column = "gender"),
                    @Result(property = "birthday", column = "birthday"),
                    @Result(property = "school", column = "school"),
                    @Result(property = "major", column = "major"),
                    @Result(property = "cid", column = "cid"),
                    @Result(property = "cname", column = "cname")
            }
    )
    List<StudentsView> queryStudentsViewBySid(int sid);
}