如何将我的sql查询结果集导出为CSV格式？

Question

import com.opencsv.CSVWriter;

public class DbToCSV {

    @SuppressWarnings("deprecation")
    public static String RetrieveData(String filename) throws Exception {

        String readFile = readFile(filename);

        String sql = readFile;
        System.out.println(sql);
        PreparedStatement statement;

        DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
        String mysqlUrl = "jdbc:mysql://localhost:3306/employee";
        Connection con = DriverManager.getConnection(mysqlUrl, "root", "root");
        statement = con.prepareStatement(sql);
        ResultSet rs = statement.executeQuery();
        System.out.println("Process Started");
        String file = filename + "/elastic1.csv";
        System.out.println(file);
        CSVWriter writer = new CSVWriter(new FileWriter(file));

        ResultSetMetaData Mdata = rs.getMetaData();
        String line1[] = { "id", "tenant_id", "hierarchy_name", "attribute_name", "item_pk" };
        writer.writeNext(line1);
        String data[] = new String[5];
        while (rs.next()) {
            data[0] = new Integer(rs.getInt("id")).toString();
            data[1] = new Integer(rs.getInt("tenant_id")).toString();
            data[2] = rs.getString("hierarchy_name");
            data[3] = rs.getString("attribute_name");
            data[4] = rs.getString("item_pk");
            writer.writeNext(data);
        }
        writer.flush();
        System.out.println("Data entered");
        return file;
    }

    public static void main(String[] args) throws Exception {
        RetrieveData("/home/niteshb/Pictures");
    }
}

id","tenant_id","hierarchy_name","attribute_name","item_pk
1",    "1",         "PRODUCT",            "BU",        "SCM
2",    "1",         "PRODUCT",            "BU",       "SPR
3",    "1",         "PRODUCT",            "BU",       "SSC

                     

id,tenant_id,hierarchy_name,attribute_name,item_pk
1,  1,      PRODUCT,        BU,               SCM
2,  1,       PRODUCT,       BU,               SPR
3,  1,      PRODUCT ,        BU,               SSC

Answer 1

您可以使用writeNext的重载方法，它接受boolean作为参数。

void writeNext(String[] nextLine, boolean applyQuotesToAll)

在此，将布尔值传递为false，以忽略引号。

[编辑]

CSVWriter writer = new CSVWriter(outputfile, ',',
                                 CSVWriter.NO_QUOTE_CHARACTER,
                                 CSVWriter.DEFAULT_ESCAPE_CHARACTER,
                                 CSVWriter.DEFAULT_LINE_END);