通用`Query`解决方案(中）：https://developer.aliyun.com/article/1516551
支持传统的Query并通过参数形式生成Query列
- 支持传统的
Query形式。 - 支持通过参数形式定义列，不需要指定
ROWSPEC参数。 - 优化将
^CacheTemp为^||CacheTemp。
定义M.CommonQuery
Class M.CommonQuery Extends %Query { ClassMethod Close(ByRef qHandle As %Binary) As %Status [ CodeMode = generator, PlaceAfter = Execute, ProcedureBlock = 1, ServerOnly = 1 ] { s %code($i(%code))= (" s pid = $li(qHandle, 2)") s %code($i(%code))= (" k ^||GlobalTemp(pid)") s %code($i(%code))= (" q $$$OK") q $$$OK } ClassMethod Fetch(ByRef qHandle As %Binary, ByRef row As %List, ByRef end As %Integer = 0) As %Status [ CodeMode = generator, PlaceAfter = Execute, ProcedureBlock = 1, ServerOnly = 1 ] { s %code($i(%code))= (" s end = $li(qHandle, 1)") s %code($i(%code))= (" s pid = $li(qHandle, 2)") s %code($i(%code))= (" s ind = $li(qHandle, 3)") s %code($i(%code))= (" s ind = $o(^||GlobalTemp(pid, ind))") s %code($i(%code))= (" if (ind = """") { ") s %code($i(%code))= (" s end = 1") s %code($i(%code))= (" s row = """"") s %code($i(%code))= (" } else { ") s %code($i(%code))= (" s row = ^||GlobalTemp(pid, ind)") s %code($i(%code))= (" }") s %code($i(%code))= (" s qHandle = $lb(end, pid, ind)") s %code($i(%code))= (" q $$$OK") q $$$OK } ClassMethod GetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRef qHandle As %Binary, extoption As %Integer = 0, ByRef extinfo As %List) As %Status [ CodeMode = generator, ServerOnly = 1 ] { s %code($i(%code))= (" s colinfo = $lb()") s %code($i(%code))= (" s column = $lg(qHandle, 4)") s %code($i(%code))= (" if ($lv(column)) {") s %code($i(%code))= (" for i = 1 : 1 : $ll(column) {") s %code($i(%code))= (" s $li(colinfo, i) = $lb(""Column"" _ i )") s %code($i(%code))= (" } ") s %code($i(%code))= (" } else {") s %code($i(%code))= (" s len = $l(column, "","")") s %code($i(%code))= (" for i = 1 : 1 : len {") s %code($i(%code))= (" s $li(colinfo, i) = $lb($p(column, "","", i))") s %code($i(%code))= (" }") s %code($i(%code))= (" }") s %code($i(%code))= (" s parminfo = """"") s %code($i(%code))= (" s idinfo = """"") s %code($i(%code))= (" q $$$OK") q $$$OK } }
定义QueryName
Query CustomColumnQuery(column As %String = "") As M.CommonQuery { }
column- 表示要自定义参数列的变量。M.CommonQuery- 自定义Query类型，不需要写GetInfo、Fetch、Close方法。
定义QueryNameExecute
QueryNameExecute 支持三种定义列头方式：
- 通过
column参数传入列头，实现如下：
ClassMethod CustomColumnQueryExecute(ByRef qHandle As %Binary, column As %List) As %Status { s pid = $i(^||GlobalTemp) s qHandle = $lb(0, pid, 0) s $li(qHandle, 4) = column // 方式1此位置必填 s ind = 1 s id = "" for { s id = $o(^M.T.PersonD(id)) q:(id = "") s data = ^M.T.PersonD(id) s i = 1 s name = $lg(data, $i(i)) s age = $lg(data, $i(i)) s no = $lg(data, $i(i)) d output } q $$$OK output s data = $lb(id, name) s ^||GlobalTemp(pid, ind)=data s ind = ind + 1 }
USER> d ##class(%ResultSet).RunQuery("M.Query","CustomColumnQuery","ID,Name") ID:Name: 1:yaoxin: 2:yx: 3:Umansky,Josephine Q.: 4:Pape,Ted F.: 5:Russell,Howard T.:
- 不传入
column参数，自动根据列表数据数量生成列头，实现如下：
ClassMethod CustomColumnQueryExecute(ByRef qHandle As %Binary, column As %String = "") As %Status { s pid = $i(^||GlobalTemp) s qHandle = $lb(0, pid, 0) s ind = 1 s id = "" for { s id = $o(^M.T.PersonD(id)) q:(id = "") s data = ^M.T.PersonD(id) s i = 1 s name = $lg(data, $i(i)) s age = $lg(data, $i(i)) s no = $lg(data, $i(i)) s data = $lb(id, name, no) q:(id > 5) d output } s $li(qHandle, 4) = data // 方式2此位置必填 q $$$OK output s ^||GlobalTemp(pid, ind)=data s ind = ind + 1 }
USER>d ##class(%ResultSet).RunQuery("M.Query","CustomColumnQuery") Column1:Column2:Column3: 1:yaoxin:314629: 2:yx:685381: 3:Umansky,Josephine Q.:419268: 4:Pape,Ted F.:241661: 5:Russell,Howard T.:873214:
- 不传入
column参数，通过Execute方法自定义列头信息，实现如下：
ClassMethod CustomColumnQueryExecute0(ByRef qHandle As %Binary, column As %String = "") As %Status { s pid = $i(^||GlobalTemp) s qHandle = $lb(0, pid, 0) s ind = 1 s id = "" for { s id = $o(^M.T.PersonD(id)) q:(id = "") s data = ^M.T.PersonD(id) s i = 1 s name = $lg(data, $i(i)) s age = $lg(data, $i(i)) s no = $lg(data, $i(i)) s data = $lb(id, name, no) q:(id > 5) d output } s $li(qHandle, 4) = "id,name,age" // 方式3此位置必填 q $$$OK output s ^||GlobalTemp(pid, ind)=data s ind = ind + 1 }
USER>d ##class(%ResultSet).RunQuery("M.Query","CustomColumnQuery") id:name:age: 1:yaoxin:314629: 2:yx:685381: 3:Umansky,Josephine Q.:419268: 4:Pape,Ted F.:241661: 5:Russell,Howard T.:873214:
定义通用Query，只需要实现Exceute方法
实现通用Query，需要通过抽象方法，子类去重写的方式去实现。所以首先定义父类。
定义M.CommonQuery
Class M.BaseQuery Extends %RegisteredObject { /// d ##class(%ResultSet).RunQuery("M.BaseQuery","CustomQuery","id,name") Query CustomQuery(column As %List, arg...) As %Query { } ClassMethod CustomQueryExecute(ByRef qHandle As %Binary, column As %List, arg...) As %Status { s qHandle = $lb(0, 0) // 注释1 s $li(qHandle, 3) = column // 注释2 d ..QueryLogic(arg...) // 注释3 q $$$OK } ClassMethod CustomQueryGetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRef qHandle As %Binary, extoption As %Integer = 0, ByRef extinfo As %List) As %Status { s colinfo = $lb() s column = $lg(qHandle ,3) s len = $l(column, ",") for i = 1 : 1 : len { s $li(colinfo, i) = $lb($p(column, ",", i)) // 注释5 } s parminfo = "" s idinfo = "" q $$$OK } ClassMethod CustomQueryClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = CustomQueryExecute ] { k %zQueryList // 注释7 q $$$OK } ClassMethod CustomQueryFetch(ByRef qHandle As %Binary, ByRef row As %List, ByRef end As %Integer = 0) As %Status [ PlaceAfter = CustomQueryExecute ] { s end = $li(qHandle,1) s index = $li(qHandle,2) s index = $o(%zQueryList(index)) if index = "" { // 注释6 s end = 1 s row = "" } else { s row = %zQueryList(index) } s qHandle = $lb(end, index) Quit $$$OK } ClassMethod QueryLogic(arg...) [ Abstract ] { // 注释4 } }
column- 表示要自定义参数列的变量。arg...- 传入的参数。- 注释
1代码，这里做了一些改变，qHandle只记录了end与index。因为这里如果是全局变量或者进程私有Global只针对当前进程有效，所以pid可省略。 - 注释
2代码，将qHandle第三个位置传入列头名称。 - 注释
3代码，调用待实现的业务逻辑方法，此方法为抽象方法，需要子类去实现。 - 注释
4代码，子类需要实现的具体业务逻辑，得到数据集。 - 注释
5代码，获取到column动态设置列头。
- 注释
6代码，遍历全局变量。 - 注释
7代码，遍历结束后，将全局变量清空。
定义子类M.PersonQuery继承M.BaseQuery实现QueryLogic方法
- 这里只需要给
%zQueryList($i(count))全局变量赋值即可。固定模版已经抽象到父类。
ClassMethod QueryLogic(arg...) { s pName = arg(1) s id = "" for { s id = $o(^M.T.PersonD(id)) q:(id = "") s data = ^M.T.PersonD(id) s i = 1 s name = $lg(data, $i(i)) continue:(pName '= "")&&(name '= pName) s age = $lg(data, $i(i)) s no = $lg(data, $i(i)) s %zQueryList($i(count)) = $lb(id, name, age) } }
调用CustomQuery方法
USER>d ##class(%ResultSet).RunQuery("M.PersonQuery","CustomQuery","ID,Name,Age", "yaoxin") ID:Name:Age: 1:yaoxin:21:
注：这里是用的是全局变量作为数据传递，如果数据过大，则可能会出现内存泄漏问题。改成进程私有Global即可。由读者基于此逻辑自行实现。
注：这种方式一个类只能声明一个Query，如果想一个类声明多个Query，则考虑换成支持传统的Query方式。
通过Query生成Json
ClassMethod Query2Json(className, queryName, arg...) { s array = [] s rs = ##class(%ResultSet).%New() s rs.ClassName = className s rs.QueryName = queryName d rs.Execute(arg...) s array = [] #; 属性值 while (rs.Next()) { s valStr = "" s obj = {} for i = 1 : 1 : rs.GetColumnCount(){ s columnName = rs.GetColumnName(i) s val = rs.Data(columnName) d obj.%Set(columnName, val) } d array.%Push(obj) } q array.%ToJSON() }
USER>w ##class(Util.JsonUtils).Query2Json("%SYSTEM.License","Summary") [{"LicenseUnitUse":"当前使用的软件许可单元 ","Local":"1","Distributed":"1"},{"Li censeUnitUse":"使用的最大软件许可单元数 ","Local":"15","Distributed":"15"},{"Lic enseUnitUse":"授权的软件许可单元 ","Local":"300","Distributed":"300"},{"LicenseU nitUse":"当前连接 ","Local":"3","Distributed":"3"},{"LicenseUnitUse":"最大连接数 ","Local":"17","Distributed":"17"}]
通过Query生成Csv
ClassMethod Query2Csv(className, queryName, filePath, arg...) { s file = ##class(%FileCharacterStream).%New() s file.Filename = filePath s array = [] s rs = ##class(%ResultSet).%New() s rs.ClassName = className s rs.QueryName = queryName d rs.Execute(arg...) #; 列名 s colStr = "" for i = 1 : 1 : rs.GetColumnCount(){ s columnName = rs.GetColumnName(i) s colStr = $s(colStr = "" : columnName, 1 : colStr _ "," _ columnName) } d file.Write(colStr) #; 属性值 while (rs.Next()) { s valStr = "" for i = 1 : 1 : rs.GetColumnCount(){ s columnName = rs.GetColumnName(i) s val = rs.Data(columnName) s valStr = $s(valStr = "" : val, 1 : valStr _ "," _ val) } d file.Write($c(10) _ valStr) } d file.%Save() q $$$OK }
USER>w ##class(Util.FileUtils).Query2Csv("%SYSTEM.License","Summary","E:\m\CsvFile2.csv") 1
总结
- 理解
qHandle参数与GetInfo方法是实现通用Query的关键。 - 使用通用
Query可以提升开发效率。 - 使用通用
Query可以解决数据适配问题。
以上是个人对基于Query的一些理解，由于个人能力有限，欢迎大家提出意见，共同交流。
如果一个好点子，只是因为某个人先到想到就禁止后人使用，这会让整个人类社会多走很多弯路，这也是自由软件精神一直以来所表达的内容。
- 理查德·马修·斯托曼
