@Override
public void update(User user) throws DbException {
PreparedStatement ps = null;
try {
prepareConnection();
ps = connection.prepareStatement(USERS_UPDATE);
ps.setString(1, user.getFirstName());
ps.setString(2, user.getLastName());
ps.setString(3, user.getNickName());
ps.setString(4, user.getEmail());
ps.setInt(5, user.getRoleId());
ps.setString(6, user.getPassword());
ps.setDate(7, user.getHiredDay());
ps.setDate(8, user.getFiredDay());
ps.setString(9, user.getUserNote());
ps.setString(10, user.getStatus().toDataBaseVal());
ps.setString(11, user.getState().toDataBaseVal());
ps.setInt(12, user.getUserId());
ps.executeUpdate();
} catch (SQLException e) {
throw new DbException("Can't execute SQL = '" + USERS_UPDATE + "'", e);
} finally {
daoHelper.closeDataBaseEntities(ps, null, connection);
}
}
@Override
public User getByNickName(String nickName) throws DbException {
PreparedStatement ps = null;
ResultSet rs = null;
User user = null;
try {
prepareConnection();
ps = connection.prepareStatement(USERS_SELECT_BY_NICKNAME);
ps.setString(1, nickName);
rs = ps.executeQuery();
rs.next();
int id = rs.getInt("id");
String firstName = rs.getString("firstName");
String lastName = rs.getString("lastName");
String email = rs.getString("email");
String password = rs.getString("userpassword");
int roleId = rs.getInt("roleid");
Date hDite = rs.getDate("hiredday");
Date fDite = rs.getDate("firedday");
String userNotes = rs.getString("usernote");
UserStatus status = UserStatus.fromDataBaseVal(rs.getString("userstatus"));
UserState state = UserState.fromDataBaseVal(rs.getString("userstate"));
user = new User();
user.setUserId(id);
user.setFirstName(firstName);
user.setLastName(lastName);
user.setNickName(nickName);
user.setEmail(email);
user.setPassword(password);
user.setRoleId(roleId);
user.setHiredDay(hDite);
user.setFiredDay(fDite);
user.setUserNote(userNotes);
user.setStatus(status);
user.setState(state);
} catch (SQLException e) {
throw new DbException("Can't execute SQL = '" + USERS_SELECT_BY_NICKNAME + "'", e);
} finally {
daoHelper.closeDataBaseEntities(ps, rs, connection);
}
return user;
}
@Override
public long insert(User user) throws DbException {
PreparedStatement ps = null;
long result;
try {
prepareConnection();
ps = connection.prepareStatement(USERS_INSERT, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, user.getFirstName());
ps.setString(2, user.getLastName());
ps.setString(3, user.getNickName());
ps.setString(4, user.getEmail());
ps.setInt(5, user.getRoleId());
ps.setString(6, user.getPassword());
ps.setDate(7, user.getHiredDay());
ps.setDate(8, user.getFiredDay());
ps.setString(9, user.getUserNote());
ps.setString(10, user.getStatus().toDataBaseVal());
ps.setString(11, user.getState().toDataBaseVal());
ps.executeUpdate();
try (ResultSet rs = ps.getGeneratedKeys()) {
if (rs.next()) {
result = rs.getLong(1);
} else {
throw new DbException(
"Inserting failed on query: '" + USERS_INSERT + "' no ID obtained.");
}
}
} catch (SQLException e) {
throw new DbException("Can't execute SQL = '" + USERS_INSERT + "'", e);
} finally {
daoHelper.closeDataBaseEntities(ps, null, connection);
}
return result;
}