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.
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