sql statement "into outfile" not working with jdbc

Question

I am attempting to add an "export to CSV" feature to a webapp that displays data from a MySQL database. I have a written a "queryExecuter" class to execute queries from my servlet. I have used this to successfully execute insert queries so I know the connection works etc however queries with the "into outfile" statement are simply not executing. Here is my code,

the java class...

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

public class queryExecuter {
    public void exportToCSV(String query) {
        DBase db = new DBase();
        Connection conn = db.connect(
               mydatabaseurl ,"myusername","mypassword");

        db.exportData(conn,query);
    }

}

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 query) {
        Statement stmt;

        try {
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);

            stmt.execute(query);

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

The servlet...

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;

public class MyExportServlet extends HttpServlet {

  public void doPost(HttpServletRequest request, HttpServletResponse response)

     throws IOException, ServletException {

            String query = "select into outfile 'theoutfile.txt' * from mytable;";

            request.setAttribute("query", query);

            queryExecuter mydata = new queryExecuter();

            mydata.exportToCSV(query);

            RequestDispatcher view = request.getRequestDispatcher("ConfirmationPage.jsp");

            view.forward(request, response);
    }
}

Any help would be greatly appreciated.

Thank you

Answer 1

Normally you'd execute the query, and get a ResultSet. You can read the ResultSet and write the contents to file (with Java). This is Good(tm) because the Java server is normally on another machine as the database, and outfile is always on the database machine.

Apart from that wisdom :) you should do

select * into outfile

and not

select into outfile *

It may also be that you even have to do

select <entire_query> into outfile

see Export to CSV with java and MySQL Select syntax.

Answer 2

How did you conclude that it's not executing?

Let me guess, the file isn't written there where you expect it to be? This can be very true since your code uses relative disk file system paths like 'theoutfile.txt' instead of absolute file system paths like 'c:/theoutfile.txt' and analogously probably also new File('theoutfile.txt').

A relative disk file system path is relative to the current working directory which depends on the way how you started the application (in commandline, as a service, in a server, etc). You should never rely on that when programming. Always use absolute disk file system paths.

In MySQL the actual root of the file is probably c:/mysql/bin and in webapp (the servlet) the actual root of the file is probably c:/webserverinstallationfolder/webapps. Nothing is sure with relative paths. Don't use them.