最新消息:20210816 当前crifan.com域名已被污染,为防止失联,请关注(页面右下角的)公众号

【已解决】Spring Boot中JPA中指定生成MySQL表结构的Charset和Encoding以及Collation

MySQL crifan 3138浏览 0评论
折腾:
【已解决】spring boot中添加访问操作mysql数据库
期间,从Spring Boot中的JPA生成的Mysql的表结构
-》很明显看出:
Encoding:不是我们希望的utf-8
Collation:也不是我们希望的 utf8mb4 之类的
-》
默认是latin的charset
以及Encoding和Collation都不是我们希望的
所以要去想办法把表结构改为我们希望的
mysql – spring data jpa utf-8 encoding not working – Stack Overflow
好像加上:
?useUnicode=yes&characterEncoding=UTF-8
就可以了?
但是感觉也缺少Collation啊
Hibernate Community • View topic – Problem With UTF-8 in database
【springboot】关于jpa自动建表 DEFAULT CHARSET=latin1 的简单解决方法_初级菜鸟-CSDN博客
java – Spring Boot JPA set mysql charset to utf-8 – Stack Overflow
Spring boot2, Mysql UTF-8 encoding not working – Stack Overflow
spring – Set jpa utf-8 encoding in application.properties – Stack Overflow
jpa charset collation
Spring Data Jpa Mysql使用utf8mb4编码_HoJian的博客-CSDN博客
Spring Data Jpa Mysql使用utf8mb4编码的示例代码_java_脚本之家
jpa jdbc charset
jdbc charset
java – JDBC character encoding – Stack Overflow
JDBC – Character Set Can Now Be Specified in the Connection URL
jdbc:ctree:port@host_name:db_name?characterEncoding=encoding
MySQL :: MySQL Connector/J 5.1 Developer Guide :: 5.6 Using Character Sets and Unicode
JDBC: Inserting unicode UTF-8 characters into MySQL (Example)
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname?useUnicode=yes&characterEncoding=UTF-8","username", "password");
JDBC – Character Encoding [Gerardnico – The Data Blog]
5.4 Using Character Sets and Unicode
看了半天感觉是:
此处的jdbc
jdbc:mysql://localhost:3306/dbname?useUnicode=yes&characterEncoding=UTF-8
只是连接时的参数,而不是创建mysql表的时候的参数
所以应该是
src/server/xxx/xxx/src/main/resources/application.properties
spring.jpa.hibernate.ddl-auto=update
中的jpa和hibernate的ddl,去创建的mysql
spring.jpa.hibernate mysql charset
Spring-Boot, Can’t save unicode string in MySql using spring-data JPA – Stack Overflow
好像是:
mysql的自己的配置my.cnf
中配置
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8
可以让mysql自动创建表时,charset是utf-8
-》难道是:
hibernate或jpa,生成mysql时,没法指定默认charset?
只能是mysql自己的配置?
mysql – Hibernate Spring encode charater set wrong [UTF-8] – Stack Overflow
springboot + jpa + mysql项目搭建 – 简书
spring.datasource.url=jdbc:MySQL://localhost:3306/my-first?characterEncoding=utf8&useSSL=false
spring.datasource.connection-init-sql ="SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;"
好像是用connection-init-sql可以实现我们的目的?
另外顺带也注意时区:
show variables like '%time_zone%';
set global time_zone='+8:00';
此处的是:
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.02 sec)
暂时不去改。后续(部署到服务器端后)需要再改。
spring.datasource.connection-init-sql
How can i make a init-sql in Spring Boot-1.4.1-RELEASE – Stack Overflow
java – Run SQL statement at beginning of each DB connection in Spring Boot – Stack Overflow
Apache Tomcat 7 (7.0.99) – The Tomcat JDBC Connection Pool
initSQL – the ability to run an SQL statement exactly once, when the connection is created
SpringBoot配置属性之DataSource – 简书
  • spring.datasource.connection-init-sql 指定连接被创建,再被添加到连接池之前执行的sql.
  • spring.datasource.connection-init-sqls 使用DBCP connection pool时,指定初始化时要执行的sql
  • spring.datasource.init-sql当连接创建时,执行的sql
