How to save html file in MySQL DB using JDBC?

Question

I have html file as String and I want to insert it into MySQL DB, using update query. I tried this:

Statement st = connection.createStatement();
String query = "UPDATE orders SET status='ready', html='"+html+"' WHERE id='123'";
int num = st.executeUpdate(query);

But I get following exception:

MySQLSyntaxErrorException: 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 'zglosBladWindow').ck_window.showCenter('this');" href="#">zg?o? b??d na stronie<' at line 1

This is somwhere inside HTML - probably I cant just quote html with "" and insert it as it contains many special characters and quotes also - so how I can insert it? Should I encode it somehow?

You'll have to escape the variable html.

hjpotter92
– hjpotter92

2013-01-17 11:24:27 +00:00
Commented Jan 17, 2013 at 11:24 — hjpotter92
– hjpotter92, Commented Jan 17, 2013 at 11:24

PermGenError · Accepted Answer · 2013-01-17 14:17:42Z

7

I'd advice you to use PreparedStatement rather than Statement.

String query = "UPDATE orders SET status=?, html=? WHERE id=?";
PreparedStatement stmnt = conn.PreparedStatement(query);
stmnt.setString(1, yourtext);
....
int num = st.executeUpdate();

edited Jan 17, 2013 at 14:17

answered Jan 17, 2013 at 11:24

PermGenError

46.5k8 gold badges90 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user606521 Over a year ago

Thanks, this is what I was looking for but you have little mistake in your code: instead of int num = st.executeUpdate(query); there should be int num = st.executeUpdate();

PermGenError Over a year ago

@user606521 you are welcome, and thanks for pointing it out, it was basically copy/paste thingy .. :)

Archer · Accepted Answer · 2013-01-17 11:24:28Z

0

You should use PreparedStatement construct, as your HTML string may contain quotes or double qoutes. Read more here

answered Jan 17, 2013 at 11:24

Archer

5,1379 gold badges58 silver badges98 bronze badges

Comments

Gabriel Câmara · Accepted Answer · 2013-01-17 11:28:24Z

0

You should use PreparedStatement, but I'd rather save the path in MySQL instead of saving the file.

answered Jan 17, 2013 at 11:28

Gabriel Câmara

1,24915 silver badges22 bronze badges

Comments

Lucky · Accepted Answer · 2013-01-17 11:29:20Z

0

You should probably use something like this,

StringBuilder contentBuilder = new StringBuilder();
try {
BufferedReader in = new BufferedReader(new FileReader("mypage.html"));
String str;
while ((str = in.readLine()) != null) {
    contentBuilder.append(str);
}
in.close();
} catch (IOException e) {
}
String content = contentBuilder.toString();

answered Jan 17, 2013 at 11:29

Lucky

17.4k20 gold badges121 silver badges156 bronze badges

Collectives™ on Stack Overflow

How to save html file in MySQL DB using JDBC?

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related