dbaccess.java

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

package com.forum.nokia.taskmanager.database;


import java.sql.Connection;
import java.sql.DriverManager;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import com.forum.nokia.taskmanager.beans.Group;
import com.forum.nokia.taskmanager.beans.Task;
import com.forum.nokia.taskmanager.beans.User;

/**
 * This class handles the connection to the MySQL database. It is used directly
 * by the Controller to perform database duties as well as by the beans to get
 * data from the database to be shown on the Java Server Pages. The beans are
 * merely an interface to access this object from the JSP side of the system.
 */
public class DBAccess
{
    // !!! CHANGE THESE !!!
    // The application username to your database (default "taskman").
    private static final String USERNAME = "<database username>";

    // The password of the user specified above.
	private static final String PASSWORD = "<database password>";

    // The URL to the database, must start with "jdbc:mysql://" so that
    // DriverManager knows you're trying to establish a JDBC connection through
    // a MySQL driver. The URL should be in the following form:
    // "jdbc:mysql://name.of.server.com/name_of_database"
    
    private static final String DATABASEURL = "jdbc:mysql://<your database server>:<port>/taskdb";
	
    private Connection conn = null;

    private boolean connIsFree = true;

    /**
     * The constructor opens a connection to the MySQL database.
     * 
     * @throws Exception
     *             An Exception is thrown if opening the connection fails.
     */
    public DBAccess() throws Exception
    {
        try
        {
            Class.forName( "com.mysql.jdbc.Driver" ).newInstance();
        }
        catch( Exception e )
        { // Can be thrown when loading MySQL-driver
            throw new Exception();
        }

        try
        { // a new connection to the database is opened.
            connect();
        }
        catch( SQLException e )
        {
            throw new Exception();
        }
    }

    /**
     * Connection is reserved for the transaction. If the connection is in use,
     * the thread stops to wait for its turn. This must be done because the
     * database connection is shared between multiple threads.
     * 
     * @return The Connection to the database.
     */
    private synchronized Connection getConnection()
    {
        while( !connIsFree )
        {
            try
            {
                wait();
            }
            catch( InterruptedException e )
            {
            }
        }
        connIsFree = false;
        notify();
        return conn;
    }

    /**
     * The connection is freed. If the variable connIsFree is marked as false,
     * this function was called "illegally" and so it must wait for the
     * connection to actually become free before declaring it free. This is to
     * ensure the integrity of the algorithm is maintained.
     */
    private synchronized void releaseConnection()
    {
        while( connIsFree )
        {
            try
            {
                wait();
            }
            catch( InterruptedException e )
            {
            }
        }
        connIsFree = true;
        notify();
    }

    /**
     * This function tests if the connection is still alive. MySQL disconnects
     * all connections after they've idled for 8 hours so we must test that the
     * connection is still alive before making actual queries.
     * 
     * @return True if the connection is alive, otherwise false.
     */
    public boolean testConnection()
    {
        // a "random" select query that is guaranteed to return a non-empty
        // resultset. The "roles" table is established when configuring the
        // system so it should always be non-empty.
        try
        {
            ResultSet rs = executeSelectQuery( "SELECT * FROM roles" );
            if( !rs.next() )
            { // Shouldn't actually happen at all, but if it does, the example
                // hasn't been configured correctly and should never go past
                // this
                // code line.
                return false;
            }
        }
        catch( SQLException e )
        { // if the connection is down, the select query execution throws an
            // exception.
            return false;
        }
        return true;
    }

    /**
     * This function gets a new connection to the database.
     * 
     * @throws SQLException
     *             If the connection forming fails.
     */
    public void connect() throws SQLException
    {
        Properties prop = new Properties();
        prop.put( "charSet", "ISO-8859-15" );
        prop.put( "characterEncoding", "ISO-8859-15" );
        prop.put( "useUnicode", "true" );
        prop.put( "user", USERNAME );
        prop.put( "password", PASSWORD );

        // throws an SQLException if it fails. This is handled in the
        // Controller.
        conn = DriverManager.getConnection( DATABASEURL, prop );
    }

    /**
     * Terminates the connection to the database.
     */
    public void terminateConnection()
    {
        try
        {
            if( conn != null )
            {
                conn.close();
            }
        }
        catch( SQLException e )
        {
        }
    }

    /**
     * Validates the user's username and password from the database.
     * 
     * @param username
     *            The user's username as String.
     * @param password
     *            The user's password as String.
     * @return A struct containing info about the user.
     * @throws SQLException
     *             Thrown if something goes wrong while communicating with the
     *             database.
     */
    public UserInfo validateUser( String username, String password )
            throws SQLException
    {
        // The connection is reserved for this action to avoid concurrency
        // problems.
        getConnection();

        UserInfo userInfo = null;
        try
        {
            userInfo = new UserInfo();

            // This SQL query retrieves user id number, login name and role from
            // the database
            // or returns an empty set if the username/password pair can't be
            // found.
            String query = "SELECT user.id, userrolemap.roleid, user.login"
                    + " FROM user, userrolemap"
                    + " WHERE userrolemap.userid = user.id" + " AND login = '"
                    + username + "'" + " AND password = Password('" + password
                    + "')";

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

            if( !rs.next() )
            { // the ResultSet was empty -> invalid user.
                userInfo.userValid = false;
                return userInfo;
            }

            // Otherwise the login was succesful and we need to set the
            // variables.
            userInfo.userValid = true;
            userInfo.userId = rs.getString( "id" );
            userInfo.userRole = rs.getString( "roleid" );
        }
        catch( SQLException e )
        {
            releaseConnection();
            throw e;
        }
        finally
        {
            // The connection is released, even if an exception is thrown.
            releaseConnection();
        }

        return userInfo;
    }

