//需要的查询条件为 a and (b or c or d) 可以转换为 (a and b) or (a and c) or (a and d)//第一种方式 使用andEqualTo拼接条件
privateExample madeExample(R request) {
Example example= new Example(C.class);
example.setOrderByClause("createddat desc");
Example.Criteria c=example.createCriteria();
Example.Criteria c1=example.createCriteria();
Example.Criteria c2=example.createCriteria();
c.andEqualTo("activityType", (byte) 1);
c1.andEqualTo("activityType", (byte) 1);
c2.andEqualTo("activityType", (byte) 1);
c.andEqualTo("deleteState", (byte) 1);
c1.andEqualTo("deleteState", (byte) 1);
c2.andEqualTo("deleteState", (byte) 1);if(StringUtils.isNotBlank(request.getCompanyId())) {
c.andEqualTo("companyId", request.getCompanyId());
c1.andEqualTo("companyId", request.getCompanyId());
c2.andEqualTo("companyId", request.getCompanyId());
}if(StringUtils.isNotBlank(request.getActivityState())) {
c.andEqualTo("activityState", request.getActivityState());
c1.andEqualTo("activityState", request.getActivityState());
c2.andEqualTo("activityState", request.getActivityState());
}if(StringUtils.isNotBlank(request.getCreatedby())) {
c.andEqualTo("createdby", request.getCreatedby());
c1.andEqualTo("createdby", request.getCreatedby());
c2.andEqualTo("createdby", request.getCreatedby());
}if (StringUtils.isNotBlank(request.getCreateddatStart()) &&StringUtils.isNotBlank(request.getCreateddatEnd())) {
c.andGreaterThanOrEqualTo("createddat", request.getCreateddatStart());
c1.andGreaterThanOrEqualTo("createddat", request.getCreateddatStart());
c2.andGreaterThanOrEqualTo("createddat", request.getCreateddatStart());
String endDate=request.getCreateddatEnd();if (!endDate.contains(" ")) {
endDate+= " 23:59:59";
}
c.andLessThanOrEqualTo("createddat", endDate);
c1.andLessThanOrEqualTo("createddat", endDate);
c2.andLessThanOrEqualTo("createddat", endDate);
}if(StringUtils.isNotBlank(request.getMerchandise())) {
c.andLike("activityRule", "%" + request.getMerchandise() + "%");
c1.andLike("activityRule", "%" + request.getMerchandise() + "%");
c2.andLike("activityRule", "%" + request.getMerchandise() + "%");
}//校验有效期
if (StringUtils.isNotBlank(request.getValidStart()) &&StringUtils.isNotBlank(request.getValidEnd())) {
String endDate=request.getValidEnd();if (!endDate.contains(" ")) {
endDate+= " 23:59:59";
}
c.andBetween("validStart", request.getValidStart(),endDate);
c1.andBetween("validEnd",request.getValidStart(),endDate);
c2.andCondition("valid_start <=",request.getValidStart());
c2.andCondition("valid_end >=",endDate);
example.or(c1);
example.or(c2);
}returnexample;
}//第二种方式 使用andCondition拼接sql
privateExample madeExample1(ZnyOilActivityConsumeRequest request) {
Example example= new Example(ZnyOilActivityMerch.class);
example.setOrderByClause("createddat desc");
Example.Criteria c=example.createCriteria();
c.andEqualTo("activityType", (byte) 1);
c.andEqualTo("deleteState", (byte) 1);if(StringUtils.isNotBlank(request.getEnterpriseId())) {
c.andCondition("company_id = '"+request.getEnterpriseId()+"'");
}if(StringUtils.isNotBlank(request.getActivityState())) {
c.andCondition("activity_state = "+request.getActivityState());
}if(StringUtils.isNotBlank(request.getCreatedby())) {
c.andCondition("createdby = '"+request.getCreatedby()+"'");
}if (StringUtils.isNotBlank(request.getCreateddatStart()) &&StringUtils.isNotBlank(request.getCreateddatEnd())) {
String createddatStart=request.getCreateddatStart();
String createddatEnd=request.getCreateddatEnd();if (!createddatEnd.contains(" ")) {
createddatEnd+= " 23:59:59";
}
c.andCondition("createddat between '"+createddatStart+"' and '"+createddatEnd+"'");
}if(StringUtils.isNotBlank(request.getMerchandise())) {
c.andCondition("activity_rule like '%" + request.getMerchandise() + "%'");
}//校验有效期逻辑//1.活动有效期限起在查询日期起止范围之内//2.活动有效期限止在查询日期起止范围之内//3.活动有效期限起止在查询日期起止范围之内符合1,2情况//4.查询开始日期起止包含在活动有效期限起止之内
if (StringUtils.isNotBlank(request.getValidStart()) &&StringUtils.isNotBlank(request.getValidEnd())) {
String validStart=request.getValidStart();
String validEnd=request.getValidEnd();if (!validEnd.contains(" ")) {
validEnd+= " 23:59:59";
}
c.andCondition("((valid_start between '"+validStart+"' and '"+validEnd+"') or (valid_end between '"+validStart+"' and '"+validEnd+"') or (valid_start <= '"+validStart+"' and valid_end >= '"+validEnd+"'))");
}returnexample;
}//第三种方式 可以直接在CustomMapper文件里面用Mybatis方法写查询sql