关于ensemble使用sql+global实现简单分页
前言 {#1}
ensemble里边实现分页比较麻烦，毕竟对于sql的书写比较麻烦，单表的查询相对简单，对于多表的关联查询单纯的sql不好查询，我们使用sql进行先查询出主表满足条件的rowId,在根据根据满足条件的rowid进行遍历取值。
思路
我们先取对比一下其他数据库实现的原理。
-
Mysql的实现原理 总数：SELECT COUNT(*) AS total FROM person WHERE (name LIKE ?) 分页：SELECT id,name,age,email FROM person WHERE (name LIKE ?) LIMIT ?,?
-
ORACLE的实现原理 rownum 总数：SELECT COUNT() AS total FROM person WHERE (name LIKE ?) 分页：SELECT * FROM ( SELECT TMP., ROWNUM ROW_ID FROM ( SELECT id,name,age,email FROM person WHERE (name LIKE ?) ) TMP WHERE ROWNUM <=?) WHERE ROW_ID > ?
-
由于cache没有limit关键字，看看有没有和oracle里边rownum一样的原理。Cache的实现原理和oracle类似 %VID 只查询主键id,在遍历取值 总数：select count(*) FROM Design_Page.Person WHERE birth<'1988-12-1' 分页：SELECT * FROM ( SELECT %VID ROWNUM ,TMP.* FROM ( SELECT * FROM Design_Page.Person WHERE birth<'1988-12-1' ) TMP WHERE %VID <=15) WHERE ROWNUM > 5
代码构建
-
构建查询的抽象的AbstractQueryWrapper包装类 `
Class Design.Page.V1.AbstractQueryWrapper Extends %RegisteredObject {
/// 构建sql的运算符号 Parameter AND = "AND"; Parameter OR = "OR"; Parameter NOT = "NOT"; Parameter IN = "IN"; Parameter NOTIN = "NOT IN"; Parameter LIKE = "LIKE"; Parameter NOTLIKE = "NOT LIKE"; Parameter EQ = "="; Parameter NE = "!="; Parameter GT = ">"; Parameter GE = ">="; Parameter LT = "<"; Parameter LE = "<="; Parameter ISNULL = "IS NULL"; Parameter ISNOTNULL = "IS NOT NULL"; Parameter GROUPBY = "GROUP BY"; Parameter HAVING = "HAVING"; Parameter ORDERBY = "ORDER BY"; Parameter EXISTS = "EXISTS"; Parameter NOTEXISTS = "NOT EXISTS"; Parameter BETWEEN = "BETWEEN"; Parameter NOTBETWEEN = "NOT BETWEEN"; Parameter ASC = "ASC"; Parameter DESC = "DESC"; /// 抽象类 Method addCondition(coloumParams As %String) [ Abstract ] { } /// 添加字段之间的条件连接 Method addConditionOperate(operate As %String) [ Abstract ] { } /// 等于的条件 Method eq(column As %String, val As %String) { d ..addCondition(" "_column_..#EQ _"'"_val_"' ") q $this } /// 不等于 Method ne(column As %String, val As %String) { d ..addCondition(" "_ column_..#NE _"'"_val_"' ") q $this } /// 大于的条件 Method gt(column As %String, val As %String) { d ..addCondition( " "_column_..#GT _"'"_val_"' ") q $this } /// 大于等于的条件 Method ge(column As %String, val As %String) { d ..addCondition( " "_column_..#GE _"'"_val_"' ") q $this } /// 小于的条件 Method lt(column As %String, val As %String) { d ..addCondition(" "_column_..#LT _"'"_val_"' ") q $this } /// 小于等于条件 Method le(column As %String, val As %String) { d ..addCondition( " "_column_..#LE _"'"_val_"' ") q $this } /// like 模糊匹配 Method like(column As %String, val As %String) { d ..addCondition( " "_column_" "_..#LIKE _" '%"_val_"%' ") q $this } /// not like 模糊匹配 Method notLike(column As %String, val As %String) { d ..addCondition( " "_column_" "_..#NOTLIKE _" '%"_val_"%' ") q $this } /// 左匹配 模糊匹配 Method likeLeft(column As %String, val As %String) { d ..addCondition( " "_column_" "_..#LIKE _" '"_val_"%' ") q $this } /// 右匹配 Method likeRight(column As %String, val As %String) { d ..addCondition( " "_column_" "_..#NOTLIKE _" '%"_val_"' ") q $this } /// between 拼接 Method between(column As %String, startVal As %String, endVal As %String) { d ..addCondition( " "_column_" "_..#BETWEEN _" '"_startVal_"' "_..#AND_"'"_endVal_"' ") q $this } /// notBetween 拼接 Method notBetween(column As %String, startVal As %String, endVal As %String) { d ..addCondition( " "_column_" "_..#NOTBETWEEN _" '"_startVal_"' "_..#AND_"'"_endVal_"' ") q $this } /// 字段值为空 Method isNull(column As %String) { d ..addCondition( " "_column_" "_..#ISNULL _" ") q $this } /// 非空 Method isNotNull(column As %String) { d ..addCondition( " "_column_" "_..#ISNOTNULL_" ") q $this } /// in Method in(column As %String, valueList As %String, Separator As %String = "^") { d ..addCondition( " "_column_" "_..#IN_"("_..ConcatListParams(valueList,Separator)_")") q $this } /// not in Method notIn(column As %String, valueList As %String, Separator As %String = "^") { d ..addCondition( " "_column_" "_..#NOTIN_"("_..ConcatListParams(valueList,Separator)_")") q $this } /// in list的参数拼接 Method ConcatListParams(valList As %String, Separator As %String) { s paramsLen=$l(valList,Separator) q:paramsLen=1 "'"_valList_"'" s paramsList =$lb("") for i=1:1:paramsLen{ if (i=1) s $LIST(paramsList,1)=$p(valList,Separator,i) else s $LIST(paramsList,*+1)=$p(valList,Separator,i) } q "'"_$LISTTOSTRING(paramsList,"','")_"'" } } ` 2. 构建查询的包装类queryWrapper ` /// 包装查询的列和查询条件以及运算符 Class Design.Page.V1.QueryWrapper Extends AbstractQueryWrapper { /// sql查询的列 Property SelectColoums As %String; /// 查询的表对应的schema Property querySchema As %String [ InitialExpression = "SQLUser" ]; /// 查询的表 Property queryTable As %String; /// 查询条件 Property queryCondition As %String; /// 字段，值，关系运算符，逻辑运算符构建查询条件 Method addCondition(coloumParams As %String) { s ..queryCondition=..queryCondition_" "_coloumParams } /// and 连接字段 Method and() { s ..queryCondition=..queryCondition_" "_..#AND q $this } Method or() { s ..queryCondition="("_..queryCondition_") "_..#OR q $this } } ` 3. 构建查询总数和过滤条件的sqlBuilder ` /// 构建查询的sql语句 Class Design.Page.V1.SqlBuilder Extends %RegisteredObject { Property queryWrapper As QueryWrapper; Method %OnNew(queryWrapper As QueryWrapper) As %Status [ Private, ServerOnly = 1 ] { s ..queryWrapper=queryWrapper Quit $$$OK } /// 构建查询的总数据的sql Method bulidCountSql() { q "select count(*) totalcount from "_..queryWrapper.querySchema_"."_..queryWrapper.queryTable_" where"_..queryWrapper.queryCondition } /// 构建查询执行查询业务的sql Method bulidBusiSql() { q "select "_..queryWrapper.SelectColoums_" from "_..queryWrapper.querySchema_"."_..queryWrapper.queryTable_" where"_..queryWrapper.queryCondition } Method bulidPageSql(stOffset As %Integer, endOffset As %Integer) { s businessSql=..bulidBusiSql() q "SELECT * FROM ( SELECT %VID ROWNUM ,TMP.* FROM ( "_businessSql_") TMP WHERE %VID <= "_endOffset_" ) WHERE ROWNUM > "_stOffset}
}
4. 构建返回数据列表的IPage
/// 分页插件
Class Design.Page.V1.IPage Extends %RegisteredObject
{
/// 数据列表
Property Data As list Of %RegisteredObject;
/// 查询列表总记录数 0
Property total As %Integer [ InitialExpression = 0 ];
/// 总页数
Property pages As %Integer [ InitialExpression = 0 ];
/// 每页显示条数，默认 10
Property pageSize As %Integer [ InitialExpression = 10 ];
/// 当前页
Property currentPage As %Integer [ InitialExpression = 1 ];
/// 当前计数器
Property currentCount As %Integer [ InitialExpression = 0 ];
/// 单页分页条数限制
Property maxLimit As %Integer;
/// 分页的最后一次循环的ID
Property currentId As %String;
/// /插入数据
Method InternalInsert(obj As %ObjectHandle)
{
q ..Data.Insert(obj)
}
/// 执行往list里边插入对象的操作
Method doInsert(obj As %ObjectHandle) As %Status
{
s currentPage=..currentPage
s pageSize=..pageSize
s currentCount=..currentCount+1
s ..currentCount=currentCount
d:(currentPage=1) ..InternalInsert(obj)
d:((currentCount>((currentPage-1)*pageSize))&&(pageSize>0)&&(currentPage>1)) ..InternalInsert(obj)
;实际的页数大于等于分页的数 退出循环
q ..Data.Count()>=pageSize
}
/// 根据计算起始数和限制查询的条数
Method getOffset(Output stOffset, Output endOffset)
{
;分页数
i ..total # ..pageSize=0 d
.s ..pages= ..total/..pageSize
e s ..pages=$System.SQL.FLOOR(..total/..pageSize) +1
;当前页数
s currentPage = ..currentPage
i currentPage=1{
s stOffset=0
s endOffset=..pageSize
}else{
s stOffset=(currentPage-1)*..pageSize
s endOffset=currentPage*..pageSize
}
q $$$OK
}
/// 获取查询的结果的ID
Method selectPage(queryWrapper As QueryWrapper, Output ok) As %ArrayOfDataTypes
{
s ret = ##Class(%ArrayOfDataTypes).%New()
//拼接sql执行查询总数
s ok=$$$OK
s sqlBuilder=##class(Design.Page.V1.SqlBuilder).%New(queryWrapper)
s countTotalSql=sqlBuilder.bulidCountSql()
d ..exeCountTotalSql(countTotalSql)
q:..total=0 ret
///计算分页
d ..getOffset(.stOffSet,.edOffSet)
///获取分页执行sql
s pageSql=sqlBuilder.bulidPageSql(stOffSet,edOffSet)
///返回结果集的ID
q ..exePageSql(pageSql)
}
/// 执行查询分页sql
Method exePageSql(sql) As %ArrayOfDataTypes
{
s ret = ##Class(%ArrayOfDataTypes).%New()
s rset = ##class(%ResultSet).%New()
d rset.Prepare(sql)
d rset.Execute()
i rset.QueryIsValid() d
.While (rset.Next()) {
.d ret.SetAt(rset.GetData(1),rset.GetData(2))
.}
q ret
}
/// 执行查询总数的sql
Method exeCountTotalSql(sql) As %Status
{
s rset = ##class(%ResultSet).%New()
d rset.Prepare(sql)
s sc= rset.Execute()
i rset.QueryIsValid() d
.While (rset.Next()) {
. s ..total= rset.GetData(1)
.}
q $$$OK
}
}
`
测试
-
自定义的objlist需要继承IPage `
///定义返回的对象列表 Class Design.Page.ObjList Extends (Design.Page.V1.IPage, %XML.Adaptor) { /// 数据列表 Property Data As list Of Object; } ///单个对象 Class Design.Page.Object Extends (%RegisteredObject, %XML.Adaptor) { Property PatientName As %String; Property PatientNo As %String; }
`
2.测试代码
`
/// 分页查询
ClassMethod selectPage()
{
s $zt="Err"
//当前页数
s currentPage=1
//每页的大小
s pageSize=10
s objlist=##class(Design.Page.ObjList).%New()
s objlist.currentPage=currentPage
s objlist.pageSize=pageSize
//构建查询的条件
s queryWrapper =##class(Design.Page.QueryWrapper).%New()
s queryWrapper.SelectColoums="ID"
s queryWrapper.querySchema="Design_Page"
s queryWrapper.queryTable="Person"
d queryWrapper.lt("birth",$zdh("2023-12-1",3)).and().like("name","in")
;执行查询查询获取Id
s rset=objlist.selectPage(queryWrapper,.ok)
q:ok'=1 "调用出现异常"
q:objlist.total=0 "未查询到数据！"
q:rset.Count()=0 "未查询到数据！"
s RowId=""
while(rset.GetNext( .RowId)){
continue:'$d(^Design.Page.PersonD(RowId))
s obj=##class(Design.Page.Object).%New()
s obj.PatientName=$lg(^Design.Page.PersonD(RowId),2) ;患者姓名
s obj.PatientNo=$lg(^Design.Page.PersonD(RowId),3) ;病人ID号
d objlist.Data.Insert(obj)
}
w objlist.Data.Count(),!
d objlist.XMLExportToString(.xml)
w xml,!
q
Err
w $ze,!
q $$$OK
}
`
3.查询结果 `
23
3
10
1
0
`