0

I created a MySQL database from a script, along with inserting rows of data that include special characters such as č, š and ž. For that reason I set default character set on schema in the script:

CREATE SCHEMA IF NOT EXISTS schemaname DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;

Now if I go through MySQL console and try retrieving data from a table with these special characters:

SELECT name FROM tablename;

I'll get a response with these special characters:

+-------------------------------------------------+
| name                                            |
+-------------------------------------------------+
| Test of š, and č, and ž                         |
+-------------------------------------------------+

I've also created a Spring Boot application (hibernate with Spring Boot Starter Data JPA) in which I implemented rest API for entities that I have in my database.

The problem arises when I try to retrieve (previously inserted) entities using JPARepository (EntityRepository.findAll()). Instead of correctly written special characters which are normal in MySQL console, I get:

Å¡, ž and such characters (should be š, ž)

However, if I store a new entity using the Rest API with special characters and retrieve that one, the special characters are fine (š, ž).

Any idea as to why there is a difference between these and how I might solve the issue? Without having to insert every entity through Rest API instead of MySQL inserts.

My application.properties file contains (I don't think the issue is here, since entities created through rest API are fine):

spring.datasource.url=jdbc:mysql://localhost:3306/schemaname?useSSL=false&allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF-8

Could be an issue: The .sql files were created on Windows, but I am running sql server (in docker) and my application on Linux (all .sql files have UTF-8 character encoding).

Mesut Akcan
  • 899
  • 7
  • 19
TilenA
  • 36
  • 9
  • Where do you print the characters? On the console? – areus Mar 08 '20 at 18:32
  • I'm using Swagger to retrieve the data (so in web), but even if I use debugger mode and stop right after findAll function, the characters are wrong (unless I created them through rest API) – TilenA Mar 08 '20 at 19:08
  • This helps? https://stackoverflow.com/questions/33669612/how-to-properly-set-utf8-encoding-with-jdbc-and-mysql/33669691#33669691 – areus Mar 08 '20 at 19:48
  • Unfortunately not. I don't believe the issue is in the url string, because if I save entities through rest, the special characters are just fine. The problem is with data that I inserted through MySQL console, there the special characters retrieved have faulty special characters. – TilenA Mar 08 '20 at 20:03
  • The solution I found was actually really simple, can't believe I missed this. I just had to put N in front of every string with special character in the .sql file. Example: INSERT INTO X(FIELD1) VALUE (N'ščž'); – TilenA Mar 08 '20 at 21:00

1 Answers1

0

The solution I found was actually really simple, can't believe I missed this.

I just had to put N in front of every string with special character in the .sql file. Example:

INSERT INTO X(FIELD1) VALUE (N'ščž');

Simply means I'm passing a NCHAR/NVARCHAR/NTEXT value instead of normal CHAR/VARCHAR/TEXT.

TilenA
  • 36
  • 9