Java游标分页

记录一次某数据库版本不支持关键字(如limit)分页的问题处理方法

public class Test {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/test?allowMultiQueries=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";
        String username = "root";
        String pwd= "root";
        String sql = "select user_name,nick_name from table1 where user_name=#{userName} and nick_name=#{nickName}";
        //where条件
        Map<String,Object> map = new HashMap<>();
        map.put("userName","zhangsan");
        map.put("nickName","zhangsan");
        for(Map.Entry<String, Object> entry : map.entrySet()){
            sql = sql.replace("#{"+entry.getKey()+"}", entry.getValue().toString());
        }
        int page = 1; //当前页码
        int size = 10; //每页数据量
        List<Map<String, Object>> list = connectDB(url,username,pwd,sql,page,size);
        System.out.println(list.size());
    }

    /**
     * @param page 当前页码
     * @param size 每页数据量
     * @param url 数据库地址
     * @param username 用户名
     * @param password 密码
     * @param sql sql语句
     */
    public static List<Map<String, Object>> connectDB(String url, String username, String password, String sql, int page, int size) {
        if (page < 1) {
            page = 1;
        }
        List<Map<String, Object>> list = new ArrayList<>();
        int startNo = (page - 1) * size+ 1;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url,username,password);
            log.info("数据库已经连接...");
            pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            //pstmt.setMaxRows(startNo+maxCount-1);
            rs = pstmt.executeQuery();
            rs.first();
            rs.relative(startNo-1);
            int i = startNo-1;
            ResultSetMetaData rsm = rs.getMetaData();
            int columnNum = rsm.getColumnCount();
            String[] crr = new String[columnNum];
            for(int c=1;c<=columnNum;c++){
                //获取所有字段名
                crr[c-1] = rsm.getColumnName(c);
            }
            while(i < startNo + size-1 && !rs.isAfterLast()){
                Map<String, Object> map = new HashMap<>();
                for (String str : crr){
                    //获取字段数据
                    map.put(str, rs.getObject(str));
                }
                i++;
                rs.next();
                list.add(map);
            }
            rs.close();
            pstmt.close();
            conn.close();
        }catch (Exception e){
            log.error(e.getMessage());
        } finally {
            if (rs != null){
                try {
                    rs.close();
                }catch (SQLException e){log.error(e.getMessage());}
            }
            if (pstmt != null){
                try {
                    pstmt.close();
                }catch (SQLException e){log.error(e.getMessage());}
            }
            if (conn != null){
                try {
                    conn.close();
                }catch (SQLException e){log.error(e.getMessage());}
            }
        }
        log.info("数据库连接关闭...");
        return list;
    }
}