关于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类型集成与反对