Spring-Boot, Can't save unicode string in MySql using spring-data JPA

Question

I have my application.properties set up like this :

spring.datasource.username = root
spring.datasource.password = root
spring.datasource.url = jdbc:mysql://localhost:3306/dbname?useUnicode=yes&characterEncoding=UTF-8
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.show-sql = true
spring.jpa.hibernate.ddl-auto = update
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

And In my pom.xmlI have property set up like this :

<properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <start-class>toyanathapi.Application</start-class>
        <java.version>1.8</java.version>
</properties>

My entity : @Entity public class DailyRashifalEntity {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String date;
private int rollno;
private String name;
//Constructors and getters/setters 
}

Problem 1: If I use the above setup I get the exception

java.sql.SQLException: Incorrect string value: '\xE0\xA4\xA7\xE0\xA4\xBE...

Problem 2 : If I change the datasource url into this :

spring.datasource.url = jdbc:mysql://localhost:3306/dbname

The unicodes in my database get saved like this

 29 | 2074-03-04 |        3 | ?????????????? ?????,?????? ??????, ??????????? ????? ? ???? ???? ???? ??????

How can I save them in Mysql like they are in unicode instead of getting all the unicode data converted into ???????? .

drop the database and try again. u can set spring.jpa.hibernate.ddl-auto = create for testing. — prem kumar
– prem kumar, Commented Jun 16, 2017 at 11:28
have you tried setting the charset on the connection as well ? — Florian Humblot
– Florian Humblot, Commented Jun 16, 2017 at 11:30
@FMashiro I am starting spring in last few days and I am not quite sure what is it like that you are telling . Can you explain plase ? — erluxman
– erluxman, Commented Jun 16, 2017 at 11:33

amagain · Accepted Answer · 2017-06-19 06:50:49Z

10

+100

In your /etc/mysql/my.cnf file change the following.

[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8

answered Jun 19, 2017 at 6:50

amagain

2,0822 gold badges22 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Harsha Jayamanna · Accepted Answer · 2017-06-19 09:39:11Z

9

Keep your hibernate configuration Like this

jdbc:mysql://localhost:3306/dbname?useUnicode=yes&characterEncoding=UTF-8

And Change your DB Collation Like this

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

More information : Link

answered Jun 19, 2017 at 9:39

Harsha Jayamanna

2,2586 gold badges27 silver badges47 bronze badges

2 Comments

Harsha Jayamanna Over a year ago

My project had the same issue you have described. and this worked for me.

erluxman Over a year ago

Problem was not in my project, rather it was in mysql config in Mechine

Rick James · Accepted Answer · 2017-06-28 16:11:58Z

3

See "question marks" in http://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored .

Also,

⚈  spring.jpa.properties.hibernate.connection.characterEncoding=utf-8 
⚈  spring.jpa.properties.hibernate.connection.CharSet=utf-8 
⚈  spring.jpa.properties.hibernate.connection.useUnicode=true

answered Jun 28, 2017 at 16:11

Rick James

144k15 gold badges144 silver badges255 bronze badges

Comments

S34N · Accepted Answer · 2019-04-24 10:19:10Z

2

Solved it by applying to the column definition as shown below:

public class Goal{

@Column(name = "SOURCE_OF_FUNDS", length = 6000, columnDefinition = "VARCHAR(6000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL")
private String sourceOfFunds;

}

answered Apr 24, 2019 at 10:19

S34N

8,4199 gold badges40 silver badges43 bronze badges

Comments

crifan · Accepted Answer · 2020-02-01 14:56:55Z

if you want save non-ascii character correctly, you should make sure:

Created MySQL table with charset=encoding is UTF-8
Connect MySQL table with charset=encoding is UTF-8

following is how to achieve it:

Create mysql table with utf-8 charset

method 1 (I use this method): config in Spring Boot JPA

after refer another Chinese post, my final working configuration:

file: src/main/java/com/crifan/smartelectricserver/MySQL55DialectUtf8mb4.java
code:

package com.crifan.smartelectricserver;

import org.hibernate.dialect.MySQL55Dialect;;
// import org.hibernate.dialect.MySQL5InnoDBDialect; // Deprecated

// public class MySQL5InnoDBDialectUtf8mb4 extends MySQL5InnoDBDialect {
public class MySQL55DialectUtf8mb4 extends MySQL55Dialect {
  @Override
  public String getTableTypeString() {
    return "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci";
  }
}

which set table charset to utf8mb4 and collation to utf8mb4_unicode_ci

and

file: src/main/resources/application.properties
config:

spring.jpa.database-platform=com.crifan.smartelectricserver.MySQL55DialectUtf8mb4

in which:

com.crifan.smartelectricserver.MySQL55DialectUtf8mb4: corresponding above file com/crifan/smartelectricserver/MySQL55DialectUtf8mb4.java

method 2: set utf-8 in MySQL config file `my.cnf`

find your my.cnf in your machine, edit to:

[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 
init_connect='SET NAMES utf8mb4'

Connect MySQL with UTF-8

file: src/main/resources/application.properties
config:

spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/smart_electric?useUnicode=yes&characterEncoding=UTF-8

in which:

useUnicode=yes&characterEncoding=UTF-8: means connect MySQL with charset utf-8

securecurve · Accepted Answer · 2018-05-15 09:57:54Z

Non of the answer worked for me … except the url encoding part.

The solution in my case is twofold:

1- Add encoding in the URL of Database config bean:

<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/customersdb?useSSL=false&amp;useUnicode=yes&amp;characterEncoding=UTF-8&amp;characterSetResults=UTF-8" />

2- Add this configuration to your dispatcher config file:

<filter>
    <filter-name>encoding-filter</filter-name>
    <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
    <init-param>
        <param-name>encoding</param-name>
        <param-value>UTF-8</param-value>
    </init-param>
    <init-param>
        <param-name>forceEncoding</param-name>
        <param-value>true</param-value>
    </init-param>
</filter>

<filter-mapping>
    <filter-name>encoding-filter</filter-name>
    <url-pattern>/*</url-pattern>
    <dispatcher>REQUEST</dispatcher>
    <dispatcher>FORWARD</dispatcher>
</filter-mapping>

Otherwise, other configurations don't take effect.

Collectives™ on Stack Overflow

Spring-Boot, Can't save unicode string in MySql using spring-data JPA

6 Answers 6

Comments

2 Comments

Comments

Comments

Create mysql table with utf-8 charset

method 1 (I use this method): config in Spring Boot JPA

method 2: set utf-8 in MySQL config file `my.cnf`

Connect MySQL with UTF-8

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

2 Comments

Comments

Comments

Create mysql table with utf-8 charset

method 1 (I use this method): config in Spring Boot JPA

method 2: set utf-8 in MySQL config file my.cnf

Connect MySQL with UTF-8

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

method 2: set utf-8 in MySQL config file `my.cnf`