Query to latin1 column from java

4
votes

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.

2
javamysqljdbc
Note that PreparedStatement's arguments are 1-based. - Andy Turner
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
This should work : stackoverflow.com/a/33023792/180100 - user180100
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
@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

2 Answers

1
votes

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

1
votes

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");