一种Java调用Sybase存储过程实现取前N条数据的方法

一.在Sybase中创建存储过程如下：

drop procedure getPageWiseData
go
 
create procedure getPageWiseData  
(  
@sqlStr varchar(8000),  
@start int,  
@limit int 
)  
as  
DECLARE @dt varchar(10)    --生成临时表的随机数  
BEGIN  
 
--# variable to hold the first row number of the page.  
SELECT @dt= substring(convert(varchar, rand()), 3, 10)    --一个字符型的随机数  
 
SELECT @sqlStr = stuff(@sqlStr, 1, 7, 'select rownum=identity(12), ')  
SELECT @sqlStr = stuff(@sqlStr, charindex(' FROM ', upper(@sqlStr)), 6 ,' into tempdb..Lining' + @dt + ' from ')  
execute (@sqlStr)  
 
--# select the data with the calculated range for first and last row on page.  
select @sqlStr = 'select * from tempdb..Lining' + @dt + ' where rownum >= '+convert(varchar, @start)+' and rownum < '+convert(varchar, (@start+@limit))   
execute (@sqlStr)  
 
--删除临时表  
SELECT @sqlStr = 'DROP TABLE tempdb..Lining'+@dt 
EXECUTE (@sqlStr)   
END

  

二.用jdts驱动调用Sybase数据库

public class JdbcSybaseProcedure {
 public static void main(String[] args) {
  Connection conn = null;
  CallableStatement cs = null;
  try {
   Class.forName("net.sourceforge.jtds.jdbc.Driver");
   conn = DriverManager.getConnection("jdbc:jtds:sybase://192.9.190.98:4100/inner_dbs", "emp", "empemp");
   //下面的意思要调用那个存储过程，存储过程名字是getPageWiseData;
   cs = conn.prepareCall("{call getPageWiseData(?,?,?)}");
   cs.setString(1, "select * from lps_mst order by pan");
   cs.setInt(2, 1);
   cs.setInt(3, 5);
   //执行存储过程
   ResultSet rs = cs.executeQuery();
   while(rs.next()) {
    System.out.println(rs.getString("pan"));
   }
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    cs.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }finally {
    cs = null;
   }
   try {
    conn.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }finally {
    conn = null;
   }
  }
 }
}