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这个类代表常量表达式。Cons

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

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

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

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

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

8、查处理类PersistenceHelper.manager.find(qs) ;QuerySpec qs = new QuerySpec(WTPart.class);/ wt.query.SearchCondition,这里有很多条件操作符，如> = < != .SearchCondition temp = new SearchCondition(WTPart.class,wt.part.WTPart.NUMBER, SearchCondition.EQUAL, "3215");qs.appendSearchCondition(temp);QueryResult

9、 qr = PersistenceHelper.manager.find(qs);Select * from wtpart where number='3215'QuerySpec queryspec = new QuerySpec();queryspec.setUseBind( false);queryspec.appendClassList(WTPart. class, true);queryspec.appendClassList(WTDocument.class, true);QueryResult queryResult = PersistenceHelper.man

10、ager.find(qs);QuerySpec 对象是用来指定查询表达式的，queryspec.appendClassList() 是用来指定From子句的，如果有多个这样的表达式而没有其他条件，那么就是对相应的被查询对象“求笛卡儿积”;例如上面的例子就会形成下面的Form子句：From WTPART,WTDOCUMENT指定From子句有两种方式：/指定具体的对象queryspec.appendClassList(WTPart. class, false);/指定具体的表qs.appendFrom( new ExternalTableExpression("G ZMTRECR&qu

11、ot;);在appendClassList() 中有两个参数，第一个参数是用来指定被查询对象，第二个参数用来指定查询的返回形式：true以对象的形式返回,false以属性的方式返回;如果是用appendClassList() 来指定查询对象，那么查询结果QueryResult qr对象qr.nextElement() 得到的是Object数组，具体的结果保存在这个数组中;3、为SELECT语句指定返回字段不管queryspec.appendClassList() 方法的第二个参数是true还是false,结果都会返回整个对象或者所有的字段;要想过滤返回的字段，需要增加下面的代码：SQL语句：S

12、ELECT A1.NUMBER FROM WTPART A0, WTPARTMASTER A1 WHEREA0.IDA3MASTERREFERENCE = A1.IDA2A2;QuerySpec queryspec = new QuerySpec();int a = queryspec.appendClassList(WTPart.class, false);int b = queryspec.appendClassList(WTDocument.class, true);/-/master>number就是相当于调用getMaster().getNumber() ClassAttrib

13、ute ca = new ClassAttribute(WTPart. class, "master>number" );queryspec.appendSelect(ca, false);/-CompositeWhereExpression andExpression = newCompositeWhereExpression(LogicalOperator.AND );andExpression.append( new SearchCondition(ca, "=", newConstantExpression ("TEST_PROD

14、UCT_ONE"), null);queryspec. appendWhere(andExpression);/-4、通过TABLECOLUMN指定查询条件SQL语句：SELECT PARTTYPE FROM WTPART WHERE IDA2A2 = 4638;QuerySpec queryspec = new QuerySpec();int classIndex = queryspec.appendClassList(WTPart. class, false);ClassAttribute classAttr = new ClassAttribute(WTPart. class,

15、 "partType ");queryspec.appendSelect(classAttr, new int classIndex , false);TableColumn tc = new TableColumn( null, "IDA2A2");queryspec.appendWhere(new SearchCondition(tc, "=", newConstantExpression( new Long(4638), new int classIndex );QueryResult qr = PersistenceHelpe

16、r.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, true);int b = queryspec.appendClassList(WTPartMaste

17、r.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()

18、.getAliasAt(b);TableColumn tc1 = new TableColumn(aliases0, "IDA2A2" );TableColumn tc2 = new TableColumn(aliases0, "IDA3MASTERREFERENCE" );TableColumn tc3 = new TableColumn(aliases1, "IDA2A2" );CompositeWhereExpression andExpression = newCompositeWhereExpression(LogicalO

19、perator. 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语句写查询语句

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

21、。wt.pom.checkAdvancedQueryAccess=false说明two:从前面的例子可以看出,进行这些查询所返回的结果要么是整个类,要么是这些类的常量属性的值所对应的列.我们如果要想获取某个对象所对应记录的任何向量的值时,应该怎么做呢？在进行QuerySpec查询时,设置setAdvancedQueryEnabled(true);由于该方法所设置的属性advancedQueryEnabled是transient的,是不能够被序列化的,因此进行了这种设置的查询必须要在服务端运行。2、范围限制 IN ArrayExpression arrayExp=new ArrayExpress

22、ion(oidList);QuerySpec queryspec = new wt.query.QuerySpec(WfProcess.class);ClassAttribute ca=new ClassAttribute(WfProcess.class, "businessObjReference");SearchCondition sc1=new SearchCondition(ca, SearchCondition.IN, arrayExp);SearchCondition sc2=new SearchCondition(WfProcess.class, "

23、state", SearchCondition.NOT_EQUAL, "CLOSED_COMPLETED_EXECUTED");SearchCondition sc3=new SearchCondition(WfProcess.class, "name", SearchCondition.LIKE, "ECNpor%");queryspec.appendWhere(sc1);queryspec.appendAnd();queryspec.appendWhere(sc2);queryspec.appendAnd();query

24、spec.appendWhere(sc3);QueryResult qr=PersistenceHelper.manager.find(queryspec);3、SQL函数使用SQL语句: SELECT SUM(A0.IDA2A2) FROM WTPARTQuerySpec qs = new QuerySpec();qs.setAdvancedQueryEnabled(true);int a = qs.appendFrom(newExternalTableExpression("G ZMTRECR");String aliases = new String1;aliases

25、0 = qs.getFromClause().get AliasAt(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(newExternalTableExpression("G Z

26、MTRECR");String aliases = new String1;aliases0 = qs.getFromClause().get AliasAt(a);TableColumn tc0 = new TableColumn(aliases0,"gzmtrbudgetchange" );/指定函数SQLFunction sf0 = SQLFunction.newSQLFunction(SQLFunction.SUM); sf0.setArgumentAt(tc0, 0);/为函数设置参数qs.appendSelect(tc0, false);SQL语句：S

27、ELECT SUBSTRING(CLASSNAMEA2,0,14) FROM WTPART 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,&

28、quot;CLASSNAMEA2,0,14" ); /指定函数SQLFunction sf0 =SQLFunction.newSQLFunction(SQLFunction.SUB_STRING);/为函数设置参数sf0.setArgumentAt(tc0, 0);qs.appendSelect(tc0, false);4、复合查询（子查询）SQL语句：SELECT * FROM (SELECT IDA2A2 FROM WTPARTMASTER WHERE ROWNUM<5) A; String aliases = new String2;QuerySpec queryspec

29、 = new QuerySpec();QuerySpec subqueryspec = new QuerySpec();subqueryspec.setAdvancedQueryEnabled(true);/-int a = subqueryspec.appendClassList(WTPartMaster. class,false);aliases0 = subqueryspec.getFromClause().get AliasAt(a);TableColumn tc0 = new TableColumn(aliases0, "IDA2A2" );subqueryspe

30、c.appendSelect(tc0, false);TableColumn tc1 = new TableColumn( null, "rownum" );subqueryspec.appendWhere( new SearchCondition(tc1, "<", newConstantExpression( new Long(5);/-int b = queryspec.appendFrom( newSubSelectExpression(subqueryspec);aliases1 = queryspec.getFromClause().g

31、etAliasAt(b);TableColumn tc2 = new TableColumn(aliases1, "*");TableColumn tc3 = new TableColumn( null, "'我是一个兵,来自'");queryspec.appendSelect(tc2, false);queryspec.appendSelect(tc3, false);四、QUERYRESULT过滤qs = new LatestConfigSpec().appendSearchCriteria(qs);qr = PersistenceH

32、elper.manager.find(qs); qr = PersistenceHelper.manager.find(qs); / wt.fc.PersistenceHelperLatestConfigSpec cfg = new LatestConfigSpec(); / wt.vc.config.LatestConfigSpecQueryResult qr1 = cess(qr);五、常用示例QuerySpec qs = new QuerySpec();int partIndex = qs.appendClassList(wt.part.WTPartMaster.class

33、, false);int alternatePartIndex = qs.appendClassList(wt.part.WTPartMaster.class,false);int linkIndex = qs.appendClassList(wt.part.WTPartAlternateLink.class,false);/ Define the attributes in the queryClassAttribute partName = new ClassAttribute(wt.part.WTPartMaster.class, wt.part.WTPartMaster.NAME);C

34、lassAttribute alternatePartName = new ClassAttribute(wt.part.WTPartMaster.class, wt.part.WTPartMaster.NAME);ClassAttribute partNumber = new ClassAttribute(wt.part.WTPartMaster.class, wt.part.WTPartMaster.NUMBER);ClassAttribute alternatePartNumber = new ClassAttribute(wt.part.WTPartMaster.class, wt.p

35、art.WTPartMaster.NUMBER);/ Define constants used in the criteriaConstantExpression subStringStart = new ConstantExpression(new Long(2);ConstantExpression subStringEnd = new ConstantExpression(new Long(4);ConstantExpression wildcardExpression = new ConstantExpression("E% ");/ Add items to t

36、he select and join the classesqs.appendSelect(partName, new int 0 , false);qs.appendSelect(alternatePartName, new int 1 , false);qs.appendJoin(linkIndex, wt.part.WTPartAlternateLink.ALTERNATES_ROLE,partIndex);qs.appendJoin(linkIndex,wt.part.WTPartAlternateLink.ALTERNATE_FOR_ROLE,alternatePartIndex);

37、/ CompositeWhereExpression示例CompositeWhereExpression orExpression = new CompositeWhereExpression(LogicalOperator.OR);orExpression.append(new SearchCondition(SQLFunction.newSQLFunction(SQLFunction.SUB_STRING, partNumber, subStringStart,subStringEnd), SearchCondition.EQUAL, SQLFunction.newSQLFunction(

38、SQLFunction.SUB_STRING, alternatePartNumber,subStringStart, subStringEnd);orExpression.append(new SearchCondition(partName, SearchCondition.LIKE,wildcardExpression);orExpression.append(new SearchCondition(alternatePartName,SearchCondition.LIKE, wildcardExpression);qs.appendWhere(orExpression, new in

39、t partIndex, alternatePartIndex,partIndex, alternatePartIndex );SELECT A0.*FROM WTPartMaster A0WHERE NOT (EXISTS (SELECT B0.ida2a2FROM WTPartAlternateLink B0WHERE (A0.ida2a2 = B0.ida3a5)QuerySpec select = new QuerySpec();int partIndex = select.appendClassList(wt.part.WTPartMaster.class, true);QueryS

40、pec subSelect = new QuerySpec();subSelect.getFromClause().setAliasPrefix("B");int altIndex = subSelect.appendClassList(wt.part.WTPartAlternateLink.class, false);subSelect.appendSelect(new ClassAttribute(wt.part.WTPartAlternateLink.class, WTAttributeNameIfc.ID_NAME),new int altIndex , true)

41、;/ TableExpression示例TableExpression tables = new TableExpression2;String aliases = new String2;tables0 = select.getFromClause().getTableExpressionAt(partIndex);aliases0 = select.getFromClause().getAliasAt(partIndex);tables1 = subSelect.getFromClause().getTableExpressionAt(altIndex);aliases1 = subSel

42、ect.getFromClause().getAliasAt(altIndex);SearchCondition correlatedJoin = new SearchCondition(wt.part.WTPartMaster.class, WTAttributeNameIfc.ID_NAME,wt.part.WTPartAlternateLink.class,WTAttributeNameIfc.ROLEA_OBJECT_ID);subSelect.appendWhere(correlatedJoin, tables, aliases);/ NegatedExpression和Exists

43、Expression示例select.appendWhere(new NegatedExpression(newExistsExpression(subSelect), null);SELECT A0.*FROM WTPartMaster A0WHERE (A0.modifyStampA2 IN (SELECT MIN(B0.modifyStampA2)FROM WTPartMaster B0WHERE B0.modifyStampA2 > cutoff) )Class targetClass = wt.part.WTPartMaster.class;QuerySpec subSelec

44、t = new QuerySpec();/设置别名subSelect.getFromClause().setAliasPrefix("B");int subIndex = subSelect.appendClassList(targetClass, false);int fromIndicies = subIndex ;ClassAttribute subModifyStamp =new ClassAttribute(targetClass,WTAttributeNameIfc.MODIFY_STAMP_NAME);/ SQLFunction示例SQLFunction mi

45、nFunction = SQLFunction.newSQLFunction(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 = select.appendClassList(targetClass, true);select.appendWhere(new SearchCondition(modifyStamp,SearchCondition.