Hibernate + MySQL:如何为数据库和表设置utf-8编码。

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

我的系统运行在Linux Mandriva, RDBMS - MySQL 5上。我需要用UTF-8创建数据库和表。

Here is a fragment of hibernate.cfg.xml -

这是冬眠的片段。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 -

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-

但是在生成表时，我看到了例如latin1的编码

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?

如何将编码更改为UTF-8?

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

我将非常感谢这个信息!谢谢你！

...

…

This is strange, I have changed all to utf8 -

这很奇怪，我把所有的都改成了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 |

7 个解决方案

#1

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

您还可以使用编码创建数据库。简单地使用phpmyadmin作为数据库/表创建。

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

您将在hibernate设置的URL中指定一些URL参数，以便使用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

只有在使用时，才需要将数据库中的整个编码设置为utf8

        <!-- 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.

您必须将MySQL的默认编码设置为utf8。因为hbm2dll将使用数据库的默认编码。

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

您可能仍然使用hbm2ddl。自动，并手动修改数据库的表，使其具有utf8排序规则。

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.

如果不使用hbm2ddl。auto，您可以简单地使用您喜欢的编码创建表。不需要将数据库设置为特殊的编码。

Sebastian

塞巴斯蒂安。

#2

How to change the encoding to UTF-8?

如何将编码更改为UTF-8?

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

我使用了一个本地方言类，扩展了MySQLDialect，并更改了表类型字符串:

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:

我扩展了MySQL5InnoDBDialect类型

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

#3

Consider changing the connection url configuration like this:

考虑这样更改连接url配置:

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

It solves the case.

它解决了。

#4

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

我用spring数据。我尝试激活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

另外，我也尝试过hibernate属性，但是最终对我有效的解决方案是@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());

#5

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

首先，在Java方面，您应该指定UTF-8而不是utf8，请参见这里的表。

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:

MySQL文档说，在创建表时，如果在这些方面没有指定任何内容，将使用DB字符集。这意味着，为了实现这一点，应该像这样创建数据库(而不是MySQL服务器):

create database DB_NAME character set utf8;

创建数据库DB_NAME字符集utf8;

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

之后，应该使用utf8编码创建这个数据库中的表。同样的故事与排序。

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

当然，您不应该依赖Hibernate的hbm2ddl，请阅读这里了解更多细节。

#6

how about change database collation?

更改数据库排序怎么样?

ALTER DATABASE [database] CHARACTER SET utf8 COLLATE utf8_unicode_ci;

修改数据库[数据库]字符集utf8比对utf8_unicode_ci;

#7

Via Spring Java Config dataSource() this should help:

通过Spring Java Config dataSource()，这应该会有所帮助:

@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!

注意';'在属性字符串的末尾!