将 SQL Server 打印到 CSV 的 JAVA 代码返回数据不完整

Question

我有一个 JAVA 代码可以将 SQL Server 表输出为 CSV。打印在中间停止，我只得到部分数据作为 CSV 输出。下面是我的代码。

public class SQLServerConnection {

    public static void main(String[] args) throws IOException, SQLException {
        // TODO Auto-generated method stub

        Connection conn = null;
        SQLConnection cnn = new SQLConnection();
        FileWriter fw = new FileWriter(cnn.fileName);

        try {conn = DriverManager.getConnection(cnn.dbURL);

        if (conn != null) {
            DatabaseMetaData dm = (DatabaseMetaData) conn.getMetaData();
        }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } 

        String sql = String.format(cnn.Query);
        assert conn != null;

        PreparedStatement preStatement;
        preStatement = conn.prepareStatement(sql);
        ResultSet result = preStatement.executeQuery();
        ResultSetMetaData rmsd = result.getMetaData();
        int Columncount = rmsd.getColumnCount();

      //Get the column name and print the column name
        for (int iterator=1; iterator<= Columncount; iterator++) {
            fw.append(rmsd.getColumnName(iterator)+",");
        }
        fw.append('\n'); 

        while(result.next()){
            try {
                for (int jterator=1; jterator<=Columncount; jterator++){
                    fw.append(result.getString(jterator));
                    fw.append(',');
                }
                fw.append('\n');
             } catch (SQLException e)
            {
                e.printStackTrace();
            }}
        conn.close();
    }}

DB 和 Excel 参数的类

public class SQLConnection {
    public String ServerName = "Server1";
    public String DBName = "DB1";
    public String FileLoc = "Location of the file";
    public String dbURL = "jdbc:sqlserver://"+ServerName+";databaseName="+DBName+";integratedSecurity=true";
    public String Query = "SELECT * from [scdHSBCHK]";
    public String fileName = (FileLoc + DBName +"_QueryResult.csv"); 
}

实际的数据库返回 57 条记录，但 csv 仅返回 29 条记录。我尝试使用不同的数据库名称以及相同的问题。当我在程序窗口中输出结果时，数据显示正确。

Answer 1

我会确保您关闭FileWriter。这将确保所有缓冲区都刷新到文件中。通过对资源使用 try 来确保关闭所有 Closeable 资源(Connection、PreparedStatement、ResultSet、FileWriter)。

我建议的实现类似于:

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.sql.*;

public class SQLServerConnection {

    private static final String LINE_SEP = System.lineSeparator();

    public static void main(String[] args) throws IOException {
        SQLConnection cnn = new SQLConnection();
        try (Connection conn = DriverManager.getConnection(cnn.dbURL, cnn.user, cnn.pass);
             PreparedStatement preStatement = conn.prepareStatement(cnn.Query);
             ResultSet result = preStatement.executeQuery();
             Writer fw = new BufferedWriter(new FileWriter(cnn.fileName))) {
            ResultSetMetaData rmsd = result.getMetaData();
            int columnCount = rmsd.getColumnCount();
            //Get the column name and print the column name
            for (int iterator = 1; iterator <= columnCount; iterator++) {
                fw.append(rmsd.getColumnName(iterator)).append(",");
            }
            fw.append(LINE_SEP);
            while (result.next()) {
                try {
                    for (int jterator = 1; jterator <= columnCount; jterator++) {
                        fw.append(result.getString(jterator)).append(',');
                    }
                    fw.append(LINE_SEP);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

}