0

I am trying to zip 3 CSV files from 3 different tables in single zip file your_files_12354627.zip.

So, I've the following method which works fine for zip file generation with 1 csv file in it. I'm trying to do this for multiple files and hence I've modified the below code (as shown below the working code):

WORKING CODE BELOW: 

public void sendMessage(String msg) throws DaoException {

    DataSource ds = null;
    Connection conn = null;
    PreparedStatement pstmt = null;
    PreparedStatement pstmtEmployee = null;
    PreparedStatement pstmtCompany = null; 
    PreparedStatement pstmBuilding = null; 

    ResultSet rs = null;
    ResultSet resultSetFirst = null;
    ResultSet resultSetSecond = null;
    ResultSet resultSetThird = null;


     String[] parts = msg.split("#");
     String requestID = parts[0].trim();
     String userName = parts[1].trim();
     String applicationName = parts[2].trim();




    try {

        ds = jdbcTemplate.getDataSource();
        conn = ds.getConnection();  

        pstmtEmployee = conn.prepareStatement(getPatientEmployeeSQL);
        pstmtEmployee.setString(1, requestID);
        resultSetFirst = pstmtEmployee.executeQuery();

        pstmtCompany = conn.prepareStatement(getCompanySQL);
        pstmtCompany.setString(1, requestID);
        resultSetSecond = pstmtCompany.executeQuery();

        pstmtBuilding = conn.prepareStatement(getBuildingSQL);
        pstmtBuilding.setString(1, requestID);
        resultSetThird = pstmtBuilding.executeQuery();




        Path dir = Paths.get("/srv/custom_users", userName);
        Files.createDirectories(dir);

        OutputStream fos = Files.newOutputStream(dir.resolve("your_files_"+ unixTimestamp +".zip"));
        BufferedOutputStream bos = new BufferedOutputStream(fos);
        ZipOutputStream zos = new ZipOutputStream(bos); 

        Path employeeFile = dir.resolve("employee_custom_file_" + unixTimestamp + ".csv");
        Path companyFile = dir.resolve("company_custom_file_" + unixTimestamp + ".csv");
        Path buildingFile = dir.resolve("building_custom_file_" + unixTimestamp + ".csv");


        ZipEntry firstEntry = new ZipEntry(employeeFile.getFileName().toString());
        zos.putNextEntry(firstEntry);
        try (CSVWriter writer = new CSVWriter(new OutputStreamWriter(zos,StandardCharsets.UTF_8)))) {
               writer.writeAll(resultSetFirst, true);
               writer.flush();
               zos.closeEntry();
        }

        /*ZipEntry secondEntry = new ZipEntry(companyFile.getFileName().toString());
        zos.putNextEntry(secondEntry);
        try (CSVWriter writer = new CSVWriter(new OutputStreamWriter(zos,StandardCharsets.UTF_8)))) {
               writer.writeAll(resultSetSecond, true);
               writer.flush();
               zos.closeEntry();
        }

        ZipEntry thirdEntry = new ZipEntry(buildingFile.getFileName().toString());
        zos.putNextEntry(thirdEntry);
        try (CSVWriter writer = new CSVWriter(new OutputStreamWriter(zos,StandardCharsets.UTF_8)))) {
               writer.writeAll(resultSetThird, true);
               writer.flush();
               zos.closeEntry();
        }*/




        zos.close();

        }
        catch(Throwable th) {
            throw new DaoException(th.getMessage(), th);
        }
        finally {
            //resource Closing statements

        }   



}

MODIFIED CODE BELOW:

How should I loop through 3 different resultsetsso that I can make use of writer.writeAll(resultSetFirst, true);, writer.writeAll(resultSetSecond, true); and writer.writeAll(resultSetThird, true); respectively? I am trying to loop through the file names as shown in the code below but not sure how should I handle different result sets inside the for loop as shown below. I've commented at the location where I'm trying to figure out this loop thing.

