SpringData JPA--Specification实现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类型集成与支持