UserInfoService

PagingVO retrieveUserinfo(PagingVO page, UserinfoSearchVo searchVo);

@Override
@Transactional
public PagingVO retrieveUserinfo(PagingVO page, UserinfoSearchVo searchVo) {


PagingVO vo = page;
List<Long> provincesIds = new ArrayList<>();
if (ArrayUtils.isNotEmpty(searchVo.getDistrictList())) {
List<Province> provinces = provinceDAO.findProviceByDistIds(searchVo.getDistrictList());
if (CollectionUtils.isNotEmpty(provinces)) {
for (Province province : provinces) {
provincesIds.add(province.getId());
}
} 
}

//在页面上城市和区域没有直接的联系，但在页面上必须根据区域查询对应的的城市，因此必须获得proinceId.

String[] allDepartments = searchVo.getDepartmentList();
List<String> departments = new ArrayList<String>();
List<String> otherDepartments = new ArrayList<String>();
      if (ArrayUtils.isNotEmpty(allDepartments)) {
                 List<String> otherTypeDepartment = userInfoDAO.getOtherUserTypeDepartmentlist();
                 for (String dept : allDepartments) {
                     if (StringUtils.isNotBlank(dept)) {
                            if (otherTypeDepartment.contains(dept)) {
                                      otherDepartments.add(dept);
                             } else {
                                departments.add(dept);
                            }
                    }
            }
}
vo = this.userInfoDAO.getUserInfo(page, searchVo, provincesIds, departments, otherDepartments);
return vo;
}

@Column(name = "district_id")
private Long districtId;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "district_id", referencedColumnName = "district_id", updatable = false, insertable = false)
private District distric;

总结：findProviceByDistIds(searchVo.getDistrictList())

public List<Province> findProviceByDistIds(Long[] distId) {
                DetachedCriteriaBuilder dcb = DetachedCriteriaBuilder.instance(Province.class, "pv");
                DetachedCriteriaBuilder sub = DetachedCriteriaBuilder.instance(DistrictProvince.class, "dp");
sub.addPropertyEq("pv.id", "dp.provinceId");
sub.addIn("dp.districtId", distId);
sub.setProjection(Projections.property("dp.id"));
                dcb.withExists(sub.getDetachedCriteria());
                return select(dcb);
}

/**

select
        this_.id as id1_40_0_,
        this_.en_name as en_name2_40_0_,
        this_.name as name3_40_0_ 
    from
        PROVINCE this_ 
    where
        exists (
            select
                dp_.id as y0_ 
            from
                DISTRICT_PROVINCE dp_ 
            where
                this_.id=dp_.province_id 
                and dp_.district_id in (
                    ?, ?
                )
        )

**/

      /** The Constant PHILIPS_USER_TYPE. */
private static final Long PHILIPS_USER_TYPE = 10L;


/** The Constant USER_TYPE_END. */
private static final Long USER_TYPE_END = 20L;

public List<String> getOtherUserTypeDepartmentlist() {

DetachedCriteriaBuilder builder = DetachedCriteriaBuilder.instance(UserInfo.class);
builder.addGt("userType", PHILIPS_USER_TYPE);
builder.addLe("userType", USER_TYPE_END);
builder.addIsNotNull("nonePhilipsDepartment");
builder.setProjection(Projections.distinct(Projections.property("nonePhilipsDepartment")));
return this.selectE(builder);

}

/**

select
        distinct this_.NONE_PHILIPS_DEPARTMENT as y0_ 
    from
        USER_INFO this_ 
    where
        this_.USER_TYPE>? 
        and this_.USER_TYPE<=? 
        and this_.NONE_PHILIPS_DEPARTMENT is not null

**/