Spring Data JPA + Pgsql 实现多条件搜索查询
目录
结构： Spring Data JPA + Pgsql +JS
实现方式： 用JpaSpecificationExecutor 接口
目标： web上能根据多个输入条件查询出正确的数据
结构： Spring Data JPA + Pgsql +JS
实现方式： 用JpaSpecificationExecutor 接口
实现代码：
表
TelVerifySample（主表）， Myuser（关联表）， SurveyUser（关联表）
HTML Part
<div class="panel-body">
<form class="form-inline" action="/" method="POST" style="margin-bottom: 0px; justify-content:center">
<div class="form-group col-md-2">
<select class="form-control" id="selectProvince" >
<option value="">--请选择省份--</option>
<option th:each="province:${provinceList}" th:value="${province.id}" th:text="${province.name}"> ${province.id} </option>
</select>
</div>
<div class="form-group col-md-2">
<select class="form-control" id="selectQa" >
<option value="">--请选择核查员--</option>
<option th:each="qa:${qaSurveyList}" th:value="${qa.id}" th:text="${qa.name}"> ${qa.id} </option>
</select>
</div>
<div class="form-group col-md-2">
<select class="form-control" id="selectSurveyer" >
<option value="">--请选择调查员--</option>
<option th:each="surveyer:${surveyList}" th:value="${surveyer.id}" th:text="${surveyer.name}"> ${surveyer.id} </option>
</select>
</div>
<div class="form-check col-md-2">
<select class="form-control" id="selectSampleType" >
<option value="">--请选择核查状态--</option>
<option th:each="telVerifyType:${telVerifyTypes}" th:value="${telVerifyType.id}" th:text="${telVerifyType.name}"> ${telVerifyType.id} </option>
</select>
</div>
<div class="form-group col-md-2">
<div class="input-group date form_date col-sm-12" data-date="" data-date-format="yyyy-mm-dd" data-link-field="dtp_input_nexttime" data-link-format="yyyy-mm-dd">
<input type="text" class="form-control" name="nexttime" id="nexttime" placeholder="核查完成时间"/>
<span class="input-group-addon"><span class="glyphicon glyphicon-remove"></span></span>
</div>
<input type="hidden" id="dtp_input_nexttime" value="" /><br/>
</div>
<div class="form-check col-md-1">
<span class="btn btn-sm btn-primary m-r-5" id="searchsubmit">查找</span>
</div>
</form>
</div>
JS Part
function getSelectTelVerifyItem() {
// Get selected condition
qaTime = document.getElementById("nexttime").value;
var selectProvince = document.getElementById("selectProvince");
var index = selectProvince.selectedIndex; // 选中索引
selectProvinceId = selectProvince.options[index].value; // 选中值
var selectSampleType = document.getElementById("selectSampleType");
var sampleTypeIndex = selectSampleType.selectedIndex; // 选中索引
sampleTypeId = selectSampleType.options[sampleTypeIndex].value; // 选中值
var selectQa= document.getElementById("selectQa");
var selectQaIndex = selectQa.selectedIndex; // 选中索引
selectQaId = selectQa.options[selectQaIndex].value; // 选中值
var selectSurveyer= document.getElementById("selectSurveyer");
var selectSurveyerIndex = selectSurveyer.selectedIndex; // 选中索引
selectSurveyerId = selectSurveyer.options[selectSurveyerIndex].value; // 选中值
if(typeof qaTime == "undefined" || qaTime == null || qaTime == "") {
qaTime = "1970/01/01 00:00";
}
if(typeof selectProvinceId == "undefined" || selectProvinceId == null || selectProvinceId == "") {
selectProvinceId = 0;
}
if(typeof selectQaId == "undefined" || selectQaId == null || selectQaId == "") {
selectQaId = 0;
}
if(typeof selectSurveyerId == "undefined" || selectSurveyerId == null || selectSurveyerId == "") {
selectSurveyerId = 0;
}
if(typeof sampleTypeId == "undefined" || sampleTypeId == null || sampleTypeId == "") {
sampleTypeId = 0;
}
condition = selectProvinceId + "," + sampleTypeId + "," + qaTime + "," + selectQaId + "," + selectSurveyerId;
console.log(" Select condition is " + condition);
}
var qaTime;
var selectProvinceId;
var sampleTypeId;
var selectQaId;
var selectSurveyerId;
$("#searchsubmit").bind("click", function (){
getSelectTelVerifyItem();
$.ajax({
Type: 'GET',
url:"/qasurvey/multiConditionTelVerifySampleSearch?qaTime=" + qaTime + "&selectProvinceId=" + selectProvinceId + "&sampleTypeId=" + sampleTypeId + "&surveyuserId=" + selectSurveyerId+ "&qaUserId=" + selectQaId,
dataType:"json",
}).done(function (result) {
console.log(typeof result);
$('#example').dataTable().fnClearTable();
$('#example').DataTable().rows.add(result.data).draw();
});
});
Dao Part
public interface TelVerifySampleRepository extends JpaRepository<TelVerifySample,Long>,JpaSpecificationExecutor<TelVerifySample> {
}
Service Part
public List<TelVerifySample> findSearch(TelVerifySample model) {
Assert.notNull(model);
List<TelVerifySample> result = telVerifySampleRepository.findAll(new Specification<TelVerifySample>() {
@Override
public Predicate toPredicate(Root<TelVerifySample> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
List<Predicate> list = new ArrayList<>();
if (StringUtils.isNoneBlank(model.getProvince())) {
list.add((Predicate) cb.like(root.get("province").as(String.class), "%" + model.getProvince() + "%"));
}
if (model.getSurveyUser() != null) {
list.add(cb.equal(root.get("surveyuser").as(SurveyUser.class), model.getSurveyUser().getId()));
}
if (model.getTelVerifyUser() != null) {
list.add(cb.equal(root.get("telVerifyUser").as(Myuser.class), model.getTelVerifyUser().getId()));
}
if (model.getSampletype() != null) {
list.add(cb.equal(root.get("telVerifyType").as(TelVerifyType.class), model.getSampletype().getId()));
}
if (model.getAllocatetime() != null) {
list.add(cb.greaterThanOrEqualTo(root.get("allocatetime").as(Date.class), model.getAllocatetime()));
}
Predicate[] p = new Predicate[list.size()];
return cb.and(list.toArray(p));
}
});
return result;
}
JAVA Part
@RequestMapping(value = "/multiConditionTelVerifySampleSearch", method = RequestMethod.GET)
@ResponseBody
public Object multiConditionTelVerifySampleSearch(HttpServletRequest request, Model model) {
int surveyuserId = Utils.getParameter(request, "surveyuserId", -1);
int qaUserId = Utils.getParameter(request, "qaUserId", -1);
int telSampleTypeId = Utils.getParameter(request, "sampleTypeId", -1);
int selectProvinceId = Utils.getParameter(request, "selectProvinceId", -1);
Timestamp dateQaTime = null;
String strQaTime = request.getParameter("qaTime").replace("/", "-") + " 00:00" ;
if (strQaTime.contains("-")) {
dateQaTime = strToSqlDate(strQaTime, "yyyy-MM-dd HH:mm");
}
String province = "";
SurveyUser surveyUser = null;
Myuser qaUser = null;
TelVerifyType telVerifyType = null;
if (selectProvinceId != 0) {
province = sysdictService.findSysdictById(selectProvinceId).getName();
}
if (surveyuserId != 0){
surveyUser = surveyuserService.findMyuserById(surveyuserId);
}
if (qaUserId != 0){
qaUser = myuserService.findMyuserById(qaUserId);
}
if (telSampleTypeId != 0){
telVerifyType = telVerifyTypeService.findTelVerifyTypeById(telSampleTypeId);
}
System.out.println("============ Condition is dateQaTime " + dateQaTime + ", surveyuserId "+ surveyuserId + ", qaUserId "+ qaUserId);
Map<String, Object> map = new HashMap<String, Object>();
TelVerifySample telVerifySample = new TelVerifySample();
telVerifySample.setProvince(province);
telVerifySample.setSurveyUser(surveyUser);
telVerifySample.setTelVerifyUser(qaUser);
telVerifySample.setSampletype(telVerifyType);
telVerifySample.setAllocatetime(dateQaTime);//Need update time
List<TelVerifySample> telVerifySampleList = telVerifySampleService.findSearch(telVerifySample);
List<ArrayList<Object>> result = new ArrayList<>();
telVerifySampleList.forEach(sample -> result.add(sample.getTelVerifySampleSummary()));
map.put("data", result);
map.put("recordsTotal", telVerifySampleList.size());
map.put("recordsFiltered", telVerifySampleList.size());
map.put("draw", refreshTime++);
return map;
}