Spring MVC, MySQL and UTF-8

Question

I have a problem with UTF-8. I use Spring MVC and MySQL.

In Spring, I have configured web.xml with:

<filter>
    <filter-name>encodingFilter</filter-name>
    <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
    <init-param>
        <param-name>encoding</param-name>
        <param-value>UTF-8</param-value>
    </init-param>
    <init-param>
        <param-name>forceEncoding</param-name>
        <param-value>true</param-value>
    </init-param>
</filter>
<filter-mapping>
    <filter-name>encodingFilter</filter-name>
    <url-pattern>/*</url-pattern>
</filter-mapping>

and in my bean datasource I have:

 <bean id="dataSource"
    class="it.roundtable.db.manager.CustomDataSource" init-method="init">
    <property name="driverClassName" value="${jdbc.driver}" />
    <property name="url" value="${jdbc.url}" />
    <property name="username" value="${jdbc.username}" />
    <property name="password" value="${jdbc.password}" />
</bean>

where CustomDataSource is:

import org.apache.tomcat.dbcp.dbcp.BasicDataSource;

public class CustomDataSource extends BasicDataSource {

private void init() {
    addConnectionProperty("useUnicode", "true");
    addConnectionProperty("characterEncoding", "UTF-8");
}

}

In the my layout page I have:

<%@page pageEncoding="UTF-8" contentType="text/html; charset=UTF-8"%>
<meta http-equiv="Content-Type" content="text/html; charset=utf8" charset="utf8" >

In MySQL I have:

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.08 sec)

and

mysql> SHOW VARIABLES LIKE 'colla%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

and in my.cnf:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
default-character-set=utf8
....
[mysqld]
character-set-server=utf8
default-character-set=utf8
default-collation=utf8_general_ci
init-connect='SET NAMES utf8'
character-set-client=utf8
skip-external-locking
....

Now when I wrote in a form a phrase like this:

This is a prove: àèìòù

in Spring controller I print in the log:

...
logger.info("text --> " + text);
...

and the result printed in eclipse console is correct.

Then i save my text in a table in mysql with the data source and if I connect to mysql with the bash, I can read the correct text saved in my table. In the next step, a spring controller retrive the data from mysql with a query, but I can look from the logger (eclipse console) that the letter "àèìòù" are wrong encoded:

This is a prove: �����

so the result in my web page is the same. Can you help me please?

---

I have found the error: the type I used to save the text was BLOB, now I use the type TEXT and everything works perfectly. Sorry!

Answer 1

Make sure your JDBC connection is using UTF8 as well. For example:

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

For example, to use 4-byte UTF-8 character sets with Connector/J, configure the MySQL server with character_set_server=utf8mb4, and leave characterEncoding out of the Connector/J connection string. Connector/J will then autodetect the UTF-8 setting.

To override the automatically detected encoding on the client side, use the characterEncoding property in the URL used to connect to the server.

See 5.4 Using Character Sets and Unicode for more info.