关于spring-data-jpa:SpringData-JPASpecification实现mysql中json字段查询

1.mysql 中实现 json 字段查问

select * from item where is_remove = 0 and json_extract(item_info,'$.GoodType') ='cat'

2.JPA @Query 实现 mysql 中 json 字段查问

@Query(value = "select * from item where json_extract(item_info,?1) =?2 and item_id in (?3)",nativeQuery = true)
List<Item> selectItems(String featuresKey,String featuresValue,List<Long> itemIds);

List<Item> items = itemRepository.selectItemListByFeatures("$.GoodType","cat",ids);

3.Specification 实现 mysql 中 json 字段查问

items=itemRepository.findAll(new Specification<Item>() {
   @Override
 public Predicate toPredicate(Root<Item> root, CriteriaQuery<?> query, CriteriaBuilder cb) {List<Predicate> list = new ArrayList<>();
 for(Features fea:features){if( fea.getFeaturesAliasName()!=null && fea.getFeaturesValue()!= null){
            list.add(cb.equal(
                  cb.function(
                        "JSON_EXTRACT",
                        String.class,
                        root.get("itemInfo"),
                        cb.literal("$."+fea.getFeaturesAliasName())
                  ), fea.getFeaturesValue())
            );
 }
      }
      list.add(cb.in(root.get("itemId")).value(itemIds));
 Predicate[] p = new Predicate[list.size()];
 query.where(cb.and(list.toArray(p)));
 return query.getGroupRestriction();}
});

4. 备注类

Item{
  "itemId":"1",
  "itemInfo":{"GoodId":"1002","GoodType":"cat","GoodName":"catyeall"}
}

Features{
"featuresName":"GoodType",
"featuresValue":"cat"
}

5. 附录

深入浅出学 Spring Data JPA
Spring Data JPA 与 PostgreSQL 的 jsonb 类型集成与反对