使用Java将数据库导出为CSV文件

Question

我在网上找到了这段代码，据说它可以备份数据库中的所有表格。我的问题是关于这一行：

res = st.executeQuery("select * from xcms." + tableName);

我得到了以下异常信息：SQLException信息。

“xcms”代表什么？我还可以在这里放什么？

res = st.executeQuery("select * from " + tableName);

顺便提一下，如果我将xcms.删除并像这样^放置，我只能保存第一个表格而不是所有表格，谢谢。

源代码网页：

https://gauravmutreja.wordpress.com/2011/10/13/exporting-your-database-to-csv-file-in-java/#comment-210

    public static void main(String[] args) {
        Connection con = null;
        String url = "jdbc:mysql://localhost:3306/";
        String db = "gg";
        String driver = "com.mysql.jdbc.Driver";
        String user = "root";
        String pass = "";
        FileWriter fw;

        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url + db, user, pass);
            Statement st = con.createStatement();
            ResultSet res = st.executeQuery("SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'gg'");
            List<String> tableNameList = new ArrayList<String>();
            while (res.next()) {
                tableNameList.add(res.getString(1));
            }
            String filename = "C:\\Users\\Angel Silva\\Documents";
            for (String tableName : tableNameList) {
                int k = 0;
                int j = 1;
                System.out.println(tableName);
                List<String> columnsNameList = new ArrayList<String>();
                res = st.executeQuery("select * from " + tableName);

                int columnCount = getColumnCount(res);

                try {
                     fw = new FileWriter("C:\\Users\\Angel Silva\\Documents\\popo1121.csv");

                    for (int i = 1; i <= columnCount; i++) {
                        fw.append(res.getMetaData().getColumnName(i));
                        fw.append(",");

                    }
                    fw.append(System.getProperty("line.separator"));

                    while (res.next()) {
                        for (int i = 1; i <= columnCount; i++) {
                            if (res.getObject(i) != null) {
                                String data = res.getObject(i).toString();
                                fw.append(data);
                                fw.append(",");
                            } else {
                                String data = "null";
                                fw.append(data);
                                fw.append(",");
                            }
                        }
                        fw.append(System.getProperty("line.separator"));
                    }
                    fw.flush();
                    fw.close();
                } catch (IOException ioe) {
                    ioe.printStackTrace();
                }
            }
            con.close();
        } catch (ClassNotFoundException cnfe) {
            System.err.println("Could not load JDBC driver");
            cnfe.printStackTrace();
        }catch (SQLException sqle) {System.err.println("SQLException information");}
    }

    public static int getRowCount(ResultSet res) throws SQLException {
        res.last();
        int numberOfRows = res.getRow();
        res.beforeFirst();
        return numberOfRows;
    }
    public static int getColumnCount(ResultSet res) throws SQLException {
        return res.getMetaData().getColumnCount();
    }
}

- Angel Silva

Answer 1

这是我使用的:

public void sqlToCSV (String query, String filename){

       log.info("creating csv file: " + filename);

       try {

         FileWriter fw = new FileWriter(filename + ".csv");
         if(conn.isClosed()) st = conn.createStatement();
         ResultSet rs = st.executeQuery(query);

         int cols = rs.getMetaData().getColumnCount();

         for(int i = 1; i <= cols; i ++){
            fw.append(rs.getMetaData().getColumnLabel(i));
            if(i < cols) fw.append(',');
            else fw.append('\n');
         }

         while (rs.next()) {

            for(int i = 1; i <= cols; i ++){
                fw.append(rs.getString(i));
                if(i < cols) fw.append(',');
            }
            fw.append('\n');
        }
        fw.flush();
        fw.close();
        log.info("CSV File is created successfully.");
        conn.close();
    } catch (Exception e) {
        log.fatal(e);
    }
}

- Yanir Calisar

Answer 2

< p > 在您的Java程序中，xms代表数据库名称，在 Connection 中，您已经建立了与数据库的连接：

“xms”代表数据库名称，在Java程序的Connection中，您已经建立了与数据库的连接：

(DriverManager.getConnection(url + db, user, pass);

db这个字符串是连接的数据库名称。

所以不需要使用xms.，例如使用这个查询：

db 是要连接的数据库名。

因此，无需使用 xms。 只需使用以下示例查询：

"SELECT * FROM "+tableName+";"

这将在你所连接的数据库中执行，例如在你的代码中gg。

想要编写CSV文件可以参考chillyfacts.com/export-mysql-table-csv-file-using-java/。

- Mahmoud Hossam

Answer 3

SELECT * FROM <MENTION_TABLE_NAME_HERE> INTO OUTFILE <FILE_NAME> FIELDS TERMINATED BY ',';

例子：

SELECT * FROM topic INTO OUTFILE 'D:\5.csv' FIELDS TERMINATED BY ',';

- Anwar Reefat

Answer 4

使用opencsv依赖项，以最少的代码行将SQL数据导出到CSV文件。

import com.opencsv.CSVWriter;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class CsvWriter {

    public static void main(String[] args) throws Exception {

        CSVWriter writer = new CSVWriter(new FileWriter("filename.csv"), '\t');
        Boolean includeHeaders = true;
         Statement statement = null;
         ResultSet myResultSet = null;
         Connection connection = null;
        try {
            connection = //make database connection here

            if (connection != null) {

                statement = connection.createStatement();
                myResultSet = statement.executeQuery("your sql query goes here");

                writer.writeAll(myResultSet, includeHeaders);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

- Dhruv Choudhary

---

请更新此代码片段，opencsv构造函数需要更多参数。 - erickdeoliveiraleal