QueryDSL使用
往期文章链接
前文提要:
公司项目使用的数据源框架为Spring Data JPA,在实现复杂功能时,我选择了用原生SQL语句来实现.最近有客户要求项目能够支持SQL Server数据库,需要把项目中的所有的原生SQL语句改为QueryDSL,复杂的SQL用QueryDSL来实现相当麻烦,下面记录一下转换的过程.
之前使用原生SQL语句的实现代码(细节请看往期文章链接):
@PersistenceContext
private EntityManager entityManager;
@Override
@SuppressWarnings("unchecked")
public List<AttendanceStatisticsVo> 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<AttendanceStatisticsVo> resultList = dataQuery.getResultList();
return dataQuery.getResultList();
}
使用 QueryDSL实现代码,仅供参考:
maven依赖
<!-- orm -->
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-sql</artifactId>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-ehcache</artifactId>
</dependency>
- 替换
SUM,CASE THEN
举例: 替换下面的SQL
SUM(CASE when floor(status/10) = 1 then 1 else 1 end) as absences
- 创建
CaseBuilder
/*
相当于 CASE when floor(status/10) = 1 then 1 else NULL end 部分
qAttendanceRecord为生成QuerySQL实体类
*/
NumberExpression<Integer> absences = 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 List<AttendanceStatisticsVo> pageAttendanceStatistics(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<Integer> absences = new CaseBuilder().when(qAttendanceRecord.status.divide(10).floor().eq(1)).then(1).otherwise(0);
NumberExpression<Integer> lateNumber = new CaseBuilder().when(qAttendanceRecord.status.divide(10).floor().eq(2)).then(1).otherwise(0);
NumberExpression<Integer> leaveEarly = new CaseBuilder().when(qAttendanceRecord.status.divide(10).floor().eq(3)).then(1).otherwise(0);
NumberExpression<Integer> normalDays = new CaseBuilder().when(qAttendanceRecord.status.divide(10).floor().eq(4)).then(1).otherwise(0);
NumberExpression<Integer> bAbsences = new CaseBuilder().when(qAttendanceRecord.statusB.eq(1)).then(1).otherwise(0);
NumberExpression<Integer> bLateNumber = new CaseBuilder().when(qAttendanceRecord.statusB.eq(2)).then(1).otherwise(0);
NumberExpression<Integer> bLeaveEarly = new CaseBuilder().when(qAttendanceRecord.statusB.eq(3)).then(1).otherwise(0);
NumberExpression<Integer> bNormalDays = new CaseBuilder().when(qAttendanceRecord.statusB.eq(4)).then(1).otherwise(0);
NumberExpression<Integer> cAbsences = new CaseBuilder().when(qAttendanceRecord.statusC.eq(1)).then(1).otherwise(0);
NumberExpression<Integer> cLateNumber = new CaseBuilder().when(qAttendanceRecord.statusC.eq(2)).then(1).otherwise(0);
NumberExpression<Integer> cLeaveEarly = new CaseBuilder().when(qAttendanceRecord.statusC.eq(3)).then(1).otherwise(0);
NumberExpression<Integer> cNormalDays = new CaseBuilder().when(qAttendanceRecord.statusC.eq(4)).then(1).otherwise(0);
NumberExpression<Integer> dAbsences = new CaseBuilder().when(qAttendanceRecord.statusD.eq(1)).then(1).otherwise(0);
NumberExpression<Integer> dLateNumber = new CaseBuilder().when(qAttendanceRecord.statusD.eq(2)).then(1).otherwise(0);
NumberExpression<Integer> dLeaveEarly = new CaseBuilder().when(qAttendanceRecord.statusD.eq(3)).then(1).otherwise(0);
NumberExpression<Integer> dNormalDays = new CaseBuilder().when(qAttendanceRecord.statusD.eq(4)).then(1).otherwise(0);
NumberExpression<Integer> workOvertime = new CaseBuilder().when(qAttendanceRecord.status.mod(10).eq(5)).then(qAttendanceRecord.overtimeHours).otherwise(0);
NumberExpression<Integer> festivalOvertime = new CaseBuilder().when(qAttendanceRecord.status.mod(10).eq(6)).then(qAttendanceRecord.overtimeHours).otherwise(0);
NumberExpression<Integer> holidayOvertime = new CaseBuilder().when(qAttendanceRecord.status.mod(10).eq(7)).then(qAttendanceRecord.overtimeHours).otherwise(0);
NumberExpression<Integer> askForLeave = new CaseBuilder().when(qAttendanceRecord.status.eq(51)).then(1).otherwise(0);
NumberExpression<Integer> 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<AttendanceStatisticsVo> query = super.getJpaQueryFactory()
.select(
/*
这里使用的是 constructor 构造器封装成VO对象
as 后的别名为构造方法中的参数名
*/
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为 lombok 注解,不想使用的可以手动生成 getter,setter 方法
构造方法按需实现
*/
@Data
public class AttendanceStatisticsVo implements Serializable {
private static final long serialVersionUID = -8440165638031432031L;
/** 员工id */
private Integer employeeId;
/** 考勤记录id */
private Integer attendanceId;
/** 人员编号 */
private String personNo;
/** 人员姓名 */
private String personName;
/** 分组名 */
private String groupName;
/** 分组id */
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对象的封装使用的方法,我这里使用的构造器方法.
- 封装的属性的类型,必须与查询出来的类型相对应.
- 有一个不会拒绝的项目经理是多么可悲的一件事.
1148
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
