@Override
public boolean deleteAll(List<Role> entities) throws SQLException {
boolean retval = false;
try {
con = new DBConnection();
if (con.connect()) {
Iterator<Role> iterator = entities.iterator();
while (iterator.hasNext()) {
Role entity = iterator.next();
cstmt = (CallableStatement) con.getConnection().prepareCall("{call sp_del_role(?)}");
cstmt.setString(1, entity.getRolename());
con.customQuery(cstmt);
}
}
retval = true;
} catch (ClassNotFoundException ex) {
logger.log(Priority.ERROR, ex.toString());
} catch (SQLException ex) {
throw ex;
} finally {
con.disconnect();
}
return retval;
}
@Override
public boolean delete(String key) throws SQLException {
boolean retval = false;
ResultSet rs = null;
try {
con = new DBConnection();
if (con.connect()) {
cstmt = (CallableStatement) con.getConnection().prepareCall("{call sp_del_role(?)}");
cstmt.setString(1, key);
rs = con.customQuery(cstmt);
}
retval = true;
} catch (ClassNotFoundException ex) {
logger.log(Priority.ERROR, ex.toString());
} catch (SQLException ex) {
throw ex;
} finally {
con.disconnect();
}
return retval;
}
public UserRole findUserRoleByUserId(int key) throws SQLException {
UserRole retval = null;
ResultSet rs = null;
PreparedStatement pstmt = null;
try {
con = new DBConnection();
if (con.connect()) {
String sql = "select * from userrole where userid=?";
pstmt = con.getConnection().prepareStatement(sql);
pstmt.setInt(1, key);
rs = con.customQuery(pstmt);
while (rs.next()) {
UserRole userrole = new UserRole();
userrole.setRolename(rs.getString("rolename"));
userrole.setUserid(rs.getInt("userid"));
userrole.setUsername(rs.getString("username"));
retval = userrole;
}
}
} catch (ClassNotFoundException ex) {
logger.log(Priority.ERROR, ex.toString());
} catch (SQLException ex) {
throw ex;
} finally {
con.disconnect();
}
return retval;
}
@Override
public Role findById(String key) throws SQLException {
Role retval = null;
ResultSet rs = null;
PreparedStatement pstmt = null;
try {
con = new DBConnection();
if (con.connect()) {
String sql = "select * from role where rolename=?";
pstmt = con.getConnection().prepareStatement(sql);
pstmt.setString(1, key);
rs = con.customQuery(pstmt);
while (rs.next()) {
Role role = new Role();
role.setRolename(rs.getString("rolename"));
role.setDescription(rs.getString("description"));
retval = role;
}
}
} catch (ClassNotFoundException ex) {
logger.log(Priority.ERROR, ex.toString());
} catch (SQLException ex) {
throw ex;
} finally {
con.disconnect();
}
return retval;
}
@Override
public void saveOrUpdateAll(List<Role> entities) throws SQLException {
ResultSet rs = null;
try {
con = new DBConnection();
if (con.connect()) {
Iterator<Role> iterator = entities.iterator();
while (iterator.hasNext()) {
Role entity = iterator.next();
if (entity.getRolename() != null) {
cstmt = (CallableStatement) con.getConnection().prepareCall("{call sp_upd_role(?)}");
} else {
cstmt = (CallableStatement) con.getConnection().prepareCall("{call sp_ins_role(?,?)}");
}
cstmt.setString("p_rolename", entity.getRolename());
cstmt.setString("p_description", entity.getDescription());
rs = con.saveOrUpdate(cstmt);
}
}
} catch (ClassNotFoundException ex) {
logger.log(Priority.ERROR, ex.toString());
} catch (SQLException e) {
throw e;
} finally {
cstmt.close();
con.disconnect();
}
}
@Override
public String saveOrUpdate(Role entity) throws SQLException {
ResultSet rs = null;
String retval = null;
try {
con = new DBConnection();
if (con.connect()) {
if (entity.getRolename() != null) {
cstmt = (CallableStatement) con.getConnection().prepareCall("{call sp_upd_role(?)}");
} else {
cstmt = (CallableStatement) con.getConnection().prepareCall("{call sp_ins_role(?,?)}");
}
cstmt.setString("p_rolename", entity.getRolename());
cstmt.setString("p_description", entity.getDescription());
rs = con.saveOrUpdate(cstmt);
}
} catch (ClassNotFoundException ex) {
logger.log(Priority.ERROR, ex.toString());
} catch (SQLException e) {
throw e;
} finally {
cstmt.close();
con.disconnect();
}
return entity.getRolename();
}
@Override
public boolean assignRole(String rolename, Integer userid) throws SQLException {
boolean retval = false;
ResultSet rs = null;
try {
con = new DBConnection();
if (con.connect()) {
cstmt =
(CallableStatement) con.getConnection().prepareCall("{call sp_ins_userrole(?,?,?)}");
cstmt.setString("p_rolename", rolename);
cstmt.setInt("p_userid", userid);
UserDAOImpl userDao = new UserDAOImpl();
cstmt.setString("p_username", userDao.findById(userid).getUsername());
rs = con.saveOrUpdate(cstmt);
}
retval = true;
} catch (ClassNotFoundException ex) {
logger.log(Priority.ERROR, ex.toString());
} catch (SQLException ex) {
throw ex;
} finally {
con.disconnect();
}
return retval;
}
@Override
public List<Role> getAll() throws SQLException {
List<Role> retval = new ArrayList<Role>();
ResultSet rs = null;
try {
con = new DBConnection();
if (con.connect()) {
cstmt = (CallableStatement) con.getConnection().prepareCall("{call sp_sel_role()}");
rs = con.customQuery(cstmt);
while (rs.next()) {
Role role = new Role();
role.setRolename(rs.getString("rolename"));
role.setDescription(rs.getString("description"));
retval.add(role);
}
}
} catch (ClassNotFoundException ex) {
logger.log(Priority.ERROR, ex.toString());
} catch (SQLException ex) {
throw ex;
} finally {
con.disconnect();
}
return retval;
}