How to Solve UTF-8 Encoding Issues in MySQL and Java JDBC Connector 5.1?

Troubleshooting UTF-8 Encoding Issues with MySQL and Java JDBC Connector 5.1

In a scenario involving synchronization between two UTF-8 MySQL databases using IBATIS or JDBC, Java developers may encounter issues when handling UTF-8 data. Here's an in-depth analysis of the problem and its solution.

Symptoms:

• Upon reading data from the first database, Java code encounters non-standard characters like 'ó' instead of 'ó'.
• The written data in the second database remains unchanged.
• Viewing the loaded data in a web application (despite setting the Content-Type in HTML as "text/html; charset=utf-8") still displays the non-standard characters.
• While decoding the data using "new String(data.getBytes("UTF-8"))" produces the correct character ('ó'), applying this solution globally is not viable.

Cause:

• Mismatched character encoding settings between the database, Java application, and web application.

Database Settings:

• character_set_server: latin1
• character_set_connection: utf8
• character_set_results: utf8

The problem lies mainly with the "character_set_server" setting being "latin1."

Solution:

To read and write UTF-8 data correctly using JDBC Connector, these settings must be explicitly set on connection initialization:

<code class="java">DriverManager.getConnection(
           "jdbc:mysql://" + host + "/" + dbName 
           + "?useUnicode=true&amp;characterEncoding=UTF-8", user, pass);</code>

This ensures that the connection uses UTF-8 encoding throughout, including character encoding, result sets, exception messages, and any internal strings created by the driver.

Note:

• The problem primarily affects data retrieval from the first database and writing to the second database.
• By adhering to these settings, the Java code and the web application will accurately handle UTF-8 data, resulting in proper character display.

The above is the detailed content of How to Solve UTF-8 Encoding Issues in MySQL and Java JDBC Connector 5.1?. For more information, please follow other related articles on the PHP Chinese website!