使用JDBC的ResultSet.absolute实现通用数据库分页

使用JDBC的ResultSet.absolute实现通用数据库分页

由于不同的数据库，对应的分页功能用SQL实现起来通用性不是很好，但是我们可以通过JDBC的ResultSet游标的滚动来实现分页。首先在创建Statement对象的时候需要允许resultset游标滚动，现这样的ResultSet在创建Statement时用如下的方法

Statement st =conn.createStatement(int resultSetType, int resultSetConcurrency)

其中两个参数的意义是：（。）

resultSetType取值如下，设置ResultSet对象的类型标示可滚动，或者是不可滚动

ResultSet.TYPE_FORWARD_ONLY  ----   只能向前滚动(这是默认值)

以下这两个参数值都能够实现任意的前后滚动，使用各种移动的ResultSet指针的方法。二者的区别在于前者对于修改不敏感，而后者对于修改敏感

ResultSet.TYPE_SCROLL_INSENSITIVE   

ResultSet.TYPE_SCROLL_SENSITIVE

resultSetConcurrency是设置ResultSet对象能够修改的，取值如下：

ResultSet.CONCUR_READ_ONLY ----   设置为只读类型的参数

ResultSet.CONCUR_UPDATABLE  ----   设置为可修改类型的参数

所以如果只是想要可以滚动的类型的ResultSet，只要按照如下赋值即可

Statement  stm = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

 

