通用`Query`解决方案(上)：https://developer.aliyun.com/article/1516548
现状
上面2个Query的基本使用示例，可能是大家最常用的两种方式。
但是经常写查询或者写报表的同学可能会面临如下几个问题：
- 每次写
Query都需要定义列头ROWSPEC很麻烦，是否可以自己指定列头ROWSPEC？ - 现在很多方法返回的值是
JSON，如何将JSON方法快速转成Query？ - 是否可以写一个通用
Query，只需要写Execute主要逻辑即可？
- 是否可以优化现在的模版，例如
^CacheTemp替换成^||CacheTemp？
以上的问题，都是可以解决的，请继续阅读下面文章部分。
方案
如果想实现通用Query还得需要知道一个回调方法QueryNameGetInfo。
ClassMethod Json2QueryGetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRef qHandle As %Binary, extoption As %Integer = 0, extinfo As %List) As %Status { q $$$OK }
其中：
colinfo- 此参数最关键用于定义ROWSPEC列头部分。为在ROWSPEC中声明的每一列包含一个列表元素。形式为name:exttype:caption。
name- 为列头名称。exttype- 为数据类型。caption- 为描述说明。
colinfo类型必须是%Library.List，定义的列头的类型也是%Library.List。
例如：
ClassMethod QueryPersonByAgeGetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRef %qHandle As %Binary, extoption As %Integer = 0, extinfo As %List) As %Status { s colinfo = $lb($lb("id", "%Integer", "ID"), $lb("age", "%String", ""), $lb("MT_Name", "%String", "name"), $lb("no", "%String", "")) s parminfo = "" s idinfo = "" q $$$OK }
说明：Query执行顺序 Execute -> GetInfo -> Fetch(n) -> Close。
下面分别描述以下几种解决方案：
- 通过
Json数据或方法动态生成Query - 通过
Select Sql语句动态生成Query
- 通过
Query动态生成Query - 支持传统的Query并通过参数形式生成Query列
- 定义通用
Query，只需要实现Exceute方法
通过Json数据或方法动态生成Query
定义Json方法
Json方法可任意定义，此示例仅为了测试使用。如下方法：查询当前电脑盘符以Json结果输出。
/// desc：查询盘符 ClassMethod QueryDrives(fullyQualified = 1, type = "D") { s array = [] s rs = ##class(%ResultSet).%New() s rs.ClassName = "%File" s rs.QueryName = "DriveList" d rs.Execute(fullyQualified) while (rs.Next()) { s drive = rs.Get("Drive") s drive = $zcvt(drive, "U") s obj = {} s obj.type = "D" continue:(type '= "D") s obj.drive = drive d array.%Push(obj) } q array }
运行：
USER> w ##class(M.Query).QueryDrives().%ToJSON() [{"type":"D","drive":"C:\\"},{"type":"D","drive":"D:\\"},{"type":"D","drive":"E:\\"},{"type":"D","drive":"F:\\"},{"type":"D","drive":"G:\\"}]
定义QueryName
Query Json2Query(className As %String, methodName As %String, arg...) As %Query { }
其中：
className- 类名。methodName- 需要执行的Json方法名称。arg... - 需要执行的方法参数。
定义QueryNameExecute
ClassMethod Json2QueryExecute(ByRef qHandle As %Binary, className As %String, methodName As %String, arg...) As %Status { s array = $classmethod(className, methodName, arg...) // 注释1 if ('$isobject(array)) { // 注释2 s array = [].%FromJSON(array) } q:('array.%IsA("%Library.DynamicArray")) $$$ERROR($$$GeneralError, "不是数组对象") // 注释3 q:(array.%Size() = 0) $$$ERROR($$$GeneralError, "没有数据") // 注释4 s qHandle = array // 注释5 q $$$OK }
- 注释
1代码，利用反射机制调用目标方法并获取返回值。 - 注释
2代码，判断如果返回的字符串则转成Json对象。 - 注释
3代码，判断该对象不是%Library.DynamicArray抛出错误信息。
- 注释
4代码，Json数组长度为0抛出错误信息。 - 注释
5代码，获取数组对象。
定义QueryNameGetInfo
ClassMethod Json2QueryGetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRef qHandle As %Binary, extoption As %Integer = 0, extinfo As %List) As %Status { s colinfo = $lb() // 注释1 s count = 1 s obj = qHandle.%GetIterator() if obj.%GetNext(.key, .value) { s obj = value.%GetIterator() while obj.%GetNext(.objKey, .objValue) { // 注释2 s $li(colinfo, count) = $lb(objKey) s count = $i(count) } } s parminfo = "" // 注释3 s idinfo = "" // 注释4 s qHandle = qHandle.%GetIterator() // 注释5 q $$$OK }
- 注释
1代码，初始化colinfo数组，将obj赋值qHandle.%GetIterator()迭代器对象。 - 注释
2代码，遍历Json对象获取Key，并通过$li给colinfo赋值。 - 注释
3代码，初始化parminfo，否则报错。
- 注释
4代码，初始化idinfo，否则报错。 - 注释
5代码，获取的迭代器对象
定义QueryNameFetch
ClassMethod Json2QueryFetch(ByRef qHandle As %Binary, ByRef row As %List, ByRef end As %Integer = 0) As %Status [ PlaceAfter = Json2QueryExecute ] { s iter = qHandle q:($g(iter) = "") $$$OK if iter.%GetNext(.key, .value) { // 注释1 s row = "" s obj = value.%GetIterator() while obj.%GetNext(.objKey, .objValue) { // 注释2 if ( $g(row) = "" ) { s row = $lb(objValue) } else { s row = row _ $lb(objValue) } } s end = 0 } else { s row = "" s end = 1 // 注释3 } q $$$OK }
- 注释
1代码，获取当前迭代器Json数据行。 - 注释
2代码，遍历当前Json对象并把value与row进行$lb串联。 - 注释
3代码，如果没有数据设置end为1表示遍历结束。
定义QueryNameClose
ClassMethod Json2QueryClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = Json2QueryFetch ] { s qHandle = "" // 注释1 q $$$OK }
- 注释
1代码，将对象qHandle清空。
注:其实M有相关回收机制，实际上Close方法不声明也可以。
调用Json2Query方法
USER>d ##class(%ResultSet).RunQuery("M.Query","Json2Query","M.Query","QueryDrives","0","D") type:drive: D:D:: D:E:: D:F:: D:G::
USER>d ##class(%ResultSet).RunQuery("M.Query","Json2Query","M.Query","QueryDrives","1","D") type:drive: D:D:\: D:E:\: D:F:\: D:G:\:
通过Select Sql语句动态生成Query
定义QueryName
Query Sql2Query(sql As %String, mode As %String = 1) As %Query { }
sql- 表述需要写入SQL语句的变量。mode- 显示数据格式类型。
- 0 - 逻辑格式
- 1 -
OBDC格式 - 2 - 显示格式
定义QueryNameExecute
ClassMethod Sql2QueryExecute(ByRef qHandle As %Binary, sql As %String, mode As %String = 1) As %Status { s sqlStatement = ##class(%SQL.Statement).%New() s sqlStatement.%SelectMode = mode // 注释1 s sqlStatus = sqlStatement.%Prepare(.sql) // 注释2 q:$$$ISERR(sqlStatus) sqlStatus s sqlResult = sqlStatement.%Execute() s stateType = sqlStatement.%Metadata.statementType q:('stateType = 1 ) $$$ERROR($$$GeneralError, "不是select语句") // 注释3 s qHandle = {} s qHandle.sqlResult = sqlResult // 注释4 s qHandle.sqlStatement = sqlStatement q $$$OK }
- 注释
1代码，设置SQL的数据显示格式。 - 注释
2代码，传入SQL语句得到sqlStatement与sqlResult对象。 - 注释
3代码，传入的SQL非Select语句，抛出错误信息。
- 注释
4代码，将qHandle传入两个对象分别是sqlResult、sqlStatement。
sqlResult用于遍历数据使用。sqlStatement用于得到数据列头信息。
定义QueryNameGetInfo
ClassMethod Sql2QueryGetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRef qHandle As %Binary, extoption As %Integer = 0, extinfo As %List) As %Status { s colinfo = $lb() s sqlStatement = qHandle.sqlStatement // 注释1 s count = 1 for i = 1 : 1 : sqlStatement.%Metadata.columnCount { s data = sqlStatement.%Metadata.columns.GetAt(i).label s $li(colinfo, count) = $lb(data) // 注释2 s count = $i(count) } s parminfo = "" s idinfo = "" q $$$OK }
- 注释
1代码，通过qHandle得到sqlStatement对象。 - 注释
2代码，给colinfo列表进行循环赋值列头信息，
定义QueryNameFetch
ClassMethod Sql2QueryFetch(ByRef qHandle As %Binary, ByRef row As %List, ByRef end As %Integer = 0) As %Status [ PlaceAfter = Sql2QueryExecute ] { s sqlStatement = qHandle.sqlStatement // 注释1 s sqlResult = qHandle.sqlResult s colCount = sqlResult.%ResultColumnCount // 注释2 if (sqlResult.%Next()) { for i = 1 : 1 : colCount{ s val = sqlResult.%GetData(i) if ( $g(row) = "" ) { // 注释3 s row = $lb(val) } else { s row = row _ $lb(val) } } s end = 0 } else { s row = "" s end = 1 } s qHandle.sqlResult = sqlResult // 注释4 q $$$OK }
- 注释
1代码，通过qHandle得到sqlStatement、sqlResult对象。 - 注释
2代码，得到列数，相当于得到一行数据有多少项。 - 注释
3代码，遍历数据给row赋值。
- 注释
4代码，将qHandle.sqlResult对象，赋值给循环当前对象。
定义QueryNameClose
此处省略。
注:其实M有相关回收机制，实际上Close方法不声明也可以。
调用Sql2Query方法
USER>d ##class(%ResultSet).RunQuery("M.Query","Sql2Query","select * from M_T.Person", 1) id:MT_Age:MT_Name:MT_No: 1:21:yaoxin:314629: 2:29:yx:685381: 3:18:Umansky,Josephine Q.:419268: 4:27:Pape,Ted F.:241661: 5:25:Russell,Howard T.:873214: 6:30:Xenia,Ashley U.:420471: 7:24:Rotterman,Martin O.:578867: 8:18:Drabek,Hannah X.:662167: 9:19:Eno,Mark U.:913628: ... 100:24:Nathanson,Jocelyn A.:147578:
USER>d ##class(%ResultSet).RunQuery("M.Query","Sql2Query","select ID,MT_Name from M_T.Person") id:MT_Name: 1:yaoxin: 2:yx: 3:Umansky,Josephine Q.: 4:Pape,Ted F.: 5:Russell,Howard T.: 6:Xenia,Ashley U.: 7:Rotterman,Martin O.: ... 100:Nathanson,Jocelyn A.:
USER>d ##class(%ResultSet).RunQuery("M.Query","Sql2Query","select top 10 ID as id from M_T.Person") id: 1: 2: 3: 4: 5: 6: 7: 8: 9: 11:
通过Query生成动态Query
定义QueryName
Query Query2Query(className As %String, queryName As %String, arg...) As %Query { }
className- 类名。queryName- 需要执行的Query方法名称。arg... - 需要执行的Query方法参数。
定义QueryNameExecute
ClassMethod Query2QueryExecute(ByRef qHandle As %Binary, className As %String, queryName As %String, arg...) As %Status { s sqlStatement = ##class(%SQL.Statement).%New() s sqlStatus = sqlStatement.%PrepareClassQuery(className, queryName) q:$$$ISERR(sqlStatus) sqlStatus s sqlResult = sqlStatement.%Execute() s qHandle = {} s qHandle.sqlResult = sqlResult s qHandle.sqlStatement = sqlStatement q $$$OK }
- 与
Sql2Query类似。
定义QueryNameGetInfo
ClassMethod Query2QueryGetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRef qHandle As %Binary, extoption As %Integer = 0, extinfo As %List) As %Status { s colinfo = $lb() s sqlStatement = qHandle.sqlStatement s count = 1 s column = "" for { s column = $o(sqlStatement.%Metadata.columnIndex(column)) q:(column = "") s data = sqlStatement.%Metadata.columnIndex(column) s $li(colinfo, count) = $lb($lg(data, 2)) s count = $i(count) } s parminfo = "" s idinfo = "" q $$$OK }
- 与
Sql2Query类似。
定义QueryNameFetch
ClassMethod Query2QueryFetch(ByRef qHandle As %Binary, ByRef row As %List, ByRef end As %Integer = 0) As %Status [ PlaceAfter = Query2QueryExecute ] { s sqlStatement = qHandle.sqlStatement s sqlResult = qHandle.sqlResult s colCount = sqlResult.%ResultColumnCount if (sqlResult.%Next()) { for i = 1 : 1 : colCount{ s val = sqlResult.%GetData(i) if ( $g(row) = "" ) { s row = $lb(val) } else { s row = row _ $lb(val) } } s end = 0 } else { s row = "" s end = 1 } s qHandle.sqlResult = sqlResult q $$$OK }
- 与
Sql2Query类似。
调用Query2Query
USER>d ##class(%ResultSet).RunQuery("M.Query","Query2Query","M.Query","QueryPersonByName") age:id:MT_Name:no: 1:21:yaoxin:314629: 2:29:yx:685381: 3:18:Umansky,Josephine Q.:419268: 4:27:Pape,Ted F.:241661: 5:25:Russell,Howard T.:873214: 6:30:Xenia,Ashley U.:420471: 7:24:Rotterman,Martin O.:578867: 8:18:Drabek,Hannah X.:662167: 9:19:Eno,Mark U.:913628: 11:18:Tsatsulin,Dan Z.:920134:
通用`Query`解决方案(下）：https://developer.aliyun.com/article/1516552