spring.datasource.init-sql doesn’t get executed because it isn’t supported · Issue #1 · spring-cloud-stream-app-starters/jdbc
spring-boot/DataSourceProperties.java at v1.4.2.RELEASE · spring-projects/spring-boot
springboot2的hikari数据库连接池默认配置 – 掘金
{
  "sourceType": "com.zaxxer.hikari.HikariDataSource",
  "name": "spring.datasource.hikari.connection-init-sql",
  "type": "java.lang.String"
},
Common Application properties
  • spring.datasource.dbcp2.connection-init-sqls
  • spring.datasource.hikari.connection-init-sql
但是却没找到:
  • spring.datasource.connection-init-sql
  • spring.datasource.connection-init-sqls
  • spring.datasource.init-sql
Spring Boot Reference Documentation
Spring Boot spring.datasource
31. Working with SQL Databases
brettwooldridge/HikariCP: 光 HikariCP・A solid, high-performance, JDBC connection pool at last.
如果用,则是:
<dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
        <version>3.4.2</version>
    </dependency>
DBCP – Overview
自己此处项目中,自动生成的
src/server/xxx/xxx/.factorypath
中有:
    <factorypathentry kind="VARJAR" id="M2_REPO/com/zaxxer/HikariCP/3.4.1/HikariCP-3.4.1.jar" enabled="true" runInBatchMode="false"/>
对于DataSource来说,选择顺序:
  • 优先选:HikariCP
    • 对应配置:spring.datasource.hikari.*
  • 其次选:Tomcat
    • 对应配置:spring.datasource.tomcat.*
  • 最后选:Commons DBCP2
    • 对应配置:spring.datasource.dbcp2.*
