java 如何使用java从mysql导出表

How to export a table from mysql using java

提问by Dhinakar

I need to export a table from mysql using java. I tried using

我需要使用 java 从 mysql 导出一个表。我尝试使用

public class automateExport {
    public static void main(String[] argv) throws Exception {
        String driverName = "com.mysql.jdbc.Driver";
        Class.forName(driverName);
        String serverName = "192.168.0.189";
        String mydatabase = "ArchiveIndexer";
        String url = "jdbc:mysql://" + serverName + "/" + mydatabase;
        String username = "username";
        String password = "password";
        Connection connection = DriverManager.getConnection(url, username, password);
        Statement stmt = connection.createStatement();
        String filename = "c:/outfiless.txt";
        String tablename = "D_Centre";
        System.err.println("SELECT * INTO OUTFILE \"" + filename + "\" FROM " + tablename);
        stmt.executeUpdate("SELECT * INTO OUTFILE \"" + filename + "\" FROM " + tablename);
        // stmt.executeQuery("SELECT * INTO OUTFILE \"" + filename + "\" FROM " + tablename);
        // stmt.execute("SELECT * INTO OUTFILE \"" + filename + "\" FROM " + tablename);
    }
}

But this is throwing error like

但这会引发错误，例如

"Exception in thread "main" java.sql.SQLException: Can not issue SELECT via executeUpdate().
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1764)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1725)
    at automateexceldatabase.automateExport.main(automateExport.java:38)
Java Result: 1"

回答by Prasath Bala

This will help:

这将有助于：

public class DatabaseToCSV {
    public static void main(String[] args) {
        String filename =
            "C:\Documents and Settings\admin\My Documents\NetBeansProjects\AutomateExcelDatabase\myjdbcfile.csv";
        try {
            FileWriter fw = new FileWriter(filename);
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            Connection conn = DriverManager.getConnection(
                "jdbc:mysql://192.168.0.189:3306/ArchiveIndexer"
                , "username"
                , "password"
            );
            String query = "select * from D_Centre";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                fw.append(rs.getString(1));
                fw.append(',');
                fw.append(rs.getString(2));
                fw.append(',');
                fw.append(rs.getString(3));
                fw.append(',');
                fw.append(rs.getString(4));
                fw.append('\n');
            }
            fw.flush();
            fw.close();
            conn.close();
            System.out.println("CSV File is created successfully.");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

回答by Meinkraft

The error means that you are using executeUpdate()when you should be using executeQuery()

该错误意味着您executeUpdate()在应该使用的时候使用executeQuery()

executeUpdate()is for executing updates (duh)

executeUpdate()用于执行更新（废话）

executeQuery()is for searching the database and returning a ResultSet

executeQuery()用于搜索数据库并返回一个 ResultSet

Hope this helps

希望这可以帮助

回答by Annadate Piyush

this might help u : Export database to csv file

这可能对你有帮助：将数据库导出到 csv 文件

Example below exports data from MySQL Select query to CSV file.

testtable structure
CREATE TABLE testtable
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
text varchar(45) NOT NULL,
price integer not null);

Application takes path of output file as an argument.

应用程序将输出文件的路径作为参数。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class automateExport {
 public static void main(String[] args) {
    DBase db = new DBase();
    Connection conn = db.connect(
            "jdbc:mysql://localhost:3306/test","root","caspian");

    if (args.length != 1) {
        System.out.println(
                "Usage: java automateExport [outputfile path] ");
        return;
    }
    db.exportData(conn,args[0]);
}

}

class DBase {
public DBase() {
}

public Connection connect(String db_connect_str, 
        String db_userid, String db_password) {
    Connection conn;
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        conn = DriverManager.getConnection(db_connect_str,
                db_userid, db_password);

    } catch(Exception e) {
        e.printStackTrace();
        conn = null;
    }
    return conn;
}

public void exportData(Connection conn,String filename) {
    Statement stmt;
    String query;
    try {
        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

        //For comma separated file
        query = "SELECT id,text,price into OUTFILE  '"+filename+
                "' FIELDS TERMINATED BY ',' FROM testtable t";
        stmt.executeQuery(query);

    } catch(Exception e) {
        e.printStackTrace();
        stmt = null;
    }
}

};

};