Windchill高级查询感悟笔记.doc

Windchill高级查询感悟笔记首先从这个例子来开始我们的“高级查询”之旅 QuerySpec queryspec = new QuerySpec(); queryspec.appendClassList(WTPart.class, true); queryspec.appendClassList(WTDocument.class, true); PersistenceManagerSvr pms = (PersistenceManagerSvr) InvocationHandlerFwd.getInterfaceObject(PersistenceManagerSvr.class); QueryResult qr = pms.query(queryspec);QuerySpec对象是用来指定查询表达式的， queryspec.appendClassList()是用来指定From子句的，如果有多个这样的表达式而没有其他条件，那么就是对相应的被查询对象“求笛卡儿积”; 例如上面的例子就会形成下面的Form子句：From WTPART,WTDOCUMENT指定From子句有两种方式：queryspec.appendClassList(WTPart.class, false);/指定具体的对象qs.appendFrom(new ExternalTableExpression(GZMTRECR);/指定具体的表 在appendClassList()中有两个参数，第一个参数是用来指定被查询对象，第二个参数用来指定查询的返回形式：true以对象的形式返回,false以属性的方式返回; 如果是用appendClassList()来指定查询对象，那么查询结果QueryResult qr对象qr.nextElement()得到的是Object数组，具体的结果保存在这个数组中;为Select子句中指定返回字段 不管queryspec.appendClassList()方法的第二个参数是true还是false,结果都会返回整个对象或者所有的字段; 要想过滤返回的字段，需要增加下面的代码 QuerySpec queryspec = new QuerySpec();int a = queryspec.appendClassList(WTPart.class, false);int b = queryspec.appendClassList(WTDocument.class, true);/-ClassAttribute ca = new ClassAttribute(WTPart.class, masternumber); /queryspec.appendSelect(ca, false);/-CompositeWhereExpression andExpression = new CompositeWhereExpression(LogicalOperator.AND);andExpression.append(new SearchCondition(ca, =, new ConstantExpression(TEST_PRODUCT_ONE), null);queryspec.appendWhere(andExpression);/- SELECT A1.NUMBER FROM WTPART A0, WTPARTMASTER A1 WHERE A0.IDA3MASTERREFERENCE = A1.IDA2A2;通过TableColumn指定查询条件 QuerySpec queryspec = new QuerySpec(); int classIndex = queryspec.appendClassList(WTPart.class, false); ClassAttribute classAttr = new ClassAttribute(WTPart.class, partType); queryspec.appendSelect(classAttr, new int classIndex , false); TableColumn tc = new TableColumn(null, IDA2A2); queryspec.appendWhere(new SearchCondition(tc, =, new ConstantExpression(new Long(4638), new int classIndex ); PersistenceManagerSvr pms = (PersistenceManagerSvr) InvocationHandlerFwd.getInterfaceObject(PersistenceManagerSvr.class); QueryResult qr = pms.query(queryspec); SELECT PARTTYPE FROM WTPART WHERE IDA2A2 = 4638;=Column ExpressionClassAttribute SQLFunction ConstantExpression KeywordExpression TableColumnTable ExpressionClassTableExpression ClassViewExpression SubSelectExpression ExternalTableExpressionWHERE ClauseSearchCondition ExistsExpression CompositeWhereExpression NegatedExpression=多对象多条件查询 QuerySpec queryspec = new QuerySpec(); int a = queryspec.appendClassList(WTPart.class, true); int b = queryspec.appendClassList(WTPartMaster.class, true); /TableExpression tables = new TableExpression2; /tables0 = queryspec.getFromClause().getTableExpressionAt(a); /tables1 = queryspec.getFromClause().getTableExpressionAt(b); String aliases = new String2; aliases0 = queryspec.getFromClause().getAliasAt(a); aliases1 = queryspec.getFromClause().getAliasAt(b); TableColumn tc1 = new TableColumn(aliases0,IDA2A2); TableColumn tc2 = new TableColumn(aliases0,IDA3MASTERREFERENCE); TableColumn tc3 = new TableColumn(aliases1,IDA2A2); CompositeWhereExpression andExpression = new CompositeWhereExpression(LogicalOperator.AND); andExpression.append(new SearchCondition(tc1, =, new ConstantExpression(new Long(4638); andExpression.append(new SearchCondition(tc2, =, tc3); queryspec.appendWhere(andExpression, null); PersistenceManagerSvr pms = (PersistenceManagerSvr) InvocationHandlerFwd.getInterfaceObject(PersistenceManagerSvr.class); QueryResult qr = pms.query(queryspec); SELECT A0.*, A1.* FROM WTPART A0, WTPARTMASTER A1 WHERE (A0.IDA2A2 = 4638)AND(A1.IDA2A2 = A0.IDA3MASTERREFERENCE); 说明one:用SQL语句写查询语句时,可以为被查询的表、视图指定别名。在使用QuerySpec进行高级查询时,也可以指定别名,别名分为两个部分:前缀+序号,我们能够进行修改的部分只是前缀,并且必须在为QuerySpec扩展类列表(queryspec.appendClassList(WTPart.class, true);)之前设置别名(queryspec.getFromClause().setAliasPrefix(B);)。序号部分是在扩展类列表时自动产生的,第一类的别名为前缀+0，第二个为前缀+1,第三个为前缀+2,依次类推。wt.pom.checkAdvancedQueryAccess=false 说明two:从前面的例子可以看出,进行这些查询所返回的结果要么是整个类,要么是这些类的常量属性的值所对应的列.我们如果要想获取某个对象所对应记录的任何向量的值时,应该怎么做呢？在进行QuerySpec查询时,设置 setAdvancedQueryEnabled(true);由于该方法所设置的属性advancedQueryEnabled是transient的,是不能够被序列化的,因此进行了这种设置的查询必须要在服务端运行。queryspec.appendClassList(WTPart.class, false); queryspec.appendSelect(tablecolumn0, false);的第二个参数需为falseQuerySpec queryspec = new QuerySpec();queryspec.setAdvancedQueryEnabled(true);/如果不直接在服务端运行,那么该段代码就不会生效int a = queryspec.appendClassList(WTPartMaster.class, false);String aliases = new String2;aliases0 = queryspec.getFromClause().getAliasAt(a);TableColumn tc0 = new TableColumn(aliases0, IDA2A2);queryspec.appendSelect(tc0, false);/TableColumn tc1 = new TableColumn();/tc1.setColumnName(rownum);/queryspec.appendWhere(new SearchCondition(tc1, , new ConstantExpression(new Long(5);/用来指定返回的行数复合查询(子查询)String aliases = new String2;QuerySpec queryspec = new QuerySpec();QuerySpec subqueryspec = new QuerySpec();subqueryspec.setAdvancedQueryEnabled(true); /-int a = subqueryspec.appendClassList(WTPartMaster.class, false);aliases0 = subqueryspec.getFromClause().getAliasAt(a);TableColumn tc0 = new TableColumn(aliases0, IDA2A2);subqueryspec.appendSelect(tc0, false); TableColumn tc1 = new TableColumn(null, rownum); subqueryspec.appendWhere(new SearchCondition(tc1, , new ConstantExpression(new Long(5);/-int b = queryspec.appendFrom(new SubSelectExpression(subqueryspec);aliases1 = queryspec.getFromClause().getAliasAt(b);TableColumn tc2 = new TableColumn(aliases1, *);TableColumn tc3 = new TableColumn(null, 我是一个兵,来自);queryspec.appendSelect(tc2, false);queryspec.appendSelect(tc3, false);SELECT * FROM (SELECT IDA2A2 FROM WTPARTMASTER WHERE ROWNUM5) A;高级查询(函数使用)SELECT SUM(A0.IDA2A2) FROM WTPART 方法一：QuerySpec qs = new QuerySpec();qs.setAdvancedQueryEnabled(true);int a = qs.appendFrom(new ExternalTableExpression(GZMTRECR);String aliases = new String1;aliases0 = qs.getFromClause().getAliasAt(a);TableColumn tc0 = new TableColumn(null, SUM( + aliases0 + .gzmtrbudgetchange) as Total);qs.appendSelect(tc0, false);方法二：QuerySpec qs = new QuerySpec();qs.setAdvancedQueryEnabled(true);int a = qs.appendFrom(new ExternalTableExpression(GZMTRECR);String aliases = new String1;aliases0 = qs.getFromClause().getAliasAt(a);TableColumn tc0 = new TableColumn(aliases0, gzmtrbudgetchange);SQLFunction sf0 = SQLFunction.newSQLFunction(SQLFunction.SUM);/指定函数sf0.setArgumentAt(