Shulou(Shulou.com)06/02 Report--

This article is to share with you about the SQL Server 2000 JDBC query paging solution is how, the editor feels very practical, so share with you to learn, I hope you can get something after reading this article, say no more, follow the editor to have a look.

There is no more nonsense about how to add msbase.jar,mssqlserver.jar,msutil.jar to the classpath of the project. What needs to be said is that the SQL Server 2005 JDBC query driver sqlJDBC.jar I used was put into the project, and the later program reported an error. In retrospect, it makes sense to report an error. SQL Server 2005 already supports rownum paging.

First of all, let's talk about the implementation of paging in SQL Server 2000. At present, there are probably three ways to implement it. Personally, I still like to use stored procedures, because it is very convenient to use. As for the stored procedures used, I still put them out here to have a look. It is estimated that everyone uses more or less the same.

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID (N' [dbo]. [Pr_QueryByPage]') AND OBJECTPROPERTY (id,N'IsProcedure') = 1) BEGIN EXEC dbo.sp_executesql @ statement = N'create procedure [dbo]. [Pr_QueryByPage] @ sqlstr nvarchar (4000),-- query sql @ currentpage int,-- number of records on page @ pagesize int-- as set nocount on declare @ P1 int per page P1 is the ID @ rowcount int exec sp_cursoropen @ P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output select ceiling (1.0*@rowcount/@pagesize) as TotalPage,@rowcount as [RowCount] set @ currentpage= (@ currentpage-1) * @ pagesize+1 exec sp_cursorfetch @ P1 pageSize exec sp_cursorclose @ P1 set nocount off 'END GO.

The implementation of this stored procedure uses three system stored procedures sp_cursoropen, sp_cursorfetch, and sp_cursorclose, which literally means that he asks the result set to open with cursors, and then reads the @ pageSize records in it, so in terms of query alone, the performance is not as good as that of using implementations such as select top.

It's very easy to use, just exec Pr_QueryByPage 'select * from yourtable',1,10. The trouble is that he returns three tables. * A table is a query table, but there is no record. The second table has a row and two columns, the * columns are the total number of pages, and the second column is the total number of records. The third table is the data you need. This makes it a little troublesome to pick up, because I only knew that you can fill (DataSet) directly in. Net, and then you can get DataTable in DataSet. But there is only one table in my image of ResultSet in JDBC. Later, I found some information, and it turns out that PreparedStatement,CallableStatement,Statement supports query to return multiple ResultSet. OK, very good. Here is the result set I got using CallableStatemnt.

CallableStatement cs = conn.prepareCall ("exec Pr_QueryByPage 'select * from ckdmzd',1,10"); ResultSet rs = null; / * execute returns: * true: returns ResultSet (s) * false: returns rows affected * / boolean hasResultSet = cs.execute () If (hasResultSet) {/ * skip the first ResultSet * / rs=cs.getResultSet () / * * second ResultSet: pageCount & recordCount * / if (cs.getMoreResults ()) {rs=cs.getResultSet (); while (rs.next ()) {String pageCount=rs.getString (1) String recordCount=rs.getString (2);}} / * the thrid one is the paged result * / if (cs.getMoreResults ()) {rs=cs.getResultSet () While (rs.next ()) {/ / do somthing with ResultSet}

In this way, paging is achieved, and many people on the Internet have tested it. The performance of this method is not as good as that of other methods. I would like to point out here that other methods cannot return the total number of records. To know the total number of records, it usually takes less time for select count (*) from (your sql) to call up these two queries.

Summary of JDBC query

Using the above method to achieve SQL Server 2000 JDBC query paging, easy to use, the performance is reasonable. I don't know if the query performance of SQL Server 2005 has improved again.

The above is what the JDBC query pagination solution is under SQL Server 2000. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.