ClassInfo

Windchill에서 QuerySpec 사용 시 Windchill 객체가 데이터베이스 상 어떤 이름인지 알아야 할 때 사용하게 된다. 밑에 테이블에서 Query Key 컬럼을 QuerySpec에서 사용한다. 보통 /jsp/classinfo.jsp를 직접 열면 볼 수 있다.

QuerySpec

기본

//선언
QuerySpec qs = new QuerySpec();

//FROM 절에 WTUser 추가 후 jj라고 Alias 선언.
int jj = qs.addClassList(WTGroup.class, true);
//WHERE 절 같은 곳에 jj라고 끝에 지정해줘야 한다.
qs.appendWhere(new SearchCondition(WTGroup.class, "containerReference.key.classname", 
	SearchCondition.EQUAL, "wt.pdmlink.PDMLinkProduct"), new int[jj]);

//간단한 방법
QuerySpec qs = new QuerySpec(WTGroup.class);
//"containerReference.key.classname" 컬럼의 Java Type을 맞춰야 한다. 아래 경우는 String.
qs.appendWhere(new SearchCondition(WTGroup.class, "containerReference.key.classname", 
	SearchCondition.EQUAL, "wt.pdmlink.PDMLinkProduct"), new int[0]);
	
//쿼리문 출력
LOGGER.info("qs == " + qs.toString());

//쿼리 결과
QueryResult qr = PersistenceHelper.manager.find(qs);
while(qr.hasMoreElements()) {
   WTGroup group = (WTGroup) qr.nextElement();
   WTGroupData gData = new WTGroupData(group);
   list.add(gData);
}

DB에서 직접 쿼리 호출

List<Department> list = new ArrayList<>();		
MethodContext methodcontext = null;
WTConnection wtconnection = null;
PreparedStatement st = null;
ResultSet rs = null;

try {
	methodcontext = MethodContext.getContext();
	wtconnection = (WTConnection) methodcontext.getConnection();
	Connection con = wtconnection.getConnection();
		
	StringBuffer sb = new StringBuffer();
	sb.append("			SELECT 								   											    ");
	sb.append("				classnameA2A2 || ':' || idA2A2 OID 												");
	sb.append("			FROM Department 																	");
	sb.append("  			WHERE idA3parentReference = ? 												    ");
	if(!isAdmin){
		sb.append("  		AND isDisable = 0 															    ");
	}
	sb.append("			ORDER BY SORT	 															        ");
	
	st = con.prepareStatement(sb.toString());
	st.setLong(1, CommonUtil.getOIDLongValue(root));
		
	rs = st.executeQuery();
		
	while(rs.next()) {
		String oid = rs.getString("OID");
		Department dept = (Department) CommonUtil.getObject(oid);
		list.add(dept);
	}
			
}catch(Exception e) {
	throw new Exception(e);
}finally {
	if ( rs != null ) {
   		rs.close();;
    }
    if ( st != null ) {
        st.close();
    }
	if (DBProperties.FREE_CONNECTION_IMMEDIATE
			&& !wtconnection.isTransactionActive()) {
		MethodContext.getContext().freeConnection();
	}
}

CompoundQuerySpec

CompoundQuerySpec compound = new CompoundQuerySpec();
		compound.setSetOperator(SetOperator.UNION_ALL);
		
		if(type.length() == 0){
			boolean isAllsearch = type.length()== 0 && (
				relatedPart.size() == 0 
				&& relatedEpm.size() == 0 
				&& relatedPart.size() == 0
				);
			if(isAllsearch){
				QuerySpec part = setSubQuery(DistributeToPartLink.class, WTPart.class, reqMap, relatedPart, count);
				compound.addComponent(part);
				QuerySpec epm = setSubQuery(DistributeToEPMDocLink.class, EPMDocument.class, reqMap, relatedEpm, count);
				compound.addComponent(epm);
				QuerySpec doc = setSubQuery(DistributeToDocumentLink.class, E3PSDocument.class, reqMap, relatedDoc, count);
				compound.addComponent(doc);
			}else{
				if(relatedPart.size() > 0){
					QuerySpec part = setSubQuery(DistributeToPartLink.class, WTPart.class, reqMap, relatedPart, count);
					compound.addComponent(part);
				}
				if(relatedEpm.size() > 0){
					QuerySpec epm = setSubQuery(DistributeToEPMDocLink.class, EPMDocument.class, reqMap, relatedEpm, count);
					compound.addComponent(epm);
				}
				if(relatedDoc.size() > 0){
					QuerySpec doc = setSubQuery(DistributeToDocumentLink.class, E3PSDocument.class, reqMap, relatedDoc, count);
					compound.addComponent(doc);
				}
			}
		}else{
			if(type.equals("part") ){
				QuerySpec part = setSubQuery(DistributeToPartLink.class, WTPart.class, reqMap, relatedPart, count);
				compound.addComponent(part);
			}
			if(type.equals("epm") ){
				QuerySpec epm = setSubQuery(DistributeToEPMDocLink.class, EPMDocument.class, reqMap, relatedEpm, count);
				compound.addComponent(epm);
			}
			if(type.equals("doc") ){
				QuerySpec doc = setSubQuery(DistributeToDocumentLink.class, E3PSDocument.class, reqMap, relatedDoc, count);
				compound.addComponent(doc);
			}
		}

일반 쿼리 호출

List<Department> list = new ArrayList<>();		
MethodContext methodcontext = null;
WTConnection wtconnection = null;
PreparedStatement st = null;
ResultSet rs = null;

try {
	methodcontext = MethodContext.getContext();
	wtconnection = (WTConnection) methodcontext.getConnection();
	Connection con = wtconnection.getConnection();
		
	StringBuffer sb = new StringBuffer();
	sb.append("			SELECT 								   											    ");
	sb.append("				classnameA2A2 || ':' || idA2A2 OID 												");
	sb.append("			FROM Department 																	");
	sb.append("  			WHERE idA3parentReference = ? 												    ");
	if(!isAdmin){
		sb.append("  		AND isDisable = 0 															    ");
	}
	sb.append("			ORDER BY SORT	 															        ");
	
	st = con.prepareStatement(sb.toString());
	st.setLong(1, CommonUtil.getOIDLongValue(root));
		
	rs = st.executeQuery();
		
	while(rs.next()) {
		String oid = rs.getString("OID");
		Department dept = (Department) CommonUtil.getObject(oid);
		list.add(dept);
	}
			
}catch(Exception e) {
	throw new Exception(e);
}finally {
	if ( rs != null ) {
   		rs.close();;
    }
    if ( st != null ) {
        st.close();
    }
	if (DBProperties.FREE_CONNECTION_IMMEDIATE
			&& !wtconnection.isTransactionActive()) {
		MethodContext.getContext().freeConnection();
	}
}