Spring+Hibernate中@Lob字段存波兰字符触发SqlException问题

我已尝试解决该问题多日。使用Spring 5.3.22、Hibernate 5.6.10.Final的应用连接MySQL 8.0.30（默认utf8mb4编码），普通波兰字符存储正常，但@Lob标注的字段出现编码异常。

根据MySQL文档：

clobCharacterEncoding - 用于发送和检索TEXT、MEDIUMTEXT和LONGTEXT值的字符编码，替代配置的connection characterEncoding。

理论上longtext值应采用UTF-8编码，但通过Hibernate会话保存含波兰字符的实体时，触发JpaSystemException: could not execute statement (...)，根源为GenericJDBCException/SQLException: java.sql.SQLException: Incorrect string value: '\x9C\xE6' for column 'tresc' at row 1。

相关代码与配置 #

实体类Notatka #

@Entity
@Indexed
public class Notatka {
   public static class Widok {
      public interface Podstawowy {}
      public interface Rozszerzony extends Podstawowy {}
      public interface Pelny extends Rozszerzony {}
   }

   @JsonIgnore
   @Id
   @GeneratedValue
   private Long id;

   @JsonView(Widok.Podstawowy.class)
   @Column(unique = true)
   @NotNull
   private UUID uuid;

   @JsonView(Widok.Podstawowy.class)
   @NotBlank
   @FullTextField(analyzer = "autocomplete_indexing", searchAnalyzer = "autocomplete_search")
   private String tytul;

   @JsonView(Widok.Podstawowy.class)
   private Instant dataUtworzenia;

   @JsonView(Widok.Podstawowy.class)
   private Instant dataOstatniejEdycji;

   @JsonView(Widok.Podstawowy.class)
   @NotNull
   @Lob
   private String tresc;

   @JsonView(Widok.Rozszerzony.class)
   @ManyToOne
   @NotNull
   private Mieszkaniec mieszkaniec;

   // Getters and setters
}

编码测试（可正常运行） #

@Test
public void insertBlobTest() {
   HikariDataSource dataSource = new HikariDataSource();
   dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
   dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/db");
   dataSource.setUsername("username");
   dataSource.setPassword("password");

   JdbcTemplate template = new JdbcTemplate(dataSource);

   String insert = "INSERT INTO Notatka(id, uuid, tytul, tresc, dataUtworzenia, dataOstatniejEdycji, mieszkaniec_id) VALUES (-1, 'c599725c-61d5-4be7-b147-2e1695213e0a', 'tytul', 'ęśćźżąłó', '2020-01-01 12:00:00', '2020-01-01 12:00:00', 1)";
   template.update(insert);

   String select = "SELECT tresc FROM Notatka WHERE id = -1";
   String tresc = template.queryForObject(select, String.class);

   Truth.assertThat(tresc).isEqualTo("ęśćźżąłó");
}

JPA配置 #

@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource, HibernateJpaVendorAdapter jpaVendorAdapter) {
   LocalContainerEntityManagerFactoryBean entityManagerFactory = new LocalContainerEntityManagerFactoryBean();
   entityManagerFactory.setDataSource(dataSource);
   entityManagerFactory.setJpaVendorAdapter(jpaVendorAdapter);
   entityManagerFactory.setPackagesToScan(
      "***.common.persistence",
      "***.model",
      "***.converter"
   );
   entityManagerFactory.setJpaPropertyMap(ImmutableMap.of(
      AvailableSettings.IMPLICIT_NAMING_STRATEGY, ImplicitNamingStrategyComponentPathImpl.class,
      AvailableSettings.HBM2DDL_CHARSET_NAME, "UTF-8",
      AvailableSettings.HBM2DDL_AUTO, Action.VALIDATE,
      AvailableSettings.DIALECT, MySQL8Dialect.class.getName()
   ));
   return entityManagerFactory;
}
// packages hidden

Controller的PUT方法 #

@Transactional
@PutMapping("/{uuid}")
public void put(@PathVariable UUID uuid, @RequestBody @Valid Notatka notatkaZadanie) {
   if (!Objects.equal(notatkaZadanie.getUuid(), uuid)) {
      throw new ResponseStatusException(HttpStatus.BAD_REQUEST);
   }
   Mieszkaniec mieszkaniec = mieszkaniecService.getByUuid(notatkaZadanie.getMieszkaniec().getUuid())
         .orElseThrow(() -> new ResponseStatusException(HttpStatus.BAD_REQUEST));

   Notatka notatka = notatkaService.getByUuid(uuid)
         .orElseGet(() -> nowaNotatka(uuid, mieszkaniec));

   if (!Objects.equal(notatka.getMieszkaniec().getUuid(), notatkaZadanie.getMieszkaniec().getUuid())) {
      throw new ResponseStatusException(HttpStatus.BAD_REQUEST);
   }

   notatka.setTytul(notatkaZadanie.getTytul());
   notatka.setTresc(notatkaZadanie.getTresc());
   notatka.setDataOstatniejEdycji(Instant.now());

   if (notatka.getId() == null) {
      notatkaService.add(notatka);
   }
}

已知clobCharacterEncoding=UTF-8或useServerPrepStmts=true添加到JDBC URL可解决问题，但不想修改JDBC URL，也试过useUnicode=true&characterEncoding=UTF-8无效，求其他解决方案。

以下几种无需修改JDBC URL的方法可以尝试：

1. 通过数据源配置连接属性 #

如果使用HikariCP（测试代码中使用的数据源），可以直接在DataSource Bean中设置连接属性，无需修改URL字符串：

@Bean
public DataSource dataSource() {
    HikariDataSource dataSource = new HikariDataSource();
    dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
    dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/db");
    dataSource.setUsername("username");
    dataSource.setPassword("password");
    // 添加clobCharacterEncoding属性
    dataSource.addDataSourceProperty("clobCharacterEncoding", "UTF-8");
    return dataSource;
}

2. 自定义Hibernate方言 #

扩展MySQL8Dialect，强制Lob字段使用utf8mb4编码处理：

public class CustomMySQL8Dialect extends MySQL8Dialect {
    @Override
    public SqlTypeDescriptor getSqlTypeDescriptorOverride(int sqlCode) {
        if (sqlCode == Types.CLOB) {
            return new VarcharTypeDescriptor() {
                @Override
                public int getSqlType() {
                    return Types.CLOB;
                }

                @Override
                public String getCastTypeName(int length) {
                    return "longtext character set utf8mb4";
                }
            };
        }
        return super.getSqlTypeDescriptorOverride(sqlCode);
    }
}

然后在JPA配置中替换原方言：

entityManagerFactory.setJpaPropertyMap(ImmutableMap.of(
    // ...其他配置
    AvailableSettings.DIALECT, CustomMySQL8Dialect.class.getName()
));

3. 显式指定@Column的列定义 #

在@Lob字段上添加columnDefinition，明确指定字符集：

@JsonView(Widok.Podstawowy.class)
@NotNull
@Lob
@Column(columnDefinition = "LONGTEXT CHARACTER SET utf8mb4")
private String tresc;

注意需提前验证数据库中该字段实际为utf8mb4编码，若不是需先修改表结构。

4. 通过配置文件设置数据源属性 #

如果是Spring Boot项目，可直接在application.properties或application.yml中配置：

application.properties #

spring.datasource.hikari.data-source-properties.clobCharacterEncoding=UTF-8

application.yml #

spring:
  datasource:
    hikari:
      data-source-properties:
        clobCharacterEncoding: UTF-8

内容的提问来源于stack exchange，提问作者KarolJanaszek