Spring data jpa utf-8 encoding is not working

Question

I use the database spring-data-jpa and mysql . My character set tables - utf-8. I also added ?useUnicode=yes&characterEncoding=utf8 = utf8 in mysql url in application.properties file. The problem is when I bring characters like "ąčęėį", controller, to store it in mysql. In mysql I got ??? Tags. But when I use the example of the CLI the mysql update projects_data set data="ąęąčę" where id = 1; , everything works well.

application.properties:

 # "root" as username and password. spring.datasource.url = jdbc:mysql://localhost:3306/gehive?useUnicode=yes&characterEncoding=utf8 spring.datasource.username = gehive spring.datasource.password = pass spring.datasource.driver-class-name=com.mysql.jdbc.Driver # Keep the connection alive if idle for a long time (needed in production) spring.datasource.testWhileIdle = true spring.datasource.validationQuery = SELECT 1 # Show or not log for each sql query spring.jpa.show-sql = true # Hibernate ddl auto (create, create-drop, update) spring.jpa.hibernate.ddl-auto = update # Naming strategy spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy # Use spring.jpa.properties.* for Hibernate native properties (the prefix is # stripped before adding them to the entity manager) # The SQL dialect makes Hibernate generate better SQL for the chosen database spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect . # "root" as username and password. spring.datasource.url = jdbc:mysql://localhost:3306/gehive?useUnicode=yes&characterEncoding=utf8 spring.datasource.username = gehive spring.datasource.password = pass spring.datasource.driver-class-name=com.mysql.jdbc.Driver # Keep the connection alive if idle for a long time (needed in production) spring.datasource.testWhileIdle = true spring.datasource.validationQuery = SELECT 1 # Show or not log for each sql query spring.jpa.show-sql = true # Hibernate ddl auto (create, create-drop, update) spring.jpa.hibernate.ddl-auto = update # Naming strategy spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy # Use spring.jpa.properties.* for Hibernate native properties (the prefix is # stripped before adding them to the entity manager) # The SQL dialect makes Hibernate generate better SQL for the chosen database spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect : # "root" as username and password. spring.datasource.url = jdbc:mysql://localhost:3306/gehive?useUnicode=yes&characterEncoding=utf8 spring.datasource.username = gehive spring.datasource.password = pass spring.datasource.driver-class-name=com.mysql.jdbc.Driver # Keep the connection alive if idle for a long time (needed in production) spring.datasource.testWhileIdle = true spring.datasource.validationQuery = SELECT 1 # Show or not log for each sql query spring.jpa.show-sql = true # Hibernate ddl auto (create, create-drop, update) spring.jpa.hibernate.ddl-auto = update # Naming strategy spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy # Use spring.jpa.properties.* for Hibernate native properties (the prefix is # stripped before adding them to the entity manager) # The SQL dialect makes Hibernate generate better SQL for the chosen database spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect characterEncoding = utf8? # "root" as username and password. spring.datasource.url = jdbc:mysql://localhost:3306/gehive?useUnicode=yes&characterEncoding=utf8 spring.datasource.username = gehive spring.datasource.password = pass spring.datasource.driver-class-name=com.mysql.jdbc.Driver # Keep the connection alive if idle for a long time (needed in production) spring.datasource.testWhileIdle = true spring.datasource.validationQuery = SELECT 1 # Show or not log for each sql query spring.jpa.show-sql = true # Hibernate ddl auto (create, create-drop, update) spring.jpa.hibernate.ddl-auto = update # Naming strategy spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy # Use spring.jpa.properties.* for Hibernate native properties (the prefix is # stripped before adding them to the entity manager) # The SQL dialect makes Hibernate generate better SQL for the chosen database spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect manager) # "root" as username and password. spring.datasource.url = jdbc:mysql://localhost:3306/gehive?useUnicode=yes&characterEncoding=utf8 spring.datasource.username = gehive spring.datasource.password = pass spring.datasource.driver-class-name=com.mysql.jdbc.Driver # Keep the connection alive if idle for a long time (needed in production) spring.datasource.testWhileIdle = true spring.datasource.validationQuery = SELECT 1 # Show or not log for each sql query spring.jpa.show-sql = true # Hibernate ddl auto (create, create-drop, update) spring.jpa.hibernate.ddl-auto = update # Naming strategy spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy # Use spring.jpa.properties.* for Hibernate native properties (the prefix is # stripped before adding them to the entity manager) # The SQL dialect makes Hibernate generate better SQL for the chosen database spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect 

tables:

 +---------------+--------------------+ | TABLE_NAME | character_set_name | +---------------+--------------------+ | customer | utf8 | | projects | utf8 | | projects_data | utf8 | +---------------+--------------------+ 

Answer 1

Try

 spring.datasource.url = jdbc:mysql://localhost:3306/gehive?useUnicode=yes&characterEncoding=UTF-8 

It seems that the problem is related to the lack of "-".

FAQ: - https://forum.hibernate.org/viewtopic.php?f=1&t=1037497&view=next

Answer 2

I had the same problem and I solved it by adding this line in my file application.properties :

 spring.datasource.tomcat.connection-properties=useUnicode=true;characterEncoding=utf-8; 

Note. It does not work as follows:

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

Answer 3

Be sure to avoid any special characters, as shown below: spring.datasource.url=jdbc\:mysql\://localhost\:3306/${SERVER_DB_NAME}\?useUnicode=true\&characterEncoding=utf\-8\&characterSetResults=utf\-8 true \ & characterEncoding = utf \ -8 \ & characterSetResults? spring.datasource.url=jdbc\:mysql\://localhost\:3306/${SERVER_DB_NAME}\?useUnicode=true\&characterEncoding=utf\-8\&characterSetResults=utf\-8

Answer 4

In my case, to solve my problem https://mathiasbynens.be/notes/mysql-utf8mb4

 [client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci FALSE [client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci 

Answer 5

For those using the HikariCP connection pool who prefer not to add parameters directly to the JDBC URL:

Property spring.datasource.connectionProperties was removed some time ago. Since you need to use the specific properties of the connection pool, as shown in the response spring.datasource.tomcat.connection-properties=useUnicode=true;characterEncoding=utf-8; characterEncoding = utf- spring.datasource.tomcat.connection-properties=useUnicode=true;characterEncoding=utf-8; : spring.datasource.tomcat.connection-properties=useUnicode=true;characterEncoding=utf-8;

At Hikari no connection-properties , but it works:

 spring.datasource.hikari.data-source-properties.useUnicode=true spring.datasource.hikari.data-source-properties.characterEncoding=UTF-8