【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, "master>number"); //µÚ¶þ¸ö²ÎÊý±ØÐëʱÏàÓ¦ÒµÎñ¶ÔÏóµÄÊôÐÔ
			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 Expression	ClassAttribute           SQLFunction           ConstantExpression         KeywordExpression        TableColumn
Table Expression	ClassTableExpression     ClassViewExpression   SubSelectExpression        ExternalTableExpression
WHERE Clause		SearchCondition          ExistsExpression      CompositeWhereExpression   NegatedExpression
========================================================================================================================================

多对象多条件查询

{
	  QuerySpec queryspec = new QuerySpec();
	  int a = queryspec.appendClassList(WTPart.class, true);
	  int b = queryspec.appendClassList(WTPartMaster.class, true);
	  //TableExpression[] tables = new TableExpression[2];
	  //tables[0] = queryspec.getFromClause().getTableExpressionAt(a);
	  //tables[1] = queryspec.getFromClause().getTableExpressionAt(b);
	  String[] aliases = new String[2];
	  aliases[0] = queryspec.getFromClause().getAliasAt(a);
	  aliases[1] = queryspec.getFromClause().getAliasAt(b);
	  TableColumn tc1 = new TableColumn(aliases[0],"IDA2A2");
	  TableColumn tc2 = new TableColumn(aliases[0],"IDA3MASTERREFERENCE");
	  TableColumn tc3 = new TableColumn(aliases[1],"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);的第二个参数需为false
			QuerySpec queryspec = new QuerySpec();
			queryspec.setAdvancedQueryEnabled(true);		//如果不直接在服务端运行,那么该段代码就不会生效
			int a = queryspec.appendClassList(WTPartMaster.class, false);
			String aliases[] = new String[2];
			aliases[0] = queryspec.getFromClause().getAliasAt(a);
			TableColumn tc0 = new TableColumn(aliases[0], "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 String[2];
			QuerySpec queryspec = new QuerySpec();
			QuerySpec subqueryspec = new QuerySpec();
			subqueryspec.setAdvancedQueryEnabled(true); 
			//--------------------------------------------------------------------
			int a = subqueryspec.appendClassList(WTPartMaster.class, false);
			aliases[0] = subqueryspec.getFromClause().getAliasAt(a);
			TableColumn tc0 = new TableColumn(aliases[0], "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));
			aliases[1] = queryspec.getFromClause().getAliasAt(b);
			TableColumn tc2 = new TableColumn(aliases[1], "*");
			TableColumn tc3 = new TableColumn(null, "'我是一个兵,来自'");
			queryspec.appendSelect(tc2, false);
			queryspec.appendSelect(tc3, false);
		}	SELECT * FROM (SELECT IDA2A2 FROM WTPARTMASTER WHERE ROWNUM<5) A;

高级查询(函数使用)

SELECT SUM(A0.IDA2A2) FROM WTPART

方法一：
		{
			QuerySpec qs = new QuerySpec();                 //new一个新的查询
			qs.setAdvancedQueryEnabled(true);               //打开查询
			int a = qs.appendFrom(new ExternalTableExpression("GZMTRECR"));//from wtpart指定查询的表
			String[] aliases = new String[1];
			aliases[0] = qs.getFromClause().getAliasAt(a);//得到所有的结果
			TableColumn tc0 = new TableColumn(null, "SUM(" + aliases[0] + ".gzmtrbudgetchange) as Total");
			qs.appendSelect(tc0, false);                     //关闭查询
		}
	方法二：
		{
			QuerySpec qs = new QuerySpec();
			qs.setAdvancedQueryEnabled(true);
			int a = qs.appendFrom(new ExternalTableExpression("GZMTRECR"));
			String[] aliases = new String[1];
			aliases[0] = qs.getFromClause().getAliasAt(a);
			TableColumn tc0 = new TableColumn(aliases[0], "gzmtrbudgetchange");
			SQLFunction sf0 = SQLFunction.newSQLFunction(SQLFunction.SUM);	//指定函数
			sf0.setArgumentAt(tc0, 0);							//为函数设置参数
			qs.appendSelect(tc0, false);
		}
		进行函数处理时的常见的写法:SELECT SUBSTRING(CLASSNAMEA2,0,14) FROM WTPART
		{
			QuerySpec qs = new QuerySpec();
			qs.setAdvancedQueryEnabled(true);
			int a = qs.appendFrom(new ExternalTableExpression("GZMTRECR"));
			String[] aliases = new String[1];
			aliases[0] = qs.getFromClause().getAliasAt(a);
			TableColumn tc0 = new TableColumn(aliases[0], "CLASSNAMEA2,0,14");
			SQLFunction sf0 = SQLFunction.newSQLFunction(SQLFunction.SUB_STRING);	//指定函数
			sf0.setArgumentAt(tc0, 0);								//为函数设置参数
			qs.appendSelect(tc0, false);
		}

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