sqlServer2008 分页

jndi方式：

基础SQL：select top 10
m.str_modelName,m.dt_creatTime,m.str_creatorName,m.str_standard,m.int_parentID from t_modelLevel m
where   m.int_isFact != 1  and m.str_standard ='gsm'
and m.int_ID not in
(select top 0 mm.int_ID from t_modelLevel mm where  int_isFact != 1 and  str_standard ='gsm' order by mm.dt_creatTime
)
order by m.dt_creatTime

=====================================

public ActionForward execute(ActionMapping mapping, ActionForm form,
   HttpServletRequest request, HttpServletResponse response) {

   int currPageIndex = 1;
   int jumpPageIndex = 1;
   int pageSize = 20;
   int pageCount = 0;

   int index = 0;
   Map<String, Object> modelMap = null;
   String sql = getQuerySql(standard, queryModelName,
     queryModelAuthor, currPageIndex, pageSize);

   String jndi = "java:comp/env/jndi/ibms/NetworkModelDatabase";
   try {
    conn = GetConnection.getConnection(jndi);
    ps = conn.prepareStatement(sql);
    rs = ps.executeQuery();
    while (rs.next()) {
     
     modelMap = new HashMap<String, Object>();
     index = index + 1;
     
     modelName = rs.getString("name");
     createTime = rs.getTimestamp("crdate");
     createModelDateStr = simpleDateFormat.format(createTime);
     createAuthor = rs.getString("str_creatorName");
     standard = rs.getString("str_standard");
     viewTimes = rs.getInt("int_viewTimes");
     parentID = rs.getInt("int_parentID");
     remark = rs.getString("str_modelSummary");
     levelName = getLevelNameByParentID(indisvr1,parentID);
     modelMap.put("index", String.valueOf(index));
     modelMap.put("remark", remark);
     modelMap.put("standard", standard);
     modelMap.put("viewTimes", viewTimes);
     modelMap.put("modelName", modelName);
     modelMap.put("createTime", createModelDateStr);
     modelMap.put("createAuthor", createAuthor);
     modelMap.put("levelName", levelName);
     
     modelNameList.add(modelMap);

    }

   } catch (Exception e) {
    e.printStackTrace();
   } finally {
    CloseConnection.closeConnection(rs, ps, conn);
   }

   pageCount = getPageCount(standard, queryModelName,queryModelAuthor, pageSize);

    request.setAttribute("pageSize", pageSize);
    request.setAttribute("pageCount", pageCount);
    request.setAttribute("modelList", modelNameList);
    request.setAttribute("currPageIndex", currPageIndex);

    return mapping.findForward(FORWARD_SUCCESS);

}

=============================================

/**
  * 拼接查询sql
  *
  * @param standard
  * @param queryModelName
  * @param queryModelAuthor
  * @return
  */
 private String getQuerySql(String standard, String queryModelName,String queryModelAuthor, int pageIndex, int pageSize) {

  int page = (pageIndex - 1) * pageSize;

  String sql = "select top "
    + pageSize
    + " s.name,s.crdate,m.str_creatorName,m.str_standard,m.int_viewTimes,m.int_parentID,m.str_modelSummary from sysobjects s "
    + "left join t_modelLevel m on m.str_modelName = s.name "
    + "where s.xtype='U' " + "and s.name != 't_comment' "
    + "and s.name != 't_modelAndIndicatorIndex' "
    + "and s.name != 't_modelLevel' "
    + "and s.name != 't_onlyIndicatorInfo' "
    + "and s.name != 't_primIndicatorInfomation' "
    + "and s.name != 't_referenceInformation' "
    + "and s.name != 't_resultSet' "
    + "and s.name != 't_tableAndColumnIndex' "
    + "and m.int_edit = 2";
  
  String sql2 = "select top " + page + " s.crdate from sysobjects s "
    + "left join t_modelLevel m on m.str_modelName = s.name "
    + "where s.xtype='U' " + "and s.name != 't_comment' "
    + "and s.name != 't_modelAndIndicatorIndex' "
    + "and s.name != 't_modelLevel' "
    + "and s.name != 't_onlyIndicatorInfo' "
    + "and s.name != 't_primIndicatorInfomation' "
    + "and s.name != 't_referenceInformation' "
    + "and s.name != 't_resultSet' "
    + "and s.name != 't_tableAndColumnIndex' "
    + "and m.int_edit = 2";

  if (!CheckUtils.isNullOrBlank(queryModelName)) {
   sql = sql + " and m.str_modelName like N'%" + queryModelName + "%'";
   sql2 = sql2 + " and m.str_modelName like N'%" + queryModelName
     + "%'";
  }
  if (!CheckUtils.isNullOrBlank(queryModelAuthor)) {
   sql = sql + " and m.str_creatorName like N'%" + queryModelAuthor
     + "%'";
   sql2 = sql2 + " and m.str_creatorName like N'%" + queryModelAuthor
     + "%'";
  }
  sql = sql + " and s.crdate not in (" + sql2
    + " order by s.crdate) order by s.crdate";
  return sql;

 }