public void sendMessage(String msg) throws DaoException {

    DataSource ds = null;
    Connection conn = null;
    PreparedStatement pstmt = null;
    PreparedStatement pstmtEmployee = null;
    PreparedStatement pstmtCompany = null; 
    PreparedStatement pstmBuilding = null; 

    ResultSet rs = null;
    ResultSet resultSetFirst = null;
    ResultSet resultSetSecond = null;
    ResultSet resultSetThird = null;


     String[] parts = msg.split("#");
     String requestID = parts[0].trim();
     String userName = parts[1].trim();
     String applicationName = parts[2].trim();




    try {

        ds = jdbcTemplate.getDataSource();
        conn = ds.getConnection();  

        pstmtEmployee = conn.prepareStatement(getPatientEmployeeSQL);
        pstmtEmployee.setString(1, requestID);
        resultSetFirst = pstmtEmployee.executeQuery();

        pstmtCompany = conn.prepareStatement(getCompanySQL);
        pstmtCompany.setString(1, requestID);
        resultSetSecond = pstmtCompany.executeQuery();

        pstmtBuilding = conn.prepareStatement(getBuildingSQL);
        pstmtBuilding.setString(1, requestID);
        resultSetThird = pstmtBuilding.executeQuery();




        Path dir = Paths.get("/srv/custom_users", userName);
        Files.createDirectories(dir);

        OutputStream fos = Files.newOutputStream(dir.resolve("your_files_"+ unixTimestamp +".zip"));
        BufferedOutputStream bos = new BufferedOutputStream(fos);
        ZipOutputStream zos = new ZipOutputStream(bos); 

        Path employeeFile = dir.resolve("employee_custom_file_" + unixTimestamp + ".csv");
        Path companyFile = dir.resolve("company_custom_file_" + unixTimestamp + ".csv");
        Path buildingFile = dir.resolve("building_custom_file_" + unixTimestamp + ".csv");


        List<String> csvFileNames = new ArrayList<String>();
             csvFileNames.add(employeeFile.getFileName().toString());
             csvFileNames.add(companyFile.getFileName().toString());
             csvFileNames.add(buildingFile.getFileName().toString());


        for (String entries : csvFileNames) {

                 ZipEntry entry = new ZipEntry(entries);
                 zos.putNextEntry(entry);

                 CSVWriter writer = new CSVWriter(new OutputStreamWriter(zos,StandardCharsets.UTF_8));
                 //How should I loop through different resultsets here so that I can make use of writer.writeAll(resultSetFirst, true);
                 // writer.writeAll(resultSetSecond, true); and writer.writeAll(resultSetThird, true); respectively?


                System.out.println("Printing entries");
                System.out.println(entries); 

             }   



        /*ZipEntry firstEntry = new ZipEntry(employeeFile.getFileName().toString());
        zos.putNextEntry(firstEntry);
        try (CSVWriter writer = new CSVWriter(new OutputStreamWriter(zos,StandardCharsets.UTF_8)))) {
               writer.writeAll(resultSetFirst, true);
               writer.flush();
               zos.closeEntry();
        }*/

        /*ZipEntry secondEntry = new ZipEntry(companyFile.getFileName().toString());
        zos.putNextEntry(secondEntry);
        try (CSVWriter writer = new CSVWriter(new OutputStreamWriter(zos,StandardCharsets.UTF_8)))) {
               writer.writeAll(resultSetSecond, true);
               writer.flush();
               zos.closeEntry();
        }

        ZipEntry thirdEntry = new ZipEntry(buildingFile.getFileName().toString());
        zos.putNextEntry(thirdEntry);
        try (CSVWriter writer = new CSVWriter(new OutputStreamWriter(zos,StandardCharsets.UTF_8)))) {
               writer.writeAll(resultSetThird, true);
               writer.flush();
               zos.closeEntry();
        }*/




        zos.close();

        }
        catch(Throwable th) {
            throw new DaoException(th.getMessage(), th);
        }
        finally {
            //resource Closing statements

        }   



}
Tan
  • 1,433
  • 5
  • 27
  • 47
  • Clarify your question. Are trying to write data from across three tables into a single CSV file? If so, do a JOIN. If not, why combine the three together? Just have three separate methods that each read a table table and write to a file. – Basil Bourque Jun 17 '19 at 22:32
  • I was trying to zip 3 csv files from 3 different tables in single zip file `your_files_12354627.zip`. The answer below worked for me except for the fact that I had to get rid of `try` block to avoid `Stream Closed` error – Tan Jun 18 '19 at 14:25
  • Clarify your Question by editing the body rather than posting as Comments. I did that for you, copying your comment to be the first sentence in the Question. – Basil Bourque Jun 18 '19 at 16:09

1 Answers1

2

First, I'd suggest you break your code down into several different methods. There's a lot of repetition here.

I'm a bit confused as to whether you're trying to write one ResultSet for each of the files, or all three to each file. A part of this is the files file, fileFacts and fileEncounters which aren't mentioned anywhere else.

If you're trying to put one ResultSet in each Entry, then you could use a Map to map File to ResultSet and then iterate over the Entries or Keys. Something like....

....
  Map<String,ResultSet> dataMap = new HashMap<>();
  dataMap.put(file.getFileName().toString(),resultSetFirst);
  dataMap.put(filefacts.getFileName().toString(),resultSetSecond);
  dataMap.put(fileEncounters.getFileName().toString(),resultSetThird);

  for (Map.Entry<String,ResultSet> e : dataMap.entrySet()){
    zos.putNextEntry(new ZipEntry(e.getKey()));
    try (CSVWriter writer = new CSVWriter(new OutputStreamWriter(zos,StandardCharsets.UTF_8))){
      writer.writeAll(e.getValue(), true);
      writer.flush();
      zos.closeEntry();
    }
  }
....

But it'd probably easier and clearer to just use a method...

....
  makeEntry(zos,file.getFileName().toString(),resultSetFirst);
  makeEntry(zos,filefacts.getFileName().toString(),resultSetSecond);
  makeEntry(zos,fileEncounters.getFileName().toString(),resultSetThird);
....
}

private static void makeEntry(ZipOutputStream zos,String name, ResultSet res) throws SomeExceptions{
    zos.putNextEntry(new ZipEntry(name));
    try (CSVWriter writer = new CSVWriter(new OutputStreamWriter(zos,StandardCharsets.UTF_8))){
      writer.writeAll(res, true);
      writer.flush();
      zos.closeEntry();
  }
}

This way you don't have to create and populate the map (or list) just to run the same code three times.

You can/should still use a method if you decide to iterate over a map to make things clearer/cleaner:

  Map<String,ResultSet> dataMap = new HashMap<>();
  dataMap.put(file.getFileName().toString(),resultSetFirst);
  dataMap.put(filefacts.getFileName().toString(),resultSetSecond);
  dataMap.put(fileEncounters.getFileName().toString(),resultSetThird);

  for (Map.Entry<String,ResultSet> e : dataMap.entrySet()){
    makeEntry(zos, e.getKey(), e.getValue());
  }
...

Look for other areas like this to refactor into methods. A decent rule is to consider creating a method every time you think you should cut and paste code.

Note that none of this code has actually been compiled or run

WillD
  • 875
  • 1
  • 7
  • 14
  • Thanks very much. To clarify the question, I was trying to zip 3 csv files into `your_files_12354627.zip`. I tested your code and just had to get rid of `try` block as that was closing the resources and I was getting Stream closed error. Once I removed it, it started working fine. Thanks – Tan Jun 18 '19 at 14:22