CODEWITHSUNDEEP
mysqllinuxhibernatejpacharacter-encoding
user1134181
user1134181

Reputation:

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

My system runs on Linux Mandriva, RDBMS - MySQL 5. I need to have the database and tables created in UTF-8.

Here is a fragment of hibernate.cfg.xml -

... 
 <property name="hibernate.hbm2ddl.auto">create-drop</property>   
 <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
 <property name="hibernate.connection.characterEncoding">utf8</property> 
...

my.cnf -

# The MySQL server
[mysqld]
...
default-character-set=cp1251
character-set-server=cp1251
collation-server=cp1251_general_ci
init-connect="SET NAMES cp1251"
skip-character-set-client-handshake
...
[mysqldump]
...    
default-character-set=cp1251
...

Some class, for example -

@Entity
@Table(name = "USER")
public class User {
    @Id 
    @Column(name = "USERID")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(name = "USERNAME")
    private String name;

    @Column(name = "USERPASSWORD")
    private String password;

    @Column(name = "USERIP")
    private String ip;
        // getter's and setter's here
        ...

But when the tables are generated, I see the encoding latin1 For example- 

SHOW CREATE TABLE USER;

USER  | CREATE TABLE `user` (
  `USERID` int(11) NOT NULL auto_increment,
  `USERIP` varchar(255) default NULL,
  `USERNAME` varchar(255) default NULL,
  `USERPASSWORD` varchar(255) default NULL,
  PRIMARY KEY  (`USERID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

How to change the encoding to UTF-8?

I would be most grateful for the information! Thank you!

...

This is strange, I have changed all to utf8 -

# The MySQL server
    [mysqld]
    ...
    default-character-set=utf8
    character-set-server=utf8
    collation-server=utf8_general_ci
    init-connect="SET NAMES utf8"
    skip-character-set-client-handshake
    ...
    [mysqldump]
    ...    
    default-character-set=utf8
    ...

And now -

SHOW CREATE TABLE USER;

USER  | CREATE TABLE `USER` (
  `USERID` int(11) NOT NULL auto_increment,
  `USERIP` varchar(255) default NULL,
  `USERNAME` varchar(255) default NULL,
  `USERPASSWORD` varchar(255) default NULL,
  PRIMARY KEY  (`USERID`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 |

Upvotes: 27

Views: 83966

Answers (9)

TryChai
TryChai

Reputation: 59

You can Use Hibernate @Type attribute,Based on your requirement you can customize the annotation and apply on top of the fied. like :

  public class PhoneNumberType implements UserType {
    @Override
    public int[] sqlTypes() {
  return new int[]{Types.INTEGER, Types.INTEGER, Types.INTEGER};
    }

    @Override
    public Class returnedClass() {
        return PhoneNumber.class;
    }

    // other methods
    }

First, the null SafeGet method:

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, 
    SharedSessionContractImplementor session, Object owner) throws HibernateException,                   
      SQLException {
    int countryCode = rs.getInt(names[0]);
     if (rs.wasNull())
        return null;
     int cityCode = rs.getInt(names[1]);
    int number = rs.getInt(names[2]);
    PhoneNumber employeeNumber = new PhoneNumber(countryCode, cityCode, number);
     return employeeNumber;
     }

Next, the null SafeSet method:

    @Override
     public void nullSafeSet(PreparedStatement st, Object value, 
      int index, SharedSessionContractImplementor session) 
       throws HibernateException, SQLException {
        if (Objects.isNull(value)) {
        st.setNull(index, Types.INTEGER);
        } else {
        PhoneNumber employeeNumber = (PhoneNumber) value;
        st.setInt(index,employeeNumber.getCountryCode());
        st.setInt(index+1,employeeNumber.getCityCode());
        st.setInt(index+2,employeeNumber.getNumber());
      }
     }

Finally, we can declare our custom PhoneNumberType in our OfficeEmployee entity class:

    @Entity
    @Table(name = "OfficeEmployee")
     public class OfficeEmployee {

      @Columns(columns = { @Column(name = "country_code"), 
      @Column(name = "city_code"), @Column(name = "number") })
      @Type(type = "com.baeldung.hibernate.customtypes.PhoneNumberType")
       private PhoneNumber employeeNumber;

     // other fields and methods
      }

This might solve your problem, This will work for all database. if you want more info refer :: https://www.baeldung.com/hibernate-custom-types similarly you have to do UTF-8 encoding/Decoding and ISO-8859-1 Decoding/encoding

Upvotes: 1

Yamashiro Rion
Yamashiro Rion

Reputation: 1966

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

Upvotes: 4

seba.wagner
seba.wagner

Reputation: 3860

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

Upvotes: 60

Stanislav Bashkyrtsev
Stanislav Bashkyrtsev

Reputation: 15308

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.

Upvotes: 6

Dmitry Skirdin
Dmitry Skirdin

Reputation: 11

Via Spring Java Config dataSource() this should help:

@Bean
public DataSource dataSource() {    
    BasicDataSource dataSource = new BasicDataSource();    
    //your username/pass props
    dataSource.setConnectionProperties("useUnicode=true;characterEncoding=utf8;characterSetResults=UTF-8;");
    return dataSource;
}

Be careful about ';' at the end of properties string!

Upvotes: 1

Gray
Gray

Reputation: 116878

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";
    }
}

Upvotes: 19

jo8937
jo8937

Reputation: 580

how about change database collation?

ALTER DATABASE [database] CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Upvotes: 2

camposer
camposer

Reputation: 5612

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

jdbc:mysql://localhost:3306/DATABASE?createDatabaseIfNotExist=true&amp;useUnicode=true&amp;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());

Upvotes: 5

Duc Tran
Duc Tran

Reputation: 6294

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.

Upvotes: 15

Related Questions