jdbc、mybatis使用游标

游标使用

1、对于游标的理解

游标是数据库提供的一种获取大量数据的方案，它可以让用户在获取大量数据过程中减少IO次数。对比分页获取数据方式，游标通过临时表的方式保存查询结果，所以游标指向的不是实际查询到的数据，因此游标总是只读的。

2、游标的使用

比如我的数据库中有一个表：T_POL_LOGDATA，这个表有大概400万数据，下面分别通过几种方式举例使用不同方案获取数据

1、通过JDBC使用游标

    long startTime = System.currentTimeMillis();
    System.out.println("开始读取数据：" + DateTimeUtils.formatDateTime(new Date()));
    
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    int count = 0;
    try {
        List<Object> params = new ArrayList<Object>();
        String sql = "SELECT FID,FNAME FROM T_POL_LOGDATA WHERE 1=1 ORDER BY FID ASC";
        Class.forName("oracle.jdbc.driver.OracleDriver");
        conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:DEVDB", "test", "test");
        pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();
		
        while(rs.next()) {
            long id = rs.getLong("FID");
            String name = rs.getString("FNAME");
            System.out.println(id + "----" + name + "\n");
            ++count;
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if(null != pstmt) pstmt.close();
            if(null != conn) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    System.out.println("读取数据完成：" + DateTimeUtils.formatDateTime(new Date()));
    System.out.println("================================JDBC游标获取数据【" + count + "】共计用时================================");
    long diff = endTime - startTime;
    System.out.println(diff + " ms");
		

2、通过Mybatis使用游标

Mybatis使用游标也很简单，第一步写游标获取接口：

    @Options(fetchSize = 5000)
    @Select("SELECT FID AS id,FNAME AS name FROM T_POL_LOGDATA WHERE 1=1 ORDER BY FID ASC")
    Cursor<LogData> findLogDataByCursor();

这里只是举一个简单的例子，复杂的sql语句通过xml文件做映射
第二步在调用的方法中获取游标，并通过遍历游标获取数据：

	@Transactional
	public void testByCursor(String key) {
		long startTime = System.currentTimeMillis();
		
		System.out.println("开始读取数据：" + DateTimeUtils.formatDateTime(new Date()));
		try {
			Cursor<LogData> cursor = cursorMapper.findLogDataByCursor(key);
			Iterator<LogData> iterator = cursor.iterator();
			while(iterator.hasNext()) {
				LogData next = iterator.next();
				System.out.println(next.getId() + "----" + next.getName() + "\n");
				++count;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		long endTime = System.currentTimeMillis();

		System.out.println("读取数据完成：" + DateTimeUtils.formatDateTime(new Date()));
		System.out.println("================================MyBatis游标获取数据【" + count + "】共计用时================================");
		long diff = endTime - startTime;
		System.out.println(diff + " ms");
	}

这里需要注意一点，在调用方法上要加上@Transactional注解

3、分页获取数据

下面介绍一种大家有可能在获取或导出大量数据时常用的方案，就是分页获取数据：
第一步做映射关系：

    @Select("SELECT COUNT(*) FROM T_POL_LOGDATA WHERE 1=1")
    int findLogDataPageCount();

    @Select("SELECT * FROM ( SELECT T1.*,ROWNUM RN FROM (SELECT * FROM T_POL_LOGDATA WHERE 1=1 ORDER BY FID ASC) T1 WHERE ROWNUM <= #{end}) T1 WHERE RN > #{start}")
    Cursor<LogData> findLogDataByCursor(@Param(value = "start") int start, @Param(value = "end") int end);

第二步循环获取数据：

    long startTime = System.currentTimeMillis();

    int total = cursorMapper.findLogDataPageCount(key);
    System.out.println("开始读取数据：" + DateTimeUtils.formatDateTime(new Date()) + "|" + total);
    int size = 5000;

    int count = 0;
    try {
        for(int start = 0; start < total; start += size) {
            int end = start + size;
            List<LogData> list = cursorMapper.findLogDataByPage(key, start, end);
            if(null != list) {
                for(LogData item : list) {
                    System.out.println(item.getId() + "----" + item.getName() + "\n");
                    ++count;
                }
            }
        }
    } catch (Exception e) {
      e.printStackTrace();
    }
    long endTime = System.currentTimeMillis();

    System.out.println("读取数据完成：" + DateTimeUtils.formatDateTime(new Date()));
    System.out.println("================================分页获取数据【" + count + "】共计用时================================");
    long diff = endTime - startTime;
    System.out.println(diff + " ms");

总结：通过上面获取数据方式可知，游标方式在获取大量数据时还是比较有优势的，基本上性能提升了一个数量级，这是因为分页获取数据是每次都去数据库中获取数据，然后舍弃掉不用的数据，在这个过程中越分页到后面越浪费资源，因为查询出的大量数据都舍弃掉；而游标则不同，它通过临时表方式存储查询出来的数据，通过空间换取时间，这里也有一个例外情况，当临时表空间不足时，数据库会建一个临时磁盘表来存放，这种情况下性能也会很糟。