淘先锋技术网

首页 1 2 3 4 5 6 7

最近在做项目功能时 ,发现有20万以上的数据。要求导出时直接导出成压缩包。原来的逻辑是使用poi导出到excel,他是操作对象集合然后将结果写到excel中。

使用poi等导出时,没有考虑数据量的问题,大数据量无法满足,有个几千行jvm就哭了。更别提几万行几百万行数据了。

经过一天的研究发现一种不会消耗过多内存的方法:

导出成csv格式

大数据量的导出成csv格式分为以下几步:

1.首先引入需要的jar包 一下是我maven的配置方式

1

2

3

4

5

6

7

8

9

10

<dependency>

        <groupId>org.mvel</groupId>

        <artifactId>mvel2</artifactId>

        <version>2.2.8.Final</version>

    </dependency>

    <dependency>

        <groupId>net.sourceforge.javacsv</groupId>

        <artifactId>javacsv</artifactId>

        <version>2.0</version>

    </dependency>

 2.以下是具体的执行代码,我是用的是jdbcTemplate

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

public class DownloadVehicleRepair extends AbstractJob {

 

    @Autowired

    private JdbcTemplate jdbcTemplate;

 

    @Override

    protected void executeBusiness(Long aLong) {

        System.out.println("开始执行!!!!!!!!!!");

        final String fileName = "车辆维修清单.csv";//压缩包里面的文件

        final String[] header = {"序号""第三方机构代码""机构名称""分公司""合作机构""单位类别""主品牌""品牌名称",

                "被投诉""涉及欺诈""黑名单""审核状态""维护时间""维护人员代码"};

        final String sql = "您需要执行sql”;

 

        jdbcTemplate.execute(new PreparedStatementCreator() {

            @Override

            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {

                PreparedStatement pstmt = connection.prepareStatement(sql);

                return pstmt;

            }

        }, new PreparedStatementCallback<Integer>() {

            @Override

            public Integer doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException {

                ResultSet rs = preparedStatement.executeQuery();

                try {

                    CsvUtil.writeCsv(RuntimeEnvironmentUtil.getValue(SysConstent.code,SysConstent.path) + "\\VehicleRepairDetail.zip",

                            fileName, header, rs);//RuntimeEnvironmentUtil.getValue()是为了获取你导出到服务器的路径

                catch (Exception e) {

                    e.printStackTrace();

                }

                return 0;

            }

        });

        System.out.println("导出完成!!!!!!!!!!!");

 

    }

}

 3.以下是帮助类

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

82

public class CsvUtil {

    // 编码类型

    public static final Charset CHARSET = Charset.forName("GBK");

 

    // 分隔符

    public static final char DELIMITER = ',';

 

    // 文件后缀

    public static final String SUFFIX = ".csv";

 

 

    public static void writeCsv(OutputStream out, String[] header, ResultSet rs)

            throws IOException, SQLException {

        CsvWriter writer = null;

        try {

            writer = new CsvWriter(out, CsvUtil.DELIMITER, CsvUtil.CHARSET);

            writeCsv(writer, header, rs);

        finally {

            if (writer != null)

                writer.close();

        }

    }

 

    public static void writeCsv(CsvWriter writer, String[] header, ResultSet rs)

            throws IOException, SQLException {

        if (header != null)

            writer.writeRecord(header);

        ResultSetMetaData md = rs.getMetaData();

        int columnCount = md.getColumnCount();

        while (rs.next()) {

            for (int i = 1; i <= columnCount; i++)

                writer.write(rs.getString(i));

            writer.endRecord();

        }

    }

 

    public static void writeCsv(File file, String[] header, ResultSet rs)

            throws IOException, SQLException {

        BufferedOutputStream out = null;

        FileOutputStream fileOutputStream = null;

        try {

            fileOutputStream = new FileOutputStream(file);

            out = new BufferedOutputStream(fileOutputStream);

            writeCsv(out, header, rs);

        finally {

            if (out != null) {

                out.flush();

                out.close();

            }

            if (fileOutputStream != null) {

                fileOutputStream.close();

            }

        }

    }

 

    public static void writeCsv(String csvFilePath, String[] header,

                                ResultSet rs) throws IOException, SQLException {

        writeCsv(new File(csvFilePath), header, rs);

    }

 

    public static void writeCsv(String zipFilePath, String csvName, String[] header, ResultSet rs)

            throws IOException, SQLException {

        FileOutputStream fos = null;

        BufferedOutputStream bos = null;

        ZipOutputStream zos = null;

        try {

            fos = new FileOutputStream(zipFilePath);

            bos = new BufferedOutputStream(fos);

            zos = new ZipOutputStream(bos);

            zos.putNextEntry(new ZipEntry(csvName));

            writeCsv(zos, header, rs);

        finally {

            StreamUtil.flush(zos);

            StreamUtil.close(zos);

            //StreamUtil.flush(bos);

            StreamUtil.close(bos);

            //StreamUtil.flush(fos);

            StreamUtil.close(fos);

        }

    }

 

}

 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

public class StreamUtil {

    public static void flush(Flushable flushable) {

        if (flushable != null) {

            try {

                flushable.flush();

            catch (IOException e) {

                e.printStackTrace();

            }

        }

    }

    public static void close(Closeable closeable){

        if(closeable!=null){

            try {

                closeable.close();

            catch (IOException e) {

                e.printStackTrace();

            }

        }

    }

}

 4.下面是下载时的action

1

2

3

4

5

6

7

8

9

10

@RequestMapping(value = "/downloadVehicleRepair", method = RequestMethod.POST)

   public ResponseEntity<byte[]> download() throws IOException {

       String path = RuntimeEnvironmentUtil.getValue(SysConstent.code,SysConstent.path)+"\\VehicleRepairDetail.zip";

       File file = new File(path);

       HttpHeaders headers = new HttpHeaders();

       String fileName = new String("车辆维修清单.zip".getBytes("UTF-8"), "iso-8859-1");//为了解决中文名称乱码问题

       headers.setContentDispositionFormData("attachment", fileName);

       headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);

       return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file), headers, HttpStatus.OK);

   }

 总结:以上只是关键代码。使用时只需要稍加改变就可以运行。