Sybase 大資料量(100W條)分頁 jdbc實現目前要求無排序

首次分頁大概為10秒左右。以後翻頁基本上是瞬間完成。要求是無排序情況下。

分析：sybase不提供分頁方法。top函式還不能放在子查詢語句中。使用hibernate分頁前幾頁和後幾頁沒問題，如果資料量大翻到30000頁時就記憶體溢位了。而且效率慢。

使用儲存過程分頁大資料量也不適合。效率低而且不支援併發。

由於 select top pageSize column1,column2,column3 from tableName where id > ?這種語句也相當快但如果資料列多會有大量流量產生。

select count(*) from table   這個語句比較慢100W條也得10秒

綜上所述，分三個步驟

1，把所資料總數取出來

      如果每次都從資料庫取會浪費不必要的時間。所以第一次載入的時候使用快取儲存起來。

     SQL:select count(*) from table

2，找到需呀資料的id

      如果資料量比較大100W條，如果查詢第50W條速度也比較慢。所以第一次載入的時候需要快取，快取的內容為資料的id以及位置。

      例如：100W條資料快取50個位置，每2W條一個 。那麼從100W條資料中找某一個id就相當於從2W條中找

     SQL:select top pageSize+startNumber id from tableName where id > ?

3，把資料取出來

     SQL:select top pageSize column1,column2,column3 from tableName where id >=?

 

