原文出處:
【一目了然】Spring Data JPA使用Specification動(dòng)態(tài)構(gòu)建多表查詢、復(fù)雜查詢及排序示例
最近的一個(gè)需求是帶條件查詢,可是有的條件不是必填的選項(xiàng),所以后端接收的時(shí)候有的參數(shù)可能為null,該怎么解決呢,網(wǎng)上查詢了相關(guān)做法,我是這么做的:
實(shí)體類(lèi)就用Student為例子,大家可以自行替換成自己的
- 首先給出實(shí)體類(lèi)Student的定義
@Entity
@Table(name = "tblStudent")
@Data
public class TblStudent {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;
@Column(name = "name")
private String name;
@Column(name = "age")
private Integer age;
@Column(name = "sex")
private String sex;
}
- 這里是后端Controller接口定義
@RestController
@Slf4j
@RequestMapping(value = "/student")
public class StudentController {
@GetMapping("/list")
public JsonResult<List<Student>> getStudentList(@RequestParam() Integer _page,
@RequestParam() Integer _limit,
@RequestParam(required = false) String name,
@RequestParam(required = false) Integer age,
@RequestParam(required = false) String sex){
}
}
因?yàn)槭菞l件查詢,他們可能會(huì)從name/age/sex里面選擇一兩個(gè)或者全部查詢所以 我用上了@RequestParam(required=false)
代表該參數(shù)(字段?)不是必須的,如果前臺(tái)提交的請(qǐng)求沒(méi)有這個(gè)參數(shù),默認(rèn)是null.
- 在注入需要的service后就是調(diào)用對(duì)應(yīng)的Service里面的方法
@RestController
@Slf4j
@RequestMapping(value = "/student")
public class StudentController {
@Autowired
private StudentService studentService;
@GetMapping("/list")
public JsonResult<List<Student>> getLogList(@RequestParam() Integer _page,
@RequestParam() Integer _limit,
@RequestParam(required = false) String name,
@RequestParam(required = false) Integer age,
@RequestParam(required = false) String sex){
//這里因?yàn)橐獙?xiě)分頁(yè),通過(guò)前臺(tái)傳來(lái)參數(shù)進(jìn)行操作,如果不用分頁(yè)可以不寫(xiě)
if (0 == _page.intValue() || 1 == _page.intValue()) {
_page = 0;
} else {
_page = page - 1;
}
//這里pageable因?yàn)槲曳猪?yè)用到了,當(dāng)然你們不用的話可以不要
Pageable pageable = PageRequest.of(_page, _limit);
Page<TblStudent> page = studentService.getStudentService(name,age,sex,pageable);
//這樣你就取到了你需要的數(shù)據(jù)了,后面需要做些什么操作就看具體業(yè)務(wù)需要了
}
}
- 下面是StudentService的內(nèi)容
@Service
@Slf4j
public class StudentService {
@Autowired
private StudentRepository studentRepository;//注入StudentRepository
public Page<TblStudent> getStudentList(String name, Integer age, String sex,Pageable pageable) {
Specification<TblStudent> specification = (Specification<TblStudent>) (root, criteriaQuery, cb) -> {
//存放查詢條件
List<Predicate> predicatesList = new ArrayList<>();
//like模糊查詢
if (!StringUtils.isEmpty(name)) {
Predicate namePredicate = cb.like(root.get("name"), '%' + name + '%');
predicatesList.add(namePredicate);
}
//equals精確查詢 Integer類(lèi)型
if (!StringUtils.isEmpty(age)) {
Predicate moduleNmPredicate = cb.gt(root.get("age"), age);
predicatesList.add(agePredicate);
}
//精確查詢String類(lèi)型
if (!StringUtils.isEmpty(sex)) {
Predicate sexPredicate = cb.equal(root.get("sex"), sex);
predicatesList.add(sexPredicate);
}
//between相當(dāng)于SQL的between ... and ...這里是where age between 10 and 20
if (age != null) {
Predicate crtTimePredicate = cb.between(root.get("age"), 10, 20);
predicatesList.add(crtTimePredicate);
}
//排序
criteriaQuery.orderBy(cb.asc(root.get("age")));
//最終將查詢條件拼好然后return
Predicate[] predicates = new Predicate[predicatesList.size()];
return cb.and(predicatesList.toArray(predicates));
}
Page<TblStudent> page = studentRepository.findAll(specification, pageable);
return page;
}
}
- 最后在DAO層的Repository中繼承JpaSpecificationExecutor和JpaRepository
public interface StudentRepository extends JpaRepository<TblStudent, Integer>, JpaSpecificationExecutor<TblStudent> {}
