如何根据postgres的jsonb列的where条件(无原生查询)，使用jpa选择spring boot中的数据？

Question

我有Postgres数据库与jsonb字段。我在jpa中使用了带有criteria builder的where条件的谓词列表。现在，我想获取存储在数据库中的基于多个where cause的JSON数据以及JSON。怎么可能做到呢？

Database table

private List<Predicate> whereClause(CriteriaBuilder criteriaBuilder, Root<KafkaLog> kafkaLog,
  KafkaLogSearchDto search) {
List<Predicate> predicates = new ArrayList<>();
if (search.getTopic() != null && !search.getTopic().isEmpty()) {
  Expression<String> literal =
      criteriaBuilder.literal("%" + search.getTopic().toUpperCase() + "%");
  predicates.add(criteriaBuilder.like(criteriaBuilder.upper(kafkaLog.get("topic")), literal));
}
if (search.getOffset() != null) {
  predicates.add(criteriaBuilder.equal(kafkaLog.get("offset"), search.getOffset()));
}
if (search.getResourceId() != null) {
  predicates.add(criteriaBuilder.equal(kafkaLog.get("invoiceId"), search.getResourceId()));
}
if (search.getPartition() != null) {
  predicates.add(criteriaBuilder.equal(kafkaLog.get("partition"), search.getPartition()));
}
if (search.getStatus() != null) {
  predicates.add(criteriaBuilder.equal(kafkaLog.get("status"), search.getStatus()));
}
if (search.getCreatedAtFrom() != null && search.getCreatedAtTo() != null) {
  predicates.add(criteriaBuilder.and(
      criteriaBuilder.greaterThanOrEqualTo(kafkaLog.get("createdAt").as(Date.class),
          search.getCreatedAtFrom()),
      criteriaBuilder.lessThanOrEqualTo(kafkaLog.get("createdAt").as(Date.class),
          search.getCreatedAtTo())));
}
if (search.getPayload() != null && !search.getPayload().isEmpty()) {
  Expression<String> literal =
      criteriaBuilder.literal("%" + search.getPayload().toUpperCase() + "%"); 



}
return predicates;

}

private CriteriaQuery<KafkaLog> getKafkaBySearchCriteria(KafkaLogSearchDto search, String orderBy,
  int sortingDirection) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<KafkaLog> criteriaQuery = criteriaBuilder.createQuery(KafkaLog.class);
Root<KafkaLog> kafkaLog = criteriaQuery.from(KafkaLog.class);

List<Predicate> predicates = whereClause(criteriaBuilder, kafkaLog, search);
criteriaQuery.where(predicates.toArray(new Predicate[] {}));

if (orderBy == null || orderBy.isEmpty()) {
  orderBy = "topic";
}
Expression<?> orderColumn = kafkaLog.get(orderBy);
if (orderColumn != null) {
  if (sortingDirection == 0) {
    criteriaQuery.orderBy(criteriaBuilder.asc(orderColumn));
  } else if (sortingDirection == 1) {
    criteriaQuery.orderBy(criteriaBuilder.desc(orderColumn));
  }
}
return criteriaQuery;

}

Answer 1

显然，直到JDBC/数据库级别的自定义数据类型支持都不是JPA本身的一部分。对于读取JSON，您可以使用实体映射到String (如果JPA实现允许这样做的话)。

但是，您有一些替代方法：

如果您需要继续使用JPA，您可以实现一个转换器来为您处理JSON (可以是本文中的泛型，也可以是更具体的--视您的喜好而定)：Using JPA with PostgreSQL JSON

• If您可以抛弃JPA，直接使用JDBC/SQL完成它，这样就可以充分发挥PostgreSQL的潜力：。

当读取JSONB字段时，您可以使用显式类型转换让数据库执行到字符串的转换(因为JDBC中还没有直接的JSON支持)，例如：

SELECT payload::text FROM my_table WHERE ...

...or对JSON字段本身进行一些特殊的过滤，或者只返回JSON数据的一部分，即aso。

Answer 2

这是使用like子句搜索jsonb的示例谓词：

predicate.getExpressions().add(cb.and(cb.like(cb.function("jsonb_extract_path_text"，String.class，root.get("tags")，cb.literal(this.key))，"%"+ this.value + "%“))；