Sybase存储过程分页

查询方法

	/**
     * 存储过程 分页查询
     *
     * @param sql
     * @param pageNo
     * @param pageSize
     * @return 只返回数据，不需返回分页信息
     */
    public List<Map<String, Object>> listDataBySplitPage(String sql, int pageNo, int pageSize) {
        List<Map<String, Object>> mapList = new ArrayList<>();
        ResultSet rs = null;
        CallableStatement prepareCall = null;
        try (Connection con = dataSource.getConnection()) {
            prepareCall = con.prepareCall("{call splitpage_sybase(?,?,?)}");
            prepareCall.setString(1, sql);
            prepareCall.setInt(2, pageNo);
            prepareCall.setInt(3, pageSize);
            rs = prepareCall.executeQuery();
            while (rs.next()) {
                Map<String, Object> resultMap = new HashMap<String, Object>();
                ResultSetMetaData metaData = rs.getMetaData();
                int colCount = metaData.getColumnCount();
                for (int j = 1; j <= colCount; j++) {
                    String rawColName = metaData.getColumnLabel(j);
                    String colName = rawColName.toLowerCase();
                    Object value = this.transResultSetColumn2Object(rs, metaData, j);
                    resultMap.put(colName, value);
                }
                mapList.add(resultMap);
            }
        } catch (Exception e) {
            logger.error("Executor listDataBySplitPage error={}", ErrorUtil.errorInfoToString(e));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    logger.error("Executor listDataBySplitPage rs close error={}", ErrorUtil.errorInfoToString(e));
                }
            }
            if (prepareCall != null) {
                try {
                    prepareCall.close();
                } catch (SQLException e) {
                    logger.error("Executor getListByPage listDataBySplitPage close error={}", ErrorUtil.errorInfoToString(e));
                }
            }
        }
        return mapList;
    }

存储过程

CREATE PROCEDURE dbo.splitpage_sybase @qry varchar(16384),@ipage int,@num int 
as
begin
  
  declare @rcount int
  declare @execsql varchar(16384)
  declare @hz varchar(36)
  select @rcount=@ipage*@num
  set @hz=convert(varchar,convert(int,RAND() * 10000))
  set @execsql = 'set rowcount '|| convert(varchar,@rcount)
  set @qry=  str_replace(@qry,'$','')
  set @execsql = @execsql || stuff(@qry,charindex('SELECT ',upper(@qry)),6,' SELECT sybid=identity(12),')
  set @execsql = stuff(@execsql,charindex(' FROM ',upper(@execsql)),6,'  INTO  #temptable'||@hz||' FROM ')
  set @execsql = @execsql || ' SELECT * FROM  #temptable'||@hz||'  WHERE sybid >' || convert(varchar,(@ipage-1)*@num)
  set @execsql = @execsql || ' AND sybid<=' || convert(varchar,@ipage*@num) + ' set rowcount 0'
  execute(@execsql)
  
end