@Override
public ManagerPanel getById(int userId) throws DbException {
PreparedStatement ps = null;
ResultSet rs = null;
ManagerPanel managerPanel = null;
try {
managerPanel = new ManagerPanel();
prepareConnection();
ps = connection.prepareStatement(MANAGER_SELECT_BY_ID);
ps.setInt(1, userId);
rs = ps.executeQuery();
rs.next();
Money finresult = Money.dollars(rs.getDouble("summaryfinresult"));
managerPanel.setSummaryFinRes(finresult);
} catch (SQLException e) {
throw new DbException("Can't execute SQL = '" + MANAGER_SELECT_BY_ID + "'", e);
} finally {
daoHelper.closeDataBaseEntities(ps, rs, connection);
}
return managerPanel;
}
@Override
public long insert(ManagerPanel panel, int userId) throws DbException {
PreparedStatement ps = null;
long result;
try {
prepareConnection();
ps = connection.prepareStatement(MANAGER_INSERT, Statement.RETURN_GENERATED_KEYS);
ps.setInt(1, userId);
ps.setDouble(2, panel.getSummaryFinRes().getAmount());
ps.executeUpdate();
try (ResultSet rs = ps.getGeneratedKeys()) {
if (rs.next()) {
result = rs.getLong(1);
} else {
throw new DbException(
"Inserting failed on query: '" + MANAGER_INSERT + "' no ID obtained.");
}
}
} catch (SQLException e) {
throw new DbException("Can't execute SQL = '" + MANAGER_INSERT + "'", e);
} finally {
daoHelper.closeDataBaseEntities(ps, null, connection);
}
return result;
}
@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 List<User> getAll() throws DbException {
PreparedStatement ps = null;
ResultSet rs = null;
List<User> users = new ArrayList<>();
try {
prepareConnection();
ps = connection.prepareStatement(USERS_SELECT_ALL);
rs = ps.executeQuery();
while (rs.next()) {
User tempUser = new User();
int userId = rs.getInt("id");
String firstName = rs.getString("firstname");
String lastName = rs.getString("lastname");
String nickName = rs.getString("nickname");
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"));
tempUser.setUserId(userId);
tempUser.setFirstName(firstName);
tempUser.setLastName(lastName);
tempUser.setNickName(nickName);
tempUser.setEmail(email);
tempUser.setPassword(password);
tempUser.setRoleId(roleId);
tempUser.setHiredDay(hDite);
tempUser.setFiredDay(fDite);
tempUser.setUserNote(userNotes);
tempUser.setStatus(status);
tempUser.setState(state);
users.add(tempUser);
}
} catch (SQLException e) {
throw new DbException("Can't execute SQL = '" + USERS_SELECT_ALL + "'", e);
} finally {
daoHelper.closeDataBaseEntities(ps, rs, connection);
}
return users;
}
@Override
public void deleteById(int userId) throws DbException {
PreparedStatement ps = null;
try {
prepareConnection();
ps = connection.prepareStatement(MANAGER_DELETE_BY_ID);
ps.setInt(1, userId);
ps.executeUpdate();
} catch (SQLException e) {
throw new DbException("Can't execute SQL = '" + MANAGER_DELETE_BY_ID + "'", e);
} finally {
daoHelper.closeDataBaseEntities(ps, null, connection);
}
}
@Override
public void update(ManagerPanel panel, int userId) throws DbException {
PreparedStatement ps = null;
try {
prepareConnection();
ps = connection.prepareStatement(MANAGER_UPDATE);
ps.setDouble(1, panel.getSummaryFinRes().getAmount());
ps.setInt(2, userId);
ps.executeUpdate();
} catch (SQLException e) {
throw new DbException("Can't execute SQL = '" + MANAGER_UPDATE + "'", e);
} finally {
daoHelper.closeDataBaseEntities(ps, null, connection);
}
}
@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;
}
/**
* Returns a JDBC template for performing database operations.
*
* @return A JDBC template.
*/
public JdbcTemplate getJdbcTemplate() {
return daoHelper.getJdbcTemplate();
}
private void prepareConnection() {
daoHelper = new DaoHelper();
daoHelper.initConn();
connection = daoHelper.getConnection();
}
public UserDaoImpl() {
daoHelper = new DaoHelper();
this.connection = daoHelper.getConnection();
}