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

Question

提问by

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-

这是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-

我的.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 latin1For 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 |

Answer 1

采纳答案by seba.wagner

You can also create databases with encoding.
Simply use phpMyAdminfor 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:

您可以在休眠设置的 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 hbm2dllwill 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.auto, 并手动修改数据库的表以进行 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

塞巴斯蒂安

Answer 2

回答by Stanislav Bashkyrtsev

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

首先在 Java 端你应该指定 UTF-8 而不是 utf8，参考这里的表格。

Second, characterEncodingis 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.

其次，characterEncoding不是您的表将在其中创建的字符集，这只是在通信和从数据库读取/写入数据时将使用的字符集。

MySQL Docssay 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 Docs说在创建表的过程中，如果没有在这些方面指定任何内容，将使用 DB 字符集。这意味着为了使这成为可能，您的数据库（不是 MySQL 服务器）应该像这样创建：

create database DB_NAME character set 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 herefor more details.

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

Answer 3

回答by Duc Tran

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.

它解决了这个案子。

Answer 4

回答by Gray

How to change the encoding to UTF-8?

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

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

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

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

I was actually extending the MySQL5InnoDBDialecttype so I was really using:

我实际上是在扩展MySQL5InnoDBDialect类型，所以我真的在使用：

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

Answer 5

回答by camposer

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

我正在使用 Spring-Data。我试过在 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

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

Answer 6

回答by jo8937

how about change database collation?

如何更改数据库排序规则？

ALTER DATABASE [database] CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER DATABASE [数据库] CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Answer 7

回答by Dmitry Skirdin

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!

小心';' 在属性字符串的末尾！

Answer 8

回答by Yamashiro Rion

For those who use Spring Boot: add the characterEncoding=utf8parameter to your application.propertiesfile to this line:

对于那些使用 Spring Boot 的人：将characterEncoding=utf8参数添加到您的application.properties文件到这一行：

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

Answer 9

回答by TryChai

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

您可以使用 Hibernate @Type 属性，根据您的要求，您可以自定义注释并应用到字段之上。喜欢：

  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:

首先，null SafeGet 方法：

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

接下来是 null SafeSet 方法：

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

最后，我们可以在我们的 OfficeEmployee 实体类中声明我们的自定义 PhoneNumberType：

    @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-typessimilarly you have to do UTF-8 encoding/Decoding and ISO-8859-1 Decoding/encoding

这可能会解决您的问题，这将适用于所有数据库。如果您想了解更多信息，请参考 :: https://www.baeldung.com/hibernate-custom-types同样，您必须执行 UTF-8 编码/解码和 ISO-8859-1 解码/编码

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

提问by

采纳答案by seba.wagner

回答by Stanislav Bashkyrtsev

回答by Duc Tran

回答by Gray

回答by camposer

回答by jo8937

回答by Dmitry Skirdin

回答by Yamashiro Rion

回答by TryChai

相关推荐

最近更新

标签

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

提问by

采纳答案by seba.wagner

回答by Stanislav Bashkyrtsev

回答by Duc Tran

回答by Gray

回答by camposer

回答by jo8937

回答by Dmitry Skirdin

回答by Yamashiro Rion

回答by TryChai

相关推荐

哪种 sql server 数据类型最能代表 C# 中的双精度值？

C# 在短字节和字节之间转换的好方法？

C# 407 需要身份验证 - 未发送质询

在 Android 设备上开发 C#/.NET

相关推荐

最近更新

标签