dbaccess.java

The Software cannot constitute the primary value of any new software derived from or incorporating

     * This list comprises of worker and manager users, since administrators
     * can't have any tasks.
     * 
     * @return A list of users that can have tasks.
     */
    public List getTaskUsers()
    {
        List users = new ArrayList();

        try
        {
            ResultSet rs = executeSelectQuery( "SELECT user.login, user.id "
                    + "FROM user,userrolemap "
                    + "WHERE user.id = userrolemap.userid AND userrolemap.roleid <> 1" );

            while( rs.next() )
            {
                User user = new User( /* user id */rs.getString( "user.id" ),
                /* user login */rs.getString( "user.login" ),
                /* mobile number */"",
                /* group name */"", /* group id */"" );
                users.add( user );
            }
        }
        catch( Exception e )
        {
            users.clear();
        }

        return users;
    }

    /**
     * Returns a list of users that do not have their tasks up to date. This
     * list is used in sending SMS messages to those users that do not have
     * their tasks up to date.
     * 
     * @return A list of users.
     */
    public List smsUsers()
    {
        List users = new ArrayList();

        try
        {
            ResultSet rs = executeSelectQuery( "SELECT login, mobile FROM user"
                    + " WHERE tasks_up_to_date = 'no'" );

            while( rs.next() )
            {
                User user = new User( /* user id */"",
                /* user login */rs.getString( "login" ),
                /* user mobile */rs.getString( "mobile" ),
                /* group name */"", /* group id */"" );
                users.add( user );
            }
        }
        catch( Exception e )
        {
            users.clear();
        }
        return users;
    }

    /**
     * Returns a list of all users in the database. The administrator needs to
     * see all users so that he can change everyones attributes.
     * 
     * @return A list of all the users in the system.
     */
    public List getAllUsers()
    {
        List users = new ArrayList();
        try
        {
            ResultSet rs = executeSelectQuery( "SELECT user.id, user.login, user.mobile, roles.name, roles.id"
                    + " FROM user,userrolemap, roles"
                    + " WHERE user.id = userrolemap.userid"
                    + " AND userrolemap.roleid = roles.id" );
            while( rs.next() )
            {
                User user = new User( /* user id */rs.getString( "user.id" ),
                /* user login */rs.getString( "user.login" ),
                /* user mobile */rs.getString( "user.mobile" ),
                /* group name */rs.getString( "roles.name" ),
                /* group id */rs.getString( "roles.id" ) );
                users.add( user );
            }
        }
        catch( Exception e )
        {
            users.clear();
        }

        return users;
    }

    /**
     * Does a check on the users current password.
     * 
     * @param currentPw
     *            The user's current password.
     * @param userId
     *            The user's id number.
     * @return True if that password was valid, otherwise false.
     */
    public boolean checkCurrentPassword( String currentPw, String userId )
    {
        try
        {
            ResultSet rs = executeSelectQuery( "SELECT * FROM user "
                    + "WHERE password = OLD_PASSWORD('" + currentPw
                    + "') AND id = " + userId );

            if( !rs.next() )
            { // empty set, password was wrong.
                return false;
            }
        }
        catch( Exception e )
        {
            return false;
        }

        return true;
    }

    /**
     * Changes the user's password.
     * 
     * @param newPw
     *            The new password.
     * @param userId
     *            The id of the user in question.
     * @return True if the operation was successful, otherwise false.
     */
    public boolean changePassword( String newPw, String userId )
    {
        String[] sqls = new String[1];
        sqls[0] = "UPDATE user SET password = OLD_PASSWORD('" + newPw
                + "') where id = " + userId;
        return executeUpdateQuery( sqls );
    }

    /**
     * Returns a list of all available user groups.
     * 
     * @return A list of groups.
     */
    public List getGroups()
    {
        List groups = new ArrayList();
        try
        {
            ResultSet rs = executeSelectQuery( "SELECT id, name FROM roles" );

            while( rs.next() )
            {
                Group group = new Group( /* group id */rs.getString( "id" ),
                /* group name */rs.getString( "name" ) );
                groups.add( group );
            }
        }
        catch( Exception e )
        {
            groups.clear();
        }

        return groups;
    }
	
    /**
     * Returns all available server info.
     * 
     * @return A list of groups.
     */
    public String[] getServerInfo()
    {
		String[] serverInfo = new String[2];
		
        try
        {
            ResultSet rs = executeSelectQuery( "SELECT address, port FROM serversettings" );
            while( rs.next() )
            {
				serverInfo[0] = rs.getString("address");
				serverInfo[1] = rs.getString("port");
            }
        }
        catch( Exception e )
        {
			System.out.println("SQL Error getting server info" + e.toString());
        }

        return serverInfo;
    }

    /**
     * This function adds a new user account in to the database.
     * 
     * @param login
     *            Login of the new user.
     * @param password
     *            Password of the new user.
     * @param mobile
     *            Mobile number of the new user.
     * @param group
     *            Id of the group the new user belongs to.
     * @return True if the adding succeeded, otherwise false.
     */
    public boolean addNewUser( String login, String password, String mobile,
            String group )
    {
        String[] sqls = new String[2];

        // First SQL command.
        sqls[0] = "INSERT INTO user (login, password, mobile) VALUES ('"
                + login + "',OLD_PASSWORD('" + password + "'),'" + mobile
                + "')";

        // Second SQL command.
        String userIdSQL = "(SELECT id FROM user WHERE login = '" + login + "')";
        sqls[1] = "INSERT INTO userrolemap (userid, roleid) VALUES ("
                + userIdSQL + "," + group + ")";

        return executeUpdateQuery( sqls );
    }
	
    public boolean setServerInfo( String address, String port )
    {
		String[] sqls = new String[2];
		
		sqls[0] = "DELETE from serversettings";
		
        sqls[1] = "INSERT INTO serversettings(address, port, servertype) " +
        		"VALUES ('"+ address +"','"+ port +"', 'JSP')";

        return executeUpdateQuery( sqls );
    }


    /**
     * This function changes the users settings.
     * 
     * @param login
     *            New login to be set.
     * @param pw
     *            New password (can be left empty).
     * @param mobile
     *            New mobile number.
     * @param groupId
     *            New group id.
     * @param userId
     *            The id of the user that is being modified.
     * @return True if modification was successful, otherwise false.
     */
    public boolean changeUser( String login, String pw, String mobile,
            String groupId, String userId )
    {
        boolean returnValue = true;
        String[] sqls = new String[2];

        // First SQL command.
        sqls[0] = "UPDATE user SET login = '" + login + "', mobile = '"
                + mobile + "'";
        if( pw != null && pw != "" )
        {
            sqls[0] += ", password = OLD_PASSWORD('" + pw + "')";
        }
        sqls[0] += " where id =" + userId;

        // Second SQL command.
        sqls[1] = "UPDATE userrolemap SET roleid = " + groupId
                + " where userid = " + userId;

        return executeUpdateQuery( sqls );
    }

    /**
     * This function removes the specified user from the database.
     * 
     * @param userId
     *            The id number of the user scheduled for removal.
     * @return True if the remove operation succeeded, otherwise false.
     */
    public boolean deleteUser( String userId )
    {
        String[] sqls = new String[1];
        sqls[0] = "DELETE FROM user WHERE id = " + userId;
        return executeUpdateQuery( sqls );
    }

    /**
     * This helper function executes an array of SQL statements. The "batch job"
     * is not committed until all statements have been successfully executed.
     * This means that either all changes are made or none of them are made
     * (e.g. the batch job is executed in a transactional manner).
     * 
     * @param sqls
     *            The array of SQL statements.
     * @return True if the entire batch could be executed, otherwise false.
     */
    private boolean executeUpdateQuery( String[] sqls )
    {
        boolean returnValue = true;

        getConnection();
        boolean oldCommitMode = false;
        boolean commitModeChecked = false;

        try
        {
            // The old mode is saved.
            oldCommitMode = conn.getAutoCommit();
            // Since getAutoCommit() can throw an exception, we have to have a
            // flag that tells us whether the old commit mode was retrieved
            // successfully.
            commitModeChecked = true;
            // Auto-commit is disabled.
            conn.setAutoCommit( false );

            Statement statement = conn.createStatement();

            // The SQL commands sent to the function are added as batch jobs
            // to the statement
            for( int i = 0; i < sqls.length; ++i )
            {
                statement.addBatch( sqls[i] );
            }

            // The batch is executed all at once. If one of the statements
            // returns an error,
            // the rest of the batch does not execute.
            int[] batchState = statement.executeBatch();
            boolean batchSuccessful = true;
            for( int i = 0; i < batchState.length; ++i )
            { // If one of the actions in the batch returned an error,
                // we must execute a rollback
                if( batchState[i] == Statement.EXECUTE_FAILED )
                {
                    batchSuccessful = false;
                    break;
                }
            }
            if( !batchSuccessful )
            {
                returnValue = false;
                conn.rollback();
            }
            else
            { // All went fine so we commit the changes.
                conn.commit();
            }
        }
        catch( Exception e )
        {
            returnValue = false;
        }
        finally
        {
            // The old commit mode is returned, assuming it was read
            // successfully.
            if( commitModeChecked )
            {
                try
                {
                    conn.setAutoCommit( oldCommitMode );
                }
                catch( SQLException e )
                { // Nothing can be done here.
                }
            }
            releaseConnection();
        }
        return returnValue;
    }

    /**
     * This function executes an SQL "SELECT" statement and returns a ResultSet
     * type object containing the result received from the database.
     * 
     * @param query
     *            The query to be executed.
     * @return A ResultSet containing the data found from the database.
     * @throws SQLException
     *             Thrown if the database communication fails.
     */
    private ResultSet executeSelectQuery( String query ) throws SQLException
    {
        getConnection();
        boolean ok = true;
        ResultSet rs = null;
        try
        {
            // The old mode is saved.
            boolean oldCommitMode = conn.getAutoCommit();
            // Auto-commit is disabled.
            conn.setAutoCommit( false );

            Statement statement = conn.createStatement();
            rs = statement.executeQuery( query );
            conn.commit();

            // statement.close();
            // statement = null;
        }
        catch( Exception e )
        {
            ok = false;
        }
        finally
        {
            releaseConnection();
        }
        if( !ok )
        {
            throw new SQLException();
        }

        return rs;
    }
}