Spring Boot实现PostgreSQL JSONB过滤分页API开发问题咨询

你之前写的@Query查询不生效是三个核心问题导致的：

• JPQL不支持PostgreSQL独有的->>JSONB操作符，直接写在普通JPQL里Hibernate会直接报语法错误
• JSONB取值的键名必须用单引号包裹，你写的->>firstName不符合原生SQL语法，正确写法是->>'firstName'
• 没有开启原生SQL查询，也没有配置分页需要的总数查询、分页参数，自然无法返回分页结果

1. Repository层查询写法 #

因为用到PostgreSQL专属JSONB语法，直接走原生SQL即可，同时接入Spring Data自带的分页能力，不需要自己写分页逻辑：

public interface QuoteRepository extends JpaRepository<Quote, Long> {
    @Query(
        value = "SELECT * FROM quote " +
                "WHERE column1 LIKE CONCAT('%', :keyword, '%') " +
                "OR customer_details->>'firstName' LIKE CONCAT('%', :keyword, '%') " +
                "OR customer_details->>'email' LIKE CONCAT('%', :keyword, '%') " +
                "OR column2 LIKE CONCAT('%', :keyword, '%')",
        countQuery = "SELECT count(*) FROM quote " +
                "WHERE column1 LIKE CONCAT('%', :keyword, '%') " +
                "OR customer_details->>'firstName' LIKE CONCAT('%', :keyword, '%') " +
                "OR customer_details->>'email' LIKE CONCAT('%', :keyword, '%') " +
                "OR column2 LIKE CONCAT('%', :keyword, '%')",
        nativeQuery = true
    )
    Page<Quote> searchByKeyword(@Param("keyword") String keyword, Pageable pageable);
}

查询说明：

• 必须单独写countQuery，原生SQL分页需要靠这个查询计算总条数、总页数
• 用CONCAT拼接模糊查询参数，比直接写%?1%参数绑定兼容性更强，不会出现特殊字符转义问题
• 要扩展其他JSONB字段过滤时，直接在WHERE条件后追加customer_details->>'对应键名' LIKE CONCAT('%', :keyword, '%')即可，不需要调整整体逻辑

2. Controller层接口实现 #

按照你定义的路径规则写接口即可，固定每页返回10条：

@RestController
public class SearchController {
    @Resource
    private QuoteRepository quoteRepository;

    @GetMapping("/search/{keyword}/pageNo/{pageNo}")
    public Page<Quote> search(
            @PathVariable String keyword,
            @PathVariable Integer pageNo
    ) {
        // Spring Data分页页码从0开始计数，前端传的页码从1开始，这里做偏移
        Pageable pageable = PageRequest.of(pageNo - 1, 10);
        return quoteRepository.searchByKeyword(keyword, pageable);
    }
}

返回结果自带分页所需的全部字段：

• content：当前页匹配的10条数据
• totalElements：匹配关键词的总记录数
• totalPages：总页数
• number：当前页码（从0开始计数）
• size：每页固定条数10

• 实体类里的JSONB字段需要加类型绑定，否则读写会报错：

  // Hibernate5及以下版本，需要引入hibernate-types依赖
  @Column(name = "customer_details", columnDefinition = "jsonb")
  @Type(type = "com.vladmihalcea.hibernate.type.json.JsonBinaryType")
  private Map<String, Object> customerDetails;
  
  // Hibernate6+版本不需要第三方依赖，直接用内置注解即可
  // @Column(name = "customer_details", columnDefinition = "jsonb")
  // @JdbcTypeCode(SqlTypes.JSON)
  // private Map<String, Object> customerDetails;
  

• JSONB键名大小写敏感，写查询时要和你实际存入的键名完全一致
• 不需要排序就不用给PageRequest传Sort参数，查询会按照数据库默认存储顺序返回结果

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