0
\$\begingroup\$

First things first, I made a DatabaseConnection-class which connects to the database like so:

public class DatabaseConnection {
private Connection con;


public Connection getConnection() {
    try {
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/todolistdb",
                "root", "x");
    } catch (SQLException e) {
        e.printStackTrace();
        System.out.println("Failed to create database connection!");
    }
    return con;
}

And then I have this in my main class:

public static void main(String args[]) throws SQLException {
DatabaseConnection dbConn = new DatabaseConnection();
new UI(dbConn).start();

I did it like so, because I had a previous iteration of this app where I had to use a Storage-class in multiple places and I implemented it like so. (Passing the DBConn as a parameter to UI.)

All in all I’d like to get some feedback on the ToDoListDAO class and how I dealt with the database connection as a whole. This is my first time connecting to a database and I am quite proud of myself, but I want to know what can be done to improve it.

Also I am updating the database right after the user enters a new item to be added to the to-do list. Would I be better off gathering them to a data structure, e.g. a list, and update the database when the program is quit?

Everything aside from what I’ve specifically asked about is more than welcome as well!

ToDoListDAO:

public class ToDoListDAO {

private ToDoList list = new ToDoList();
private ArrayList<String> fromDB;
private Connection con;
private PreparedStatement stmt;
private ResultSet rs;
private DatabaseConnection dbCon;

public ToDoListDAO(DatabaseConnection DBConn) throws SQLException {
    this.dbCon = DBConn;
    this.fromDB = new ArrayList<>();
    this.fromDB = getFromDB();
}


public ToDoList returnList() {
    for (String line : fromDB) {
        ToDo todo = new ToDo(line);
        list.addToDo(todo);
    }
    return this.list;
}


public ArrayList<String> getFromDB() throws SQLException {
    try {
        this.con = dbCon.getConnection();
        this.stmt = con.prepareStatement("select * from todolist");
        this.rs = stmt.executeQuery();

        while (rs.next()) {
            fromDB.add(rs.getString("task"));
        }
    } finally {

        rs.close();
        stmt.close();
        con.close();
    }
    return fromDB;
}

public void addToDB(String task) {
    try {
        this.con = dbCon.getConnection();
        this.stmt = con.prepareStatement("INSERT INTO todolist(task) VALUES " + "(" + "\"" + task +"\")");
        stmt.executeUpdate();

        stmt.close();
        con.close();
    } catch (SQLException SQLe) {
        SQLe.printStackTrace();
    }
}

public void deleteFromDB(String task) {
    try {
        this.con = dbCon.getConnection();
        this.stmt = con.prepareStatement("DELETE FROM todolist WHERE task = " + "\"" + task + "\"");
        stmt.executeUpdate();

        stmt.close();
        con.close();
    } catch (SQLException SQLe) {
        SQLe.printStackTrace();
    }
}

And finally here's the current UI:

public class UI {

private final Scanner reader;
private ToDoList list;
private ToDoListDAO tdldao;

public UI(DatabaseConnection DBConn) throws SQLException {
    this.tdldao = new ToDoListDAO(DBConn);
    this.reader = new Scanner(System.in);
    this.list = tdldao.returnList();
}

public void start() {
    this.list.printToDoList();
    while (true) {
        System.out.println("");
        System.out.println("1: Add a to-do item.");
        System.out.println("2. Remove a to-do item.");
        System.out.println("3. Print a list of my to-do items.");
        System.out.println("4. Quit and save");
        System.out.print("Type the number of desired action: ");

        String input = reader.nextLine();

        if (input.equals("4")) {
            System.out.println("Quitting!");
            break;
        } else if (input.equals("1")) {
            System.out.println("What would you like to add?");
            String add = reader.nextLine();
            ToDo item = new ToDo(add);
            this.list.addToDo(item);
            this.tdldao.addToDB(item.toString());
        }

        else if (input.equals("2")) {
            if (this.list.getList().size() < 1) {
                System.out.println();
                System.out.println("Nothing to delete!");
            }
            else {
                System.out.print("Type the index of the item you wish to remove: ");
                try {
                    int remove = Integer.parseInt(reader.nextLine());
                    this.tdldao.deleteFromDB(this.list.getList().get(remove - 1).toString());
                    this.list.removeToDo(remove);
                } catch (NumberFormatException nfe) {
                    System.out.println("Please enter a number.");
                } catch (IndexOutOfBoundsException ib) {
                    System.out.println("Please enter a number between or equal to 1 or " + this.list.getList().size() + ".");
                }
            }
        }

        else if (input.equals("3")) {
            System.out.println("");
            this.list.printToDoList();
        }
    }
}
\$\endgroup\$

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.