Spring Data JPA原生查询分页排序：实体属性映射问题求助

原生SQL查询不会自动应用JPA的物理命名策略转换排序参数里的实体属性名，所以直接传入createdAt这类驼峰属性名时，会被当作数据库字段名查找，导致找不到p.createdAt（实际数据库列名为created_at）。以下是两种可行的解决方法：

方案1：手动转换排序参数属性名 #

利用已配置的物理命名策略，将Sort中的实体属性名转换为对应的数据库列名后再传入查询：

1. 编写属性转换工具方法 #

注入PhysicalNamingStrategy，实现属性名到列名的转换：

@Service
public class PostService {
    @Autowired
    private PhysicalNamingStrategy physicalNamingStrategy;

    @Autowired
    private PostRepository postRepository;

    public Page<Post> getPostsWithReports(Pageable pageable) {
        Sort convertedSort = convertSortPropertiesToColumns(pageable.getSort());
        Pageable convertedPageable = PageRequest.of(pageable.getPageNumber(), pageable.getPageSize(), convertedSort);
        return postRepository.findPostsWithReports(convertedPageable);
    }

    private Sort convertSortPropertiesToColumns(Sort sort) {
        return sort.stream()
                .map(order -> {
                    Identifier propIdentifier = Identifier.toIdentifier(order.getProperty());
                    Identifier columnIdentifier = physicalNamingStrategy.toPhysicalColumnName(propIdentifier, null);
                    return new Sort.Order(order.getDirection(), columnIdentifier.getText());
                })
                .collect(Sort.toSort());
    }
}

2. 保持原生查询不变 #

仓库中的原生@Query无需修改，直接接收转换后的Pageable：

@Repository
public interface PostRepository extends JpaRepository<Post, Long> {
    @Query(value = "SELECT p.* FROM posts p LEFT JOIN post_reports pr ON p.id = pr.post_id", nativeQuery = true)
    Page<Post> findPostsWithReports(Pageable pageable);
}

方案2：全局AOP自动转换（多查询场景） #

如果多个原生查询都需要支持实体属性名排序，用AOP拦截带Pageable参数的仓库方法，自动完成排序参数转换：

@Aspect
@Component
public class NativeQuerySortConverter {
    @Autowired
    private PhysicalNamingStrategy physicalNamingStrategy;

    @Around("execution(* com.your.project.repository.*.*(..)) && args(pageable,..)")
    public Object convertSortArgs(ProceedingJoinPoint joinPoint, Pageable pageable) throws Throwable {
        if (pageable == null || !pageable.getSort().isSorted()) {
            return joinPoint.proceed();
        }

        Sort convertedSort = convertSortPropertiesToColumns(pageable.getSort());
        Pageable convertedPageable = PageRequest.of(pageable.getPageNumber(), pageable.getPageSize(), convertedSort);
        
        // 替换原参数中的Pageable
        Object[] args = joinPoint.getArgs();
        for (int i = 0; i < args.length; i++) {
            if (args[i] == pageable) {
                args[i] = convertedPageable;
                break;
            }
        }
        return joinPoint.proceed(args);
    }

    private Sort convertSortPropertiesToColumns(Sort sort) {
        return sort.stream()
                .map(order -> {
                    Identifier propIdentifier = Identifier.toIdentifier(order.getProperty());
                    Identifier columnIdentifier = physicalNamingStrategy.toPhysicalColumnName(propIdentifier, null);
                    return new Sort.Order(order.getDirection(), columnIdentifier.getText());
                })
                .collect(Sort.toSort());
    }
}

注意事项 #

• 确保项目中已正确配置物理命名策略（如SpringPhysicalNamingStrategy），该策略会自动处理驼峰转下划线，同时兼容@Column注解自定义的列名
• 转换逻辑会保留原排序方向（ASC/DESC），仅替换排序字段名

内容的提问来源于stack exchange，提问作者Prafulla Kumar Sahu