MyBatis流式查询

精品 | 发表于 | 分类于 | 浏览: | 2.5k | 14 分钟

在开发中，我们经常会遇到统计数据，将数据导出到excel表格中。

由于生成报表逻辑要从数据库读取大量数据并在内存中加工处理后再生成excel返回给客户端。

如果数据量过大，采用默认的读取方式（一次性获取全部）会导致内存飙升，甚至是内存溢出（OOM），而导出数据又需要查询大量的数据，因此采用流式查询就比较合适了。


JDBC三种读取方式：

  1. 一次性全部（默认）：一次获取全部
  2. 流式：多次获取，一次一行
  3. 游标：多次获取，一次多行

模拟excel导出

创建数据表

1
2
3
4
5
6
7
8
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(50) DEFAULT NULL COMMENT '名字',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  `phone` varchar(20) DEFAULT NULL COMMENT '电话',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

造数据

新建springboot项目，使用批处理插入500万条数据到MySQL

Pom文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.8</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.itjing</groupId>
    <artifactId>excel-batch-deal</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>excel-batch-deal</name>
    <description>excel-batch-deal</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>

        <!-- easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>
        
        <!-- mybatis和mysql驱动 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- hutool -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.9</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

yaml配置文件

注意：使用批量提交数据，url一定要设置允许重写批量提交【rewriteBatchedStatements=true】，以及此时的sql语句一定不能有分号，否则有【BatchUpdateException】异常。

1
2
3
4
5
6
7
8
9
10
11
12
13
server:
  port: 22222
spring:
  application:
    name: excel-batch-deal
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/stream_query?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true&nullCatalogMeansCurrent=true&allowMultiQueries=true&rewriteBatchedStatements=true
    username: root
    password: root

mybatis:
  mapper-locations: classpath:mapper/**/*.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
package com.itjing.excel.util;

import cn.hutool.core.date.StopWatch;
import cn.hutool.db.DbUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @Description: JDBC工具类
 * @Author: lijing
 * @CreateTime: 2023-01-23 14:28
 */
@Component
public class JdbcUtil {

    @Autowired
    private DataSource dataSource;

