Character encoding problem using ScrollableResults and MySql

Question

I'm doing

private void doSomething(ScrollableResults scrollableResults) {
    while(scrollableResults.next()) {
       Object[] result = scrollableResults.get();
       String columnValue = (String) result[0];
    }
}

I tried this in two computers

1. It works fine. It is a Windows 7. System.getProperty("file.encoding") returns Cp1252.
2. When the word in the database has accents columnValue gets strange values. Is is a CentOS. System.getProperty("file.encoding") returns UTF-8.

Both databases are MySql, Charset: latin1, Collation: latin1_swedish_ci.

What should I do to correct this?

Answer 1

My suggestion would be to use UTF-8 everywhere:

• at the database/tables level (the following ALTER will change the character set not only for the table itself, but also for all existing textual columns)

  ALTER TABLE <some table> CONVERT TO CHARACTER SET utf8
  

• in the connection string (which is required with MySQL's JDBC driver or it will use the client's encoding)

  jdbc:mysql://localhost:3306/db_name?useUnicode=yes&characterEncoding=UTF-8
  

References

• MySQL 5.0 Reference Manual
  • 9.1.3.2. Database Character Set and Collation
  • 9.1.3.3. Table Character Set and Collation
• Connector/J (JDBC) Reference
  • 20.3.4.4. Using Character Sets and Unicode