Hibernate + MySQL: How to set the encoding utf-8 for database and tables

Question

You can also create databases with encoding.
Simply use phpMyAdmin for the database/table creation.

There are some URL parameters you would specify in the URL of the hibernate settings to have the connection using UTF8:

<!-- Database Settings -->
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<!--  for performance reasons changed to MyISAM from org.hibernate.dialect.MySQLInnoDBDialect -->
<property name="dialect">org.openmeetings.app.hibernate.utils.MySQL5MyISAMDialect</property>
<property name="connection.url">jdbc:mysql://localhost/openmeetings?autoReconnect=true&amp;useUnicode=true&amp;createDatabaseIfNotExist=true&amp;characterEncoding=utf-8</property>    

<property name="hibernate.connection.CharSet">utf8</property>
<property name="hibernate.connection.characterEncoding">utf8</property>
<property name="hibernate.connection.useUnicode">true</property>

You don't need to set the whole encoding in the database to utf8 Only if you are using

<!-- Database Scheme Auto Update -->
<property name="hbm2ddl.auto">update</property>   

You WILL have to set the default encoding of MySQL to utf8. Cause the hbm2dll will use the default encoding of the database.

You might still use hbm2ddl.auto, and modify the table's of the database manually to have utf8 collation.

If you are not using hbm2ddl.auto, you can simply create the tables with your favorite encoding. No need to set the database to a special encoding.

Sebastian

How to change the encoding to UTF-8?

I used a local dialect class that extended the MySQLDialect and changed the table-type string:

public class LocalMysqlDialect extends MySQLDialect {
    @Override
    public String getTableTypeString() {
        return " DEFAULT CHARSET=utf8";
    }
}

I was actually extending the MySQL5InnoDBDialect type so I was really using:

public class LocalMysqlDialect extends MySQL5InnoDBDialect {
    @Override
    public String getTableTypeString() {
        return " ENGINE=InnoDB DEFAULT CHARSET=utf8";
    }
}

Consider changing the connection url configuration like this:

<property name="hibernate.connection.url">
    jdbc:mysql://localhost/yourdatabase?UseUnicode=true&amp;characterEncoding=utf8
</property>

It solves the case.

First of all on Java side you should specify UTF-8 instead of utf8, refer to table here.

Second, characterEncoding is not a character set your tables will be created in, this is just a charset that will be used while communication and reading/writing data to/from database.

MySQL Docs say that during the creation of tables, a DB charset will be used if nothing was specified in these regards. Which means that in order to make this possible, your database (not MySQL Server) should be created like that:

create database DB_NAME character set utf8;

Afterwards your tables in this database should be created in utf8 encoding. Same story with collation.

But of course you shouldn't rely on Hibernate's hbm2ddl, read here for more details.

I'm using Spring-Data. I've tried activating parameters in the URL:

jdbc:mysql://localhost:3306/DATABASE?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=utf-8

Also, I've tried with hibernate properties, but the solution which definitively worked for me is the one proposed by @Gray

@Bean
@Autowired
public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource) {
    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    vendorAdapter.setGenerateDdl(dbGenerateDdl); 
    vendorAdapter.setShowSql(dbShowSql);
    if (Arrays.asList(environment.getActiveProfiles()).contains("prod"))
        vendorAdapter.setDatabasePlatform(CustomMysqlDialect.class.getName());

    Properties jpaProperties = new Properties();
    jpaProperties.put("hibernate.connection.CharSet", "utf-8");
    jpaProperties.put("hibernate.connection.useUnicode", true);
    jpaProperties.put("hibernate.connection.characterEncoding", "utf-8");

    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
    factory.setJpaVendorAdapter(vendorAdapter);
    factory.setPackagesToScan("com.example.model");
    factory.setDataSource(dataSource);
    factory.setJpaProperties(jpaProperties);

    return factory;
}

This line saved my day:

vendorAdapter.setDatabasePlatform(CustomMysqlDialect.class.getName());

For those who use Spring Boot: add the characterEncoding=utf8 parameter to your application.properties file to this line:

spring.datasource.url=jdbc:mysql://localhost:3306/mydatabase?useUnicode=true&characterEncoding=utf8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

how about change database collation?

ALTER DATABASE [database] CHARACTER SET utf8 COLLATE utf8_unicode_ci;