Windchill10.x高级查询功能

1、键入公司名称Windchill客制化Windchill10.x高级查询功能作者：王更新2013/7/7目录一、持久层类简介21、QuerySpec2SELECT语句表达式2FROM语句表达式2WHERE语句表达式32、SearrchCondiittiion3二、基本查询31、insert、update和delete语句32、基本查询43、为select语句指定返回字段54、通过TableColumn指定查询条件5三、高级查询61、多对象多条件查询62、范围限制 in73、SQL函数使用74、复合查询（子查询）8四、QueryResult过滤9五、常用示例9一、 持久层类简介1、QUERYSPE

2、CSELECT语句表达式表达式描述示例ClassAttribute这个类代表类型属性表达式。如查询WTPart的partType属性ClassAttribute classAttr = new ClassAttribute(WTPart. class, partType );SQLFunction这个类代表SQL的函数表达式，如sum,substringd等等SQLFunction currentDate = SQLFunction.newSQLFunction(SQLFunction.SYSDATE);ConstantExpression这个类代表常量表达式。ConstantExpressi

3、on(new Long(part.getPersistInfo().getObjectIdentifier().getId();KeywordExpression这个类代表SQL关键字表达式。TableColumn这个类代表表的字段表达式，如wtpart表中的字段“IDA2A2”TableColumn dummyColumn = new TableColumn(wtpart, IDA2A2);FROM语句表达式表达式描述示例ClassTableExpression这个类代表一个类的一个表。ClassViewExpression这个类代表一个类的“视图”表。SubSelectExpression

4、这个类代表一个子查询的表达式。可以FREOM一个子查询SQL的查询结果。子查询示例ExternalTableExpression这个类代表一个表，可以直接指定表名。QuerySpec qs = new QuerySpec();int fromIndex = qs.appendFrom(newExternalTableExpression(dual);WHERE语句表达式表达式描述示例SearchCondition这个类代表一个搜索条件查询。当用到QuerySpec中,值将用于SQL WHERE子句。ExistsExpression这个类代表一个存在表达在一个WHERE子句。一个Statemen

5、tSpec实例是用于subselect。NegatedExpression和ExistsExpression示例CompositeWhereExpression这个类代表一个数量的在表达式连接使用，逻辑操作符(即：and/or)。CompositeWhereExpression示例NegatedExpression这个类代表一个否定的表达式。NegatedExpression和ExistsExpression示例2、SEARRCHCONDIITTIION表达式描述示例ClassAttributeSQLFunctionSubSelectExpressionConstantExpressionKe

6、ywordExpressionRangeExpressionDateExpressionArrayExpressionTableColum二、基本查询1、INSERT、UPDATE和DELETE语句/insert操作/store or modifyPersistenceHelper.manager.save(persistable);PersistenceHelper.manager.store(persistable);/更新，使内存和数据库同步PersistenceHelper.manager.refresh(persistable);/update操作PersistenceHelper.

7、manager.modify(persistable);/delete操作PersistenceHelper.manager.delete(persistable);2、基本查询在进行查询时，有两种方式：/不能够进行远程调用；不自动检查权限；如果需要检查权限，需要指定权限检查的处理类PersistenceServerHelper.manager.query(qs);/可以远程调用；自动会检查权限；其实find()和query()使用同样的方式进行查询，只不过使用find()进行查询时，自动指定权限检查处理类PersistenceHelper.manager.find(qs) ;QuerySpe

8、c qs = new QuerySpec(WTPart.class);/ wt.query.SearchCondition,这里有很多条件操作符，如 = number就是相当于调用getMaster().getNumber() ClassAttribute ca = new ClassAttribute(WTPart. class, masternumber );queryspec.appendSelect(ca, false);/-CompositeWhereExpression andExpression = newCompositeWhereExpression(LogicalOpera

9、tor.AND );andExpression.append( new SearchCondition(ca, =, newConstantExpression (TEST_PRODUCT_ONE), null);queryspec. appendWhere(andExpression);/-4、通过TABLECOLUMN指定查询条件SQL语句：SELECT PARTTYPE FROM WTPART WHERE IDA2A2 = 4638;QuerySpec queryspec = new QuerySpec();int classIndex = queryspec.appendClassLi

10、st(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, =, newConstantExpression( new Long(4638), new i

11、nt classIndex );QueryResult qr = PersistenceHelper.manager.find(queryspec); 三、高级查询1、多对象多条件查询SQL语句： SELECT A0.*, A1.* FROM WTPART A0, WTPARTMASTER A1 WHERE (A0.IDA2A2 =4638)AND(A1.IDA2A2 = A0.IDA3MASTERREFERENCE);QuerySpec queryspec = new QuerySpec();int a = queryspec.appendClassList(WTPart.class, tr

12、ue);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().g

13、etAliasAt(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 = newCompositeWhereExpres

14、sion(LogicalOperator. AND);andExpression.append( new SearchCondition(tc1, =, newConstantExpression( new Long(4638);andExpression.append( new SearchCondition(tc2, =, tc3);queryspec.appendWhere(andExpression, null);QueryResult qr = PersistenceHelper.manager.find(queryspec);说明one:用SQL语句写查询语句时,可以为被查询的表、

15、视图指定别名。在使用QuerySpec进行高级查询时,也可以指定别名,别名分为两个部分:前缀+序号,我们能够进行修改的部分只是前缀,并且必须在为QuerySpec扩展类列表(queryspec.appendClassList( WTPart. class,true);)之前设置别名(queryspec.getFromClause().setAliasPrefix (B);)。序号部分是在扩展类列表时自动产生的,第一类的别名为前缀+0，第二个为前缀+1,第三个为前缀+2,依次类推。wt.pom.checkAdvancedQueryAccess=false说明two:从前面的例子可以看出,进行这些

16、查询所返回的结果要么是整个类,要么是这些类的常量属性的值所对应的列.我们如果要想获取某个对象所对应记录的任何向量的值时,应该怎么做呢？在进行QuerySpec查询时,设置setAdvancedQueryEnabled(true);由于该方法所设置的属性advancedQueryEnabled是transient的,是不能够被序列化的,因此进行了这种设置的查询必须要在服务端运行。2、范围限制 IN ArrayExpression arrayExp=new ArrayExpression(oidList);QuerySpec queryspec = new wt.query.QuerySpec(W

17、fProcess.class);ClassAttribute ca=new ClassAttribute(WfProcess.class, businessObjReference);SearchCondition sc1=new SearchCondition(ca, SearchCondition.IN, arrayExp);SearchCondition sc2=new SearchCondition(WfProcess.class, state, SearchCondition.NOT_EQUAL, CLOSED_COMPLETED_EXECUTED);SearchCondition

18、sc3=new SearchCondition(WfProcess.class, name, SearchCondition.LIKE, ECNpor%);queryspec.appendWhere(sc1);queryspec.appendAnd();queryspec.appendWhere(sc2);queryspec.appendAnd();queryspec.appendWhere(sc3);QueryResult qr=PersistenceHelper.manager.find(queryspec);3、SQL函数使用SQL语句: SELECT SUM(A0.IDA2A2) FR

19、OM WTPARTQuerySpec qs = new QuerySpec();qs.setAdvancedQueryEnabled(true);int a = qs.appendFrom(newExternalTableExpression(G ZMTRECR);String aliases = new String1;aliases0 = qs.getFromClause().get AliasAt(a);TableColumn tc0 = new TableColumn(null, SUM( + aliases0+ .gzmtrbudgetchange) as Total);qs.app

20、endSelect(tc0, false); QuerySpec qs = new QuerySpec();qs.setAdvancedQueryEnabled(true);int a = qs.appendFrom(newExternalTableExpression(G ZMTRECR);String aliases = new String1;aliases0 = qs.getFromClause().get AliasAt(a);TableColumn tc0 = new TableColumn(aliases0,gzmtrbudgetchange );/指定函数SQLFunction

21、 sf0 = SQLFunction.newSQLFunction(SQLFunction.SUM); sf0.setArgumentAt(tc0, 0);/为函数设置参数qs.appendSelect(tc0, false);SQL语句：SELECT SUBSTRING(CLASSNAMEA2,0,14) FROM WTPART QuerySpec qs = new QuerySpec();qs.setAdvancedQueryEnabled(true);int a = qs.appendFrom(newExternalTableExpression(G ZMTRECR);String al

22、iases = new String1;aliases0 = qs.getFromClause().get AliasAt(a);TableColumn tc0 = new TableColumn(aliases0,CLASSNAMEA2,0,14 ); /指定函数SQLFunction sf0 =SQLFunction.newSQLFunction(SQLFunction.SUB_STRING);/为函数设置参数sf0.setArgumentAt(tc0, 0);qs.appendSelect(tc0, false);4、复合查询（子查询）SQL语句：SELECT * FROM (SELEC

23、T IDA2A2 FROM WTPARTMASTER WHERE ROWNUM5) A; 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(

24、).get AliasAt(a);TableColumn tc0 = new TableColumn(aliases0, IDA2A2 );subqueryspec.appendSelect(tc0, false);TableColumn tc1 = new TableColumn( null, rownum );subqueryspec.appendWhere( new SearchCondition(tc1, cutoff) )Class targetClass = wt.part.WTPartMaster.class;QuerySpec subSelect = new QuerySpec

25、();/设置别名subSelect.getFromClause().setAliasPrefix(B);int subIndex = subSelect.appendClassList(targetClass, false);int fromIndicies = subIndex ;ClassAttribute subModifyStamp =new ClassAttribute(targetClass,WTAttributeNameIfc.MODIFY_STAMP_NAME);/ SQLFunction示例SQLFunction minFunction = SQLFunction.newSQ

26、LFunction(SQLFunction.MINIMUM, subModifyStamp);subSelect.appendSelect(minFunction, fromIndicies, false);subSelect.appendWhere(new SearchCondition(subModifyStamp,SearchCondition.GREATER_THAN, DateExpression.newExpression(cutoff),fromIndicies);/子查询示例QuerySpec select = new QuerySpec();int index = selec

27、t.appendClassList(targetClass, true);select.appendWhere(new SearchCondition(modifyStamp,SearchCondition.IN,new SubSelectExpression(subSelect), new int index );SELECT A0.number FROM WTPartMaster A0 WHERE (A0.name = ENGINE) UNION 1616 WindchillCustomization Guide SELECT A2.number FROM WTPartMaster A0,

28、WTPartAlternateLink A1,WTPartMaster A2 WHERE (A0.name = ENGINE) AND (A0.idA2A2 = A1.idA3A5) AND (A2.idA2A2 = A1.idA3B5)QuerySpec partSelect = new QuerySpec();int partIndex = partSelect.appendClassList(wt.part.WTPartMaster.class,false);partSelect.appendWhere(new SearchCondition(wt.part.WTPartMaster.class,WTPartMaster.NAME, SearchCondition.E