ibatis 2.0分页方式的改进
ibatis 2.0采用的是游标的方式进行分页，这种分页我认为对数据库段的压力大，且SQL也不一定高效，特别是数据量大的时候进行翻页。所以在用ibatis 2.0的时候，建议使用SQL分页。下面来做一个实验：
SQL> create table test as select * from dba_objects;
SQL> insert into test select * from dba_objects;SQL> insert into test select * from test;
SQL> create table test1 as select * from dba_objects;
202620
下面的java测试代码中游标的分页是ibatis2.0的分页方式。
import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.Statement;public class Test { static final String driver_class = "oracle.jdbc.driver.OracleDriver"; static final String connectionURL = "jdbc:oracle:thin:@10.10.29.150:1522:ordb10"; static final String userID = "test"; static final String userPassword = "test"; public static void main(String[] args) throws Exception{ Test test = new Test(); //test.queryContent(); test.queryContent1(); } public void queryContent() throws Exception { long startTime =System.currentTimeMillis(); Connection conn = null; PreparedStatement pstmt = null; ResultSet rset = null; String strSQL = "SELECT * FROM (SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM "+ " FROM (select t1.* from test t,test1 t1 where t1.object_id=t.object_id order by t1.object_name desc) INNER_TABLE "+ " WHERE ROWNUM <= 200100) OUTER_TABLE "+ " WHERE OUTER_TABLE_ROWNUM > 200000 "; try { Class.forName (driver_class).newInstance(); conn = DriverManager.getConnection(connectionURL, userID, userPassword); pstmt = conn.prepareStatement(strSQL); rset = pstmt.executeQuery (); while (rset.next ()) { String s1 =rset.getString(1); String s2 =rset.getString(2); String s3 =rset.getString(3); String s4 =rset.getString(4); String s5 =rset.getString(5); String s6 =rset.getString(6); } long endTime =System.currentTimeMillis(); System.out.println("rownum分页为:"+(endTime-startTime)+"ms"); }catch (Exception e) { e.printStackTrace(); }finally{ if(rset != null) { rset.close(); } if(pstmt != null) { pstmt.close(); } } } public void queryContent1() throws Exception { long startTime =System.currentTimeMillis(); Connection conn = null; Statement stmt = null; ResultSet rs = null; String strSQL = "select t1.* from test t,test1 t1 where t1.object_id=t.object_id order by t1.object_name desc"; try { Class.forName (driver_class).newInstance(); conn = DriverManager.getConnection(connectionURL, userID, userPassword); stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery(strSQL); int j=0; //游标移动到N条数据的位置 while(rs.next() && j++<200000){ } int i=0; //依次取出36条数据 while(rs.next() && i++<100){ String s1 =rs.getString(1); String s2 =rs.getString(2); String s3 =rs.getString(3); String s4 =rs.getString(4); String s5 =rs.getString(5); String s6 =rs.getString(6); } long endTime =System.currentTimeMillis(); System.out.println("游标分页为:"+(endTime-startTime)+"ms"); }catch (Exception e) { e.printStackTrace(); }finally{ if(rs != null) { rs.close(); } if(stmt != null) { stmt.close(); } } }}