循环遍历 3 个不同的 JDBC 结果集

Question

我正在尝试在单个 zip 文件your_files_12354627.zip中从 3 个不同的表中压缩 3 个 CSV 文件。

因此，我有以下方法，该方法适用于生成包含 1 个 csv 文件的 zip 文件。我正在尝试为多个文件执行此操作，因此我修改了以下代码(如下面的工作代码所示)：

工作代码如下：

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
}   

}

修改后的代码如下：

我应该如何遍历 3 个不同的结果集以便我可以分别使用writer.writeAll(resultSetFirst, true);、writer.writeAll(resultSetSecond, true);和writer.writeAll(resultSetThird, true);？我正在尝试遍历文件名，如下面的代码所示，但不确定我应该如何处理 for 循环中的不同结果集，如下所示。我已经在我试图弄清楚这个循环事情的位置发表了评论。

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
}   

}

Answer 1

首先，我建议你将代码分解为几种不同的方法。这里有很多重复。

对于您是尝试为每个文件编写一个 ResultSet，还是为每个文件编写所有三个 ResultSet，我有点困惑。其中一部分是文件file，fileFacts和fileEncounters，这些文件在其他任何地方都没有提到。

如果尝试在每个条目中放置一个结果集，则可以使用映射将文件映射到结果集，然后循环访问条目或键。像....

....
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();
}
}
....

但是只使用一种方法可能会更容易和更清晰......

....
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();
}
}

这样，您就不必仅仅为了运行相同的代码三次而创建和填充映射(或列表)。

如果您决定迭代地图以使事情更清晰/更清晰，您仍然可以/应该使用一种方法：

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());
}
...

寻找其他类似的区域来重构为方法。一个体面的规则是每次您认为应该剪切和粘贴代码时都考虑创建一个方法。

请注意，这些代码实际上都没有编译或运行