JDBC2.0 implementation data paging
Page Operation is an essential step in our generation data table. Unlike the package of the .NET program, in the Java program, we must implement or pass some of the class libraries that provide this feature through the code. This time, in the implementation of PDM Report, some learning is performed directly using JDBC, write these things, I hope to have some help to everyone.
First, implementation method
There are three ways to implement paginity and SQL Server in JDBC2.0. They are: 1. Use the SQL statement for a particular database. For example, Select IDA in Oracle
2A
2 from (SELECT ROWNUM R, IDA
2A
2 from phse) ss where ss.r> 54084 and ss.r <= 54134 SELECT TOP 50 * from SQL Server (SELECT TOP 54134 IDA
2A
2 from phse Order by IDA
2A
2) As a order by ida
2A
2 DESC2, use the next () method in the ResultSet. This method is suitable for all DBs. For (int i = 0; i <54084; i )
{
rs.next ();
}
For (int i = 0; i <50; i )
{
rs.next ();
}
3. Use the absolute () method provided by JDBC2.0, this method is only in RS.Absolute (54084);
For (int i = 0; i <50; i )
{
rs.next ();
}
Second, data comparison
I use these three ways I have a simple test of SQL Server and Oracle's database. For two tables (PHASE) of the number of data lines on two DBs, use different paging methods, take the top 50 data and last 50 data, and calculate the time after the operation is completed. The table has 54134 row data, and the two DBs operate on the same PC. The results are as follows:
SQL for Oracle
Next for oracle
Absolute for oracle
SQL for SQL Server
Next for SQL Server
Absolute for SQL Server
First 50 row
0
0
32
78
78
703
Last 50 row
32
1515
1985
203
109
13875
First 50 row
0
0
31
78
94
468
Last 50 row
31
1703
2078
234
109
16719
First 50 row
0
0
31
78
78
453
Last 50 row
32
1578
2063
219
110
16813
It can be seen from the top-top probability data to use a specific SQL statement in the Oracle database and basically not affected by the size of the data line. The reason why the data in the table in the table is zero, and the estimation is mainly affected by Cache. The time gap in other two ways is obvious.
Some of the SQL Server cases, the use of next is the fastest, making it difficult to understand. Sort by specific SQL statement will be subject to the number of rows. The speed can still be endured.
For two DBs, the cost of the Absolute method is unacceptable. Everyone will use less.
In addition, SQL Server seems to not support the parameters behind TOP as the parameters of prepare Statement, this place please pay attention (if any discovery is not true, please tell me).
PS: About Hibernate
Pre-use in Hibernate is the Absolute method. Code is as follows if (. Session.getFactory () useScrollableResultSets ()) {// we can go straight to the first required row rs.absolute (firstRow);} else {// we need to step through the rows one row at a time (SLOW) for (int m = 0; m There is no problem with DBs that can be paid directly using the SQL statement, but SQL Server does not support offset operations in Hibernate, which means that the absolute method will be performed, so that his paging efficiency is more worried. . If you have someone using SQL Server Hibernate, please pay attention to this.
