hibernate4中 DetachedCriteriaBuilder的一般用法 （保持更新）

一  常用的CriteriaBuilder

       (1) query.addLikeAny("cc.courseCode", ccVO.getCourseCode());      相当于 模糊查询like

       (2)query.addBetween("cc.activeDate", ccVO.getActiveBegin(), ccVO.getActiveEnd());   相当between...and... 

       (3) query.addEq("cc.courseType", ccVO.getCourseType());  相当于==

       (4)  if (ArrayUtils.isNotEmpty(ccVO.getCourseCountry())) {  相当于子查询

                   DetachedCriteriaBuilder sub = DetachedCriteriaBuilder.instance(CourseCountry.class, "cct");
                   sub.addIn("cct.countryCode", ccVO.getCourseCountry()).addPropertyEq("cc.id", "cct.courseCatalogId");
                   sub.setProjection(Projections.property("cct.courseCatalogId"));

                   query.withExists(sub.getDetachedCriteria());

          }

        /*public static enum TrainingApplyStatus {
Applied(0), Approved(10), Confirmed(20), Cancelled(30), Waiting(40), NotPassed(50), Passed(60),                           NoShow(70), Rejected(80), Exempted(90), ManagerExempted(100), DummyAssigned(111);

private Integer status;

TrainingApplyStatus(Integer status) {
this.status = status;
}


public Integer getStatus() {
return status;
}
}*/

       (5)Integer[] defaultStatus = { TrainingApplyStatus.Applied.getStatus(), 

                                                   TrainingApplyStatus.Approved.getStatus(),

                                                    TrainingApplyStatus.Rejected.getStatus() };  //这是枚举类型
                                                     query.addIn("ta.status", defaultStatus);

        (6)查询时间

         

         /**

        and va1_.CRT_TIME<? 
        and va1_.CRT_TIME>=?

        **/

         if (visitorInfoVo.getDate() != null) {
                query.addLt("va.createDate",PssDateWrapper.instance(visitorInfoVo.getDate()).toNextDay()

                                    .toOneDayBegin().toDate());  //<
                query.addGe("va.createDate",PssDateWrapper.instance(visitorInfoVo.getDate()) .toOneDayBegin().

                                    toDate());                                 //>
           }

        (7)同时与多个相匹配 （同时与多个相匹配，用逗号隔开，字符串形式）

           String partNumber = desvo.getPartNumber();
if (StringUtils.isNotEmpty(partNumber)) {
if (partNumber.contains(",")) {
String[] paraNumbers = partNumber.split(",");
Disjunction dis = Restrictions.disjunction();
for (int i = 0; i < paraNumbers.length; i++) {
if (StringUtils.isNotBlank(paraNumbers[i])) {
dis.add(Property.forName("partNumber").like(paraNumbers[i].trim(),  

                                                                                   MatchMode.ANYWHERE));
}
}
query.getDetachedCriteria().add(dis);
} else {
query.addLikeAny("partNumber", partNumber);
}
}

        (8)与一个复选框或者多个复选框中的值相匹配

            String[] modalityList=searchVo.getModalityList();
if(ArrayUtils.isNotEmpty(modalityList)){
ConditionExp[] exps=new ConditionExp[modalityList.length];
for(int i=0; i<modalityList.length;i++){
 exps[i] = Like.as("u.modality", modalityList[i]);
}
query.addOr(exps);
}

            (9)假如一个下拉列表中有两个map(这个很重要)

           private DetachedCriteriaBuilder initQueryCriteria(
UserinfoSearchVo searchVo, List<Long> provinceId,
List<String> departments, List<String> otherDepartments) {

                          ..........

                          ..........

                                query.addOr(In.as("u.department", departments),In.as("u.nonePhilipsDepartment", otherDepartments));

           }

/**where
        (
            this_.DEPARTMENT in (
                ?
            ) 
            or this_.NONE_PHILIPS_DEPARTMENT in (
                ?
            )
        ) 

**/

         (10)子查询

               if (ArrayUtils.isNotEmpty(searchVo.getGroupList())) {

DetachedCriteriaBuilder subBuilder = DetachedCriteriaBuilder.instance(UserGroup.class, "ug");
subBuilder.addPropertyEq("u.id", "ug.userId").setProjection(Projections.distinct(Projections.property("ug.userId")));
subBuilder.addIn("ug.groupId", searchVo.getGroupList());
query.withExists(subBuilder.getDetachedCriteria());

}

        （11）左联接

        @ManyToOne(fetch = FetchType.EAGER)
       @JoinColumn(name = "CITY_ID", updatable = false, insertable = false)
private City city;

  

         query.leftJoin("city", "cy");
         query.addLikeAny("cy.cityName", searchVo.getCity());

         query.addIn("cy.provinceId", provincesId);  （与多个中的某一个相匹配）

          （12）条件查询

//query.addLikeAny("de.location", deviceVo.getLocation());
		59	//modify for #2864 by forever 20150604
		60	if(StringUtils.isNotEmpty(deviceVo.getLocation().trim())){
		61	query.addOr(Like.as("de.location", deviceVo.getLocation()),Like.as("ct.cityName", deviceVo.getLocation()));
		62	}

                   （13）

       public List<SystemData> getAllByType(String[] type){
DetachedCriteriaBuilder query=DetachedCriteriaBuilder.instance(SystemData.class);
query.addIn("type", type);
return this.select(query);
}

（14）查询某个字段的最大值：

public long generateIndex() {
  DetachedCriteriaBuilder query = DetachedCriteriaBuilder.instance(Route.class, "r");
  query.setProjection(Projections.max("r.index"));
  return this.selectTopE(query);
 }

sql:   select
        max(this_.`INDEX`) as y0_
    from
        realroutes this_ limit ?

（15）

public Routes generateIndex1() {
  DetachedCriteriaBuilder query = DetachedCriteriaBuilder.instance(Routes.class);
  query.setProjection(Projections.max("index"));
  query.setBeanTran(Routes.class);
  return this.selectTopOne(query);
 }