public List<ClientBean> getClients() {
String query = "select id, firstname, lastname, email, phonenumber from clients;";
Statement statement = null;
try {
statement = connection.createStatement();
final ResultSet resultSet = statement.executeQuery(query);
List<ClientBean> result = new ArrayList<>();
boolean hasNext = resultSet.first();
while (hasNext) {
int id = resultSet.getInt(1);
ClientBean client = new ClientBean(id);
String firstName = resultSet.getString(1);
client.setFirstName(firstName);
String lastName = resultSet.getString(2);
client.setLastName(lastName);
String email = resultSet.getString(3);
client.setEmail(email);
String phoneNumber = resultSet.getString(4);
client.setPhone(phoneNumber);
result.add(client);
hasNext = resultSet.next();
}
return result;
} catch (SQLException e) {
e.printStackTrace();
}
return new ArrayList<>();
}
/**
* Add client record to persistent store.
*
* @param toAdd client to add
* @return id of added client, or -1 if an error occurred
*/
public int addClient(ClientBean toAdd) {
String insertStatement =
"insert into clients (firstname, lastname, email, phonenumber) values (?,?,?,?);";
try {
PreparedStatement statement =
connection.prepareStatement(insertStatement, Statement.RETURN_GENERATED_KEYS);
statement.setNString(1, toAdd.getFirstName());
statement.setNString(2, toAdd.getLastName());
statement.setNString(3, toAdd.getEmail());
statement.setNString(4, toAdd.getPhone());
statement.executeUpdate();
ResultSet generatedKeys = statement.getGeneratedKeys();
if (generatedKeys.first()) {
return generatedKeys.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return -1;
}
public ClientBean getClient(int id) {
String query = "select id, firstname, lastname, email, phonenumber from clients where id=?;";
try {
final PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setInt(1, id);
final ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.first()) {
ClientBean client = new ClientBean(id);
String firstName = resultSet.getString(2);
client.setFirstName(firstName);
String lastName = resultSet.getString(3);
client.setLastName(lastName);
String email = resultSet.getString(4);
client.setEmail(email);
String phoneNumber = resultSet.getString(5);
client.setPhone(phoneNumber);
return client;
}
} catch (SQLException e) {
e.printStackTrace();
}
return new ClientBean();
}
