for few days I'm trying to solve my problem.
I'v got Spring 5.3.22 application with Hibernate 5.6.10.Final. App is connected to MySql 8.0.30. In this version of MySql UTF-8 (utf8mb4) is a default character encoding and polish marks works fine. Problem appear in a @Lob fields.
According to MySql doc:
clobCharacterEncoding - The character encoding to use for sending and retrieving TEXT, MEDIUMTEXT and LONGTEXT values instead of the configured connection characterEncoding.
In this case my longtext value should also be in UTF-8 encoding. Even though while I'm trying to save entity with polish marks to DB, via Hibernate session, appears exception in class CglibAopProxy.java in method proceed(). I recieved JpaSystemException: "could not execute statement (...)" coused by GenericJDBCException/SQLException: java.sql.SQLException: Incorrect string value: '\x9C\xE6' for column 'tresc' at row 1.
My Notatka entity looks like:
@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
Also my test for checking encoding works fine so @Lob field is putting to database correctly:
@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("ęśćźżąłó");
}
My JPA configuration looks like that:
@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
My PUT method in Controller:
@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);
}
}
Last thing is that, when I add to my jdbc url an argument like: jdbc:mysql://localhost:3306/db?clobCharacterEncoding=UTF-8 all starts to work fine. My question is, how to solve that problem but not by adding this param to url. I can't find what couse that problem.
Also I was trying to set useServerPrepStmts=true and it makes my code work correctly, but I don't want to set it to true.
Adding to jdbc url params like: jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=UTF-8 didn't work.