2023.11.2每日总结

/* submit assignment query
     * 提交作业查询，将提交作业信息从数据库中查询出来，返回Pd_S_assignment
     * 输入参数为：提交作业的序号，即id(String)
     * 若有记录，则返回Pd_S_assignment，若无记录，则返回null
     * Pd_S_assignment包含所有 提交作业 的信息，int id,String student_id,String time,String pu_ass_id,String content,Blob file
     * */
    public Pd_S_assignment S_assignment_query(String id) throws Exception
    {
        String sql="select * from submit_assignment where as_id = ?";
        PreparedStatement pre = connect.prepareStatement(sql);
        pre.setString(1,id);
        ResultSet rs = pre.executeQuery();
        Pd_S_assignment pdd = new Pd_S_assignment();
        while(rs.next())
        {
            pdd.setId(rs.getInt(1));
            pdd.setStudent_id(rs.getString(2));
            pdd.setTime(rs.getString(3));
            pdd.setPu_ass_id(rs.getString(4));
            pdd.setContent(rs.getString(5));
            pdd.setFile(rs.getBlob(6));
            pdd.setState(rs.getString(7));
            pdd.setScore(rs.getInt(8));
            pdd.setPu_name(rs.getString(9));
            pdd.setLesson_name(rs.getString(10));
            pdd.setStudent_name(rs.getString(11));
        }
        pre.close();
        if(pdd.getId()!=0)
            return pdd;
        else
            return null;
    }
    /* submit assignment Dim Query
     * 提交作业信息模糊查询，输入序号，名称，截止时间，所属课程id，所属老师id，文字信息中的一项或多项，实现模糊查询，6项都不输入则为全部显示
     * 输入参数：序号id(String)，学生学号student_id(String)，时间time(String)，所属发布作业id pu_ass_id(String)，文字信息content(String)
     * 返回参数类型为 wangzhan.Pd_stu[] ,储存所有符合条件的 wangzhan.Pd_stu
     * */
    public Pd_S_assignment[] S_assignment_DimQuery(String id,String student_id,String time,String pu_ass_id,String content,String state,String pu_name,String lesson_name,String student_name) throws Exception
    {
        if(!(pu_name!=null)) pu_name="";
        if(!(lesson_name!=null)) lesson_name="";
        if(!(student_name!=null)) student_name="";

        String sql="select as_id from submit_assignment where as_id like ? and as_student_id like ? and as_time like ? and as_pu_ass_id like ? and as_content like ? and as_score like ?" +
                " and as_pu_name like ? and as_lesson_name like ? and as_student_name like ?";
        PreparedStatement pre = connect.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        pre.setString(1,"%"+id+"%");
        pre.setString(2,"%"+student_id+"%");
        pre.setString(3,"%"+time+"%");
        pre.setString(4,"%"+pu_ass_id+"%");
        pre.setString(5,"%"+content+"%");
        pre.setString(6,"%"+state+"%");
        pre.setString(7,"%"+pu_name+"%");
        pre.setString(8,"%"+lesson_name+"%");
        pre.setString(9,"%"+student_name+"%");
        ResultSet rs = pre.executeQuery();
        rs.last();
        int rowCount=rs.getRow();
        rs.beforeFirst();
        Pd_S_assignment[] pdStus = new Pd_S_assignment[rowCount];
        int i=0;
        while(rs.next())
        {
            pdStus[i]=S_assignment_query(rs.getInt(1)+"");
            i++;
        }
        return pdStus;
    }

    /* publish assignment modify
     * 发布作业修改，将发布作业信息在数据库中修改
     * 输入参数为：Pd_P_assignment信息，其中包含所有publish assignment信息，
     * 包括String id,String name,String time_deadline,String lesson_id,String teacher_id,String content,Blob file
     * 若有此序号信息修改成功，则返回true，若此序号未有记录，则返回false
     * */
    private boolean P_assignment_modify(Pd_P_assignment pdd) throws Exception
    {
        if(P_assignment_query(pdd.getId()+"")!=null)
        {
            String sql = "update publish_assignment set as_name=?,as_time_deadline=?,as_lesson_id=?,as_teacher_id=?,as_content=?,as_file=? where as_id=?";
            PreparedStatement pre = connect.prepareStatement(sql);
            pre.setString(1, pdd.getName());
            pre.setString(2, pdd.getTime_deadline());
            pre.setString(3, pdd.getLesson_id());
            pre.setString(4, pdd.getTeacher_id());
            pre.setString(5, pdd.getContent());
            pre.setBlob(6, pdd.getFile());
            pre.setString(7, pdd.getId()+"");
            int count = pre.executeUpdate();
            pre.close();
            return true;
        }
        else
        {
            return false;
        }
    }

    /* submit assignment modify
     * 提交作业修改，将提交作业信息在数据库中修改
     * 输入参数为：Pd_S_assignment信息，其中包含所有submit assignment信息，
     * 包括int id,String student_id,String time, String pu_ass_id,String content,Blob file,String state
     * 若有此序号信息修改成功，则返回true，若此序号未有记录，则返回false
     * */
    private boolean S_assignment_modify(Pd_S_assignment pdd) throws Exception
    {
        if(S_assignment_query(pdd.getId()+"")!=null)
        {
            String sql = "update submit_assignment set as_student_id=?,as_time=?,as_pu_ass_id=?,as_content=?,as_file=?,as_state=?,as_score=? where as_id=?";
            PreparedStatement pre = connect.prepareStatement(sql);
            pre.setString(1, pdd.getStudent_id());
            pre.setString(2, pdd.getTime());
            pre.setString(3, pdd.getPu_ass_id());
            pre.setString(4, pdd.getContent());
            pre.setBlob(5, pdd.getFile());
            pre.setString(6, pdd.getState());
            pre.setInt(7, pdd.getScore());
            pre.setString(8, pdd.getId()+"");
            int count = pre.executeUpdate();
            pre.close();
            return true;
        }
        else
        {
            return false;
        }
    }
    /*作业打分，
    * 输入作业序号，分数
    * 返回true为成功，返回false为失败
    * 打分后state自动更新为“已批改”
    * */
    public boolean S_assignment_marking(String id,int score) throws Exception
    {
        Pd_S_assignment pd = S_assignment_query(id);
        pd.setState("已批改");
        pd.setScore(score);
        return S_assignment_modify(pd);
    }