How to compare utf8 text in mysql query using java

Question

I have a problem while selecting from a table containg data in utf-8 format in MySQL using java, in the WHERE clause I need to compare if a column value equals to a java String but they don't match

"Select age from student where name = '"+stringVariable+"';"

the stringVariable can sometimes be in arabic so in this case they don't match

The database is utf8mb4 and the connection also between java and database is utf_8 and I dont have a problem while inserting or just selecting data but the problem occurs while comparing

I tried to convert the string like this and it also didnt match

byte[] b = stringVariable.getBytes("UTF-8");
String str = new String(b,"UTF-8");

So anyone has any solution for that ?!

Thank you in advance

The conversion you show as example will probably do nothing: you are encoding to UTF-8 and decoding from UTF-8. — jjmontes
– jjmontes, Commented Jan 13, 2012 at 14:06

jjmontes · Accepted Answer · 2012-01-13 14:01:36Z

6

Use parameters. The driver should then encode them correctly according to the connection properties.

PreparedStatement statement = connection.prepareStatement(
    "select age from student where name = ?");      
statement.setString(1, stringVariable);

In addition, this also correctly escapes special SQL characters (like single quotes).

answered Jan 13, 2012 at 14:01

jjmontes

27.2k5 gold badges47 silver badges53 bronze badges

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

3 Comments

sabty Over a year ago

Thank you for your help, but I tried it and it didnt work for example when I tried this example String stringVariable = "Ãmina"; PreparedStatement statement = connection.prepareStatement( "select age from student where name = ?"); statement.setString(1, stringVariable); the result set is null but when I try it with normal string it works, so do you have any idea why this is still happening ?

jjmontes Over a year ago

Is your collation utf8_unicode_ci or utf8_unicode_cs (this is the defualt)?

sabty Over a year ago

My collation is utf8mb4_unicode_ci now it is working using prepared statement thank you so much. I knew the problem, it was not only related to the database query and I posted it

JB Nizet · Accepted Answer · 2012-01-13 14:02:00Z

3

You shouldn't have to transform anything. The driver takes care of everything. Try using a prepared statement rather than string concatenation. This will have the additional advantage of avoiding SQL injection attacks, and make sure your statement works even if the string variable contains a quote.

answered Jan 13, 2012 at 14:02

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

4 Comments

sabty Over a year ago

Thank you, but I tried to use the prepared statement and the problem is still there so do you have any idea why ?!

JB Nizet Over a year ago

If they don't match, it's probably because they're not the same string. Try making a test which inserts some arabic string and then searched for it, using the same String instance, and prepared statements.

sabty Over a year ago

yes they are different, I also tried what you said to insert and select and it worked so this means there is still a problem in encoding but I dont know how to convert the java string to utf8 in order to match the one in the database or if there is another problem

JB Nizet Over a year ago

Why would it be an encoding problem? You're able to insert a String and then find it. You aren't able to do the same with some other string that is already in database. That's just that you're not using the correct string to search. Select it by ID, and print the integer value of each of its characters. Do the same with the String you're using to search, and see where the difference is. Maybe you just have a space at the end of the String in database.

sabty · Accepted Answer · 2012-01-15 13:03:00Z

1

I solved this problem it was not only related with the database query but I get the string from ajax request so I had to decode it from utf8 first and then get the parameter value using

URLDecoder.decode(request.getQueryString(), "UTF-8")

and then pass it to the query and I used prepared statement which encodes it according to the connection properties as you mentioned

Thank you

answered Jan 15, 2012 at 13:03

sabty

736 bronze badges

Collectives™ on Stack Overflow

How to compare utf8 text in mysql query using java

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related