/**
   * jdbc分頁add by wangmeng 2013-4-18
   * 要求單表，無子查詢，無關聯查詢 
   * @param sql 執行sql語句
   * @param cls 封裝資料表
   * @param id  id列名
   * @param startNum 從哪條開始。0...n
   * @param pageSize 每頁條數
   * @return
   */
  public Page findPageBySql( final String sql, Class cls,final String id, int startNum,
      final int pageSize) {
    final Page page = new Page();
    try{
            String execsql = sql;
            String sql2 = sql.toLowerCase();
            long btime = System.currentTimeMillis();
            long etime ;
                  final Connection con = JDBCUtil.getConnection();
                  PreparedStatement stmt;
                  ResultSet rs ;
            String counthql=sql2;
            int cacount = CacheUtil.getTotalSize(sql);//讀取總數快取
                  int total = 0;
            if(cacount == -1){//沒有快取
              if(counthql.indexOf("order")>-1){
                counthql="select count(*) "+counthql.substring(counthql.indexOf("from"), counthql.lastIndexOf("order"));
              }else{
                counthql="select count(*) "+counthql.substring(counthql.indexOf("from"), counthql.length());
              }
  
              System.out.println(counthql);
  
              btime = System.currentTimeMillis();
                    stmt = con.prepareStatement(counthql);
                    rs = stmt.executeQuery();
                    rs.next();
                    page.setTotalCount(rs.getInt(1));
                    total = rs.getInt(1);
                    CacheUtil.setTotal(sql, total);
            }else{
              total = (Integer)cacount;
                    page.setTotalCount(total);
            }
                  if(total <=0){
                  	return page;
                  }
            etime = System.currentTimeMillis();
            System.out.println("countsql處理時間："+(etime - btime));

            btime = System.currentTimeMillis();
                  if(total < 1000){//小資料量處理
              System.out.println(execsql);
                    stmt = con.prepareStatement(execsql);
                    rs = stmt.executeQuery();
                    int var = 0;
                    while(var++<startNum && rs.next());
              List list = CloneUtil.cloneResultSet2List(rs,cls,null,pageSize);
              page.setData(list);
                  }else{//大資料量處理
                  	String t = sql2.substring(sql2.indexOf("from")+5);//獲取表名
              String idsql = "select "+id+" from "+t.trim().split(" ")[0]+" "; //先查詢id的sql
              if(sql2.contains("where")){//拼where子句
                if(sql2.contains("order by")){
                  idsql += sql2.substring(sql2.indexOf("where"),sql.indexOf("order by"));
                }else{
                  idsql += sql2.substring(sql2.indexOf("where"));
                }
              }
              int orderIndex = sql2.indexOf("order by");
              final String cachidsql = idsql; 
              if(orderIndex == -1){//無排序可以增加快取進行快速查詢
                if(CacheUtil.isInitIndex(sql)){//有快取
                  Entry<Integer, Object> entry = CacheUtil.getFloorEntry(sql, startNum);
                  if(entry == null){
                    
                  }
                  startNum -= entry.getKey();
                  if(idsql.contains("where")){
                    idsql +=" and "+id+" >= "+entry.getValue();
                  }else{
                    idsql +="where "+id+" >= "+entry.getValue();
                  }
                  idsql = "select top "+(startNum+pageSize)+idsql.substring(idsql.indexOf("select")+6);
                }else{//沒快取增加
                  new Thread(){
                    @Override
                    public void run() {
                      try {
                        Connection c = JDBCUtil.getConnection();
                        ResultSet rs = c.prepareStatement(cachidsql+ " order by "+id).executeQuery();
                        int i =0;
                        int cap = CacheUtil.getIndexSize(sql);
                        Map map = new HashMap();
                              while(rs.next() ){
                              	if(i % cap ==0){
                              		map.put(i, rs.getInt(1));
                              	}
                              	i++;
                        }
                              CacheUtil.initPageIndex(sql, map);
                      } catch (Exception e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                      }
                    }
                  }.start();
                }
                idsql += " order by "+id;
              }
              System.out.println(idsql);
              
                    stmt = con.prepareStatement(idsql);
                    rs = stmt.executeQuery();
                    int var = 0;
                    while(var++<startNum && rs.next());
                    int i = 0;
                    List ids = new ArrayList();
                    while(rs.next() && i++ < pageSize){
                    	ids.add(rs.getObject(1));
              }
              etime = System.currentTimeMillis();
              System.out.println("idsql處理時間："+(etime - btime));
              btime = System.currentTimeMillis();
              StringBuilder sbsql = new StringBuilder();
              if(orderIndex == -1){//無排序使用id>=?方式
                sbsql.append(" where ").append(id).append(">=").append(ids.get(0));
                execsql = "select top "+pageSize+execsql.substring(execsql.toLowerCase().indexOf("select")+6);
              }else{//有排序使用id=? or id=?
                sbsql.append(" where (");
                for (int j = 0; j < ids.size(); j++) {
                  if(sbsql.indexOf("("+id) != -1){
                    sbsql.append(" or ");
                  }
                  sbsql.append(id +" = ").append(ids.get(j));
                }
                sbsql.append(")");
              }
              if(!execsql.toLowerCase().contains("where")){
                execsql += sbsql.toString();
              }else {
                execsql = execsql.substring(0, execsql.toLowerCase().indexOf("where"))+sbsql.toString();
              }
              System.out.println(execsql);
              QueryBySqlResultSet qbc = new QueryBySqlResultSet(execsql,cls,pageSize);//使用hibernate取資料
//				            stmt = con.prepareStatement(execsql);
//				            rs = stmt.executeQuery();
              List list  = (List)getHibernateTemplate().execute(qbc);
              etime = System.currentTimeMillis();
              System.out.println("查詢資料時間："+(etime - btime));
              page.setData(list);
                  }
            btime = System.currentTimeMillis();
            new Thread(){
              public void run() {
                try {
                  JDBCUtil.closeConnection(con);
                } catch (SQLException e) {
                  // TODO Auto-generated catch block
                  e.printStackTrace();
                }
              };
            }.start();
            etime = System.currentTimeMillis();
            System.out.println("rs關閉時間："+(etime - btime));
    }catch(Exception e){
      e.printStackTrace();
    }
    return page;
  }

CacheUtil快取工具類

import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Map.Entry;

import com.metarnet.eoms.common.base.model.SQLCacheInfo;
/**
 * 給資料庫表記錄數和Id增加快取
 * @author wangmeng 
 *
 */
