apesa apesa - 6 months ago 64
SQL Question

How to escape quotes in a MySQL statement in Java

I am creating a Class that is parsing an array of 10

[fileNames / tableNames]
from an FTP site and loading them in MySQL. I am using
LOAD DATA INFILE
Below is the code block. This is all working fine with the exception being when I try to add
ENCLOSED BY '"'
When I add that param to the
LOAD DATA INFILE
statement I am unable to figure out how to escape the single quote in Java. I have several large files that have text values that are enclosed by quotes and without using
ENCLOSED BY '"'
they fail.

public int DatabaseEntry(String sql, String[]fileArray, int updFlag) {
for(int i =0; i < fileArray.length; i++)
{
parseString = fileArray[i];
String[] tokens = parseString.split("/");
filetoEat = tokens[0];
tableName = tokens[1];

final String sql1 = "LOAD DATA INFILE" + filetoEat + "INTO TABLE iber_stage." + tableName + "FIELDS TERMINATED BY',' LINES TERMINATED BY'\n' IGNORE 1 LINES";
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(url, username, password);
st = con.createStatement();
rs = st.executeQuery(sql1);

if (rs.toString() != null) {//get first result
returnMsg = rs.toString();
System.out.println(returnMsg); //Debug info
updFlag = 0;
}

} catch (SQLException ex) {
Logger lgr = Logger.getLogger(Create.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
updFlag = 1;

} catch (ClassNotFoundException e) {
Logger lgr = Logger.getLogger(Create.class.getName());
lgr.log(Level.SEVERE, e.getMessage(), e);
e.printStackTrace();
updFlag = 1;


I am looking for this final statement

"LOAD DATA INFILE" + filetoEat + "INTO TABLE iber_stage." + tableName + "FIELDS TERMINATED BY',' ENCLOSED BY '"' LINES TERMINATED BY'\n' IGNORE 1 LINES";


Like I mentioned, this works well and is fast however, If there is a better way to loop through an array of filenames and tablenames and create a prepared statement please advise.

Thanks,
Pat

Answer

Instead of using Statement, use PreparedStatment.

Look at this document: http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

It will escape your query.

To escape the single quote in the ENCLOSED BY param try this

String str = " ENCLOSED BY \"\" [REST OF THE STRING]"
Comments