关于ensemble使用sql+global实现简单分页

文章

liu bo · 九月 19, 2023 阅读大约需 4 分钟

#Globals #SQL #工具 #Ensemble

前言

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,"','")_"'"
}

}

构建查询的包装类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.查询结果
`

<ObjList>
    <total>23</total>
    <pages>3</pages>
    <pageSize>10</pageSize>
    <currentPage>1</currentPage>
    <currentCount>0</currentCount>
    <Data>
        <Object>
            <PatientName>Ingrahm,Michelle X.</PatientName>
            <PatientNo>436244981</PatientNo>
        </Object>
        <Object>
            <PatientName>Koivu,Clint W.</PatientName>
            <PatientNo>473036353</PatientNo>
        </Object>
        <Object>
            <PatientName>Avery,Josephine F.</PatientName>
            <PatientNo>815934238</PatientNo>
        </Object>
        <Object>
            <PatientName>Thompson,Clint M.</PatientName>
            <PatientNo>970071592</PatientNo>
        </Object>
        <Object>
            <PatientName>Ingersol,Diane S.</PatientName>
            <PatientNo>949798228</PatientNo>
        </Object>
        <Object>
            <PatientName>Quince,Sally E.</PatientName>
            <PatientNo>643134733</PatientNo>
        </Object>
        <Object>
            <PatientName>Novello,Clint Y.</PatientName>
            <PatientNo>612491568</PatientNo>
        </Object>
        <Object>
            <PatientName>Ingrahm,Buzz O.</PatientName>
            <PatientNo>72704061</PatientNo>
        </Object>
        <Object>
            <PatientName>Ihringer,Chris M.</PatientName>
            <PatientNo>112730429</PatientNo>
        </Object>
        <Object>
            <PatientName>Anderson,Vincent V.</PatientName>
            <PatientNo>507161056</PatientNo>
        </Object>
    </Data>
</ObjList>