JDBC MySQL Error

Question

Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/facts", "root", "");
int x = s.size();
for(int i=0; i<x; i++)
{
 String sql = "INSERT INTO facts.facts (Sno, Description) VALUES ("+ i +","+ s.get(i) + ")";
 Statement stmt = con.createStatement();
 stmt.executeUpdate(sql);
}

I am getting this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'than 1/3 of adults and over 12.5 million children and teens in the US are obese.' at line 1.

The first string of ArrayList s is "More than 1/3 of adults and over 12.5 million children and teens in the US are obese. In the last 30 years, obesity in children and teens has nearly tripled."

Answer 1

The immediate cause of your error is likely that the string you are trying to insert is not being properly quoted. You could try the following to fix that:

"'" + s.get(i) + "'"

However, you should really use a prepared statement here:

for (int i=0; i < x; i++) {
 String selectSQL = "INSERT INTO facts.facts (Sno, Description) VALUES (?, ?)";
 PreparedStatement ps = con.prepareStatement(sql);
 ps.setInt(1, i);
 ps.setString(2, s.get(i));
 ps.executeUpdate();
}

Benefits of using prepared statements include:

• string quotation/escaping is handled for you automatically
• protection against SQL injection
• query is easier to read and maintain, as SQL language is clearly demarcated from your Java app code

• whinytween96: Still Showing Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?)' at line 1
• Tim Biegeleisen: Strange...it should be working. Can you try running the raw INSERT query on MySQL?
• Luke Woodward: @TimBiegeleisen: don't use the parameter sql in the call to ps.executeUpdate().
• Tim Biegeleisen: @LukeWoodward Thanks...it helps to test code before posting it here (which I almost never do with JDBC stuff due to overhead involved).