    /**
     * Adds a new task to the database, for a single user.
     * 
     * @param owner_id
     *            Id of the owner of the task.
     * @param description
     *            Description of the task.
     * @param state
     *            The default state of the new task.
     * @return True if the adding succeeded, otherwise false.
     */
    public boolean addNewTask( String owner_id, String description, String state )
    {
        String[] sqls = new String[2];

        // first SQL command.
        sqls[0] = "INSERT INTO task (owner, description, state) " + "VALUES ("
                + owner_id + ",'" + description + "','" + state + "')";

        // second SQL command.
        sqls[1] = "UPDATE user SET tasks_up_to_date = 'no' where id = "
                + owner_id;

        return executeUpdateQuery( sqls );
    }

    /**
     * Retrieves tasks for all users from the database.
     * 
     * @return A list of tasks.
     */
    public List getTasks()
    {
        List tasks = new ArrayList();
        try
        {
            ResultSet rs = executeSelectQuery( "SELECT task.id, task.description, task.state, user.id, user.login "
                    + "FROM user,task WHERE task.owner=user.id" );

            while( rs.next() )
            {
                Task task = new Task( /* owner name */rs
                        .getString( "user.login" ),
                /* owner id */rs.getString( "user.id" ),
                /* task description */rs.getString( "task.description" ),
                /* task state */rs.getString( "task.state" ),
                /* task id */rs.getString( "task.id" ) );
                tasks.add( task );
            }
        }
        catch( Exception e )
        {
            tasks.clear();
        }

        return tasks;
    }

    /**
     * Retrieves the tasks for a single user.
     * 
     * @param user_id
     *            The id-number of the user.
     * @return A list of tasks.
     */
    public List getTasks( String user_id )
    {
        List tasks = new ArrayList();
        try
        {
            ResultSet rs = executeSelectQuery( "SELECT id, description, state "
                    + "FROM task WHERE owner = " + user_id );

            while( rs.next() )
            {
                Task task = new Task( /* owner name */"",
                /* owner id */"",
                /* task description */rs.getString( "description" ),
                /* task state */rs.getString( "state" ),
                /* task id */rs.getString( "id" ) );
                tasks.add( task );
            }
        }
        catch( Exception e )
        {
        }

        return tasks;
    }

    /**
     * Changes a single tasks attributes.
     * 
     * @param ownerId
     *            The new owner id.
     * @param description
     *            The new description.
     * @param state
     *            The new state.
     * @param taskId
     *            The id that identifies the task.
     * @return True if that password was valid, otherwise false.
     */
    public boolean changeTask( String ownerId, String description,
            String state, String taskId )
    {
        String[] sqls = new String[2];

        // First SQL command.
        sqls[0] = "UPDATE task SET owner = " + ownerId + ", description = '"
                + description + "', state = '" + state + "' WHERE id = "
                + taskId;

        // Second SQL command.
        sqls[1] = "UPDATE user SET tasks_up_to_date = 'no' where id = "
                + ownerId;

        return executeUpdateQuery( sqls );
    }

    /**
     * Marks a task done in the database.
     * 
     * @param task_id
     *            The id of the task.
     * @return True if the operation was successful, otherwise false.
     */
    public boolean markTaskDone( String task_id )
    {
        String[] sqls = new String[1];
        sqls[0] = "UPDATE task SET state = 'DONE' WHERE id = " + task_id;

        return executeUpdateQuery( sqls );
    }

    /**
     * Marks the users tasks as up to date.
     * 
     * @param userId
     *            The id of the user.
     * @return True if the operation was successful, otherwise false.
     */
    public boolean markTasksUpToDate( String userId )
    {
        String[] sqls = new String[1];
        sqls[0] = "UPDATE user SET tasks_up_to_date = 'yes' WHERE id = "
                + userId;

        return executeUpdateQuery( sqls );
    }

    /**
     * Removes a task from the database.
     * 
     * @param taskId
     *            Id number of the task to be removed.
     * @param ownerId
     *            Id number of the owner of the task.
     * @return True if the removing succeeded, otherwise false.
     */
    public boolean deleteTask( String taskId, String ownerId )
    {
        String[] sqls = new String[2];

        // First SQL command.
        sqls[0] = "DELETE FROM task WHERE id = " + taskId;

        // Second SQL command.
        sqls[1] = "UPDATE user SET tasks_up_to_date = 'no' WHERE id = "
                + ownerId;

        return executeUpdateQuery( sqls );
    }

    /**
     * Returns a list of users that can have tasks, retrieved from the database.