@Override
public Set<UserRole> getAllRoles(String sessID)
throws RemoteException, SQLException, SecurityException, SessionExpiredException {
String thisDatabase = SessionManager.getInstance().getDatabaseForSession(sessID);
TableSchema roleTable = MedSavantDatabase.UserRoleTableSchema;
SelectQuery sq = new SelectQuery();
sq.addFromTable(roleTable.getTable());
sq.addColumns(
roleTable.getDBColumn(MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ID),
roleTable.getDBColumn(MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ROLENAME),
roleTable.getDBColumn(
MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ROLE_DESCRIPTION));
sq.setIsDistinct(true);
ResultSet rs = null;
try {
rs = ConnectionController.executeQuery(sessID, sq.toString());
Set<UserRole> roleSet = new TreeSet<UserRole>();
while (rs.next()) {
int roleId = rs.getInt(1);
String roleName = rs.getString(2);
String roleDescription = rs.getString(3);
roleSet.add(new UserRole(roleId, roleName, roleDescription, thisDatabase));
}
return roleSet;
} finally {
if (rs != null) {
rs.close();
}
}
}
/** {@inheritDoc} */
@Override
public SelectQuery getQuery() {
SelectQuery selectQuery = new SelectQuery();
List<DbColumn> columns = dbTable.getColumns();
if (!columns.isEmpty()) {
selectQuery.addColumns(columns.toArray(new Column[columns.size()]));
} else {
selectQuery.addAllTableColumns(dbTable);
}
selectQuery.addFromTable(dbTable);
if (sort != null) {
selectQuery.addOrderings(sort);
}
applyFilters(selectQuery);
query = selectQuery;
return query;
}
private Set<UserRole> getRolesForUser(String sessID, String user)
throws RemoteException, SQLException, SessionExpiredException {
String database = SessionManager.getInstance().getDatabaseForSession(sessID);
TableSchema roleTable = MedSavantDatabase.UserRoleTableSchema;
TableSchema roleATable = MedSavantDatabase.UserRoleAssignmentTableSchema;
SelectQuery sq = new SelectQuery();
sq.addColumns(
roleTable.getDBColumn(MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ID),
roleTable.getDBColumn(MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ROLENAME),
roleTable.getDBColumn(
MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ROLE_DESCRIPTION));
Condition joinCondition =
BinaryCondition.equalTo(
roleTable.getDBColumn(MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ID),
roleATable.getDBColumn(
MedSavantDatabase.UserRoleAssignmentTableSchema.COLUMNNAME_OF_ROLE_ID));
sq.addJoin(
SelectQuery.JoinType.INNER, roleTable.getTable(), roleATable.getTable(), joinCondition);
sq.addCondition(
BinaryCondition.equalTo(
roleATable.getDBColumn(
MedSavantDatabase.UserRoleAssignmentTableSchema.COLUMNNAME_OF_USERNAME),
user));
ResultSet rs = null;
try {
rs = ConnectionController.executeQuery(sessID, sq.toString());
Set<UserRole> roleSet = new HashSet<UserRole>();
while (rs.next()) {
int roleId = rs.getInt(1);
String roleName = rs.getString(2);
String roleDescription = rs.getString(3);
roleSet.add(new UserRole(roleId, roleName, roleDescription, database));
}
return roleSet;
} finally {
if (rs != null) {
rs.close();
}
}
}