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&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?

Perhaps you need a semicolon after `characterEncoding=utf8` too, like mentioned here: http://stackoverflow.com/questions/13359683/how-to-use-useunicode-yes-characterencoding-utf-8-with-dbcp — GriffeyDog, Sep 27 '13 at 14:07
@GriffeyDog I do put a semicolon there, I wasn't copying it from the code. It won't let me compile without one too (well, it would - but I'd get an exception shortly after). — Haxton Fale, Sep 27 '13 at 14:17

score 40 · Accepted Answer · edited Apr 22 '14 at 07:54

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

score 12 · Answer 2 · answered Apr 13 '16 at 15:14

12

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;

answered Apr 13 '16 at 15:14

Conor O'Neill

403
5
9

Thank you. This is the best answer from all. I have Spring JPA; Hibernate & Embedded H2 db and every time it loaded from import.sql, encoding was messed up. – Kefirchiks Jun 01 '17 at 15:00
1

This didn't work for me and I'm curious how it could work for anyone else. The official documentation also doesn't mention it in the list of possible parameters: https://docs.spring.io/spring-boot/docs/1.5.x/reference/html/common-application-properties.html – Slava Semushin Sep 05 '17 at 12:24
1

This property no longer exists in Spring Boot 1.4 and above. See https://stackoverflow.com/questions/40250678/set-jpa-utf-8-encoding-in-application-properties/40255073#40255073 – Wim Deblauwe Sep 11 '17 at 12:17
Indeed as @WimDeblauwe mentions another Q in SO, you might need to switch to connection pool specific properties, eg for Tomcat: `spring.datasource.tomcat.connection-properties=myprops` as documented in https://docs.spring.io/spring-boot/docs/current/reference/html/appendix-application-properties.html#data-properties – Ivar Nov 15 '19 at 15:37

score 6 · Answer 3 · answered Sep 27 '13 at 14:09

6

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?

answered Sep 27 '13 at 14:09

Nikos Paraskevopoulos

39,514
12
85
90

I have tried your solution just now, to no avail. For a simple console application - I haven't (yet). I will soon. – Haxton Fale Sep 27 '13 at 14:20
Could you be more specific about the errors (if any) with the configuration? Also which bean did you configure? (the ``) – Nikos Paraskevopoulos Sep 27 '13 at 14:33
There are no errors at all, that's one of the issues! The only one I'm getting is when I try to specify a single property value. As described above. And I'm configuring a bean which I named dataSource, with class `org.springframework.jdbc.datasource.DriverManagerDataSource`, and properties as described above. – Haxton Fale Sep 27 '13 at 14:36
OK, the `DriverManagerDataSource.connectionProperties` is indeed a `java.util.Properties`, so the above configuration should be correct. (I am not sure about the single valued case; is Spring handling it transparently or does it throw an error? What version of Spring?) Do try the command line, I think it will be useful to narrow the problem (Java or DB). – Nikos Paraskevopoulos Sep 27 '13 at 14:41
Single-valued case throws an exception on runtime (ie. when I try to access a webpage that requires a DB connection) that says "'yes;characterEncosing=utf8;' is not an acceptable value for useUnicode". I'll see about that console app. – Haxton Fale Sep 27 '13 at 14:53

score 6 · Answer 4 · edited Aug 06 '17 at 13:26

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

score 2 · Answer 5 · answered Nov 30 '17 at 07:23

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.

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

5 Answers5

Linked