    public void batchInsertData(long batchSize) {
        // 创建一个 StopWatch 实例
        StopWatch sw = new StopWatch();
        // 开始计时
        sw.start();
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "insert into user (name,age,email,phone) values (?,?,?,?)";
        try {
            conn = dataSource.getConnection();
            // 控制事务不自动提交
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= batchSize; i++) {
                ps.setString(1, "Lijing" + i);
                ps.setInt(2, 18);
                ps.setString(3, "2427259171@qq.com");
                ps.setString(4, "1798832262");
                ps.addBatch();
                if (i % 500 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();
            ps.clearBatch();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(conn, ps);
        }
        // 停止计时
        sw.stop();
        long totalTimeMillis = sw.getTotalTimeMillis();
        System.out.println("共计耗时：" + totalTimeMillis + "毫秒");
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
@SpringBootTest
class ExcelBatchDealApplicationTests {

    @Autowired
    private JdbcUtil jdbcUtil;

    @Test
    void testBatchInsertData() {
        jdbcUtil.batchInsertData(2000000);
    }

}

模拟查询

创建用户导出实体和查询实体

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
package com.itjing.excel.vo;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @Description: 用户导出VO
 * @Author: lijing
 * @CreateTime: 2023-01-23 15:31
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserVO {

    @ExcelProperty("用户名")
    private String name;

    @ExcelProperty("年龄")
    private Integer age;

    @ExcelProperty("邮箱")
    private String email;

    @ExcelProperty("电话")
    private String phone;

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
package com.itjing.excel.dto;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @Description: 用户查询DTO
 * @Author: lijing
 * @CreateTime: 2023-01-23 15:49
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserDTO {

    private String name;

    private Integer age;

}

创建持久DAO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
package com.itjing.excel.dao;

import com.itjing.excel.dto.UserDTO;
import com.itjing.excel.vo.UserVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.session.ResultHandler;

/**
 * @Description:
 * @Author: lijing
 * @CreateTime: 2023-01-23 15:41
 */
@Mapper
public interface UserMapper {

    void getUserList(ResultHandler<UserVO> handler, @Param("userDTO") UserDTO userDTO);

}

映射文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itjing.excel.dao.UserMapper">
    <resultMap id="BaseMap" type="com.itjing.excel.vo.UserVO">
        <result column="name" property="name"/>
        <result column="age" property="age"/>
        <result column="email" property="email"/>
        <result column="phone" property="phone"/>
    </resultMap>

    <sql id="BaseColumns">
        name,age,email,phone
    </sql>

    <select id="getUserList" resultMap="BaseMap" resultSetType="FORWARD_ONLY" fetchSize="-2147483648">
        select <include refid="BaseColumns"/>
        from user
        <where>
            <if test="userDTO.name != null and userDTO.name != ''">
                and name like concat('%',#{userDTO.name},'%')
            </if>
            <if test="userDTO.age != null">
                and age = #{userDTO.age}
            </if>
        </where>
    </select>
</mapper>

用户业务类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package com.itjing.excel.service;

import com.itjing.excel.dto.UserDTO;
import com.itjing.excel.vo.UserVO;

import java.util.List;

/**
 * @Description: 用户业务接口
 * @Author: lijing
 * @CreateTime: 2023-01-23 15:39
 */
public interface UserService {

    List<UserVO> exportUserList(UserDTO userDTO);

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
package com.itjing.excel.service.impl;

import com.itjing.excel.dao.UserMapper;
import com.itjing.excel.dto.UserDTO;
import com.itjing.excel.service.UserService;
import com.itjing.excel.vo.UserVO;
import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StopWatch;

import java.util.ArrayList;
import java.util.List;

/**
 * @Description:
 * @Author: lijing
 * @CreateTime: 2023-01-23 15:40
 */
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public List<UserVO> exportUserList(UserDTO userDTO) {
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        List<UserVO> userList = new ArrayList<>();
        userMapper.getUserList(new ResultHandler<UserVO>() {
            /**
             * mybatis流查询会回调处理逻辑
             * @param resultContext
             */
            @Override
            public void handleResult(ResultContext<? extends UserVO> resultContext) {
                userList.add(resultContext.getResultObject());
            }
        }, userDTO);
        stopWatch.stop();
        System.out.println("查询共计耗费" + stopWatch.getTotalTimeMillis() + "毫秒");
        return userList;
    }
}

用户控制器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
package com.itjing.excel.controller;

import com.itjing.excel.dto.UserDTO;
import com.itjing.excel.service.UserService;
import com.itjing.excel.util.ExcelUtil;
import com.itjing.excel.vo.UserVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StopWatch;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

/**
 * @Description:
 * @Author: lijing
 * @CreateTime: 2023-01-23 15:35
 */
@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    /**
     * 导出用户数据到excel
     */
    @GetMapping("/exportUser")
    public void exportUser(HttpServletResponse response, UserDTO userDTO) throws IOException {
        StopWatch watch = new StopWatch();
        watch.start();
        List<UserVO> exportUserList = userService.exportUserList(userDTO);
        //将数据分sheet进行导出
        ExcelUtil.export(response, "用户表.xlsx", "用户表", exportUserList, UserVO.class);
        watch.stop();
        long totalTimeMillis = watch.getTotalTimeMillis();
        System.out.println("共计耗时：" + totalTimeMillis + "毫秒");
    }

}

游标查询

游标查询不会造成OOM

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package com.itjing.excel.dao;

import com.itjing.excel.dto.UserDTO;
import com.itjing.excel.vo.UserVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.cursor.Cursor;
import org.apache.ibatis.mapping.ResultSetType;
import org.apache.ibatis.session.ResultHandler;

/**
 * @Description:
 * @Author: lijing
 * @CreateTime: 2023-01-23 15:41
 */
@Mapper
public interface UserMapper {

    @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = Integer.MIN_VALUE)
    Cursor<UserVO> getUserListByCursor(UserDTO userDTO);

}

映射文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itjing.excel.dao.UserMapper">
    <resultMap id="BaseMap" type="com.itjing.excel.vo.UserVO">
        <result column="name" property="name"/>
        <result column="age" property="age"/>
        <result column="email" property="email"/>
        <result column="phone" property="phone"/>
    </resultMap>

    <sql id="BaseColumns">
        name,age,email,phone
    </sql>

    <select id="getUserListByCursor" resultMap="BaseMap">
        select <include refid="BaseColumns"/>
        from user
        <where>
            <if test="name != null and name != ''">
                and name like concat('%',#{name},'%')
            </if>
            <if test="age != null">
                and age = #{age}
            </if>
        </where>
    </select>
</mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package com.itjing.excel.service;

import com.itjing.excel.dto.UserDTO;
import com.itjing.excel.vo.UserVO;

import java.util.List;

/**
 * @Description: 用户业务接口
 * @Author: lijing
 * @CreateTime: 2023-01-23 15:39
 */
public interface UserService {

    List<UserVO> exportUserListByCursor(UserDTO userDTO);

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
package com.itjing.excel.service.impl;

import com.itjing.excel.dao.UserMapper;
import com.itjing.excel.dto.UserDTO;
import com.itjing.excel.service.UserService;
import com.itjing.excel.vo.UserVO;
import org.apache.ibatis.cursor.Cursor;
import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StopWatch;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * @Description:
 * @Author: lijing
 * @CreateTime: 2023-01-23 15:40
 */
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;

    @Override
    public List<UserVO> exportUserListByCursor(UserDTO userDTO) {
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        List<UserVO> result = new ArrayList<>();
        SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession();
        Cursor<UserVO> cursor = sqlSession.selectCursor(
                UserMapper.class.getName() + ".getUserListByCursor",
                userDTO
        );
        //Cursor<UserVO> cursor = sqlSession.selectCursor(
        //        UserMapper.class.getName() + ".getUserListByCursor",
        //        userDTO,
        //        new RowBounds(userDTO.getCurrentIndex(), userDTO.getPageSize())
        //);
        Iterator iter = cursor.iterator();
        while (iter.hasNext()) {
            UserVO obj = (UserVO) iter.next();
            result.add(obj);
        }
        try {
            cursor.close();
            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        stopWatch.stop();
        System.out.println("查询共计耗费" + stopWatch.getTotalTimeMillis() + "毫秒");
        return result;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
package com.itjing.excel.controller;

import com.itjing.excel.dto.UserDTO;
import com.itjing.excel.service.UserService;
import com.itjing.excel.util.ExcelUtil;
import com.itjing.excel.vo.UserVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StopWatch;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

/**
 * @Description:
 * @Author: lijing
 * @CreateTime: 2023-01-23 15:35
 */
@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    /**
     * 导出用户数据到excel
     */
    @GetMapping("/exportUserByCursor")
    public void exportUserByCursor(HttpServletResponse response, UserDTO userDTO) throws IOException {
        StopWatch watch = new StopWatch();
        watch.start();
        List<UserVO> exportUserList = userService.exportUserListByCursor(userDTO);
        //将数据分sheet进行导出
        ExcelUtil.export(response, "用户表.xlsx", "用户表", exportUserList, UserVO.class);
        watch.stop();
        long totalTimeMillis = watch.getTotalTimeMillis();
        System.out.println("共计耗时：" + totalTimeMillis + "毫秒");
    }

}
相关文章
点击查看

本文标题:MyBatis流式查询

文章作者:LiJing

发布时间:2023年01月23日 - 17:35:15

最后更新:2023年06月03日 - 10:02:33

原始链接:https://blog-next.xiaojingge.com/posts/1994114598.html

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

-------------------本文结束 感谢您的阅读-------------------
欢迎光临
倍感荣幸
晶哥小站
请多关照
❤️
❤️