MailDAO

package mail;

import java.sql.*;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.control.Alert;
import javafx.scene.control.Alert.AlertType;

/* The MailDAO is the most important class in the client application.

 The MailDAO class acts as the data access object for the application.
 All data going into or out of the database passes through this class.
 Naturally, this is where the database connection lives. */
public class MailDAO {

    Connection connection;
    Statement stmt;
    // This is the user id of the user who is currently logged in.
    int user;
    String name;
    ObservableList<Handle> handles;

    public MailDAO() {
        handles = FXCollections.observableArrayList();
    }

    public String getUserName() {
        return name;
    }

    private String getUserNameFromID(int id) {
        String sqlStr = "SELECT name from user where iduser =" + id;
        String userName = null;
        try {
            ResultSet rset = stmt.executeQuery(sqlStr);
            if (rset.next()) {
                userName = rset.getString(1);
            }
        } catch (SQLException ex) {
            userName = null;
        }
        return userName;
    }

    private int getUserIDFromName(String userName) {
        String sqlStr = "SELECT iduser from user where name =\'" + userName + "\'";
        int id = 0;
        try {
            ResultSet rset = stmt.executeQuery(sqlStr);
            if (rset.next()) {
                id = rset.getInt(1);
            }
        } catch (SQLException ex) {
            id = 0;
        }
        return id;
    }

    public boolean logIn(String userName, String password) {
        connect();
        boolean success = false;
        try {
            String query = "select iduser from user where name=\'"
                    + userName + "\' and password=\'" + password + "\'";
            ResultSet rset = stmt.executeQuery(query);

            if (rset.next()) {
                success = true;
                this.user = rset.getInt(1);
                this.name = userName;
                this.refreshHandles();
            }
        } catch (SQLException ex) {
            this.user = 0;
            this.name = null;
        }
        return success;
    }
    String newAcctSQL = "insert into user (name,password) values(?,?)";
    PreparedStatement newAcctStmt;

    public boolean createNewAccount(String userName, String password) {
        connect();
        boolean success = false;
        try {
            newAcctStmt.setString(1, userName);
            newAcctStmt.setString(2, password);
            newAcctStmt.executeUpdate();
            success = true;
            String query = "select iduser from user where name=\'"
                    + userName + "\' and password=\'" + password + "\'";
            ResultSet rset = stmt.executeQuery(query);

            if (rset.next()) {
                this.user = rset.getInt(1);
                this.name = userName;
                this.refreshHandles();
            }
        } catch (SQLException ex) {
            this.user = 0;
            this.name = null;
            success = false;
        }
        return success;
    }

    public Message getMessage(int id) {
        Message result = null;
        try {
            String sqlStr1 = "SELECT sender, subject, body, sent from message where idmessage=" + id;
            ResultSet rset1 = stmt.executeQuery(sqlStr1);

            int sender_id = 0;
            String subject = null;
            String body = null;
            String time = null;
            if (rset1.next()) {
                sender_id = rset1.getInt(1);
                subject = rset1.getString(2);
                body = rset1.getString(3);
                time = rset1.getString(4);
            }

            String sender = this.getUserNameFromID(sender_id);
            result = new Message(sender, this.name, subject, body, time);
        } catch (SQLException ex) {
            showMessageDialog("An SQL error took place." + ex.getMessage());
        }
        return result;
    }

    public void deleteMessage(int id) {
        try {
            String whereClause = "where message=" + id;
            String sqlStr = "delete from recipient " + whereClause
                    + " and recipient=\'" + this.user + "\'";
            stmt.execute(sqlStr);

            // If we just removed the last recipient for this message, remove it from the
            // messages table, too.
            sqlStr = "select recipient from recipient " + whereClause;
            ResultSet results = stmt.executeQuery(sqlStr);
            if (!results.isBeforeFirst()) {
                sqlStr = "delete from message where idmessage=" + id;
                stmt.execute(sqlStr);
            }

            this.refreshHandles();
        } catch (SQLException ex) {
            showMessageDialog("An SQL error took place." + ex.getMessage());
        }
    }
    String insertMsgSQL = "insert into message (subject,body,sender,sent) values (?,?,?,CURDATE())";
    PreparedStatement insertMsgStmt;
    String insertRecipientSQL = "insert into recipient (message,recipient) values (?,?)";
    PreparedStatement insertRecipientStmt;

