I want to create a simple spring boot API that fetches data from MySQL (using docker). I store Vietnamese characters in the database, but the received data is unreadable when I call API. How can I figure out my problem?. Here is my .sql file to initialize the database

CREATE DATABASE IF NOT EXISTS word_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GO

USE word_app;
GO

CREATE TABLE user (
  id INT NOT NULL AUTO_INCREMENT,
  display_name VARCHAR(50),
  username VARCHAR(100),
  password VARCHAR(100),
  PRIMARY KEY (id)
);
GO

CREATE TABLE category (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(50),
  user_id INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES user(id)
);
GO

CREATE TABLE pair (
  id INT NOT NULL AUTO_INCREMENT,
  en VARCHAR(255),
  vi VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  category_id INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (category_id) REFERENCES category(id)
);
GO

INSERT INTO pair (en, vi, category_id)
VALUES ("mediterranean", "Địa Trung Hải(adj, n)", 5);

When I use select * from pair;, the received data is seemingly fine but the data called by API is wrong, below is an example of what I got.

"data": [
        {
            "id": 42,
            "en": "mediterranean",
            "vi": "Äịa Trung Hải(adj, n)",
            "categoryId": 5
        },
        {
            "id": 43,
            "en": "stronghold",
            "vi": "pháo đài",
            "categoryId": 5
        }]

I also config application.properties file

spring.datasource.url=jdbc:mysql://localhost:3306/word_app?useUnicode=true&characterEncoding=UTF-8
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.show-sql=true


server.servlet.encoding.charset=UTF-8
server.servlet.encoding.enabled=true
server.servlet.encoding.force=true

spring.mvc.locale-resolver=fixed
spring.mvc.locale=vi_VN

You can read my entire code in my github:

I want to create a simple spring boot API that fetches data from MySQL (using docker). I store Vietnamese characters in the database, but the received data is unreadable when I call API. How can I figure out my problem?. Here is my .sql file to initialize the database

CREATE DATABASE IF NOT EXISTS word_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GO

USE word_app;
GO

CREATE TABLE user (
  id INT NOT NULL AUTO_INCREMENT,
  display_name VARCHAR(50),
  username VARCHAR(100),
  password VARCHAR(100),
  PRIMARY KEY (id)
);
GO

CREATE TABLE category (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(50),
  user_id INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES user(id)
);
GO

CREATE TABLE pair (
  id INT NOT NULL AUTO_INCREMENT,
  en VARCHAR(255),
  vi VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  category_id INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (category_id) REFERENCES category(id)
);
GO

INSERT INTO pair (en, vi, category_id)
VALUES ("mediterranean", "Địa Trung Hải(adj, n)", 5);

When I use select * from pair;, the received data is seemingly fine but the data called by API is wrong, below is an example of what I got.

"data": [
        {
            "id": 42,
            "en": "mediterranean",
            "vi": "Äịa Trung Hải(adj, n)",
            "categoryId": 5
        },
        {
            "id": 43,
            "en": "stronghold",
            "vi": "pháo đài",
            "categoryId": 5
        }]

I also config application.properties file

spring.datasource.url=jdbc:mysql://localhost:3306/word_app?useUnicode=true&characterEncoding=UTF-8
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.show-sql=true


server.servlet.encoding.charset=UTF-8
server.servlet.encoding.enabled=true
server.servlet.encoding.force=true

spring.mvc.locale-resolver=fixed
spring.mvc.locale=vi_VN

You can read my entire code in my github: https://github/phamhongphuc1999/JavaPractice/tree/main/word

• mysql
• spring-boot

• I had to amend your code to get it to run; you can see my amended version here: onecompiler/mysql/437zbjg8w . It works fine on OneCompiler. How are you displaying your output? What happens if you try displaying it in a browser? – Topological Sort Commented Feb 3 at 16:35
• Sorry, I now see you're using Spring Boot. – Topological Sort Commented Feb 3 at 16:50
• I'm not a java programmer, so take this with a grain of salt. I see that in your controller you are doing System.out.println(EncodingFixer.fixText(result.get(0).getVi())); Where EncodingFixer.fixText is doing: return new String(text.getBytes(StandardCharsets.ISO_8859_1), StandardCharsets.UTF_8); I believe what's happening here is that you are clobbering your nice unicode return from mysql into ISO_8859_1 which will destroy your unicode, and then converts it back into UTF8. – JNevill Commented Feb 3 at 18:32
• This code is only my test, and it doesn't interfere with the final result. I run it but its result is not what I expect. – Peter Present Commented Feb 4 at 2:38

1 Answer 1

I ran the query SET NAMES 'utf8mb4' on MySQL, and it worked perfectly fine. The result was as expected. See the screenshots below.