场景: 一张栏目表内(表名:cata),储存有树形结构,用id和pid进行父子级之间的关联。有字段is_show标识是否展示,is_use标识是否启用。
要求:查询的时候,通过传参isUse和isShow来控制是否展示启用和允许展示的栏目。
因为采用的是jpa自带的@OneToMany和@JoinColumn关联到自身,即cata表关联cata表,来查询父子关系,所以,不好变更返回结构,只能继续采用这种方式,记录下自身所踩的坑。
Entity实体层:
Cata
@Getter
@Setter
@Entity
@DynamicInsert
@DynamicUpdate
@Table(name="gk_cata")
@EntityListeners(AuditingEntityListener.class)
@NoArgsConstructor
@AllArgsConstructor
@JsonInclude(JsonInclude.Include.NON_NULL)
@NamedEntityGraph(name = "cata.catas" , attributeNodes = {@NamedAttributeNode(value = "catas")})
@FilterDef(name = "cataIsShowFilter", parameters = { @ParamDef(name = "isShow", type = "integer") })
@FilterDef(name = "cataIsUseFilter", parameters = { @ParamDef(name = "isUse", type = "integer") })
public class Cata {
@Id
private String id;
private Integer isShow;
private Integer isUse;
@OneToMany(targetEntity = Cata.class ,cascade = CascadeType.REFRESH , fetch = FetchType.EAGER)
@JoinColumn(name="pid",referencedColumnName = "id",updatable = false,insertable = false)
@Filter(name = "cataIsShowFilter", condition = " is_show = :isShow")
@Filter(name = "cataIsUseFilter", condition = " is_use = :isUse")
private List<Cata> catas;
}
注,两个filter用于控制是否展示和是否禁用的参数。namedEntityGraph用于控制n+1问题
dao层:
public interface CataDao extends JpaRepository<Cata,String>, JpaSpecificationExecutor<Cata> {
@EntityGraph(value = "cata.catas" , type = EntityGraph.EntityGraphType.FETCH)
Optional<Cata> findWebCataById(String id);
}
注:这里是抄的网上的解决n+1的代码
service层:
@Service
@Transactional
public class CataServiceImpl implements CataService {
@Autowired
private CataDao cataDao;
@PersistenceContext
private EntityManager entityManager;
@Override
@Transactional
public Cata cataByShowAndUse(String id, Map<String, String> map) {
Session session = entityManager.unwrap(Session.class);
if(map.containsKey("isShow")){
Filter cataIsShowFilter = session.enableFilter("cataIsShowFilter");
cataIsShowFilter.setParameter("isShow",new Integer(map.get("isShow")));
}
if(map.containsKey("isUse")){
Filter cataIsUseFilter = session.enableFilter("cataIsUseFilter");
cataIsUseFilter.setParameter("isUse",new Integer(map.get("isUse")));
}
Optional<Cata> cataById = cataDao.findWebCataById(id);
//必须要显式调用一下getCatas(),否则结果为非过滤的结果集
if(cataById.isPresent()){
System.out.println("enable filter is [{}]"+cataById.get().getCatas().size());
System.out.println("enable filter is [{}]"+cataById.get().getCatas().get(0).getCatas().size());
}
session.disableFilter("cataIsShowFilter");
session.disableFilter("cataIsUseFilter");
return cataById.isPresent()?cataById.get():null;
}
}
注:此处,有一个显式的调用,用log输出一下,不然结果更新不过来,这个还没找到原因。
controller层:
@PostMapping("cata/{id}")
public ResultData<Cata> cata(@PathVariable("id") String id,@RequestBody Map<String,String> map) {
Cata cata = cataService.cataByShowAndUse(id ,map);
}
坑:1、如果不显示调用getCatas()方法,则,如果加上session.disable后,就不走过滤了。
2、n+1问题还是没解决。