sql statement "into outfile" not working with jdbc
- by Celeste Berus
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
