criteria mysql filed_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 selectItems( String featuresKey,String featuresValue,List itemIds);

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

3.Specification实现mysql中json字段查询items=itemRepository.findAll(new Specification() {

@Override

public Predicate toPredicate(Root root, CriteriaQuery> query, CriteriaBuilder cb) {

List 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.附录