Java 多表查询语句

1.设计数据库如下

create database job_collect

用户信息表——user_info

• user_id：用户唯一ID
• u_number：用户名（学号）
• u_name：姓名
• u_passwd：密码

create table user_info
(
	pk_user_id int(11) NOT NULL  primary key auto_increment,
	u_number char(20) not null ,
	u_name varchar(30) not null ,
	u_passwd char(50) not null
)

班级信息——class_info

• c_id：班级id
• c_num：班级人数
• c_name：班级名称

drop table class_info
create table class_info
(
	pk_c_id int(11) NOT NULL primary key auto_increment,
	c_num tinyint unsigned not null,
    c_name varchar(30) not null
)

班级与用户绑定表——class_stu

• c_id：班级id
• user_id：用户id
• u_number：用户名（学号）
• u_name：姓名

create table class_stu
(
	c_id int(11) NOT NULL ,
    user_id  int(11) NOT NULL ,
	u_number char(20) not null ,
	u_name varchar(30) not null ,
    primary key(c_id , user_id),
    foreign key  (c_id) references class_info(pk_c_id),
    foreign key  (user_id) references user_info(pk_user_id)
)

作业信息——work_info

• j_id：作业id
• c_id：班级id
• j_name：收取作业的名字
• j_num：当前收取人数

drop table work_info
create table work_info
(
	pk_j_id int(11) NOT NULL primary key auto_increment,
    c_id int(11) NOT NULL ,
    j_name varchar(50) not null,
    j_num tinyint unsigned not null,
	foreign key  (c_id) references class_info(pk_c_id)
)

班级与作业关联情况——stu_work

• j_id：作业id
• c_id：班级id
• submit_time：提交时间
• user_id：用户id
• user_number：用户名（学号）
• user_name：姓名

drop table  stu_work
create table stu_work
(
	j_id int(11) NOT NULL ,
	c_id int(11) NOT NULL ,
	user_id int(11) NOT NULL ,
    primary key( j_id , c_id , user_id),
    submit_time datetime not null,
	u_number char(20) not null ,
	u_name varchar(30) not null,
	foreign key  (j_id) references work_info(pk_j_id),
    foreign key  (c_id) references class_info(pk_c_id),
    foreign key  (user_id) references user_info(pk_user_id)
)

真实作业——work_file

• j_id：作业id
• user_id：用户id
• uuid：文件的uuid

create table work_file
(
	s_id int(11) NOT NULL primary key auto_increment,
	j_id int(11) NOT NULL ,
	user_id int(11) NOT NULL ,
	file_uuid varchar(36) not null ,
    foreign key  (j_id) references work_info(pk_j_id),
	foreign key  (user_id) references user_info(pk_user_id)
)

2.创建实体类

创建与需要展示信息对应的实体类——StuInfo

private String uNumber;
    private String uName;

• 重写getter/setter
• 重写equals + hashcode
• 重写toString

3.添加UserService接口

返回参数——List

因为需要多表查询，因此对应的返回参数应该是一个数组，在Java中也就是List

函数名称——ListUnTurnInUser

获取多个对象时，使用List做前缀

函数内容是用来查询 未上交的学号 + 姓名

函数参数——无

因为函数功能为查询未上交的学生信息，因此无需传参

4.添加DAO层方法

List<StuInfo> listUnTurnInUser();

5.实现ListUnTurnInUser方法

• 从DAO中直接获取值，返回即可
• 若为空，则渲染——作业已收齐

@Override
    public List<UserInfo> listUnTurnInUser() {
        List<StuInfo> unTurnInList = null;
        try {
            UserDAO userDAO = MybatisUtils.getSession().getMapper(UserDAO.class);
            unTurnInList = userDAO.listUnTurnInUser();
        }catch (IOException e){
          
        }
        return unTurnInList;
    }

6.添加SQL语句

在之前创建的UserMapper.xml中继续插入多表查询SQL语句

①添加select语句

• id：与UserDao对应的函数名称一致
• resultType：返回参数类型——保持与数据库列一致（小驼峰）

<select 
            id="listUnTurnInUser"
            resultType="com.example.experiment.web.entity.StuInfo">
      
    </select>

②执行sql语句

1.查询表（class_stu）查询所有应该提交的

2.连接 （stu_work)作业id（j_id）和 班级id（c_id）——查询已提交的

3.执行差集操作（not exists）实现对未提交人数的查询

select u_number , u_name from class_stu as c
        where not exists
            ( select * from stu_work as s
              where c.c_id = s.c_id
                and c.user_id = s.user_id
            );

7.添加loginServlet中doLogin逻辑

我希望在点击登录之后，就立马查询未提交的同学信息

因此，希望在loginServlet中调用查询接口，获取数据后，跳转页面

仅需在重定向前，查询数据即可

private void doLogin(HttpServletRequest req, HttpServletResponse resp, UserService userService) throws IOException, ServletException {
        String userName = req.getParameter("userName");
        String passwd = req.getParameter("passwd");
        HttpSession session = req.getSession();
        boolean isSuccess = userService.login(userName , passwd,session);

        if(isSuccess){
            userService.listUnTurnInUser(session);
            resp.sendRedirect("index.jsp");
        }else{
            req.setAttribute("msg","用户名或密码错误");
            req.getRequestDispatcher("login.jsp").forward(req,resp);
        }

    }

①修改UserService接口

添加属性HttpSession session

List<StuInfo> listUnTurnInUser(HttpSession session);

②修改实现

需要在不为空时，设置sessionScope的变量中（后续在index.jsp页面中直接使用）

@Override
    public List<StuInfo> listUnTurnInUser(HttpSession session) {
        List<StuInfo> unTurnInList = null;
        try {
            UserDAO userDAO = MybatisUtils.getSession().getMapper(UserDAO.class);
            unTurnInList = userDAO.listUnTurnInUser();
        }catch (IOException e){
            e.printStackTrace();
        }
        if(null != unTurnInList){
            session.setAttribute("unTurnInList", unTurnInList);
        }
        return unTurnInList;
    }

8.JSP渲染数据

因为我们在登录近来时，就已经获取了数据，并保存在session中，因此仅仅只需要在index.jsp中实现数据渲染即可

导入JSTL

在jsp文件最上方，复制以下代码

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="jstl" %>

建立表格

<table title="未提交名单" border="1">
        <tr>
            <th>学号</th>
            <th>姓名</th>
        </tr>

        <jstl:forEach var="stu" items="${unTurnInList}">
            <tr>
                <td>${stu.uNumber}</td>
                <td>${stu.uName}</td>
            </tr>
        </jstl:forEach>

    </table>

• var：对应变量stu为每次迭代的值
• unTurnInList：为之前在session中设置的值