Spring Boot import.sql encoding issue

Question

I am using Spring Boot 1.5.2.RELEASE, and when trying to use the import.sql file to add demo data with Arabic letters as follows:

INSERT INTO city (active, name_arabic,name_english) VALUES (b'1', 'الرياض','Riyadh');

The value is inserted in the database as : الرياض

I tried adding the following to application.properties with no luck:

spring.datasource.connectionProperties=useUnicode=true;characterEncoding=utf-8;autoReconnect=true;useSSL=false
spring.datasource.sql-script-encoding=UTF-8

The database I am using is MySQL and the collation is UTF8_GENERAL_CI.

This issue happens only when I run the project on server, if run a unit test or build the application with Maven the issue doesn't happen.

Answer 1

Issue was solved by using the following maven plugin:

        <plugin>
           <groupId>org.apache.maven.plugins</groupId>
           <artifactId>maven-surefire-plugin</artifactId>
           <configuration>
              <argLine>-Dfile.encoding=UTF8</argLine>
           </configuration>
        </plugin>

the above plugin will solve encoding issue for command line build.

UPDATE: to solve the issue when you run project on server:

1- Run As

2- Run Configurations

3- Change the encoding for the server to be UTF-8 as in the image below

Answer 2

you have to use ?useUnicode=yes&characterEncoding=UTF-8 into db-url.

The error you were getting in your XML was most likely due to using '&' which is not an allowed entity in XML. You should definitely encode the connection, so the work connection url should be something like

<property name="url" value="jdbc:mysql://localhost:3306/DB_Name?useUnicode=true&amp;characterEncoding=UTF-8"/>

You can also make sure that your server is configured properly, for tomcat for instance adding URIEncoding to connector

<connector port="8080" protocol="HTTP/1.1" connectionTimeout="20000" redirectPort="8443" URIEncoding="UTF-8"/>

will specify the character encoding used to decode the URI. You should find an equivalent for your server

Answer 3

Mojibake. See http://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored for discussion of what causes it, and what needs changing in your code.

To further verify, do SELECT HEX(...) FROM ...; that string should have hex C398C2A7C399E2809EC398C2B1C399C5A0C398C2A7C398C2B6

If, instead, you get C383CB9CC382C2A7C383E284A2C3A2E282ACC5BEC383CB9CC382C2B1C383E284A2C385C2A0C383CB9CC382C2A7C383CB9CC382C2B6, then you have "double encoding".