文章
· 九月 19, 2023 阅读大约需 4 分钟

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

前言

ensemble里边实现分页比较麻烦,毕竟对于sql的书写比较麻烦,单表的查询相对简单,对于多表的关联查询单纯的sql不好查询,我们使用sql进行先查询出主表满足条件的rowId,在根据根据满足条件的rowid进行遍历取值。

思路

我们先取对比一下其他数据库实现的原理。

  1. Mysql的实现原理
    总数:SELECT COUNT(*) AS total FROM person WHERE (name LIKE ?)
    分页:SELECT id,name,age,email FROM person WHERE (name LIKE ?) LIMIT ?,?

  2. 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 > ?

  3. 由于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

代码构建

  1. 构建查询的抽象的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,"','")_"'"
    }
    
    }
    

`

  1. 构建查询的包装类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
}

}

`

测试

  1. 自定义的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>

`

讨论 (2)3
登录或注册以继续