[Spring data jpa] 使用EntityManager 原生sql使用(详细)

最新推荐文章于 2024-04-28 08:00:00 发布

动身崛起

最新推荐文章于 2024-04-28 08:00:00 发布

阅读量5.4k

点赞数 5

文章标签： java

本文链接：https://blog.csdn.net/qq_45485347/article/details/108547427

版权

spring data jpa - 动态原生SQL

前言

SpringData中们可是使用继承接口直接按照规则写方法名即可完成查询的方法，不需要写具体的实现，但是这样写又是不能满足我们的需求，比如子查询，SpringData中提供了@Query注解可以让我们写JPA的语句和原生的SQL语句，但是有些需求还是无法实现，这是可以用EntityManager来实现更加复杂的原生SQL，下面根据实际需求来介绍一下具体使用。

需求

根据考勤表中的status状态来计算每种状态的数量，status为两位例如 25 为 迟到 + 工作日加班

在这里插入图片描述

代码片段

注入EntityManager对象

@PersistenceContext
private EntityManager entityManager;

原生SQL语句的拼接
1.查询语句

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,"
                        //CASE when floor(status/10) = 1 then 1 else NULL end 判断status第一位数字，并计算其数量
                        + "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,"
                        //判断status第二位数字，并计算其数量
                        + "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");

2.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);
}

创建SQL并指定结果封装到指定对象中

Query dataQuery = entityManager.createNativeQuery(dataSql.toString());
        dataQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(AttendanceStatisticsVo.class));

封装的VO对象

@Data
@NoArgsConstructor
@AllArgsConstructor
public class AttendanceStatisticsVo implements Serializable {

    private static final long serialVersionUID = -8440165638031432031L;

    private Integer attendanceId;
    private String personNo;
    private String personName;
    private String groupName;
    private Integer groupId;
    //BigInteger 对应 sql count返回类型
    private BigInteger absences;
    private BigInteger lateNumber;
    private BigInteger leaveEarly;
    private BigInteger normalDays;
    private BigInteger bAbsences;
    private BigInteger bLateNumber;
    private BigInteger bLeaveEarly;
    private BigInteger bNormalDays;
    private BigInteger cAbsences;
    private BigInteger cLateNumber;
    private BigInteger cLeaveEarly;
    private BigInteger cNormalDays;
    private BigInteger dAbsences;
    private BigInteger dLateNumber;
    private BigInteger dLeaveEarly;
    private BigInteger dNormalDays;
    private BigInteger workOvertime;
    private BigInteger festivalOvertime;
    private BigInteger holidayOvertime;
}

动态参数传入

//设置参数
if (null != siteId) {
	//对应where等插叙条件语句中的参数 例： whereSql.append(" and a.site_id = :siteId") 中 ：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();

具体代码

	@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();
    }