Java SQL Table dumpTableToFile(Connection con, String table, String fileName)

Description

Dump a table's data to a tab-delimited file.

License

Open Source License

Declaration

public static void dumpTableToFile(Connection con, String table, String fileName) 

Method Source Code

//package com.java2s;
/*/* w  w w.  j  a  va 2 s.c o  m*/
  Copyright (C) 2002 EBI, GRL
    
  This library is free software; you can redistribute it and/or
  modify it under the terms of the GNU Lesser General Public
  License as published by the Free Software Foundation; either
  version 2.1 of the License, or (at your option) any later version.
    
  This library is distributed in the hope that it will be useful,
  but WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  Lesser General Public License for more details.
    
  You should have received a copy of the GNU Lesser General Public
  License along with this library; if not, write to the Free Software
  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*/

import java.io.FileOutputStream;
import java.io.OutputStreamWriter;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

public class Main {
    /**
     * Dump a table's data to a tab-delimited file. Null values are written as \N
     * so that they don't get read as strings by LOAD DATA INFILE.
     */
    public static void dumpTableToFile(Connection con, String table, String fileName) {

        try {

            OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(fileName));

            // according to mysql handbook this is the way to make the
            // result set "streaming" and not reading the full table
            // into memory
            Statement stmt = con.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                    java.sql.ResultSet.CONCUR_READ_ONLY);
            stmt.setFetchSize(Integer.MIN_VALUE);

            ResultSet rs = stmt.executeQuery("SELECT * FROM " + table);
            ResultSetMetaData rsmd = rs.getMetaData();
            int cols = rsmd.getColumnCount();
            while (rs.next()) {
                for (int i = 1; i <= cols; i++) {
                    String s = rs.getString(i);
                    if (s != null) {
                        writer.write(s);
                    } else {
                        writer.write("\\N");
                    }
                    if (i < cols) {
                        writer.write("\t");
                    } else {
                        writer.write("\n");
                    }
                }

            }
            writer.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

Related

1. createTable(String createTblSql, Connection conn)
2. createTableAndInsertData(Connection c)
3. dropTable(String tableName, Connection con)
4. dumpTable(Connection conn, String TableName, PrintWriter out)
5. dumpTable(Connection connection, String tablename)
6. exportTableData(String tableName, Connection con)
7. getAllTableNames(Connection connection)
8. getAllTables(Connection conn)
9. getBatchResultMessage(String tableName, int rowIdx, int resultCode)