5

I want to do a query by title like this:

String title = "transfusión";
String sql = "SELECT id FROM graph WHERE title=?";
PreparedStatement st = conn.prepareStatement(sql);
st.setString(1, title);
st.executeQuery();

The problem is that title column charset is latin1 and my java file encoding is utf8. When the title variable contains special caracters like accents, the query never finds a result.

I'm using a MySQL database an the connection url is:

jdbc:mysql://mysite:3306/mydatabase?autoReconnect=true&characterEncoding=latin1&useOldAliasMetadataBehavior=true

I tried multiple alternatives using useUnicode=true/false or characterEncoding=latin1/utf8/auto but I never get any result.

Also I tried to convert title and sql to ISO-8859-1 before prepare statement like this:

title =  new String(title.getBytes("UTF-8"), "ISO-8859-1");
sql =  new String(sql.getBytes("UTF-8"), "ISO-8859-1");

I can't change the database's charset because I'm not the administrator.

How can I solve that?

PD: Sorry for my English.

blackparrot
  • 147
  • 8
  • 3
    Note that [PreparedStatement's arguments are 1-based](https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setString(int,%20java.lang.String)). – Andy Turner Dec 22 '15 at 16:39
  • I would be very surprised if you aren't getting an exception thrown by using `0` as the argument index. Are you catching and suppressing an exception somewhere? – Andy Turner Dec 22 '15 at 16:45
  • This should work : http://stackoverflow.com/a/33023792/180100 –  Dec 22 '15 at 16:46
  • If you are using a reasonably recent version of MySQL Connector/J then the JDBC driver should be handling the character set conversions for you based on the character set of the table or column. I just tried a query for 'transfusión' using `useUnicode=true&characterEncoding=UTF-8` from my UTF-8 encoded .java file and it found the row in the `latin1` column without complaint. I suspect that there is something else wrong with your code, especially since `setString(0, ...)` will FAIL with "Parameter index out of range (0 < 1 )". – Gord Thompson Dec 22 '15 at 17:41
  • @AndyTurner you are right, the code is wrong, it is a summary of the original code. I did so to make it easier and I did a mistake. I edited the post. – blackparrot Dec 23 '15 at 10:09

2 Answers2

3

Thank you @Harlam. Your solution not exactly correct but you have helped me to find the correct one. I have to convert the title variable to Cp1252 not to ISO-8859-1 and it works perfectly:

title = new String(title.getBytes("UTF-8"), "Cp1252");

blackparrot
  • 147
  • 8
2

See Table 5.3 MySQL to Java Encoding Name Translations:
http://dev.mysql.com/doc/connector-j/en/connector-j-reference-charsets.html

jdbc:mysql://mysite:3306/mydatabase?autoReconnect=true&characterEncoding= Cp1252&useOldAliasMetadataBehavior=true

Harlam
  • 408
  • 2
  • 11