@Override
public void addPersonToDb(Person newPerson) throws AddPersonException {
try (Connection cn = DatabaseUtil.getConnection()) {
String queryString = "INSERT INTO PHONEBOOK (NAME, PHONENUMBER, ADDRESS) VALUES(?, ?, ?)";
PreparedStatement stmt = cn.prepareStatement(queryString);
stmt.setString(1, newPerson.getName());
stmt.setString(2, newPerson.getPhoneNumber());
stmt.setString(3, newPerson.getAddress());
stmt.executeUpdate();
cn.commit();
} catch (SQLException e) {
e.printStackTrace();
throw new AddPersonException("sql Exception");
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
throw new AddPersonException("sql Exception");
}
}
/** synchronize phonebook list from database records */
public Map<String, Person> getPeopleFromDb() {
try (Connection cn = DatabaseUtil.getConnection();
Statement stmt = cn.createStatement()) {
String queryString = "SELECT NAME, PHONENUMBER, ADDRESS FROM PHONEBOOK";
ResultSet rs = stmt.executeQuery(queryString);
while (rs.next()) {
Person newPerson =
new Person(rs.getString("NAME"), rs.getString("PHONENUMBER"), rs.getString("ADDRESS"));
addPersonToCache(newPerson);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return people;
}
/** generate Phonebook output string from database Why not use list? */
@Override
public String toStringFromDb() {
String str = "";
try (Connection cn = DatabaseUtil.getConnection();
Statement stmt = cn.createStatement()) {
String queryString = "SELECT NAME, PHONENUMBER, ADDRESS FROM PHONEBOOK";
ResultSet rs = stmt.executeQuery(queryString);
while (rs.next()) {
Person newPerson =
new Person(rs.getString("NAME"), rs.getString("PHONENUMBER"), rs.getString("ADDRESS"));
if (!str.isEmpty()) str += "\n";
str += newPerson.toString();
}
// cn.close(); handled by try with catch
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return str;
}
/**
* find person using name (firstname + lastName) using Phonebook database perhaps use phonebook
* list?
*/
@Override
public Person findPersonFromDb(String firstName, String lastName) {
Person newPerson = new Person();
try (Connection cn = DatabaseUtil.getConnection()) {
String queryString = "SELECT NAME, PHONENUMBER, ADDRESS FROM PHONEBOOK WHERE NAME = ?";
PreparedStatement stmt = cn.prepareStatement(queryString);
stmt.setString(1, firstName + " " + lastName);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
newPerson.setName(rs.getString("NAME"));
newPerson.setPhoneNumber(rs.getString("PHONENUMBER"));
newPerson.setAddress(rs.getString("ADDRESS"));
} else newPerson = null;
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
System.out.println("findPerson failed");
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("findPerson failed");
e.printStackTrace();
}
return newPerson;
}
