最近研究 cognos 8.4 和 已有系统的人员组织架构整合 ,采用jdbc 的验证方法, 网上到处都只是登录验证的方法 ,sdk的例子又超级稀少 ,写的我非常头疼 , 看了很多遍代码 (包括反编译的)以后 ,奋战了 一周 (总共 10来个小时以后) ,终于有点成果。
主要修改了 JDBCSample 里面 QueryUtil 中 的query 方法 ,这个方法主要被调用于 用户, 角色 ,组 的查询 , 单个节点信息查询 ,以及 目录路径的查询 等 。
// 查询该命名空间的人员
public static void query(DB2_JDBCDriver theDriver,
Connection theConnection, String theSqlCondition,
IQueryOption theQueryOption, String[] theProperties,
ISortProperty[] theSortProperties, QueryResult theResult,
INamespace theNamespace, String parentObjectID, int searchType)
throws SQLException {
StringBuffer sqlStatement = new StringBuffer();
sqlStatement
.append(" select * from (SELECT user_id uid, user_name name, 1 issqluser, 0 issqlrole ,0 issqlfolder,ORG_ID pid FROM VW_CAS_USERS_BASE_T where user_src='AD' ");
if (searchType == SearchAxis.Child) {// 取得子节点
// 父节点ID
if (parentObjectID == null) {
sqlStatement.append(" and ORG_ID is null ");
} else {
if (parentObjectID.startsWith("f:")) {
sqlStatement.append(" and ORG_ID = "
+ parentObjectID.substring(2));
} else {
sqlStatement.append(" and 1=0 ");
}
}
} else if (searchType == SearchAxis.Self) {// 取得本节点
// 父节点ID
if (parentObjectID == null) {
sqlStatement.append(" and user_id is null ");
} else {
if (parentObjectID.startsWith("u:")) {// 用户
sqlStatement.append(" and user_id = "
+ parentObjectID.substring(2));
} else {
sqlStatement.append(" and 1=0 ");
}
}
}
sqlStatement.append(" union all ");
sqlStatement
.append(" select org_id uid ,org_name name , 0 issqluser, 0 issqlrole ,1 issqlfolder, PARENT_ORG_ID pid from VW_CAS_ORGS where org_src='AD' ");
if (searchType == SearchAxis.Child) {// 取得子节点
// 父节点ID
if (parentObjectID == null) {
sqlStatement.append(" and PARENT_ORG_ID is null ");
} else {
if (parentObjectID.startsWith("f:")) {
sqlStatement.append(" and PARENT_ORG_ID = "
+ parentObjectID.substring(2));
} else {
sqlStatement.append(" and 1=0 ");
}
}
} else if (searchType == SearchAxis.Self) {// 取得本节点
// 父节点ID
if (parentObjectID == null) {
sqlStatement.append("and org_id is null ");
} else {
if (parentObjectID.startsWith("f:")) {
sqlStatement.append(" and org_id = "
+ parentObjectID.substring(2));
} else {
sqlStatement.append(" and 1=0 ");
}
}
}
// 按部门显示角色
sqlStatement.append(" union all ");
sqlStatement
.append(" select org_id uid ,org_name name , 0 issqluser, 1 issqlrole ,0 issqlfolder, PARENT_ORG_ID pid from VW_CAS_ORGS where org_src='AD' ");
if (searchType == SearchAxis.Child) {// 取得子节点
// 父节点ID
if (parentObjectID == null) {
sqlStatement.append(" and PARENT_ORG_ID is null ");
} else {
if (parentObjectID.startsWith("r:")) {
sqlStatement.append(" and PARENT_ORG_ID = "
+ parentObjectID.substring(2));
} else {
sqlStatement.append(" and 1=0 ");
}
}
} else if (searchType == SearchAxis.Self) {// 取得本节点
// 父节点ID
if (parentObjectID == null) {
sqlStatement.append("and org_id is null ");
} else {
if (parentObjectID.startsWith("r:")) {
sqlStatement.append(" and org_id = "
+ parentObjectID.substring(2));
} else {
sqlStatement.append(" and 1=0 ");
}
}
}
sqlStatement.append(") ");
// 添加查询条件
if (theSqlCondition.length() > 0) {
sqlStatement.append("WHERE ");
sqlStatement.append(theSqlCondition);
}
// 分页参数
long maxCount = theQueryOption.getMaxCount();
long skipCount = theQueryOption.getSkipCount();
String theSortClause = new String(
" issqlfolder desc ,issqlrole desc ");// 先把机构当成目录列出来
if (theSortProperties != null) {
for (int i = 0; i < theSortProperties.length; i++) {
ISortProperty property = theSortProperties[i];
if (property.getPropertyName().compareTo("name") == 0) {
if (theSortClause.length() > 0) {
theSortClause += ", ";
}
theSortClause += "name";
if (property.getSortOrder() == ISortProperty.SortOrderAscending) {
theSortClause += " ASC";
} else {
theSortClause += " DESC";
}
}
}
if (theSortClause.length() > 0) {
sqlStatement.append(" ORDER BY ");
sqlStatement.append(theSortClause);
}
}
System.out.println("sqlStatement=" + sqlStatement.toString());
Vector data = theDriver.query(theConnection, sqlStatement.toString());
if (data.size() > 1) {
long curSkip = 0, curMax = 0;
for (int i = 1; i < data.size(); i++) {
Vector row = (Vector) data.elementAt(i);
boolean bIsUser = (((String) row.elementAt(2)).compareTo("1") == 0);// 用户
boolean bIsRole = (((String) row.elementAt(3)).compareTo("1") == 0);// 角色
boolean bIsFolder = (((String) row.elementAt(4)).compareTo("1") == 0);// 目录
// We need to handle paging information
if (bIsUser || bIsRole || bIsFolder) {
if (curSkip++ < skipCount) // We need to skip skipCount
// first objects
continue;
else if (curMax >= maxCount && maxCount != -1) // If we
// already
// have
// maxCount
// objects,
// we can
// stop
// looking
break;
else
// curMax < maxCount - we need to keep retrieving
// entries
curMax++;
} else
// If the entry is neither a user nor a role, we'll skip it
continue;
String objectID = (String) row.elementAt(0);
String objectName = (String) row.elementAt(1);
if (bIsUser) {
String searchPath = "u:" + objectID;
Account account = new Account(searchPath);
account.addName(Locale.getDefault(), objectName);
account.setUserName(objectName);
// The following two custom properties used for testing
// purposes
account.addCustomProperty("newProp1", "value1");
account.addCustomProperty("newProp2", "value2");
theResult.addObject(account);
} else if (bIsRole) {// 角色
String searchPath = "r:" + objectID;
Role role = new Role(searchPath);
role.addName(Locale.getDefault(), objectName);
if (searchType == SearchAxis.Self) {// 只有显示路径时才查询它的祖先
// 查询添加祖先以便在节点路径上显示
Vector parentOrg = QueryUtil.getParentOrg(theDriver,
theConnection, objectID);// 父节点
if (parentOrg != null) {
List org_path = QueryUtil.getOrgPath(theDriver,
theConnection, parentOrg);
// 加上从根目录开始的路径
for (int k = org_path.size() - 1; k >= 0; k--) {
Vector _org = (Vector) org_path.get(k);
Role ro = new Role("r:" + (String) _org.get(0));
ro.addName(Locale.getDefault(),
(String) _org.get(1));
role.addAncestors(ro);// 加入祖先
}
}
//
// 添加角色成员
Vector memberList = theDriver
.query(theConnection,
"SELECT user_id uid, user_name name, ORG_ID pid FROM VW_CAS_USERS_BASE_T where user_src='AD' and ORG_ID ="
+ objectID);
if (memberList != null && memberList.size() > 1) {
for (int k = 1; k < memberList.size(); k++) {
Vector member = (Vector) memberList
.elementAt(k);
String member_searchPath = "u:"
+ (String) member.get(0);//成员ID
Account member_account = new Account(member_searchPath);//成员账户对象
member_account.addName(Locale.getDefault(),
(String) member.get(1));//成员名称
member_account.setUserName((String) member.get(1));//成员名称
role.addMember(member_account);
}
}
//
}
role.setIsLeaf(false);// 不是叶子节点
// QueryUtil.queryMembers(theDriver,
// theConnection,theSortProperties, role, theNamespace);
theResult.addObject(role);
} else if (bIsFolder)// 目录
{
String searchPath = "f:" + objectID;
NamespaceFolder folder = new NamespaceFolder(searchPath);
if (searchType == SearchAxis.Self) {// 只有显示路径时才查询它的祖先
Vector parentOrg = QueryUtil.getParentOrg(theDriver,
theConnection, objectID);// 父节点
if (parentOrg != null) {
List org_path = QueryUtil.getOrgPath(theDriver,
theConnection, parentOrg);
// 加上从根目录开始的路径
for (int k = org_path.size() - 1; k >= 0; k--) {
Vector _org = (Vector) org_path.get(k);
NamespaceFolder of = new NamespaceFolder("f:"
+ (String) _org.get(0));
of.addName(Locale.getDefault(),
(String) _org.get(1));
folder.addAncestors(of);// 加入祖先
}
}
}
folder.addName(Locale.getDefault(), objectName);
theResult.addObject(folder);
}
}
}
}
其中 增加 了 issqlfolder 标记, 用来把其他系统的组织机构转成 cognos 的NamespaceFolder ,形成目录结构 ,
而 NamespaceFolder 需要 用 addAncestors 递归添加它的祖先节点 , 否则无法显示它的路径 。
完成结果是