public class CacheUtil {
  private static LinkedHashMap<String,Object> lmap = new LinkedHashMap<String,Object>(){
    private static final long serialVersionUID = -3432076593791024110L;
    //建立一個LinkedHashMap匿名內部類最大size是30超過30自動刪除第一個
    private final static int MAX_SIZE = 30;
    protected boolean removeEldestEntry(java.util.Map.Entry<String,Object> eldest) {
      return size()>MAX_SIZE;
    };
  };
  private CacheUtil(){};
  /**
   * 快取總數
   * @param key
   * @param value
   */
  public static void  setTotal(String key,int value){
    SQLCacheInfo info = (SQLCacheInfo)lmap.get(key);
    if(info == null){
      info = new SQLCacheInfo();
    }
    info.setTotalSize(value);
    lmap.put(key, info);
  }
  public static int getTotalSize(String key){
    SQLCacheInfo info = (SQLCacheInfo)lmap.get(key);
    if(info == null){
      return -1;
    }
    return info.getTotalSize();
  }
  /**
   * 快取id位置
   * @param key
   * @param map
   */
  public static void initPageIndex(String key, Map<Integer,Object> map){
    SQLCacheInfo info = (SQLCacheInfo)lmap.get(key);
    if(info == null){
      info = new SQLCacheInfo();
    }
    info.put(map);
  }
  /**
   * 返回id位置資訊
   * 
   * @param key 
   * @param index
   * @return
   */
  public static Entry<Integer, Object> getFloorEntry(String key,Integer index){
    SQLCacheInfo info = (SQLCacheInfo)lmap.get(key);
    if(info == null){
      return null;
    }
    return info.getEntry(index);
  }
  public static Entry<Integer, Object> getCeilEntry(String key,Integer index){
    SQLCacheInfo info = (SQLCacheInfo)lmap.get(key);
    if(info == null){
      return null;
    }
    return info.getCeilEntry(index);
  }
  /**
   * 返回快取id位置的數量
   * @param key
   * @return
   */
  public static int getIndexSize(String key){
    SQLCacheInfo info = (SQLCacheInfo)lmap.get(key);
    if(info == null){
      return -1;
    }
    return info.getIndexSize();
  }
  public static boolean isInitIndex(String key){
    SQLCacheInfo info = (SQLCacheInfo)lmap.get(key);
    if(info == null){
      throw new RuntimeException(key+"沒有找到");
    }
    return info.isInitIndex();
  }
}

快取資訊SQLCacheInfo

import java.util.HashMap;
import java.util.Map;
import java.util.TreeMap;
import java.util.Map.Entry;

public class SQLCacheInfo {
  /**
   * 第多少條，id值多少
   */
  private  TreeMap<Integer,Object> IndexId = new TreeMap<Integer,Object>();
  public static final int MOD = 50;
  public static final int CAPTION = 10000;
  private int totalSize;
  public int getTotalSize() {
    return totalSize;
  }
  public void setTotalSize(int totalSize) {
    this.totalSize = totalSize;
  }
  /**
   * 根據條數返回id值
   * @param index
   * @return
   */
  public Entry<Integer,Object> getEntry(int index){
    return IndexId.floorEntry(index);
  }
  public Entry<Integer,Object> getCeilEntry(int index){
    return IndexId.ceilingEntry(index);
  }
  public void put(Map<Integer,Object> map){
    IndexId.putAll(map);
  }
  public int getIndexSize(){
    if(totalSize < 10000)
      return -1;
    return Math.max(CAPTION,(totalSize / 50));
  }
  public static void main(String[] args) {
    SQLCacheInfo info = new SQLCacheInfo();
    HashMap map = new HashMap();
    map.put(10, 2);
    map.put(40, 2);
    map.put(60, 2);
    map.put(80, 2);
    info.put(map);
    System.out.println(info.getEntry(100).getKey());
  }
  public boolean isInitIndex(){
    return this.IndexId.size()>0;
  }
}

Reference:

http://www.tuicool.com/articles/ryIjIn