Spring Data JPA 使用Query进行复杂操作

        接触JPA不久，在需要分组和复杂查询的时候，不知如何下手。唯一知道的是使用Query手动写SQL。

        现在有这么一个需求，查询的时候有两个条件，当某个条件是空的时候，不执行这个where（这个Mybatis来说真的太简单了），并将最终结果按照月份或某个字段进行分组。

代码如下：

    public List> applyList(String supporterInfoId,Date applySta, Date applyEnd) {
        //使用ＳｔｒｉｎｇＢｕｉｌｄｅｒ来帮助我们造句
        StringBuilder builder = new StringBuilder();
        builder.append("select date_format(created, '%Y-%m') month,count(id),supporter_info_id from rc_recruit_apply");
        if(applySta!=null && applyEnd!=null){
            //Java给sqlserver数据库中的datetime类型字段赋值，将java.util.Date类型转换为java.sql.Date：
            Date sta = new java.sql.Date(applySta.getTime());
            Date end = new java.sql.Date(applyEnd.getTime());
            builder.append(" where created between '"+sta+"' and '"+end+"'");
        }
        if(supporterInfoId!=null && applySta==null){
            builder.append(" where supporter_info_id = '"+supporterInfoId+"'");
        }else if(supporterInfoId==null){
            builder.append(" ");
        }else{
            builder.append(" and supporter_info_id = '"+supporterInfoId+"'");
        }
        if(1==1){
            builder.append(" group by date_format(created, '%Y-%m'),supporter_info_id");
        }
        Query query = entityManager.createNativeQuery(builder.toString());
        //如果数据库并没有符合条件的数据，ｒｏｗｓ是绝对空的状态，需要进行判断
        List rows = query.getResultList();
        boolean　row　=　rows.lsEmpty();
        if(row){ //绝对空
            //月份
            esMap.put("month",null);
            //数量
            esMap.put("number",null);
            //孵化器
            esMap.put("supporterInfoId",null);
       　}
        List> esMaps = new ArrayList<>();
        for (Object row : rows) {
            Map esMap = new HashMap<>();
            Object[] cells = (Object[]) row;
            //月份
            esMap.put("month",cells[0].toString());
            //数量
            esMap.put("number",cells[1].toString());
            //孵化器
            String supporterName = supporterInfoService.getEntityByIdEx(cells[2].toString()).getName();
            esMap.put("supporterInfoId",supporterName);

            esMaps.add(esMap);
        }
        return esMaps;
    }

还有类似于 IS NULL ，用于修改操作，比较蠢 不建议使用 哈哈哈哈哈。

    @Transactional//声明事务
    @Modifying(clearAutomatically = true)//文中讲解
    @Query(value="update rc_apply_information rc set rc.portrait_id = CASE WHEN ?1 IS NULL THEN rc.portrait_id ELSE ?1 END," +
            "rc.name =CASE WHEN ?2 IS NULL THEN rc.name ELSE ?2 END," +
            "rc.sex = CASE WHEN ?3 IS NULL THEN rc.sex ELSE ?3 END," +
            "rc.age = CASE WHEN ?4 IS NULL THEN rc.age ELSE ?4 END," +
            "rc.province_id = CASE WHEN ?5 IS NULL THEN rc.province_id ELSE ?5 END," +
            "rc.education_id = CASE WHEN ?6 IS NULL THEN rc.education_id ELSE ?6 END," +
            "rc.industry_id = CASE WHEN ?7 IS NULL THEN rc.industry_id ELSE ?7 END," +
            "rc.phone = CASE WHEN ?8 IS NULL THEN rc.phone ELSE ?8 END," +
            "rc.location = CASE WHEN ?9 IS NULL THEN rc.location ELSE ?9 END," +
            "rc.email = CASE WHEN ?10 IS NULL THEN rc.email ELSE ?10 END," +
            "rc.introduction = CASE WHEN ?11 IS NULL THEN rc.introduction ELSE ?11 END, " +
            "rc.context_education = CASE WHEN ?12 IS NULL THEN rc.context_education ELSE ?12 END," +
            "rc.work_experience = CASE WHEN ?13 IS NULL THEN rc.work_experience ELSE ?13 END," +
            "rc.expect_salary = CASE WHEN ?14 IS NULL THEN rc.expect_salary ELSE ?14 END," +
            "rc.issue_enable='1' where rc.personal_user_id = ?15",nativeQuery = true)
    void updateApplyInfo(String portraitId,String name,String sex,Integer age,String provinceId,String educationId,String industryId,String phone
    ,String location,String email,String introduction,String contextEducation,String workExperience,String expectSalary,String personalUserId);

@Modifying： Jpa底层有一级缓存，在更新完数据库后，如果在调用这个对象，再去查这个对象，这个对象是缓存中的，并没有与数据库同步，使用clearAutomatically=true，刷新Hibernate的一级缓存， 否则在同一接口中，更新一个对象，接着查询这个对象，那么查出来的这个对象还是之前的没有更新前的状态。