Exporting database to csv file with java

Question

so i found this code on the internet, basically what supposedly it can do is backup all the tables from a db, my question is on this line:

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

i get the following excpetion exception: SQLException information

what does xcms. stands for? what else can i put here?

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

btw if i erase xcms. and put it like this ^, i can save only the first table not all the tables, thx

the sourcecode webpage:

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

Answer 1

use opencsv dependency to export SQL data to CSV using minimal lines of code.

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

Answer 2

The xms stands for the Database name, in your Connection in the java program you already are establishing the connection to the Database:

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

The string db is the name of the DB to connect to.

So no need to have the xms. .just for example use this query:

"SELECT * FROM "+tableName+";"

This is executed in the database you are connected to, for example ggin your code.

For writting the CSV file chillyfacts.com/export-mysql-table-csv-file-using-java/ may help!

Answer 3

This is what I used:

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

Answer 4

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

Example :

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