Spring Boot查询Oracle含UTF-8字符数据的编码问题求助

核心问题 #

Oracle数据库本身不支持UTF-8编码，但部分表列通过N'...'字面量插入了Unicode字符，Spring Boot查询时这些字符被识别为?，无法正常过滤。

可行解决方案 #

方案1：调整JDBC连接参数（最简便）

Oracle JDBC驱动有专门的Unicode参数，替换你原有的连接配置：

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

• oracle.jdbc.defaultNChar=true：让JDBC默认以NCHAR类型发送字符串参数，等效于SQL中的N前缀
• oracle.jdbc.convertNcharStrings=true：自动将字符串转换为Unicode编码

修改后原生查询无需额外处理，直接使用参数绑定即可：

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

方案2：HQL中使用Oracle函数转换

在HQL里通过TO_NCHAR函数将参数转换为Unicode类型，匹配存储的N'...'字符：

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

方案3：手动指定参数类型（原生查询兜底）

如果上述方案无效，可通过EntityManager手动指定参数为NVARCHAR类型：

@Repository
public class EntityRepositoryImpl implements EntityCustomRepository {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Page<Entity> findAllWithUnicodeFilter(String filter, Pageable pageable) {
        if (filter == null || filter.isEmpty()) {
            // 无过滤条件时的查询逻辑
            TypedQuery<Entity> query = entityManager.createQuery(
                "SELECT e FROM Entity e ORDER BY e.modifiedDate DESC", Entity.class);
            query.setFirstResult((int) pageable.getOffset());
            query.setMaxResults(pageable.getPageSize());
            List<Entity> content = query.getResultList();
            long total = entityManager.createQuery(
                "SELECT COUNT(e) FROM Entity e", Long.class).getSingleResult();
            return new PageImpl<>(content, pageable, total);
        }

        // 带Unicode过滤的原生查询
        String sql = "SELECT * FROM entity WHERE text LIKE CONCAT('%', :filter, '%') ORDER BY modified_date";
        Query nativeQuery = entityManager.createNativeQuery(sql, Entity.class);
        nativeQuery.setParameter("filter", filter, Types.NVARCHAR);
        nativeQuery.setFirstResult((int) pageable.getOffset());
        nativeQuery.setMaxResults(pageable.getPageSize());
        List<Entity> content = nativeQuery.getResultList();

        // 统计总数
        String countSql = "SELECT COUNT(*) FROM entity WHERE text LIKE CONCAT('%', :filter, '%')";
        Query countQuery = entityManager.createNativeQuery(countSql);
        countQuery.setParameter("filter", filter, Types.NVARCHAR);
        long total = ((Number) countQuery.getSingleResult()).longValue();

        return new PageImpl<>(content, pageable, total);
    }
}

需先定义自定义接口EntityCustomRepository，让你的JpaRepository继承该接口。

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