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