How to set collation for table attribute as utf8_bin in either annotation or application.properties file using Spring Boot

Question

How do I set the collation for table attribute as utf8_bin in either annotation or the application.properties file using Spring Boot?

I have tried many ways but they did not work. Can you help?

I have tried the following ways.

First: Using @Column annotation like this:

@Column(name = "user_id",columnDefinition="VARCHAR(255) COLLATE utf8_bin")

Second:

 @Column(columnDefinition="VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin")

Third: Using application.properties file

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

Fourth:

spring.datasource.url =jdbc:mysql://localhost:3306/iot_schema?createDatabaseIfNotExist=true&useUnicode=true&connectionCollation=utf8_bin&characterSetResults=utf8

Answer 1

Here's a solution inspired by answer to similar question: Set Table character-set/collation using Hibernate Dialect?

Extend the preferred MySQL dialect and override its getTableTypeString() method like this:

public class MySQLCustomDialect extends MySQL8Dialect {
    @Override
    public String getTableTypeString() {
        return " ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin";
    }
}

Set that class to be used in application.properties:

spring.jpa.properties.hibernate.dialect=my.package.MySQLCustomDialect

Here's the generated SQL query:

    create table test_table (
      ...
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin