查询方法
VO实体类(Student)
@Data
@EqualsAndHashCode(callSuper = false)
public class StudentRequest extends BasePageRequest {
private String code; // 学生编号
private String name; // 学生名称
/** 学生名称集合 */
private List<String> nameList;
/**
* 起始时间 startTime
* 结束时间 endTime
* 主要用于接收日期选择器el-date-picker的数据
*/
private String startTime;
private String endTime;
private Integer state; // 入学状态
}
PO实体类(Student)
@Data
@EqualsAndHashCode(callSuper = false)
@Entity
@Table(name = "STUDENT")
public class Student extends BasePageRequest {
private String code; // 学生编号
private String name; // 学生名称
private Date enrollmentTime; // 入学时间
private Integer state; // 入学状态
}
1.Specification用法
某个属性的查询
@Override
public List<Student> selectPage(StudentRequest request) {
Specification<Student> query = new Specification<Student>() {
private static final long serialVersionUID = 1L;
@Override
public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query,
CriteriaBuilder criteriaBuilder) {
List<Predicate> list = new ArrayList<Predicate>();
// 判断学生名称是否为空
if (StringUtils.isNotBlank(request.getName())) {
// 模糊查询条件--学生名称
list.add(criteriaBuilder.like(root.get("name"),
"%" + request.getName().trim() + "%"));
}
return criteriaBuilder.and(list.toArray(new Predicate[list.size()]));
}
};
List<Student> studentList = studentRepo.findAll(query);
return studentList;
}
时间段查询
@Override
public List<Student> selectPage(StudentRequest request) {
Specification<Student> query = new Specification<Student>() {
private static final long serialVersionUID = 1L;
@Override
public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query,
CriteriaBuilder criteriaBuilder) {
List<Predicate> list = new ArrayList<Predicate>();
/**
* 查询在某个时间段入学的学生
*/
if (request.getStartTime() != null) {
SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
/**
* 当PO，时间的类型为
* Date
* String--"2021-12-24 15:20"，日期字符串
* long--1640330400000,时间戳
* 都可以这么用
*/
list.add(criteriaBuilder.greaterThanOrEqualTo(root.get("enrollmentTime"),
ft.parse(request.getStartTime())));
list.add(criteriaBuilder.lessThanOrEqualTo(root.get("enrollmentTime"),
ft.parse(request.getEndTime())));
/**
适用于String，long，无需将时间转为Date类型
list.add(criteriaBuilder.greaterThanOrEqualTo(root.get("enrollmentTime"),
request.getStartTime()));
list.add(criteriaBuilder.lessThanOrEqualTo(root.get("enrollmentTime"),
request.getEndTime()));
*/
} catch (ParseException e) {
e.printStackTrace();
}
}
return criteriaBuilder.and(list.toArray(new Predicate[list.size()]));
}
};
List<Student> studentList = studentRepo.findAll(query);
return studentList;
}
or 查询
- 两个条件
@Override
public List<Student> selectPage(StudentRequest request) {
Specification<Student> query = new Specification<Student>() {
private static final long serialVersionUID = 1L;
@Override
public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query,
CriteriaBuilder criteriaBuilder) {
List<Predicate> list = new ArrayList<Predicate>();
/**
* or 查询，两个条件
* 查询某一状态，或状态为空的学生数据
*/
if (request.getStartTime() != null) {
SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
Predicate pred1 = criteriaBuilder.equal(root.get("state"), request.getState);
Predicate pred2 = criteriaBuilder.isNull(root.get("state"));
list.add(criteriaBuilder.or(pred1, pred2));
} catch (ParseException e) {
e.printStackTrace();
}
}
return criteriaBuilder.and(list.toArray(new Predicate[list.size()]));
}
};
List<Student> studentList = studentRepo.findAll(query);
return studentList;
}
- 多个条件
@Override
public List<Student> selectPage(StudentRequest request) {
Specification<Student> query = new Specification<Student>() {
private static final long serialVersionUID = 1L;
@Override
public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query,
CriteriaBuilder criteriaBuilder) {
List<Predicate> list = new ArrayList<Predicate>();
if (CollectionUtils.isNotEmpty(request.getNameList())) {
// 定义两个参数
List<Predicate> listOr = new ArrayList<Predicate>();
Predicate[] arrayOr = new Predicate[request.getNameList().size()];
for (String name: request.getNameList()) {
listOr.add(cb.like(root.get("name"),"%" + keyword.trim() + "%"));
}
listOr.toArray(arrayOr);
list.add(cb.or(arrayOr));
}
return criteriaBuilder.and(list.toArray(new Predicate[list.size()]));
}
};
List<Student> studentList = studentRepo.findAll(query);
return studentList;
}
批量查询
@Override
public List<Student> selectPage(StudentRequest request) {
Specification<Student> query = new Specification<Student>() {
private static final long serialVersionUID = 1L;
@Override
public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query,
CriteriaBuilder criteriaBuilder) {
List<Predicate> list = new ArrayList<Predicate>();
/**
* or 批量查询，多个条件
* 根据学生名称，批量查询学生信息
*/
List<String> nameList = request.getNameList();
List predicateList = new ArrayList();
Predicate [] p = new Predicate[nameList.size()];
if (!CollectionUtils.isEmpty(nameList )) {
for (String name : nameList ) {
predicateList.add(criteriaBuilder.like(root.get("name"), "%" + name.trim() + "%"));
}
predicateList.toArray(p);
list.add(criteriaBuilder.or(p));
}
return criteriaBuilder.and(list.toArray(new Predicate[list.size()]));
}
};
List<Student> studentList = studentRepo.findAll(query);
return studentList;
}
特殊情况（时间范围存在其一为空的情况）
（出，入时间类型为字符串）当存在某一字段为空的情况，且需要使用时间控件查询某一范围的信息时，可以这样写
if (StringUtils.isNotBlank(request.getStartTime())) { // 出入场时间
/**
* 入场时间在起始时间与结束时间之间，或为空
* 出场时间在起始时间与结束时间之间，或为空
*/
Predicate inTimePred1 = criteriaBuilder.greaterThanOrEqualTo(root.get("inTime"), request.getStartTime());
Predicate inTimePred2 = criteriaBuilder.lessThanOrEqualTo(root.get("inTime"), request.getEndTime());
Predicate inTimePred3 = criteriaBuilder.isNull(root.get("inTime"));
Predicate outTimePred1 = criteriaBuilder.greaterThanOrEqualTo(root.get("outTime"), request.getStartTime());
Predicate outTimePred2 = criteriaBuilder.lessThanOrEqualTo(root.get("outTime"), request.getEndTime());
Predicate outTimePred3 = criteriaBuilder.isNull(root.get("outTime"));
// criteriaBuilder.and(inTimePred1, inTimePred2); // 入场时间在起始时间与结束时间之间
// criteriaBuilder.or(inTimePred3,criteriaBuilder.and(inTimePred1, inTimePred2)); // 入场时间在起始时间与结束时间之间，或为空
// criteriaBuilder.and(outTimePred1,outTimePred2); // 出场时间在起始时间与结束时间之间
// criteriaBuilder.or(outTimePred3,criteriaBuilder.and(outTimePred1,outTimePred2)); // 出场时间在起始时间与结束时间之间，或为空
list.add(criteriaBuilder.and(criteriaBuilder.or(inTimePred3,criteriaBuilder.and(inTimePred1, inTimePred2)),criteriaBuilder.or(outTimePred3,criteriaBuilder.and(outTimePred1,outTimePred2))));
}
}
2.编写原生SQL
查询
@Query
public List<Student > findByName(String name);
删除，修改(写法一致)
/**
* @Modifying 这个注解是通知jpa，这是一个update或者delete操作，在更新或者删除操作时，
* 此注解必须加，否则会抛异常
*
* nativeQuery 本地查询，就是使用原生的sql语句（根据数据库的不同，
* 在sql的语法或结构方面可能有所区别）进行查询数据库的操作。
* 注：必须加事务，否则也会报错
*/
@Modifying
@Query(nativeQuery = true,value = "DELETE FROM Student WHERE NAME = ?1")
Integer deleteStu(String name);
@Transactional
@Modifying()
@Query(value = " UPDATE op_student SET status = 'YES' WHERE id = ?1", nativeQuery = true)
void update(String id);
@Transactional
@Modifying
@Query(value = "update Student set status = 'NO' where id = ?1 and name = ?2")
int update(String id, String name);
op_student是数据库的表名
Student是表映射的实体类类名
总结修改操作需要注意的点：
1、要同时加上@Transactional 、@Modifying 、 @Query 这三个注解。
2、用数据库表名映射要加上nativeQuery = true
3、手写的sql可以直接使用数据库映射的java对象，但不需要加nativeQuery = true
时间插叙：查询某年，某月，某周
@Query(nativeQuery = true, value = "SELECT * FROM op_student stu WHERE YEAR(stu.birthday) = ?1 ")
List<Student> findStudentsByYear(String year);
@Query(nativeQuery = true, value = "SELECT * FROM op_student stu WHERE YEAR(stu.birthday) = ?1 and MONTH(stu.birthday) = ?2")
List<Student> findStudentsByMonth(String year, String month);
@Query(nativeQuery = true, value = "SELECT * FROM op_student stu WHERE stu.birthday between ?1 and ?2")
List<Student> findStudentsByWeek(String startTime, String endTime);
批量查询
@Query(nativeQuery = true, value = "SELECT * FROM op_student WHERE id in (:idList)")
List<Student> findDeviceList(@Param("idList") List<String> idList);
560
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
