sqlrequests.java

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

package com.sixtwenty;

import javax.mail.*;
import javax.mail.internet.*;
import java.util.Properties;
import java.sql.*;
import java.util.*;

/**
 * Class providing all the SQL requests required by the
 * submission system.
 * The class allow us to bypass the lack of stored procedure in MySQL 4.1
 */
public class SQLRequests {
    /**
     * The database's connection
     */
    protected Connection _con;

    /**
     * The database's location
     */
    protected String _source;

    /**
     * The database's user
     */
    protected String _dbmUser;


    /**
    * The database's password
    */
    protected String _dbmPassword;

    /**
     * Constructor: initialise database connection (remove the autocommit)
     */
    public SQLRequests(String source, String dbmUser, String dbmPassword) {
        // Initialisation of the attributes
        _source = source;
        _dbmUser = dbmUser;
        _dbmPassword = dbmPassword;

        // Initialisation of the connection with the database
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            _con = DriverManager.getConnection(_source, _dbmUser, _dbmPassword);

            // Remove the autocommit
            _con.setAutoCommit(false);

            if (!_con.isClosed())
               System.out.println("Connected to " + _source);
        } catch(Exception e) {
            System.err.println("Unable to connect to the database");
        }
    }

    /**
     * Get a project from given attributes
     * @param subj_code The subject's code
     * @param year The project's year
     * @param semester The project's semester
     * @param proj_name The project's name
     * @param proj_description The project's description
     * @param proj_url The project's url
     * @return The project's identifier
     */
    public int getProject(int subj_code, String year, String semester, String proj_name, StringBuffer proj_description, StringBuffer proj_url) throws SQLException {
        Statement stmt = _con.createStatement();;
        ResultSet results = null;
        String request; // The request to do

        request = "SELECT projectId, description, url INTO proj_id, proj_description, proj_url";
        request = request + " FROM project";
        request = request + " WHERE project.code = " + subj_code;
        request = request + " AND project.year = " + "'" + year + "'";
        request = request + " AND project.semester = " + "'" + semester + "'";
        request = request + " AND project.name = " + "'" + addSlashes(proj_name) + "'";

        results = stmt.executeQuery(request);
        results.next();

        proj_description.append(results.getString("description"));
        proj_url.append(results.getString("url"));

        return results.getInt("projectId");
    }

    /**
     * Add a project to the system
     * @param subj_code The subject's code
     * @param year The project's year
     * @param semester The project's semester
     * @param proj_name The project's name
     * @param proj_description The project's description
     * @param proj_url The project's url
     * @return The added project's identifier
     */
    public int addProject(int subj_code, String year, String semester, String proj_name, String proj_description, String proj_url) throws SQLException {
        Statement stmt = _con.createStatement();
        Statement stmt2 = _con.createStatement();
        ResultSet results = null;
        String request; // The request to do
        int projId; // The project identifier to return
        String studId;

        request = "INSERT INTO project (code, year, semester, name, description, url)";
        request = request + " VALUES (" + subj_code + "," + "'" + year + "'" + "," + "'" + semester + "'" + "," + "'" + addSlashes(proj_name) + "'" + "," + "'" + addSlashes(proj_description) + "'" + "," + "'" + addSlashes(proj_url) + "'" + ")";
        stmt.executeUpdate(request);

        request = "SELECT MAX(projectId) AS projId FROM project";
        results = stmt.executeQuery(request);
        results.next();

        projId = results.getInt("projId");

        /*** For the innovation component ***/
        // Get the list of student who have subscribed to this project
        request = "SELECT userId AS studId FROM usersubjects WHERE subjectId = " + subj_code;
        results = stmt.executeQuery(request);

        while (results.next()) {
            studId = results.getString("studId");
            request = "INSERT INTO preventuser (studentId, projectId, preventLate, preventImpend)";
            request = request + " VALUES (" + "'" + studId + "'" + "," + projId + "," + 0 + "," + 0 + ")";
            //throw new SQLException(request2);
            stmt2.executeUpdate(request);
            request = "";
        }


                // throw new SQLException("merde");

        return projId;
    }

    /**
     * Get the project's informations from its identifier
     * @param proj_id The project's identifier
     * @param subj_code The subject's code
     * @param year The project's year
     * @param semester The project's semester
     * @param proj_name The project's name
     * @param proj_description The project's description
     * @param proj_url The project's url
     * @return The project's identifier
     */
    public void getProjectById(int proj_id, StringBuffer subj_code, StringBuffer year, StringBuffer semester, StringBuffer proj_name, StringBuffer proj_description, StringBuffer proj_url) throws SQLException {
        Statement stmt = _con.createStatement();;
        ResultSet results = null;
        String request; // The request to do

        request = "SELECT name, code, description, url, semester, year";
        request = request + " FROM project";
        request = request + " WHERE project.projectId = " + proj_id;

        results = stmt.executeQuery(request);
        results.next();

        subj_code.append(String.valueOf(results.getInt("code")));
        year.append(results.getString("year"));
        semester.append(results.getString("semester"));
        proj_name.append(results.getString("name"));
        proj_description.append(results.getString("description"));
        proj_url.append(results.getString("url"));
    }

    /**
     * Get all the project of the system
     * @return all the projects
     */
    public com.sixtwenty.Projects[] getProjectList() 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");
        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 * FROM project");

        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;
    }

    /**
     * The service to get all the users of the system with a specific role
     * @param role The users's role expected
     * @return The list of users with this role
     */
    public com.sixtwenty.Users[] getUserList(java.lang.String role) throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;

        // The users to return
        Users[] users = new Users[]{};
        int nbUsers = 0; // The number of users in the system

        String userLogin; // The user's login name
        String name; // The user's name
        String email; // The user's email

        // Preparation for request
        results = stmt.executeQuery("SELECT COUNT(*) AS nbUsers FROM user WHERE role = " + "'" + role + "'");
        results.next();
        nbUsers = results.getInt("nbUsers");

        if (nbUsers == 0) return users;

        // Intanciation of the list of projects to return
        users = new Users[nbUsers];

        results = stmt.executeQuery("SELECT * FROM user WHERE role = " + "'" + role + "'");

        int i=0;
        while (results.next()) {
            // Get the result
            userLogin = results.getString("loginName");
            name = results.getString("name");
            email = results.getString("email");

            users[i] = new Users(userLogin, role, name, email);
            i++;
         }

        return users;
    }

    /**
     * Get all the subjects of the system
     * @return all the projects
     */
    public com.sixtwenty.Subjects[] getSubjectList() throws SQLException {
        Statement stmt = _con.createStatement();
        ResultSet results = null;

        // The subjects to return
        Subjects[] subjects = new Subjects[]{};
        int nbSubjects = 0; // The number of projects in the system

        String subjId; // The subject's identifier
        String name; // The subject's name
        String lectId; // The lecturer's identifier

        // Preparation for request
        results = stmt.executeQuery("SELECT COUNT(*) AS nbSubject FROM subject");
        results.next();
        nbSubjects = results.getInt("nbSubject");

        if (nbSubjects == 0) return subjects;

        // Intanciation of the list of subjects to return
        subjects = new Subjects[nbSubjects];

        results = stmt.executeQuery("SELECT * FROM subject");

        int i=0;
        while (results.next()) {
            // Get the result
            subjId = results.getString("subjectId");
            name = results.getString("name");
            lectId = results.getString("lecturerId");

            subjects[i] = new Subjects(subjId, name, lectId);
            i++;
         }

        return subjects;
    }

    /**
     * Get all the projects of a user
     * @param user_id The user's identifier
     * @param status The status required for these projects
     * @return All the projects
     */
    public com.sixtwenty.Projects[] getProjectUser(String user_id, String status) 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, usersubjects u WHERE p.code = u.subjectId AND p.status = " + "'" + addSlashes(status) + "'" + " AND u.userId = " + "'" + 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, usersubjects u WHERE p.code = u.subjectId AND p.status = " + "'" + addSlashes(status) + "'" + " AND u.userId = " + "'" + 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;
    }

    /**
     * Get all the projects of a staff
     * @param user_id The staff's identifier