Groovy SQL 分页组件

组件：groovy 2.2 jtds sqlserver 2000

/*

 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package apps.plugs.shop.crm.SiteReguser.domain

import apps.plugs.groovyExt.dbUtil.DefaultGroovySqlDbUtil
import apps.plugs.shop.sitemap.dao.ComanyInfo
import groovy.sql.Sql

/**
 *
 *
 * 网站注册会员
 *
 * 企业分页显示 *
 *
 * @author Administrator
 *
 * apps.plugs.shop.crm.SiteReguser.domain.ProductPageViewGroovyClass
 */
class ProductPageViewGroovyClass {

    //select top 3000 * from v_member_basic_list where  userid=1989 and  type=1 and auditing=1 and ( trueeship ='0' ) and (langid is null or langid=1) ORDER BY fee desc,regtime desc
        
    def dbUtil=new DefaultGroovySqlDbUtil();
    
    //每页30条
    def pageRows=30;
    
    //总页数
    def pageTotal=0;
    
    //当页面
    
    def currentPage=1;
          
    
 
    //总页数
    def getTotalPageCount(){
        def rowTotal=getRowTotal();
        
        def pagesize=(int) (rowTotal/pageRows)
        
        //余数不显示
        return pagesize;
    }
    
    //总记录数
    def getRowTotal(){
        
            def sql_str="select count( *)as row_size from Vmember_basic_list where  userid=1989 and  type=1 and auditing=1 and ( trueeship ='0' ) and (langid is null or langid=1)"
        
        
        Sql sql=this.dbUtil.getGroovySql()
         
        def rs
        
       // def c=0
        
        
        sql.eachRow(sql_str){
            
            row_total ->
            
           //获取数据组值
           println row_total.row_size
           
          rs=row_total.row_size
            
           // c++
           // println c
            
        }
        
       //  println rs
         
        return rs
    }
    
    
    //查询分页数据
    
    
    def getPageData(int pageIndex){
        
        println "查询页面：${pageIndex}"
        //起始记录
        
        def dataList=new java.util.ArrayList<ComanyInfo>();
        
        
        int startRow=(pageIndex-1)*this.pageRows;
        
               
        def sql_str="select top ${this.pageRows.toString()} * from Vmember_basic_list where (basicid not in ( select top ${startRow.toString()} basicid from  Vmember_basic_list  ORDER BY fee desc,regtime desc)) ORDER BY fee desc,regtime desc"
    
            println sql_str
        //第一页 OK
        //def sql_str="select top 30 * from v_member_basic_list where (basicid not in ( select top 0 basicid from  v_member_basic_list )) ORDER BY fee desc,regtime desc"
    
        //将GString 转换为String 类型数据
        def  sql_str2=sql_str.toString()
           
        Sql sql=this.dbUtil.getGroovySql()
        
        sql.eachRow(sql_str2){
            rs ->
            
          println   rs.basicid
          
            //
          def cmp=new   ComanyInfo()
          
            println "${rs.companyname}"
              // companyname
     cmp.setCompanyname(rs.companyname)
     
            cmp.setAttachtypename(rs.attachtypename)
            // name
            cmp.setName(rs.name)
            
        // email;
         cmp.setEmail(rs.email)
        // basicid;
        cmp.setBasicid(rs.basicid)
        // tel
         cmp.setTel(rs.tel)
        // mobile
           cmp.setMobile(rs.mobile)

    // name1
     cmp.setName1(rs.name1)
    // name2
     cmp.setName2(rs.name2)
    // attachtypename
           
            dataList.add(cmp)
        }
   
        return dataList
    }
    
    
    //查询分页数据
    
    //select top 30 * from v_member_basic_list where (basicid not in ( select top 310  basicid from  v_member_basic_list )) ORDER BY fee desc,regtime desc
    
    static void main(argvs){
        
        
        ProductPageViewGroovyClass dao=new ProductPageViewGroovyClass()
        
      println "总记录数：${dao.getRowTotal()} "
      
        println "总页数${dao. getTotalPageCount()}"
            
       println  "查询1页数据 :${dao.getPageData(1).size()}"
       
       println  "查询2页数据 :${dao.getPageData(2).size()}"
       
         println  "查询未页数据 :${dao.getPageData(dao. getTotalPageCount()).size() }"
       
    }
}

显示页gsp文件

<%
    def baseDir=request.getContextPath()
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
    
    
<head>    
    <title>网站注册会员</title>
    <meta charset="UTF-8">
    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">    
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">
    <!--
    <link rel="stylesheet" type="text/css" href="styles.css">
    -->

        <style type="text/css">
              body{ background-color:#ffffff;}
            #wrap{ width:700px;}            
            .dataListUL{}
            .dataListUL ul{}
            .dataListUL ul  li{ overflow:hidden; height:35px; float:left; width:280px;  margin:5px; padding:5px; border:solid 1px #DDDDDD;}
           
            .dataListUL ul  li a{color:#333333;}
        </style>
</head>

</body>


<div id="wrap">

<h1>
网站注册会员
</h1>


<div id="pagebar"  style="border:solid 1px #dddddd; padding: 10px;">
    
   总记录： ${request.getAttribute("rowTotal")}
   
   总页数： ${request.getAttribute("pateTotal")}
    
   当前显示：第${request.getAttribute("pageIndex")} 页
</div>



 <div  class="dataListUL">
     
        <ul>
            
           <%
        
                def dataList=request.getAttribute("dataList")
                
                    dataList.each(){
                            rs ->
                            
                         println "<li>"
                         
                                println "<a href='${baseDir}/apps/medCmpListForSaler/action/CmpViewAction.groovy?uid=${rs.basicid}'>${rs.companyname}</a>"
                                          
                             //   println "<td>${rs.attachtypename}</td>"
                             //    println "<td>${rs.name}</td>"
                             //     println "<td>${rs.email}</td>"
                               //    println "<td>${rs.basicid}</td>"
                                  //    println "<td>${rs.tel}</td>"
                                        //  println "<td>${rs.mobile}</td>"
                                            
                                     // println "<td>${rs.name1}</td>"
               
                                   //  println "<td>${rs.name2}</td>"
        
                                      println "</li>"
            
                    }
        %>
        </ul>
            
          
 </div>


        <div style="clear:left;"></div>
 
 <div id="pagebar"  style="border:solid 1px #dddddd; padding: 10px;">
     <%
def currentPageIndex= request.getAttribute("pageIndex")

     def upPage=currentPageIndex-1;
     
            if(upPage<0){
                upPage=1  
            }
            
     def nextPage=currentPageIndex+1
     
           def pateTotal_2= request.getAttribute("pateTotal")
            
            if(nextPage>pateTotal_2  ){
                
                nextPage=pateTotal_2
            }
    
    
         %>
     
     <a href="CmpListForSalerAction.groovy?pageIndex=1">
     首页
     </a>
     
      <a href="CmpListForSalerAction.groovy?pageIndex=${ upPage }">
  上一页
     </a>
          
    
     
      <a href="CmpListForSalerAction.groovy?pageIndex=${nextPage}">
  下一页  
 
     </a>
     
     <a href="CmpListForSalerAction.groovy?pageIndex=${request.getAttribute("pateTotal")}">
     未页
     </a>
     
     
     当前：  <a href="CmpListForSalerAction.groovy?pageIndex=${request.getAttribute("pageIndex")}">
    ${request.getAttribute("pageIndex")}
     </a>
     
     
 </div>
 
 
 </div>
 <!-- wrap -->
 
</body>
</html>