===============================================================

// 获取总分页数
 private int getPageCount(String standard, String queryModelName,String queryModelAuthor, int pageSize) {
  int pageCount = 0;
  int count = 0;
  
  PreparedStatement ps = null;
  Connection conn = null;
  ResultSet rs = null;
  
  String jndi = "java:comp/env/jndi/ibms/NetworkModelDatabase";
  conn = GetConnection.getConnection(jndi);
  String sql = "select COUNT(*) from sysobjects s "
    + "left join t_modelLevel m on m.str_modelName = s.name "
    + "where s.xtype='U' " + "and s.name != 't_comment' "
    + "and s.name != 't_modelAndIndicatorIndex' "
    + "and s.name != 't_modelLevel' "
    + "and s.name != 't_onlyIndicatorInfo' "
    + "and s.name != 't_primIndicatorInfomation' "
    + "and s.name != 't_referenceInformation' "
    + "and s.name != 't_resultSet' "
    + "and s.name != 't_tableAndColumnIndex' "
    + "and m.int_edit = 2";
  
  if (!CheckUtils.isNullOrBlank(queryModelName)) {
   sql = sql + " and m.str_modelName like N'%" + queryModelName + "%'";

  }
  if (!CheckUtils.isNullOrBlank(queryModelAuthor)) {
   sql = sql + " and m.str_creatorName like N'%" + queryModelAuthor+ "%'";

  }
  try {
   ps = conn.prepareStatement(sql);
   rs = ps.executeQuery();
   while (rs.next()) {
    count = rs.getInt(1);
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   CloseConnection.closeConnection(rs, ps, conn);
  }
  pageCount = count / pageSize;
  if (count % pageSize != 0) {
   pageCount = pageCount + 1;
  }

  return pageCount;
 }

 

 

===============================================================================

 

hibernate方式：

 

@Override
 public List findAllByPage(int pageSize,int currPageIndx,Map<String, String> parameters) {
  // TODO Auto-generated method stub
  int firstRecordIndex = (currPageIndx - 1) * pageSize;
  String noSeeAdmin = " and u.strUserRole != '4'";
  if(parameters.size()>0){
   noSeeAdmin = "";
  }
  String hql = " from TbUserInfoBak as u where 1=1 "+noSeeAdmin+" order by u.IUserId";
  
    Session   session   =   getSession();
    Query query = session.createQuery(hql);
    //query.setMaxResults(10);
    query.setFirstResult(firstRecordIndex);
    query.setFetchSize(pageSize);
    query.setMaxResults(pageSize);

    List list = query.list();
    session.close();
    return list;

   
 }

 @Override
 public int findAllCount(){
   int count = 0;
   String hql = "select count(u) from TbUserInfoBak u";
   Session   session   =   getSession();
   session.beginTransaction(); 
   Object obj = session.createQuery(hql).iterate().next();
   if(obj!=null){
   
    count =((Integer)obj).intValue();
   }
   session.close();
   return   count;
  

}