public static void main(String[] args) {
        Connection conn = null;
        Statement stm = null;
        ResultSet rs = null;
        try {
            int rows = 20;//每页的行数
            //for循环中的变量j可以理解为页数
            for(int j = 1; j <= 5; j++) {
                System.out.println("-------------------------第" + j +"页------------------------");
                StringBuffer sqlBuffer = new StringBuffer();
                sqlBuffer.append("select * from soa_sys_user order by user_id ");
                conn = JdbcUtils.getConnection();
                stm = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                int endIndex = j > 0 ? j*rows : 1 * rows;
                stm.setMaxRows(endIndex);
                rs = stm.executeQuery(sqlBuffer.toString());
                int beginIndex = (j - 1) * rows;
                if(beginIndex > 0) {
                    rs.absolute(beginIndex);
                }
                int index = 0;
                String[] columns = new String[] {"user_id", "user_name"};
                while (rs.next()) {
                    Map<String, Object> map = new HashMap<String, Object>();
                    for (int i = 0; i < columns.length; i++) {
                        String columnName = columns[i].toLowerCase();
                        map.put(columnName, rs.getObject(columnName));
                    }
                    index ++;
                    System.out.println("row num : " + index + ", "+ map);
                }
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != conn) {
                    conn.close();
                }
                if (null != stm) {
                    stm.close();
                }
                if (null != rs) {
                    rs.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

执行结果如下：

-------------------------第1页------------------------
row num : 1, {user_id=1, user_name=test1}
row num : 2, {user_id=2, user_name=test2}
row num : 3, {user_id=3, user_name=test3}
row num : 4, {user_id=4, user_name=test4}
row num : 5, {user_id=5, user_name=test5}
row num : 6, {user_id=6, user_name=test6}
row num : 7, {user_id=7, user_name=test7}
row num : 8, {user_id=8, user_name=test8}
row num : 9, {user_id=9, user_name=test9}
row num : 10, {user_id=10, user_name=test10}
row num : 11, {user_id=11, user_name=test11}
row num : 12, {user_id=12, user_name=test12}
row num : 13, {user_id=13, user_name=test13}
row num : 14, {user_id=14, user_name=test14}
row num : 15, {user_id=15, user_name=test15}
row num : 16, {user_id=16, user_name=test16}
row num : 17, {user_id=17, user_name=test17}
row num : 18, {user_id=18, user_name=test18}
row num : 19, {user_id=19, user_name=test19}
row num : 20, {user_id=20, user_name=test20}
-------------------------第2页------------------------
row num : 1, {user_id=21, user_name=test21}
row num : 2, {user_id=22, user_name=test22}
row num : 3, {user_id=23, user_name=test23}
row num : 4, {user_id=24, user_name=test24}
row num : 5, {user_id=25, user_name=test25}
row num : 6, {user_id=26, user_name=test26}
row num : 7, {user_id=27, user_name=test27}
row num : 8, {user_id=28, user_name=test28}
row num : 9, {user_id=29, user_name=test29}
row num : 10, {user_id=30, user_name=test30}
row num : 11, {user_id=31, user_name=test31}
row num : 12, {user_id=32, user_name=test32}
row num : 13, {user_id=33, user_name=test33}
row num : 14, {user_id=34, user_name=test34}
row num : 15, {user_id=35, user_name=test35}
row num : 16, {user_id=36, user_name=test36}
row num : 17, {user_id=37, user_name=test37}
row num : 18, {user_id=38, user_name=test38}
row num : 19, {user_id=39, user_name=test39}
row num : 20, {user_id=40, user_name=test40}
-------------------------第3页------------------------
row num : 1, {user_id=41, user_name=test41}
row num : 2, {user_id=42, user_name=test42}
row num : 3, {user_id=43, user_name=test43}
row num : 4, {user_id=44, user_name=test44}
row num : 5, {user_id=45, user_name=test45}
row num : 6, {user_id=46, user_name=test46}
row num : 7, {user_id=47, user_name=test47}
row num : 8, {user_id=48, user_name=test48}
row num : 9, {user_id=49, user_name=test49}
row num : 10, {user_id=50, user_name=test50}
row num : 11, {user_id=51, user_name=test51}
row num : 12, {user_id=52, user_name=test52}
row num : 13, {user_id=53, user_name=test53}
row num : 14, {user_id=54, user_name=test54}
row num : 15, {user_id=55, user_name=test55}
row num : 16, {user_id=56, user_name=test56}
row num : 17, {user_id=57, user_name=test57}
row num : 18, {user_id=58, user_name=test58}
row num : 19, {user_id=59, user_name=test59}
row num : 20, {user_id=60, user_name=test60}
-------------------------第4页------------------------
row num : 1, {user_id=61, user_name=test61}
row num : 2, {user_id=62, user_name=test62}
row num : 3, {user_id=63, user_name=test63}
row num : 4, {user_id=64, user_name=test64}
row num : 5, {user_id=65, user_name=test65}
row num : 6, {user_id=66, user_name=test66}
row num : 7, {user_id=67, user_name=test67}
row num : 8, {user_id=68, user_name=test68}
row num : 9, {user_id=69, user_name=test69}
row num : 10, {user_id=70, user_name=test70}
row num : 11, {user_id=71, user_name=test71}
row num : 12, {user_id=72, user_name=test72}
row num : 13, {user_id=73, user_name=test73}
row num : 14, {user_id=74, user_name=test74}
row num : 15, {user_id=75, user_name=test75}
row num : 16, {user_id=76, user_name=test76}
row num : 17, {user_id=77, user_name=test77}
row num : 18, {user_id=78, user_name=test78}
row num : 19, {user_id=79, user_name=test79}
row num : 20, {user_id=80, user_name=test80}
-------------------------第5页------------------------
row num : 1, {user_id=81, user_name=test81}
row num : 2, {user_id=82, user_name=test82}
row num : 3, {user_id=83, user_name=test83}
row num : 4, {user_id=84, user_name=test84}
row num : 5, {user_id=85, user_name=test85}
row num : 6, {user_id=86, user_name=test86}
row num : 7, {user_id=87, user_name=test87}
row num : 8, {user_id=88, user_name=test88}
row num : 9, {user_id=89, user_name=test89}
row num : 10, {user_id=90, user_name=test90}
row num : 11, {user_id=91, user_name=test91}
row num : 12, {user_id=92, user_name=test92}
row num : 13, {user_id=93, user_name=test93}
row num : 14, {user_id=94, user_name=test94}
row num : 15, {user_id=95, user_name=test95}
row num : 16, {user_id=96, user_name=test96}
row num : 17, {user_id=97, user_name=test97}
row num : 18, {user_id=98, user_name=test98}
row num : 19, {user_id=99, user_name=test99}
row num : 20, {user_id=100, user_name=test100}