CODEWITHSUNDEEP
spring-bootspring-data-jpa
tyro
tyro

Reputation: 795

Spring boot2, Mysql UTF-8 encoding not working

I'm using spring boot 2 jpa, mysql db at the back end, I have some fields of the table which requires special characters to be saved. I tried lot many ways to do it but it just not happening,

it should pretty straight forward I could see how others are using it but for some reason it does not work for me.

The value Würtemberg getting saved like W�rtemberg

Any help would be appreciated.

Below are the ways I tried but couldnt manage to get it work,

in application.properties,

Way 1 :-

spring.datasource.url=jdbc:mysql://${global.database.host}:${global.database.port}/${global.database.schema}?useSSL=false&useUnicode=true&characterEncoding=UTF-8

way 2 useUnicode=yes in above url

way 3 

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

way 4 

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

way 5

spring.datasource.url= jdbc:mysql://localhost:3306/database?useUnicode=yes&characterEncoding=UTF-8
connection.useUnicode=true
connection.characterEncoding=utf-8
hibernate.connection.useUnicode=true
hibernate.connection.characterEncoding=UTF-8
spring.datasource.sqlScriptEncoding=UTF-8

But no luck,

Any idea why its not working,

I'm using DatasourceConfiguration as below,

@Slf4j
@Setter
@Configuration
public class DataSourceConfiguration {

    @Value("${spring.datasource.tomcat.test-on-borrow}")
    private boolean testOnBorrow;

    @Value("${spring.datasource.tomcat.validation-query}")
    private String validationQuery;

    @Value("${global.database.host}")
    private String dbHost;

    @Value("${global.database.port}")
    private Integer dbPort;


    @Bean
    @Primary
    public DataSourceProperties dataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    public DataSource dataSource() throws DockerException, InterruptedException {

        if (StringUtils.isEmpty(dbHost) || dbPort == null) {
            // some log
        } else {
            ServiceProbe probe = new ServiceProbe(2, TimeUnit.MINUTES);
            try {
                probe.probe(new IsTcpConnectionUp(dbHost, dbPort));
            } catch (ServiceDownException e) {
                throw new RuntimeException(e);
            }
        }

        DataSourceProperties dsp = dataSourceProperties();
        DataSourceBuilder dataSourceBuilder = dsp.initializeDataSourceBuilder();
        DataSource ds = dataSourceBuilder.build();


        if (ds instanceof org.apache.tomcat.jdbc.pool.DataSource) {
            ((org.apache.tomcat.jdbc.pool.DataSource) ds).setTestOnBorrow(testOnBorrow);
            ((org.apache.tomcat.jdbc.pool.DataSource) ds).setValidationQuery(validationQuery);
        } else {
            // some logs
        }
        return ds;
    }
}

Upvotes: 1

Views: 3526

Answers (2)

Balkrushna Patil
Balkrushna Patil

Reputation: 466

By default, MySQL tables CHARSET is in latin1. In order to store UTF-8 characters, we need to encode table is in UTF-8 CHARSET.

For instance. ALTER TABLE dbName.tableName MODIFY COLUMN columName longtext CHARACTER SET utf8 COLLATE utf8_general_ci;

Upvotes: 0

gines.nt
gines.nt

Reputation: 113

It's a quite old question, so I probably post solution just for the sake of history.

1) Database collation and table's fields collation should be utf8mb4_..., for example utf8mb4_unicode_ci.

2) Application.properties connection string should contain: useUnicode=true&characterEncoding=UTF-8

Just as in your "Way 1". That's it.

Upvotes: 2

Related Questions