MySql/JAVA REPLACE

Question

I have one problem, when I want change the content in data base from java with this metod:

stmt.executeUpdate("UPDATE contentPage1 SET Content = REPLACE(Content,'"+oldContenido+"','"+newContenido+"');");

I cant change the content because i want input new text, and this text contains characters like ,`' I cant change the old content! What I do?

Welcome to SO! Do you get any errors? What errors? Could you give us an example string? — 11684
– 11684, Commented Dec 17, 2012 at 19:44
You should look into PreparedStatements. They allow you to use placeholders in your SQL and then fill out the values later. They handle all the escaping of special characters automatically! — Jesse Webb
– Jesse Webb, Commented Dec 17, 2012 at 19:45
@TedHopp - I couldn't tell if that comment would actually help out the OP or not cause the question was worded awkwardly. I posted as an answer though anyway! :) — Jesse Webb
– Jesse Webb, Commented Dec 17, 2012 at 19:51

JimmyB · Accepted Answer · 2012-12-17 19:53:10Z

1

You might want to use PreparedStatements. You can add your parameters to the statement object and it will take care for you to properly handle any character escaping, type conversions, &c.

Edit: Jesse Web is right and has provided an example solution. His answer should be accepted.

edited Dec 17, 2012 at 19:53

answered Dec 17, 2012 at 19:46

JimmyB

12.8k2 gold badges33 silver badges47 bronze badges

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

6 Comments

Woot4Moo Over a year ago

This is completely ignoring the issue of OP trying to do regex replacements in the database.

JimmyB Over a year ago

The OP did not state anything about regexps.

Ted Hopp Over a year ago

@Woot4Moo - Where did you get the idea that OP is doing regex replacements?

Ted Hopp Over a year ago

@Woot4Moo - MySQL's REPLACE statement has nothing to do with Java's String.replace() method. It's not a regex operation in any way.

Ted Hopp Over a year ago

@Woot4Moo - It happens; no worries. However, if you're the downvoter on this answer, I suggest you remove the downvote. :)

|

Jesse Webb · Accepted Answer · 2012-12-18 17:34:31Z

1

PreparedStatements allow you to use placeholders characters (?) in your SQL and fill in the values later. They also handle escaping automatically...

PreparedStatement pstmt = con.prepareStatement("UPDATE contentPage1 SET Content = REPLACE(Content, ?, ?);");
pstmt.setString(1, "first");
pstmt.setString(2, "second - characters like ' will be escaped automatically!");

UPDATE

In the comments below, it sounds like the OP isn't actually trying to use the REPLACE SQL function, they are simply trying to update the data in a given column. Here is some sample code to achieve this (slightly modified version of @TedHopp's comment):

int messageId = 123; // use the PK or unique identifier of the record you want to update
PreparedStatement pstmt = con.prepareStatement("UPDATE contentPage1 SET Content = ? WHERE Id = ?;"); // 'Id' is whatever your PK column is
pstmt.setString(1, "my new message content");
pstmt.setInt(2, messageId);
int numRowsAffected = pstmt.executeUpdate();

edited Dec 18, 2012 at 17:34

answered Dec 17, 2012 at 19:49

Jesse Webb

45.6k32 gold badges110 silver badges146 bronze badges

8 Comments

Steffan Petrov Over a year ago

I will try delete and then input the new datas. Is it a good approach?

JimmyB Over a year ago

If your new text includes those 'unwanted' characters too, this will not help in any way. - But you can of course use the PreparedStatement for deletes and inserts, too :)

Ted Hopp Over a year ago

@SteffanPetrov - It's not going to address the problem that your content has special characters that need to be escaped. The problem is not your use of REPLACE; it's that a quote mark in the data destroys the syntax of the entire SQL statement.

Steffan Petrov Over a year ago

@Ted Hopp-Tell me please, what can I do? I must finish with my homework very soon.

Jesse Webb Over a year ago

@SteffanPetrov - I don't understand your problem. Can you update your question with any new details you may have. Please describe in detail what you are trying and how it is failing to do what you want.

|

Collectives™ on Stack Overflow

MySql/JAVA REPLACE

2 Answers 2

6 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related