博客

大数据量处理时普通查询一次性将数据载入到内存中容易导致OOM，需要采用游标或者流式查询

1. 游标

@Test
    public void useCursorFetch() throws SQLException {
        // 建立数据库连接
        try (Connection connection = DriverManager.getConnection(dataSourceProperties.getUrl()+"&useCursorFetch=true",
                dataSourceProperties.getUsername(), dataSourceProperties.getPassword())) {
            // 执行查询
            String query = "SELECT * FROM test";
            StopWatch stopWatch = new StopWatch("游标查询");
            stopWatch.start();
            try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
                preparedStatement.setFetchSize(1000);
                try (ResultSet resultSet = preparedStatement.executeQuery()){
                    while (resultSet.next()){
                        // 处理逻辑
                    }
                }
            }
            stopWatch.stop();
            System.out.println(stopWatch.prettyPrint());
        }
    }

2.流式

@Test
    public void stream() throws SQLException {
        // 建立数据库连接
        try (Connection connection = DriverManager.getConnection(dataSourceProperties.getUrl(),
                dataSourceProperties.getUsername(), dataSourceProperties.getPassword())) {
            // 执行查询
            String query = "SELECT * FROM test";
            StopWatch stopWatch = new StopWatch("流式查询");
            stopWatch.start();
            try (PreparedStatement preparedStatement = connection.prepareStatement(query,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY)) {
                preparedStatement.setFetchSize(Integer.MIN_VALUE);
                try (ResultSet resultSet = preparedStatement.executeQuery()){
                    while (resultSet.next()){
                        // 处理逻辑
                    }
                }
            }
            stopWatch.stop();
            System.out.println(stopWatch.prettyPrint());
        }
    }

两种方式都可以避免OOM。实际测试185万数据下，游标耗时15s流式耗时9s