How to set useUnicode=true and characterEncoding=utf8 properties on Spring-managed MySQL JDBC connection

Question

I'm currently building a Spring MVC webapp and the database is the vital backend part. For whatever reason, however, Spring is refusing to process the data as UTF-8. Since the views, resources and browsers are set to Unicode, and so are the tables in the database (and also reading quite a few similar questions asked), I have established that the problem lies in the database connection.

The JDBC Driver should be provided two items in connectionProperties: useUnicode (set to yes and characterEncoding (set to utf8). It turns out, however, it's impossible.

JDBC is a bean, and as such is configured via an XML file, like so:

<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/<database>" />
<property name="username" value="<not telling>" />
<property name="password" value="<not telling>" />

This setup converts all non-alphanumeric characters pulled from the database (such as arrows or Greek letters) into question marks. Which is, obviously, unacceptable.

I tried multiple solutions: specified the JDBC URL as jdbc:mysql://localhost:3306/<database>?useUnicode=yes&amp;characterEncoding=utf8, played with my.ini file (and MySQL Workbench) to force everything in the database to default to utf8 charset, and something that caused the largest headache: adding <property name="connectionProperties" value="useUnicode=yes;characterEncoding=utf8" />. Turns out, it's literally impossible to set two connectionProperties within a single bean, because... there is no separating character mentioned anywhere (the bean will attempt to read it as trying to set yes;characterEncoding=utf8 as the value of useUnicode). So my question is: how does I utf8?

Answer 1

The problem may be caused by specifying utf8 and not UTF-8. From Using Character Sets and Unicode:

When specifying character encodings on the client side, use Java-style names. The following table lists MySQL character set names and the corresponding Java-style names...

and utf8 maps to UTF-8. Try the following JDBC URL:

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

Answer 2

Note that if you are using Spring Boot, you can do this using properties within application.properties instead of having to add extra parameters to your connection strings:

Put this into application.properties:

spring.datasource.connectionProperties=useUnicode=true;characterEncoding=utf-8;

Answer 3

Did you try:

<property name="connectionProperties">
    <props>
        <prop key="useUnicode">yes</prop>
        <prop key="characterEncoding">utf8</prop>
    </props>
</property>

And also: Did you try a simple Java client (console application) that connects to the DB? Does the UTF-8 work in this case?

Answer 4

I had the same issue while using Spring-boot + embedded H2 + Hibernate, but the problem was at the moment of reading SQL script. (data.sql) The encoding in the database was broken every time when I it read any foreign character.

Adding the following line to my application.properties solved the problem:

spring.datasource.sqlScriptEncoding=UTF-8

If it is possible go to the console and add some data manually. If the foreign characters appear in the right way. Then this solution may work fine for you.

I have found that solution here:

http://ufasoli.blogspot.com/2014/07/spring-boot-jpa-broken-encoding-on.html

Answer 5

I wasted time to generate utf-8 tables. Non of above worked for me. At last I have change my mysql config file & it works like a charm. If you are in linux(I am in ubuntu, I am sure there is a file for windows) open the file /etc/mysql/my.cnf and add the following code.

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8


[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

Don't forget to restart mysql service.