sqlrequests.java.svn-base

远程学生作业提交系统,所用技术JSP,Servelet,Ajax,Web Services,MySql

    }

    /**
     * Get all the projects of a staff
     * @param user_id The staff's identifier
     * @return All the projects
     */
    public com.sixtwenty.Projects[] getProjectStaff(String user_id) throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;

        // The projects to return
        Projects[] projects = new Projects[]{};
        int nbProjects = 0; // The number of projects in the system

        String projId; // The project's id
        String projDesc; // The project's description
        String url; // The project's url
        String subjCode; // The subject's code
        String year; // The project's year
        String semester; // The project's semester
        String name; // The project's name

        // Preparation for request
        results = stmt.executeQuery("SELECT COUNT(*) AS nbProject FROM project p, subject s WHERE p.code = s.subjectId AND s.lecturerId = " + "'" + user_id + "'");
        results.next();
        nbProjects = results.getInt("nbProject");

        if (nbProjects == 0) return projects;

        // Intanciation of the list of projects to return
        projects = new Projects[nbProjects];

        results = stmt.executeQuery("SELECT p.code AS code, p.year AS year, p.semester AS semester, p.name AS name, p.description AS description, p.url AS url, p.projectId AS projectId FROM project p, subject s WHERE p.code = s.subjectId AND s.lecturerId = " + "'" + user_id + "'");

        int i=0;
        while (results.next()) {
            // Get the result
            subjCode = results.getString("code");
            year = results.getString("year");
            semester = results.getString("semester");
            name = results.getString("name");
            projDesc = results.getString("description");
            url = results.getString("url");
            projId = String.valueOf(results.getInt("projectId"));

            projects[i] = new Projects(projDesc, projId, name, url, semester, subjCode, year);
            i++;
         }

        return projects;
    }

    /**
     * Set the status of a project
     * @param proj_id The project's identifier
     * @param projState The project's state to set. "C": Closed - can't submit, "O": Open, "L": Late submissions being accepted, "A": Archived - can't submit
     */
    public void setProjectStatus(int proj_id, String projState) throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;
        String request; // The request to do

        java.sql.Date proj_time_open; // The project's open date
        java.sql.Date proj_time_close; // The project's closing date
        java.sql.Date sysdate;

        request = "SELECT openDate, closingDate, SYSDATE() AS sysdate ";
        request = request + " FROM project";
        request = request + " WHERE projectId =" + proj_id;
        results = stmt.executeQuery(request);
        results.next();

        proj_time_open = results.getDate("openDate");
        proj_time_close = results.getDate("closingDate");
        sysdate = results.getDate("sysdate");

        request = "UPDATE project";
        request = request + " SET status = " + "'" + projState + "'";
        request = request + " WHERE projectId = " + proj_id;
        stmt.executeUpdate(request);

        if (projState.equals("O") || projState.equals("L")) {
            if (proj_time_open == null) {
                request = "UPDATE project";
                request = request + " SET openDate = SYSDATE()";
                request = request + " WHERE projectId = " + proj_id;
                stmt.executeUpdate(request);
            }

            if (proj_time_close != null) {
                if (proj_time_close.compareTo(sysdate) < 0) {
                    request = "UPDATE project";
                    request = request + " SET closingDate = NULL";
                    request = request + " WHERE projectId = " + proj_id;
                    stmt.executeUpdate(request);
                }
            }
        } else if (projState.equals("C") || projState.equals("A")) {
            if (proj_time_close != null) {
                //throw new SQLException(proj_time_close.toString()+ " " + sysdate.toString());
                if (proj_time_close.compareTo(sysdate) < 0) {
                    request = "UPDATE project";
                    request = request + " SET closingDate = SYSDATE()";
                    request = request + " WHERE projectId = " + proj_id;
                    stmt.executeUpdate(request);
                }
            } else {
                request = "UPDATE project";
                request = request + " SET closingDate = SYSDATE()";
                request = request + " WHERE projectId = " + proj_id;
                stmt.executeUpdate(request);
            }
        }
    }

    /**
     * Get the status of a project
     * @param proj_id The project's identifier
     * @return The project's state. "C": Closed - can't submit, "O": Open, "L": Late submissions being accepted, "A": Archived - can't submit
     */
    public String getProjectStatus(int proj_id) throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;
        String request; // The request to do

        request = "SELECT status";
        request = request + " FROM project";
        request = request + " WHERE projectId =" + proj_id;
        results = stmt.executeQuery(request);
        results.next();

        return results.getString("status");
    }

    /**
     * Set the project open date
     * @param proj_id The project's identifier
     * @param proj_time The project's open date
     */
    public void setProjectOpendate(int proj_id, java.sql.Date proj_time) throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;
        String request; // The request to do

        //throw new SQLException(proj_time.toString());

        if (proj_time != null) {
            request = "UPDATE project";
            request = request + " SET openDate = " + "'" + proj_time.toString() + "'";
            request = request + " WHERE projectId =" + proj_id;
        } else {
            request = "UPDATE project";
            request = request + " SET openDate = NULL";
            request = request + " WHERE projectId =" + proj_id;
        }
        stmt.executeUpdate(request);
    }

    /**
     * Get the project open date
     * @param proj_id The project's identifier
     * @return proj_time The project's open date
     */
    public java.sql.Date getProjectOpendate(int proj_id) throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;
        String request; // The request to do

        request = "SELECT openDate";
        request = request + " FROM project";
        request = request + " WHERE projectId =" + proj_id;
        results = stmt.executeQuery(request);

        if (!results.next()) return null;

        java.sql.Date d = results.getDate("openDate");
        return results.getDate("openDate");
    }

    /**
     * Set the project closing date
     * @param proj_id The project's identifier
     * @param proj_time The project's closing date
     */
    public void setProjectClosedate(int proj_id, java.sql.Date proj_time) throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;
        String request; // The request to do

        if (proj_time != null) {
            request = "UPDATE project";
            request = request + " SET closingDate = " + "'" + proj_time.toString() + "'";
            request = request + " WHERE projectId =" + proj_id;
        } else {
            request = "UPDATE project";
            request = request + " SET closingDate = NULL ";
            request = request + " WHERE projectId =" + proj_id;
        }
        stmt.executeUpdate(request);
    }

    /**
     * Get the project closing date
     * @param proj_id The project's identifier
     * @return proj_time The project's closing date
     */
    public java.sql.Date getProjectClosedate(int proj_id) throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;
        String request; // The request to do

        request = "SELECT closingDate";
        request = request + " FROM project";
        request = request + " WHERE projectId =" + proj_id;
        results = stmt.executeQuery(request);

        if (!results.next()) return null;
        return results.getDate("closingDate");
    }

    /**
     * Add a user to the system
     * @param loginName The user's login name
     * @param passw The user's password
     * @param role The user's role
     * @param name The user's name
     * @param email The email's name
     */
    public void addUser(String loginName, String passw, String role, String name, String email) throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;
        String request; // The request to do

        request = "INSERT INTO user (loginName, password, role, name, email)";
        request = request + " VALUES (" + "'" + loginName + "'" + "," + "'" + passw + "'" + "," + "'" + role + "'" + "," + "'" + name + "'" + "," + "'" + email + "'" +")";
        stmt.executeUpdate(request);
    }

    /**
     * Add a subject to the system
     * @param name The subject's name
     * @param lecturer_id The lecturer's login name
     */
    public void addSubject(String name, String lecturer_id) throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;
        String request; // The request to do

        request = "INSERT INTO subject (name, lecturerId)";
        request = request + " VALUES (" + "'" + name + "'" + "," + "'" + lecturer_id + "'" +")";
        stmt.executeUpdate(request);
    }

    /**
     * Add to a user a subject he is attending
     * @param loginName The user's login name
     * @param passw The user's password
     * @param role The user's role
     * @param name The user's name
     * @param email The email's name
     */
    public void setUserSubject(String loginName, int subj_id) throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;
        String request; // The request to do

        request = "INSERT INTO usersubjects (userId, subjectId)";
        request = request + " VALUES (" + "'" + loginName + "'" + "," + subj_id + ")";
        stmt.executeUpdate(request);
    }

    /**
     * Create a submission for a specific project
     * @param projectId The project's identifier
     * @param studentId The student's identifier
     * @param submissionDate The submission's date
     * @param ipAddress The ip address
     * @return The submission's identifier
     */
    public int createSubmission(int projectId, String studentId, java.sql.Date submissionDate, String ipAddress) throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;
        String request; // The request to do
        String subj_code;

        request = "SELECT code FROM project WHERE projectId = " + projectId;
        results = stmt.executeQuery(request);
        results.next();

        subj_code = String.valueOf(results.getInt("code"));

        request = "INSERT INTO submission (subjectId, projectId, studentId, submissionDate, ipAddress)";
        request = request + " VALUES (" + subj_code + "," + projectId + "," + "'" + studentId + "'" + "," + "'" + submissionDate.toString() + "'" + "," + "'" + ipAddress + "'" + ")";

        stmt.executeUpdate(request);

        request = "SELECT MAX(submissionId) AS id FROM submission";
        results = stmt.executeQuery(request);
        results.next();

        return results.getInt("id");
    }

    /**
     * Add a file to a submission for a specific project
     * @param submissionId The submission's identifier
     * @param filename The name of the file to add
     * @param fileData The data of the file to add
     * @param size The size of this file
     */
    public void addSubmissionFile(int submissionId, String filename, byte[] fileData, int size) throws SQLException {
        //Statement stmt = _con.createStatement();
        //ResultSet results = null;
        //String request; // The request to do

        // Construction of the contents of the file
        //String contents = new String(fileData, 0, fileData.length);

        //request = "INSERT INTO submissionfiles (submissionId, filename, fileData, size)";
        //request = request + " VALUES (" + submissionId + "," + "'" + filename + "'" + "," + "'" + contents + "'" + "," + size + ")";
        //stmt.executeUpdate(request);

        PreparedStatement pstmt = _con.prepareStatement("INSERT INTO submissionfiles (submissionId, filename, fileData, size) VALUES ( ?, ?, ?, ?)");
        pstmt.setInt(1, submissionId);
        pstmt.setString(2, filename);
        pstmt.setBytes(3, fileData);
        pstmt.setInt(4, size);

        pstmt.executeUpdate();

        //throw new SQLException(fileData.toString()+" --- "+String.valueOf(size)+ " --- "+ request);
    }

    /**
     * Get a submission from its identifier
     * @param subId The submission's identifier
     * @param subjectId The name of the file to add
     * @param projectId The data of the file to add
     * @param studentId The size of this file
     * @param submissionDate The submission's date
     * @param ipAddress The ip Address of the host which has done the submission
     */
    public void getSubmission(int subId, StringBuffer subjectId, StringBuffer projectId, StringBuffer studentId, java.sql.Date submissionDate, StringBuffer ipAddress) throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;
        String request; // The request to do

        request = "SELECT subjectId, projectId, studentId, submissionDate, ipAddress";
        request = request + " FROM submission";
        request = request + " WHERE submissionId =" + subId;
        results = stmt.executeQuery(request);
        results.next();