sqlrequests.java

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

     * @return The SubmitFiles done with submission
     */
    public SubmitFiles[] getSubmissionFiles(int subId) throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;
        String request; // The request to do
        SubmitFiles[] files = new SubmitFiles[]{}; // The submit files to return
        int nbFiles; // The number of submit files
        String filename; // The name of a submission's file
        int length; // The size of a submission's file
        byte[] fileData; // The data of a submission's file
        String subUser; // The submission's user

        results = stmt.executeQuery("SELECT COUNT(*) AS nbFiles FROM submissionfiles WHERE submissionId = " + subId);
        results.next();
        nbFiles = results.getInt("nbFiles");

        if (nbFiles == 0) return files;

        // Intanciation of the list of SubmitFiles to return
        files = new SubmitFiles[nbFiles];

        // Get the submission's user
        results = stmt.executeQuery("SELECT studentId FROM submission WHERE submissionId = " + subId);
        results.next();
        subUser = results.getString("studentId");

        results = stmt.executeQuery("SELECT * FROM submissionfiles WHERE submissionId = " + subId);

        int i=0;
        while (results.next()) {
            filename = results.getString("filename");
            length = results.getInt("size");
            fileData = results.getBytes("fileData");

            files[i] = new SubmitFiles();
            files[i].setFilesize(length);
            files[i].setFilename(filename);
            files[i].setFileContents(fileData);
            files[i].setFileuser(subUser);
            i++;
         }

         return files;
    }

    /**
     * Get all the submissions a user has made
     * @param loginname The user's login name
     * @return All the submissions of this user
     */
    public Submissions[] getSubmissionList(String loginname) throws SQLException {
        Statement stmt = _con.createStatement();
        Statement stmt2 = _con.createStatement();
        ResultSet resultsSubs = null;
        ResultSet resultsFiles = null;
        // The submissions to return
        Submissions[] submissions = new Submissions[]{};
        SubmitFiles[] files; // the files of a submission
        int nbFiles; // the number of submit files
        int nbSubmissions; // the number done by the user "username"
        Calendar cal; // The submission's calendar
        int i = 0;
        int j = 0;

        String subIpAd; // The submission's ip address
        java.sql.Date subTime; // The submission's date
        String subjId; // The submission's subject
        String projId; // The submission's project id
        String subId; // The submission's id
        String filename; // The name of a submission's file
        int length; // The size of a submission's file
        byte[] fileData; // The data of a submission's file

        resultsSubs = stmt.executeQuery("SELECT COUNT(*) AS nbSubmissions FROM submission WHERE studentId = " + "'" + addSlashes(loginname) + "'");
        resultsSubs.next();
        nbSubmissions = resultsSubs.getInt("nbSubmissions");

        if (nbSubmissions == 0) return submissions;

        submissions = new Submissions[nbSubmissions];

        resultsSubs = stmt.executeQuery("SELECT * FROM submission WHERE studentId = " + "'" + addSlashes(loginname) + "'");

        while (resultsSubs.next()) {
            subjId = resultsSubs.getString("subjectId");
            projId = resultsSubs.getString("projectId");
            subId = resultsSubs.getString("submissionId");
            subTime = resultsSubs.getDate("submissionDate");
            subIpAd = resultsSubs.getString("ipAddress");

            // Get all the files of this submission
            resultsFiles = stmt2.executeQuery("SELECT COUNT(*) AS nbFiles FROM submissionfiles WHERE submissionId = " + subId);
            resultsFiles.next();
            nbFiles = resultsFiles.getInt("nbFiles");

            // Intanciation of the list of files
            files = new SubmitFiles[nbFiles];

            resultsFiles = stmt2.executeQuery("SELECT * FROM submissionfiles WHERE submissionId = " + subId);

            j = 0;
            while (resultsFiles.next()) {
                filename = resultsFiles.getString("filename");
                length = resultsFiles.getInt("size");
                fileData = resultsFiles.getBytes("fileData");

                files[j] = new SubmitFiles();
                files[j].setFilesize(length);
                files[j].setFilename(filename);
                files[j].setFileContents(fileData);
                files[j].setFileuser(loginname);
                j++;
            }

            cal = Calendar.getInstance();
            cal.setTime(subTime);
            // Creation of the submission to return
            submissions[i] = new Submissions(files, subId, subIpAd, nbFiles, cal, loginname);

            i++;
        }

        return submissions;
    }

    /**
     * Get all the submissions of a specific project
     * @param proj_id The project's identifier
     * @return All the submissions of this project
     */
    public Submissions[] getProjectSubmissions(String proj_id) throws SQLException {
        Statement stmt = _con.createStatement();
        Statement stmt2 = _con.createStatement();
        ResultSet resultsSubs = null;
        ResultSet resultsFiles = null;
        // The submissions to return
        Submissions[] submissions = new Submissions[]{};
        SubmitFiles[] files = new SubmitFiles[]{}; // the files of a submission
        //Vector<SubmitFiles> files = null;
        int nbFiles = 0; // the number of submit files
        int nbSubmissions; // the number done by the user "username"
        Calendar cal = null; // The submission's calendar
        int i = 0;
        int j = 0;

        String subIpAd; // The submission's ip address
        java.sql.Date subTime; // The submission's date
        String subUser; // The submission's user
        String subjId; // The submission's subject
        String subId; // The submission's id
        String filename; // The name of a submission's file
        int length; // The size of a submission's file
        byte[] fileData; // The data of a submission's file

        // Get all the submissions of this user
        resultsSubs = stmt.executeQuery("SELECT COUNT(*) AS nbSubmissions FROM submission WHERE projectId = " + proj_id);
        resultsSubs.next();
        nbSubmissions = resultsSubs.getInt("nbSubmissions");

        if (nbSubmissions == 0) return submissions;

        submissions = new Submissions[nbSubmissions];

        resultsSubs = stmt.executeQuery("SELECT * FROM submission WHERE projectId = " + proj_id);

        while (resultsSubs.next()) {
            subjId = resultsSubs.getString("subjectId");
            subUser = resultsSubs.getString("studentId");
            subId = resultsSubs.getString("submissionId");
            subTime = resultsSubs.getDate("submissionDate");
            subIpAd = resultsSubs.getString("ipAddress");

            // Get all the files of this submission
            resultsFiles = stmt2.executeQuery("SELECT COUNT(*) AS nbFiles FROM submissionfiles WHERE submissionId = " + subId);
            resultsFiles.next();
            nbFiles = resultsFiles.getInt("nbFiles");

            // Intanciation of the list of files
            files = new SubmitFiles[nbFiles];

            resultsFiles = stmt2.executeQuery("SELECT * FROM submissionfiles WHERE submissionId = " + subId);

            j = 0;
            while (resultsFiles.next()) {
                filename = resultsFiles.getString("filename");
                length = resultsFiles.getInt("size");
                fileData = resultsFiles.getBytes("fileData");

                files[j] = new SubmitFiles();
                files[j].setFilesize(length);
                files[j].setFilename(filename);
                files[j].setFileContents(fileData);
                files[j].setFileuser(subUser);
                j++;
            }

            cal = Calendar.getInstance();
            cal.setTime(subTime);
            // Creation of the submission to return
            submissions[i] = new Submissions(files, subId, subIpAd, nbFiles, cal, subUser);

            i++;
        }

        //throw new SQLException(String.valueOf(nbFiles));
        return submissions;
    }

    /**
     * Verify if a submission has been correctly done
     * @param subId The submission's identifier
     * @param "true" is this submission has been correctly done, "false" otherwise
     */
    public String verifySubmission(int subId) throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;
        String request; // The request to do

        request = "SELECT COUNT(*) AS nb";
        request = request + " FROM submission";
        request = request + " WHERE submissionId = " + subId;
        results = stmt.executeQuery(request);
        results.next();

        return String.valueOf((results.getInt("nb") > 0));
    }

    /**
     * Verify if a submission has been correctly done
     * @param subId The submission's identifier
     * @param "true" is this submission has been correctly done, "false" otherwise
     */
    public String authenticateUser(String userName, String pass) throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;
        String request; // The request to do
        int nb;

        request = "SELECT COUNT(*) AS nb";
        request = request + " FROM user";
        request = request + " WHERE user.loginName = " + "'" + userName + "'";
        request = request + " AND user.password = " + "'" + pass + "'";
        results = stmt.executeQuery(request);
        results.next();

        nb = results.getInt("nb");

        // if the user is unknown
        if (nb == 0) {
            return "Unknown";
        }

        // otherwise we take the user's role
        request = "SELECT role";
        request = request + " FROM user";
        request = request + " WHERE user.loginName = " + "'" + userName + "'";
        request = request + " AND user.password = " + "'" + pass + "'";
        results = stmt.executeQuery(request);
        results.next();

        return results.getString("role");
    }

    /**
     * To know wich access level has a specific user
     * @param loginname The user's login name
     * @return 0: Unauthorised, 1: Student, 2: Staff, 3: Admin
     */
    public int accessLevel(String loginname) throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;
        String request; // The request to do
        String role; // The user's role
        int nb;

        request = "SELECT COUNT(*) AS nb";
        request = request + " FROM user";
        request = request + " WHERE user.loginName = " + "'" + loginname + "'";
        results = stmt.executeQuery(request);
        results.next();
        nb = results.getInt("nb");

        // if the user is unknown
        if (nb == 0) {
            return 0;
        }

        request = "SELECT role";
        request = request + " FROM user";
        request = request + " WHERE user.loginName = " + "'" + loginname + "'";
        results = stmt.executeQuery(request);
        results.next();
        role = results.getString("role");

        if (role.equals("admin")) {
            return 3;
        } else if (role.equals("staff")) {
            return 2;
        } else if (role.equals("student")) {
            return 1;
        } else {
            return 0;
        }
    }

    /**
     * Get all the emails of the users attending to a specific subject
     * @param subj_id The subject's identifier
     * @return The list of emails
     */
    public String[] getUserEmailsProject(int subj_id) throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;
        String request; // The request to do
        String[] emails = new String[]{}; // The list of emails to return
        int nbUsers; // The number of users

        results = stmt.executeQuery("SELECT COUNT(*) AS nbUsers FROM usersubjects WHERE subjectId = " + subj_id);
        results.next();
        nbUsers = results.getInt("nbUsers");

        if (nbUsers == 0) return emails;

        emails = new String[nbUsers];

        results = stmt.executeQuery("SELECT u.email AS email FROM usersubjects s, user u WHERE s.userId = u.loginName AND subjectId = " + subj_id);

        int i=0;
        while (results.next()) {
            emails[i] = results.getString("email");
            i++;
        }

        return emails;
    }

    /**
     * Commit the database
     */
    public void doCommit() throws SQLException {
        _con.commit();
    }

    /**
     * Function to add a slashe before the character '
     */
    protected String addSlashes(String str){
        if(str==null) return "";

        StringBuffer s = new StringBuffer ((String) str);
        for (int i = 0; i < s.length(); i++) {
            if (s.charAt (i) == '\'')
                s.insert (i++, '\\');
        }

        return s.toString();
    }
}