Java中两个ResultSet列值相减并输出至Excel的问题求助

数据示例 #

ResultSet1 #

ResultSet2 #

列数据类型：RunId、ScenarioId、Price_M为BigInt；runName为Varchar；Price_N为Float。

预期输出 #

Output #

当前问题 #

移除代码行System.out.print(Integer.valueOf(columnValue1) - Integer.valueOf(columnValue2) + "Difference" + resultSetMetaData1.getColumnName(i));时输出格式不符预期；保留该行则抛出java.lang.NumberFormatException: For input string: "2523922677"异常。

现有代码片段 #

Connection conn = DriverManager.getConnection("jdbc:sqlserver:DB server;username:password)
Statement st1 = conn.createStatement();
String sqlStr1 = " select * from tablename1 where runName='Machine1-DBT10251' order by RunId,ScenarioPortfolioId"; 
ResultSet rs1 = st1.executeQuery(sqlStr1);
Statement st2 = conn.createStatement();
String sqlStr2 = " select * from tablename1 where runName='Machine2-DBT10251' order by RunId,ScenarioPortfolioId";
ResultSet rs2 = st2.executeQuery(sqlStr2);
while (rs1.next()) {
rs2.next();
ResultSetMetaData resultSetMetaData1 = rs1.getMetaData();
ResultSetMetaData resultSetMetaData2 = rs2.getMetaData()
int count = resultSetMetaData1.getColumnCount();           
//System.out.println("the count is" + count);
for (int i = 1; i <= count; i++) 
{
if (rs1.getObject(i)!= null && rs2.getObject(i) != null ) 
{
if (!rs1.getObject(i).equals(rs2.getObject(i))
{
if (i > 1) System.out.print(",  ");
String columnValue1 = rs1.getInt(i); // 错误：BigInt转Int会溢出
String columnValue2 = rs2.getInt(i);
System.out.print(columnValue1 + " " + resultSetMetaData1.getColumnName(i));
System.out.print(",  ");
System.out.print(columnValue2 + " " + resultSetMetaData2.getColumnName(i));
System.out.println(columnValue1 + " is of type " + ((Object)columnValue1).getClass().getSimpleName());
System.out.print("Sowmya" + resultSetMetaData1.getColumnName(i));
System.out.print(a-b + "Difference" + resultSetMetaData1.getColumnName(i)); // 错误：a、b未定义
System.out.print(Integer.valueOf(columnValue1) - Integer.valueOf(columnValue2) + "Difference" + resultSetMetaData1.getColumnName(i));           
}

}
                        
}
System.out.println("TRUE");

解决方案 #

1. 解决NumberFormatException的核心原因 #

2523922677超过了Java Integer的最大值（2147483647），你用rs1.getInt(i)获取BigInt类型的值会导致溢出，后续转Integer自然报错。必须用getLong()获取BigInt类型的值。

2. 分类型处理列值运算 #

根据列的数据类型分别处理：

• 字符串类型（runName）：直接输出指定文本No need to display
• BigInt类型（RunId、ScenarioId、Price_M）：用Long类型做减法
• Float类型（Price_N）：用Float类型做减法

3. 修正代码并实现预期输出（含Excel导出） #

下面是修正后的完整代码，包含控制台格式化输出和Apache POI导出到Excel的逻辑（需先引入Apache POI依赖）：

import java.sql.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;

public class ResultSetDifference {
    public static void main(String[] args) {
        String url = "jdbc:sqlserver://DB server;databaseName=yourDB;username=yourUser;password=yourPass";
        try (Connection conn = DriverManager.getConnection(url);
             Statement st1 = conn.createStatement();
             Statement st2 = conn.createStatement()) {

            String sqlStr1 = "SELECT * FROM tablename1 WHERE runName='Machine1-DBT10251' ORDER BY RunId,ScenarioPortfolioId";
            ResultSet rs1 = st1.executeQuery(sqlStr1);
            String sqlStr2 = "SELECT * FROM tablename1 WHERE runName='Machine2-DBT10251' ORDER BY RunId,ScenarioPortfolioId";
            ResultSet rs2 = st2.executeQuery(sqlStr2);

            ResultSetMetaData metaData = rs1.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 初始化Excel工作簿
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Difference Result");
            Row headerRow = sheet.createRow(0);

            // 写入表头
            for (int i = 1; i <= columnCount; i++) {
                Cell cell = headerRow.createCell(i - 1);
                cell.setCellValue(metaData.getColumnName(i));
                sheet.autoSizeColumn(i - 1);
            }

            // 处理数据行
            int rowNum = 1;
            while (rs1.next() && rs2.next()) {
                Row dataRow = sheet.createRow(rowNum);
                StringBuilder consoleOutput = new StringBuilder();

                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    Object value1 = rs1.getObject(i);
                    Object value2 = rs2.getObject(i);

                    Cell cell = dataRow.createCell(i - 1);
                    String cellValue;

                    switch (columnName) {
                        case "runName":
                            cellValue = "No need to display";
                            cell.setCellValue(cellValue);
                            break;
                        case "Price_N":
                            float diffFloat = ((Float) value1) - ((Float) value2);
                            cellValue = String.format("%.2f", diffFloat);
                            cell.setCellValue(diffFloat);
                            break;
                        default: // RunId、ScenarioId、Price_M
                            long diffLong = ((Long) value1) - ((Long) value2);
                            cellValue = String.valueOf(diffLong);
                            cell.setCellValue(diffLong);
                            break;
                    }

                    // 控制台格式化输出
                    if (i > 1) consoleOutput.append("\t");
                    consoleOutput.append(cellValue);
                }

                // 打印控制台结果
                System.out.println(consoleOutput);
                rowNum++;
            }

            // 导出Excel
            try (FileOutputStream fos = new FileOutputStream("result_difference.xlsx")) {
                workbook.write(fos);
                System.out.println("Excel文件已生成：result_difference.xlsx");
            }

        } catch (SQLException | IOException e) {
            e.printStackTrace();
        }
    }
}

4. 必要依赖（Maven） #

如果用Maven管理项目，需添加Apache POI依赖：

<dependencies>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.5</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.5</version>
    </dependency>
</dependencies>

内容的提问来源于stack exchange，提问作者n_ravikumar1