Search code examples
oraclespring-bootspring-data-jpautf-8jparepository

Database UTF-8 encoding problem when trying to retrieve data from spring boot application

My Oracle Database does not support utf-8 encoding, however some tables column values still consists of utf-8 characters. it is inserted using N text literal. So my goal is to be able to filter this utf-8 characters from spring boot application. Currently when I try to filter data utf-8 characters are read as '?' question marks in database. my initial thought to fix was to achieve following oracle query in my Spring boot application SELECT * FROM entity WHERE text like N'%utf-8-characters%' (this query works correctly on database)

I was unable to find hql code for replacing N string literal

  @Query("SELECT e FROM Entity e WHERE " + "(:filter is null or :filter = '' or "
      + "LOWER(e.text) LIKE CONCAT('%',LOWER(:filter), '%')  "
      + "ORDER BY e.modifiedDate DESC")
  Page<Entity> findAllByOrderByModifiedDateDesc(@Nullable String filter, Pageable pageable);

I also tried native query, it worked if I hardcode filter for example, but could not pair with incoming argument. works but hardcoded -> WHERE text LIKE N'%აა%' invalid -> WHERE text LIKE N:filter

@Query(value = "SELECT * FROM entity WHERE text LIKE CONCAT('%',CONCAT(:filter,'%')) ORDER BY modified_date",nativeQuery = true)
Page<Entity> findAllByOrderByModifiedDateDesc(@Nullable String filter, Pageable pageable);

adding encoding properties wont work because as I said database itself does not reads utf-8 characters

spring.datasource.url=jdbc:oracle:thin:@111.11.1.111:1111:DB?useUnicode=true&characterEncoding=UTF-8
server.servlet.encoding.charset=UTF-8
server.servlet.encoding.enabled=true
server.servlet.encoding.force=true

neither does beans like CharacterEncodingFilter.

Solution

  • First you have to add property to recognized nationalized fields in database:

    spring.jpa.properties.hibernate.use_nationalized_character_data=true

    then all I had to do was not to use oracle functions. I dont know why but

           @Query("SELECT e FROM Entity e WHERE e.text LIKE %:messagePrefix% ")

    this query works correct with utf-8 characters. however when using oracle functions like Concat it did not work @Query("SELECT e FROM Entity e WHERE e.text LIKE CONCAT('%',:text,'%') ") Even for @Query("SELECT e FROM Entity e WHERE Lower(e.text) LIKE %:text% ") it does not work