    public void sendMessage(Message msg) {
        try {
// Data in this message has to be spread over two tables, the messages
// table and the recipients table. We do the insertion into the messages
// table so the database can generate a messageID for this message. We
// then use that messageID to do the insertions into the recipients table.
            insertMsgStmt.setString(1, msg.getSubject());
            insertMsgStmt.setString(2, msg.getContents());
            insertMsgStmt.setInt(3, this.user);
            insertMsgStmt.executeUpdate();
// The id for the new message is generated automatically by the database
// We have to query the database to see what it is.
            String query2 = "select LAST_INSERT_ID()";
            ResultSet rset = stmt.executeQuery(query2);
            if (rset.next()) {
                int messageID = Integer.parseInt(rset.getString(1));
                String[] recipients = msg.getRecipients().split("[ ,]");
                insertRecipientStmt.setInt(1, messageID);
                for (int n = 0; n < recipients.length; n++) {
                    int recipient_id = this.getUserIDFromName(recipients[n]);
                    if (recipient_id != 0) {
                        insertRecipientStmt.setInt(2, recipient_id);
                        insertRecipientStmt.executeUpdate();
                    }
                }
            }
        } catch (SQLException ex) {
            showMessageDialog("An SQL error took place." + ex.getMessage());
        }
    }

    public void logOut() {
        shutDown();
        handles.clear();
        user = 0;
        name = null;
    }

    public ObservableList<Handle> getHandles() { return handles; }

    public void refreshHandles() {
        handles.clear();
        try {
            String sqlString1 = "SELECT message, concat(sender,':',subject) from handle where receiver='" + user + "\'";
            ResultSet rset = stmt.executeQuery(sqlString1);

            while (rset.next()) {
                handles.add(new Handle(rset.getInt(1), rset.getString(2)));
                }
        } catch (SQLException ex) {
            showMessageDialog("Could not fetch handles: " + ex.getMessage());
        }
    }

    private void connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (Exception ex) {
            showMessageDialog("Unable to load JDBC driver. Application will exit.");
            System.exit(0);
        }

        // Establish a connection
        try {
            String url = "jdbc:mysql://localhost:3306/mail?user=root&password=cmsc250";
            connection = DriverManager.getConnection(url);
            stmt = connection.createStatement();
            newAcctStmt = connection.prepareStatement(newAcctSQL);
            insertMsgStmt = connection.prepareStatement(insertMsgSQL);
            insertRecipientStmt = connection.prepareStatement(insertRecipientSQL);
        } catch (SQLException ex) {
            showMessageDialog("Unable to connect to database. Application will exit.");
            System.exit(0);
        }
    }

    private void shutDown() {
        try {
            if(stmt != null) {
                stmt.close();
                stmt = null;
            }
            if(newAcctStmt != null) {
                newAcctStmt.close();
                newAcctStmt = null;
            }
            if(insertMsgStmt != null) {
                insertMsgStmt.close();
                insertMsgStmt = null;
            }
            if(insertRecipientStmt != null) {
                insertRecipientStmt.close();
                insertRecipientStmt = null;
            }
            if(connection != null) {
                connection.close();
                connection = null;
            }
        } catch (Exception e) {
            showMessageDialog("Error on shutdown:" + e.getMessage());
        }
    }

    private void showMessageDialog(String message) {
        Alert alert = new Alert(AlertType.ERROR);
        alert.setTitle("Error");
        alert.setContentText(message);
        alert.showAndWait();
    }
}