Spring Data
Spring Data REST
Getting Started · Accessing Data with JPA
Getting Started · Accessing JPA Data with REST
Spring Data JPA
Common Application properties
中找到了
spring.datasource.hikari.connection-init-sql
其他的是:
spring.datasource.hikari.allow-pool-suspension
spring.datasource.hikari.auto-commit
spring.datasource.hikari.catalog
spring.datasource.hikari.connection-test-query
spring.datasource.hikari.connection-timeout
spring.datasource.hikari.data-source-class-name
spring.datasource.hikari.data-source-j-n-d-i
spring.datasource.hikari.data-source-properties
spring.datasource.hikari.driver-class-name
spring.datasource.hikari.health-check-properties
spring.datasource.hikari.health-check-registry
spring.datasource.hikari.idle-timeout
spring.datasource.hikari.initialization-fail-timeout
spring.datasource.hikari.isolate-internal-queries
spring.datasource.hikari.jdbc-url
spring.datasource.hikari.leak-detection-threshold
spring.datasource.hikari.login-timeout
spring.datasource.hikari.max-lifetime
spring.datasource.hikari.maximum-pool-size
spring.datasource.hikari.metric-registry
spring.datasource.hikari.metrics-tracker-factory
spring.datasource.hikari.minimum-idle
spring.datasource.hikari.password
spring.datasource.hikari.pool-name
spring.datasource.hikari.read-only
spring.datasource.hikari.register-mbeans
spring.datasource.hikari.scheduled-executor
spring.datasource.hikari.schema
spring.datasource.hikari.transaction-isolation
spring.datasource.hikari.username
spring.datasource.hikari.validation-timeout
Documentation – 5.4 – Hibernate ORM
Hibernate ORM 5.4.10.Final User Guide
Hibernate Getting Started Guide
再去找找
spring.datasource.init-sql
发现并不存在这个配置
66. Database initialization
再去搜搜
spring.datasource.connection-init-sql
Set default schema = SOMETHING in oracle using Spring Boot and Spring JDBC – Stack Overflow
java – Run SQL statement at beginning of each DB connection in Spring Boot – Stack Overflow
java – Execute database session initialization SQL in Spring JDBC – Stack Overflow
utf 8 – Insert emoji does not work with spring-boot and MariaDB – Stack Overflow
看来是:
也不存在spring.datasource.connection-init-sql
所以,现在只剩下:
spring.datasource.hikari.connection-init-sql
brettwooldridge/HikariCP: 光 HikariCP・A solid, high-performance, JDBC connection pool at last.
“🔠connectionInitSql
This property sets a SQL statement that will be executed after every new connection creation before adding it to the pool. If this SQL is not valid or throws an exception, it will be treated as a connection failure and the standard retry logic will be followed. Default: none”
去试试
spring.datasource.hikari.connection-init-sql=SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci
不过要先去删除之前数据库
就空了:
再去新建表
发现不需要创建,已有对应database了。
然后重新运行项目:
注意到:
Control+C中断当前调试时,也显示Hikari的Pool是shutdown关闭:
2020-02-01 19:59:37,228 INFO  AbstractEntityManagerFactoryBean.java:598 - Closing JPA EntityManagerFactory for persistence unit 'default'
2020-02-01 19:59:37,232 INFO  HikariDataSource.java:350 - HikariPool-5 - Shutdown initiated...
2020-02-01 19:59:37,241 INFO  HikariDataSource.java:352 - HikariPool-5 - Shutdown completed.
^C^C[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
重新运行,结果:
问题依旧:
改为:
spring.datasource.connection-init-sql ="SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;"
spring.datasource.hikari.connection-init-sql="SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"
spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/xx?useUnicode=yes&characterEncoding=UTF-8
结果:
报错:
2020-02-01 20:10:15,650 WARN  JdbcEnvironmentInitiator.java:132 - HHH000342: Could not obtain connection to query metadata : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"' at line 1
试试:

spring.datasource.connection-init-sql =SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'


spring.datasource.hikari.connection-init-sql=SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'

结果:
问题依旧:
spring.datasource.hikari.connection-init-sql
java – How do I configure HikariCP in my Spring Boot app in my application.properties files? – Stack Overflow
java – How to use HikariCP in Spring Boot with two datasources in conjunction with Flyway – Stack Overflow
spring boot – How to configure hikari pool for eager initialization? – Stack Overflow
spring – HikariCP Idle connections staying in connection pool as active – Stack Overflow
Configuring a Hikari Connection Pool with Spring Boot | Baeldung
还是不行。
Spring Data Jpa Mysql使用utf8mb4编码的示例代码_java_脚本之家
搜:
MySQL5InnoDBDialect

spring.datasource.connection-init-sql=SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';


spring.datasource.hikari.connection-init-sql=SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';

问题依旧
spring.datasource.connection-init-sql="SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;"
问题依旧。
jpa MySQL5InnoDBDialect
MySQL5InnoDBDialect
Spring Boot (五)Spring Data JPA 操作 MySQL 8 – 个人文章 – SegmentFault 思否
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
SpringBoot整合JPA的踩坑点 | 梦起飞
https://panlf.github.io/2018/06/13/SpringBoot整合JPA的踩坑点/
旧,SpringBoot2.0之前:
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
新,SpringBoot2.0之后:
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
记JPA一次生成数据库表没有外键原因_哆啦A梦丶幻想的博客-CSDN博客
【spring boot】spring boot 2.0 项目中使用mysql驱动启动创建的mysql数据表,引擎是MyISAM,如何修改启动时创建数据表引擎为【spring boot 2.0】 – Angel挤一挤 – 博客园
Spring Boot 最佳实践(五)Spring Data JPA 操作 MySQL 8 – 云+社区 – 腾讯云
sql – Using “TYPE = InnoDB” in MySQL throws exception – Stack Overflow
org.hibernate.dialect (Hibernate JavaDocs)
“MySQL5InnoDBDialect
Deprecated
Use “hibernate.dialect.storage_engine=innodb” environment variable or JVM system property instead.”
MySQL5InnoDBDialect (Hibernate JavaDocs)
@Deprecated 过时的 MySQL5InnoDBDialect – 小不的笔记
加上配置
src/server/xxx/xxx/src/main/resources/application.properties
spring.jpa.database-platform=com.crifan.xxx.MySQL5InnoDBDialectUtf8mb4
以及:
src/server/xxx/xxx/src/main/java/com/crifan/xxx/MySQL5InnoDBDialectUtf8mb4.java
import org.hibernate.dialect.MySQL5InnoDBDialect;
public class MySQL5InnoDBDialectUtf8mb4 extends MySQL5InnoDBDialect {
  @Override
  public String getTableTypeString() {
    return "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci";
  }
}
结果却又提示:
The type MySQL5InnoDBDialect is deprecated Java(16777221)
换成:MySQL55Dialect
src/server/xxx/xxx/src/main/java/com/crifan/xxx/MySQL55DialectUtf8mb4.java
package com.crifan.xxx;


import org.hibernate.dialect.MySQL55Dialect;;
// import org.hibernate.dialect.MySQL5InnoDBDialect;


// public class MySQL5InnoDBDialectUtf8mb4 extends MySQL5InnoDBDialect {
public class MySQL5InnoDBDialectUtf8mb4 extends MySQL55Dialect {
  @Override
  public String getTableTypeString() {
    return "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci";
  }
}
结果:
至少编译没报错。
以及:
src/server/xxx/xxx/src/main/resources/application.properties
spring.jpa.database-platform=com.crifan.xxx.MySQL55DialectUtf8mb4
去试试
终于可以了:
【总结】
此处,不需要要去修改mysql的配置,只想要自己此处通过配置指定JPA所创建mysql的table时,指定对应charset是utf8mb4,以及Collation是utf8mb4_unicode_ci
则具体做法是:
创建一个mysql的dialect的类,用于设置table的type的string
src/server/xxx/xxx/src/main/java/com/crifan/xxx/MySQL55DialectUtf8mb4.java
package com.crifan.xxx;


import org.hibernate.dialect.MySQL55Dialect;;
// import org.hibernate.dialect.MySQL5InnoDBDialect;


// public class MySQL5InnoDBDialectUtf8mb4 extends MySQL5InnoDBDialect {
public class MySQL55DialectUtf8mb4 extends MySQL55Dialect {
  @Override
  public String getTableTypeString() {
    return "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci";
  }
}
然后把这个dialect去设置给jpa
src/server/xxx/xxx/src/main/resources/application.properties
spring.jpa.database-platform=com.crifan.xxx.MySQL55DialectUtf8mb4
如此,创建出来的table就是utf8的了。
其中,可以从table info
CREATE TABLE `iec104` (
  `id` int(11) NOT NULL,
  `data` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `parse_result` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
看出来其中有对应的:
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
注:
(1)另外,创建表是utf8了,连接时也要用utf8,即:
src/server/xxx/xxx/src/main/resources/application.properties
spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/xx?useUnicode=yes&characterEncoding=UTF-8
中是加了:
useUnicode=yes&characterEncoding=UTF-8
才可以。
(2)如果不用此配置,也可以换去修改mysql的配置,使得默认创建出来的table就是utf8
详见:
Spring Data Jpa Mysql使用utf8mb4编码_HoJian的博客-CSDN博客
中的:
将本地数据库改成utf8mb4,utf8mb4_unicode_ci
(3)后来也回复了帖子:
Spring-Boot, Can’t save unicode string in MySql using spring-data JPA – Stack Overflow

转载请注明:在路上 » 【已解决】Spring Boot中JPA中指定生成MySQL表结构的Charset和Encoding以及Collation

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
82 queries in 0.182 seconds, using 22.22MB memory