java - OpenShift 上的 MySQL UTF-8 编码问题

Question

我正在使用 Tomcat7 (jboss EWS 2.0) 从 OpenShift 运行一个应用程序，它使用 hibernate 作为持久层。应用程序和 UTF-8 编码在本地主机上正常工作，但是当我部署到 OpenShift 时，UTF-8 编码会中断。目前中文字符全部变成“????”。

我在 OpenShift 上使用 MySQL 5.5。

我使用的那些 hibernate 配置。

持久化JPAConfig.java

@PropertySource({ "classpath:persistence.properties" }) 
public class PersistenceJPAConfig {
@Autowired
private Environment env;

@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
    final LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
    em.setDataSource(dataSource());
    em.setPackagesToScan(new String[] { "org.plantz.persistence.model" });
    final HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    em.setJpaVendorAdapter(vendorAdapter);
    em.setJpaProperties(additionalProperties());
    return em;
}

@Bean
public DataSource dataSource() {
    final DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName(env.getProperty("jdbc.driverClassName"));
    dataSource.setUrl(env.getProperty("jdbc.url"));
    dataSource.setUsername(env.getProperty("jdbc.user"));
    dataSource.setPassword(env.getProperty("jdbc.pass"));
    return dataSource;
}

final Properties additionalProperties() {
    final Properties hibernateProperties = new Properties();
    hibernateProperties.setProperty("hibernate.connection.charSet",
            env.getProperty("hibernate.connection.charSet"));
    hibernateProperties.setProperty("hibernate.connection.characterEncoding",
            env.getProperty("hibernate.connection.characterEncoding"));
    hibernateProperties.setProperty("hibernate.connection.useUnicode",
            env.getProperty("hibernate.connection.useUnicode"));
    hibernateProperties.setProperty("hibernate.hbm2ddl.auto",
            env.getProperty("hibernate.hbm2ddl.auto"));
    hibernateProperties.setProperty("hibernate.dialect",
            env.getProperty("hibernate.dialect"));  

    return hibernateProperties;
}

}

持久化属性

################### DataSource Configuration ##########################
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://${OPENSHIFT_MYSQL_DB_HOST}:${OPENSHIFT_MYSQL_DB_PORT}/${OPENSHIFT_APP_NAME}?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=UTF-8
jdbc.user=${OPENSHIFT_MYSQL_DB_USERNAME}
jdbc.pass=${OPENSHIFT_MYSQL_DB_PASSWORD}
init-db=false
################### Hibernate Configuration ##########################
hibernate.dialect=org.hibernate.dialect.MySQLDialect
hibernate.show_sql=false
hibernate.hbm2ddl.auto=create-drop
hibernate.connection.charSet=utf8
hibernate.connection.characterEncoding=utf8
hibernate.connection.useUnicode=true

当我推送更改时，action_hooks 工作正常
post_start_mysql

#!/bin/bash
echo "********MySQL_config it's running."
/usr/bin/mysql -h $OPENSHIFT_MYSQL_DB_HOST -u $OPENSHIFT_MYSQL_DB_USERNAME \--password=$OPENSHIFT_MYSQL_DB_PASSWORD -P $OPENSHIFT_MYSQL_DB_PORT \-e 'SET NAMES "utf8"; SET CHARACTER SET utf8; SET GLOBAL character_set_client=utf8,character_set_results=utf8, character_set_connection=utf8, character_set_server=utf8, collation_server=utf8_general_ci, character_set_database=utf8;'

pre_start_jboss

echo "*******jboss_Config it's running."
export JAVA_OPTS="$JAVA_OPTS -Dfile.encoding=UTF8"

注意:当所有bean都初始化后，自动设置DB

@Component
public class SetupDB_Data implements ApplicationListener<ContextRefreshedEvent> {

@Autowired
private UserRepository userRepository;

@Override
@Transactional
public void onApplicationEvent(ContextRefreshedEvent arg0) {
    final User user = new User();
    user.setUserName("管理員");
    user.setPassword(passwordEncoder.encode("xxxXXX"));
    user.setEmail("xxxx@xmail.com");
    userRepository.save(user);

}

}

我有一个数据库如下:

mysql> SHOW VARIABLES LIKE '%character%';
    +--------------------------+------------------------------------------------+
    | Variable_name            | Value                                          |
    +--------------------------+------------------------------------------------+
    | character_set_client     | utf8                                           |
    | character_set_connection | utf8                                           |
    | character_set_database   | utf8                                           |
    | character_set_filesystem | binary                                         |
    | character_set_results    | utf8                                           |
    | character_set_server     | utf8                                           |
    | character_set_system     | utf8                                           |
    | character_sets_dir       | /opt/rh/mysql55/root/usr/share/mysql/charsets/ |
    +--------------------------+------------------------------------------------+

Answer 1

听起来列/表定义不是utf8:

col VARCHAR(22) NOT NULL CHARACTER SET utf8mb4,

我推荐 utf8mb4(在 MySQL 中)而不是 utf8，因为 utf8 中缺少一些中文字符。