往期文章链接
前文提要:[Spring data jpa] 使用EntityManager 原生sql使用(详细)
公司项目使用的数据源框架为Spring Data JPA,在实现复杂功能时,我选择了用原生SQL语句来实现.最近有客户要求项目能够支持SQL Server数据库,需要把项目中的所有的原生SQL语句改为QueryDSL,复杂的SQL用QueryDSL来实现相当麻烦,下面记录一下转换的过程.
之前使用原生SQL语句的实现代码(细节请看往期文章链接): @PersistenceContext
private EntityManager entityManager;
@Override
@SuppressWarnings("unchecked")
public List pageAttendanceStatisticse(Integer siteId, Date stateTime, Date endTime, String statue, String personName, Pageable pageable, Integer groupId) {
StringBuilder dataSql = new StringBuilder(
"select a.id as attendanceId," +
"p.person_no as personNo," +
"p.name as personName," +
"eg.name as groupName," +
"eg.id as groupId,"
+ "COUNT(CASE when floor(status/10) = 1 then 1 else NULL end) as absences,"
+ "COUNT(CASE when floor(status/10) = 2 then 1 else NULL end) as lateNumber,"
+ "COUNT(CASE when floor(status/10) = 3 then 1 else NULL end) as leaveEarly,"
+ "COUNT(CASE when floor(status/10) = 4 then 1 else NULL end) as normalDays,"
+ "COUNT(CASE when status_b = 1 then 1 else NULL end) as bAbsences,"
+ "COUNT(CASE when status_b = 2 then 1 else NULL end) as bLateNumber,"
+ "COUNT(CASE when status_b = 3 then 1 else NULL end) as bLeaveEarly,"
+ "COUNT(CASE when status_b = 4 then 1 else NULL end) as bNormalDays,"
+ "COUNT(CASE when status_c = 1 then 1 else NULL end) as cAbsences,"
+ "COUNT(CASE when status_c = 2 then 1 else NULL end) as cLateNumber,"
+ "COUNT(CASE when status_c = 3 then 1 else NULL end) as cLeaveEarly,"
+ "COUNT(CASE when status_c = 4 then 1 else NULL end) as cNormalDays,"
+ "COUNT(CASE when status_d = 1 then 1 else NULL end) as dAbsences,"
+ "COUNT(CASE when status_d = 2 then 1 else NULL end) as dLateNumber,"
+ "COUNT(CASE when status_d = 3 then 1 else NULL end) as dLeaveEarly,"
+ "COUNT(CASE when status_d = 4 then 1 else NULL end) as dNormalDays,"
+ "COUNT(CASE when status%10 = 5 then 1 else NULL end) as workOvertime,"
+ "COUNT(CASE when status%10 = 6 then 1 else NULL end) as festivalOvertime,"
+ "COUNT(CASE when status%10 = 7 then 1 else NULL end) as holidayOvertime "
+ "from tdx_attendance_record a left join tdx_employee e on a.employee_id = e.id left join tdx_person p on e.person_id = p.id left join tdx_employee_group eg on e.group_id = eg.id");
//拼接where条件
StringBuilder whereSql = new StringBuilder(" WHERe e.attendance_rule_id is not null and a.deleted_flag = 0");
if (null != siteId) {
whereSql.append(" and a.site_id = :siteId");
}
if (null != stateTime && null != endTime) {
whereSql.append(" and a.attendance_date BETWEEN :stateTime and :endTime");
}
if (StringUtils.isNotEmpty(personName)) {
whereSql.append(" and p.name like concat('%',:personName,'%')");
}
if (null != groupId && groupId != 0) {
whereSql.append(" and eg.id = :groupId");
}
StringBuilder groupBySql = new StringBuilder(" GROUP BY a.employee_id");
dataSql.append(whereSql).append(groupBySql);
if (null != pageable) {
StringBuilder limitSql = new StringBuilder(" limit :offset,:pageSize");
dataSql.append(limitSql);
}
Query dataQuery = entityManager.createNativeQuery(dataSql.toString());
dataQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(AttendanceStatisticsVo.class));
//设置参数
if (null != siteId) {
dataQuery.setParameter("siteId", siteId);
}
if (null != stateTime && null != endTime) {
dataQuery.setParameter("stateTime", stateTime);
dataQuery.setParameter("endTime", endTime);
}
if (StringUtils.isNotEmpty(personName)) {
dataQuery.setParameter("personName", personName);
}
if (null != groupId && groupId != 0) {
dataQuery.setParameter("groupId", groupId);
}
if (null != pageable) {
Long offset = pageable.getOffset();
Integer pageSize = pageable.getPageSize();
dataQuery.setParameter("offset", offset);
dataQuery.setParameter("pageSize", pageSize);
}
List resultList = dataQuery.getResultList();
return dataQuery.getResultList();
}
使用 QueryDSL实现代码,仅供参考:
- maven依赖
com.querydsl
querydsl-jpa
com.querydsl
querydsl-apt
com.querydsl
querydsl-sql
org.hibernate
hibernate-ehcache
- 替换SUM,CASE THEN
举例: 替换下面的SQL
SUM(CASE when floor(status/10) = 1 then 1 else 1 end) as absences
- 创建CaseBuilder
Numberexpressionabsences = new CaseBuilder().when(qAttendanceRecord.status.divide(10).floor().eq(1)).then(1).otherwise(0);
- 创建BooleanBuilder
//用SUM函数来计算CASE WHEN后的字段总数 BooleanBuilder havingBooleanBuilder = new BooleanBuilder(); havingBooleanBuilder.and(absences.sum().gt(0));
- 替换where语句
- 举例
if (StringUtils.isNotEmpty(personName)) {
whereSql.append(" and p.name like concat('%',:personName,'%')");
}
- 更改后的代码
BooleanBuilder booleanBuilder = new BooleanBuilder();
if (StringUtils.isNotEmpty(personName)) {
booleanBuilder.and(qAttendanceRecord.employee.person.name.like("%" + personName + "%"));
}
- 整合
- 查询完整代码
public ListpageAttendanceStatistics(Integer siteId, Date stateTime, Date endTime, String statue, String personName, Pageable pageable, Integer groupId) { QAttendanceRecord qAttendanceRecord = QAttendanceRecord.attendanceRecord; //where语句 BooleanBuilder booleanBuilder = new BooleanBuilder(); booleanBuilder .and(qAttendanceRecord.employee.attendanceRuleId.isNotNull()) .and(qAttendanceRecord.hasDeleted.eq(false)); if (null != siteId) { booleanBuilder.and(qAttendanceRecord.siteId.eq(siteId)); } if (null != stateTime && null != endTime) { booleanBuilder.and(qAttendanceRecord.attendanceDate.between(stateTime, endTime)); } if (StringUtils.isNotEmpty(personName)) { booleanBuilder.and(qAttendanceRecord.employee.person.name.like("%" + personName + "%")); } if (null != groupId && groupId != 0) { booleanBuilder.and(qAttendanceRecord.employee.employeeGroup.id.eq(groupId)); } //查询带函数字段 Numberexpression absences = new CaseBuilder().when(qAttendanceRecord.status.divide(10).floor().eq(1)).then(1).otherwise(0); Numberexpression lateNumber = new CaseBuilder().when(qAttendanceRecord.status.divide(10).floor().eq(2)).then(1).otherwise(0); Numberexpression leaveEarly = new CaseBuilder().when(qAttendanceRecord.status.divide(10).floor().eq(3)).then(1).otherwise(0); Numberexpression normalDays = new CaseBuilder().when(qAttendanceRecord.status.divide(10).floor().eq(4)).then(1).otherwise(0); Numberexpression bAbsences = new CaseBuilder().when(qAttendanceRecord.statusB.eq(1)).then(1).otherwise(0); Numberexpression bLateNumber = new CaseBuilder().when(qAttendanceRecord.statusB.eq(2)).then(1).otherwise(0); Numberexpression bLeaveEarly = new CaseBuilder().when(qAttendanceRecord.statusB.eq(3)).then(1).otherwise(0); Numberexpression bNormalDays = new CaseBuilder().when(qAttendanceRecord.statusB.eq(4)).then(1).otherwise(0); Numberexpression cAbsences = new CaseBuilder().when(qAttendanceRecord.statusC.eq(1)).then(1).otherwise(0); Numberexpression cLateNumber = new CaseBuilder().when(qAttendanceRecord.statusC.eq(2)).then(1).otherwise(0); Numberexpression cLeaveEarly = new CaseBuilder().when(qAttendanceRecord.statusC.eq(3)).then(1).otherwise(0); Numberexpression cNormalDays = new CaseBuilder().when(qAttendanceRecord.statusC.eq(4)).then(1).otherwise(0); Numberexpression dAbsences = new CaseBuilder().when(qAttendanceRecord.statusD.eq(1)).then(1).otherwise(0); Numberexpression dLateNumber = new CaseBuilder().when(qAttendanceRecord.statusD.eq(2)).then(1).otherwise(0); Numberexpression dLeaveEarly = new CaseBuilder().when(qAttendanceRecord.statusD.eq(3)).then(1).otherwise(0); Numberexpression dNormalDays = new CaseBuilder().when(qAttendanceRecord.statusD.eq(4)).then(1).otherwise(0); Numberexpression workOvertime = new CaseBuilder().when(qAttendanceRecord.status.mod(10).eq(5)).then(qAttendanceRecord.overtimeHours).otherwise(0); Numberexpression festivalOvertime = new CaseBuilder().when(qAttendanceRecord.status.mod(10).eq(6)).then(qAttendanceRecord.overtimeHours).otherwise(0); Numberexpression holidayOvertime = new CaseBuilder().when(qAttendanceRecord.status.mod(10).eq(7)).then(qAttendanceRecord.overtimeHours).otherwise(0); Numberexpression askForLeave = new CaseBuilder().when(qAttendanceRecord.status.eq(51)).then(1).otherwise(0); Numberexpression businessTrip = new CaseBuilder().when(qAttendanceRecord.status.eq(52)).then(1).otherwise(0); //having语句 BooleanBuilder havingBooleanBuilder = new BooleanBuilder(); if (StringUtils.isNotBlank(statue)) { switch (statue) { case ABSENT: havingBooleanBuilder.and( absences.sum().gt(0) .or(bAbsences.sum().gt(0)) .or(cAbsences.sum().gt(0)) .or(dAbsences.sum().gt(0))); break; case LATE: havingBooleanBuilder.and( lateNumber.sum().gt(0) .or(bLateNumber.sum().gt(0)) .or(cLateNumber.sum().gt(0)) .or(dLateNumber.sum().gt(0))); break; case LEAVE_EARLY: havingBooleanBuilder.and( leaveEarly.sum().gt(0) .or(bLeaveEarly.sum().gt(0)) .or(cLeaveEarly.sum().gt(0)) .or(dLeaveEarly.sum().gt(0))); break; case FULL_ATTENDANCE: havingBooleanBuilder.and( normalDays.sum().gt(0) .or(bNormalDays.sum().gt(0)) .or(cNormalDays.sum().gt(0)) .or(dNormalDays.sum().gt(0))); break; case WORK_OVERTIME: havingBooleanBuilder.and( workOvertime.sum().gt(0)); break; case OVERTIME_ON_HOLIDAYS: havingBooleanBuilder.and( festivalOvertime.sum().gt(0)); break; case OVERTIM_ON_PUBLIC_HOLIDAYS: havingBooleanBuilder.and( holidayOvertime.sum().gt(0)); break; case ASK_FOR_LEAVE: havingBooleanBuilder.and( askForLeave.sum().gt(0)); break; case BUSINESS_TRIP: havingBooleanBuilder.and( businessTrip.sum().gt(0)); break; default: break; } } JPAQuery query = super.getJpaQueryFactory() .select( Projections.constructor( AttendanceStatisticsVo.class, qAttendanceRecord.employeeId.as("employeeId"), absences.sum().as("absences"), lateNumber.sum().as("lateNumber"), leaveEarly.sum().as("leaveEarly"), normalDays.sum().as("normalDays"), bAbsences.sum().as("bAbsences"), bLateNumber.sum().as("bLateNumber"), bLeaveEarly.sum().as("bLeaveEarly"), bNormalDays.sum().as("bNormalDays"), cAbsences.sum().as("cAbsences"), cLateNumber.sum().as("cLateNumber"), cLeaveEarly.sum().as("cLeaveEarly"), cNormalDays.sum().as("cNormalDays"), dAbsences.sum().as("dAbsences"), dLateNumber.sum().as("dLateNumber"), dLeaveEarly.sum().as("dLeaveEarly"), dNormalDays.sum().as("dNormalDays"), workOvertime.sum().as("workOvertime"), festivalOvertime.sum().as("festivalOvertime"), holidayOvertime.sum().as("holidayOvertime"), askForLeave.sum().as("askForLeave"), businessTrip.sum().as("businessTrip") )) .from(qAttendanceRecord) .where(booleanBuilder) .groupBy(qAttendanceRecord.employeeId) .having(havingBooleanBuilder); //分页 if (null != pageable) { query.offset(pageable.getOffset()).limit(pageable.getPageSize()); } return query.fetch(); }
- VO对象
@Data
public class AttendanceStatisticsVo implements Serializable {
private static final long serialVersionUID = -8440165638031432031L;
private Integer employeeId;
private Integer attendanceId;
private String personNo;
private String personName;
private String groupName;
private Integer groupId;
private Integer absences;
private Integer lateNumber;
private Integer leaveEarly;
private Integer normalDays;
private Integer bAbsences;
private Integer bLateNumber;
private Integer bLeaveEarly;
private Integer bNormalDays;
private Integer cAbsences;
private Integer cLateNumber;
private Integer cLeaveEarly;
private Integer cNormalDays;
private Integer dAbsences;
private Integer dLateNumber;
private Integer dLeaveEarly;
private Integer dNormalDays;
private Integer workOvertime;
private Integer festivalOvertime;
private Integer holidayOvertime;
private Integer askForLeave;
private Integer businessTrip;
public AttendanceStatisticsVo() {
}
public AttendanceStatisticsVo(Integer attendanceId, String personNo, String personName, String groupName) {
this.attendanceId = attendanceId;
this.personNo = personNo;
this.personName = personName;
this.groupName = groupName;
}
public AttendanceStatisticsVo(Integer attendanceId, String personNo, String personName, String groupName, Integer groupId) {
this.attendanceId = attendanceId;
this.personNo = personNo;
this.personName = personName;
this.groupName = groupName;
this.groupId = groupId;
}
public AttendanceStatisticsVo(Integer employeeId, Integer absences, Integer lateNumber,
Integer leaveEarly, Integer normalDays, Integer bAbsences, Integer bLateNumber, Integer bLeaveEarly, Integer bNormalDays, Integer cAbsences, Integer cLateNumber,
Integer cLeaveEarly, Integer cNormalDays, Integer dAbsences, Integer dLateNumber, Integer dLeaveEarly, Integer dNormalDays, Integer workOvertime, Integer festivalOvertime,
Integer holidayOvertime, Integer askForLeave, Integer businessTrip) {
this.employeeId = employeeId;
this.absences = absences;
this.lateNumber = lateNumber;
this.leaveEarly = leaveEarly;
this.normalDays = normalDays;
this.bAbsences = bAbsences;
this.bLateNumber = bLateNumber;
this.bLeaveEarly = bLeaveEarly;
this.bNormalDays = bNormalDays;
this.cAbsences = cAbsences;
this.cLateNumber = cLateNumber;
this.cLeaveEarly = cLeaveEarly;
this.cNormalDays = cNormalDays;
this.dAbsences = dAbsences;
this.dLateNumber = dLateNumber;
this.dLeaveEarly = dLeaveEarly;
this.dNormalDays = dNormalDays;
this.workOvertime = workOvertime;
this.festivalOvertime = festivalOvertime;
this.holidayOvertime = holidayOvertime;
this.askForLeave = askForLeave;
this.businessTrip = businessTrip;
}
public AttendanceStatisticsVo(Integer attendanceId, String personNo, String personName, String groupName, Integer groupId, Integer absences, Integer lateNumber,
Integer leaveEarly, Integer normalDays, Integer bAbsences, Integer bLateNumber, Integer bLeaveEarly, Integer bNormalDays, Integer cAbsences, Integer cLateNumber,
Integer cLeaveEarly, Integer cNormalDays, Integer dAbsences, Integer dLateNumber, Integer dLeaveEarly, Integer dNormalDays, Integer workOvertime, Integer festivalOvertime,
Integer holidayOvertime, Integer askForLeave, Integer businessTrip) {
this.attendanceId = attendanceId;
this.personNo = personNo;
this.personName = personName;
this.groupName = groupName;
this.groupId = groupId;
this.absences = absences;
this.lateNumber = lateNumber;
this.leaveEarly = leaveEarly;
this.normalDays = normalDays;
this.bAbsences = bAbsences;
this.bLateNumber = bLateNumber;
this.bLeaveEarly = bLeaveEarly;
this.bNormalDays = bNormalDays;
this.cAbsences = cAbsences;
this.cLateNumber = cLateNumber;
this.cLeaveEarly = cLeaveEarly;
this.cNormalDays = cNormalDays;
this.dAbsences = dAbsences;
this.dLateNumber = dLateNumber;
this.dLeaveEarly = dLeaveEarly;
this.dNormalDays = dNormalDays;
this.workOvertime = workOvertime;
this.festivalOvertime = festivalOvertime;
this.holidayOvertime = holidayOvertime;
this.askForLeave = askForLeave;
this.businessTrip = businessTrip;
}
}
总结:
- maven依赖注意要全部倒入,其中包含多个扩展依赖.
- VO对象的封装使用的方法,我这里使用的构造器方法.
- 封装的属性的类型,必须与查询出来的类型相对应.
- 有一个不会拒绝的项目经理是多么可悲的一件事.
