B4J Code Snippet Pagination with dbutils

'support mysql only
'fromQuery:from wp_user
'whereQuery: username like '1%'
'orderby:username asc,nickname desc
'args:array(1,"123")
'pageno start from 0
Public Sub Pagination_TotalSize(SQL As SQL,fromQuery As String,whereQuery As String,args() As Object) As Int
    Dim qsb As StringBuilder
    qsb.Initialize
    qsb.Append("select count(1) cnt ")
    qsb.Append(fromQuery&" where 1=1 ")
    If whereQuery<>Null And whereQuery.Length>0 Then
        qsb.Append("and "&whereQuery&" ")
    End If
    Dim query As String=qsb.ToString
    G.mLog("PaginationTotalSize:"&query)
    Dim cur As ResultSet
    If args<>Null Then
        cur=SQL.ExecQuery2(query,args)
    Else
        cur=SQL.ExecQuery(query)
    End If
    Try
        cur.NextRow
        Return cur.GetInt("cnt")
    Catch
        Log(LastException)
        Return 0
    End Try
End Sub

'support mysql only
'selectQuery:select *
'fromQuery:from wp_user
'whereQuery: username like '1%'
'orderby:username asc,nickname desc
'args:array(1,"123")
'pageno start from 0
Public Sub Pagination(SQL As SQL,selectQuery As String,fromQuery As String,whereQuery As String,orderby As String,args() As Object,pageno As Int,pagesize As Int) As List
    Dim start As Int=pageno*pagesize
    Dim qsb As StringBuilder
    qsb.Initialize
    If selectQuery<>Null And selectQuery.Length>0 Then
        qsb.Append(selectQuery&" ")
    Else
        qsb.Append("select * ")
    End If
    qsb.Append(fromQuery&" where 1=1 ")
    If whereQuery<>Null And whereQuery.Length>0 Then
        qsb.Append("and "&whereQuery&" ")
    End If
    If orderby<>Null And orderby.Length>0 Then
        qsb.Append("order by "&orderby&" ")
    End If
    qsb.Append("limit "&start&","&pagesize)
    Dim query As String=qsb.ToString
    G.mLog("Pagination:"&query)
    Dim cur As ResultSet
    If args<>Null Then
        cur=SQL.ExecQuery2(query,args)
    Else
        cur=SQL.ExecQuery(query)
    End If
    Dim table As List
    table.Initialize
    Do While cur.NextRow
        Dim m As Map
        m.Initialize
        For col = 0 To cur.ColumnCount - 1
            m.Put(cur.GetColumnName(col).ToLowerCase,cur.GetString2(col))
        Next
        table.Add(m)
    Loop
    cur.Close
    Return table
End Sub

demo

Public Sub Pagination
    Dim i As Int=0
    Dim pagesize As Int=5
    Dim ts As Int
    ts=wpDBUtils.Pagination_TotalSize(G.db,"from wp_user","",Null)
    Log("totalSize:"&ts)
    Do While bEnd=False
        getPage(i,pagesize)
        i=i+1
    Loop
    Log("全部结束")
    StartMessageLoop
End Sub
Private Sub getPage(pn As Int,pagesize As Int)
    Dim lst As List= wpDBUtils.Pagination(G.db,"select * ","from wp_user","","username asc",Null,pn,pagesize)
    Log("第"&pn&"页开始")
    For Each m As Map In lst
        Log(m)
    Next
    Log("第"&pn&"页结束")
    If lst.Size<pagesize Then
        bEnd=True
        Return
    End If
End Sub