JPA的动态条件查询之Hql

JPA的动态条件查询之Hql

一、jap自动封装对象方法一

1.Hql的动态查询语句（重点部分）

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

//1.Hql的动态查询语句
@Query(value = "SELECT" +
            "   trr.companyName as companyName, " +
            "   trr.status as status, " +
            "   trr.rejectReason as rejectReason," +
            "   trr.businessLicense as businessLicense, " +
            "   tru.recruiterName as recruiterName, " +
            "   trr.createTime as createTime, " +
            "   trr.id as id ," +
            "   trr.status as status ," +
            "   user.userName as userName " +
            "    FROM  " +
            "    RecruiterUser AS tru  " +
            "    LEFT JOIN RecruiterRecord AS trr ON tru.id = trr.recruiterUserId " +
            "    LEFT JOIN User AS user ON user.id = tru.userId " +
            " where (?1 is null or trr.companyName  like  %?1%) and (?3 is null or tru.recruiterName  like  %?3%) and (?2 is null or tru.recruiterPhone like %?2%)  ")
    Page<RecruiterRecordResult> findRecord(String companyName, String recruiterPhone, String recruiterName, Pageable request);
    

2.接收的实体类（通过动态代理发射自动封装的对象，只有接口，不需要实现）

//接收的实体类
public interface RecruiterRecordResult {
    String getCompanyName();
    String getBusinessLicense();
    String getRecruiterName();
    Date getCreateTime();
    Integer getId();
    Integer getStatus();
    String getUserName();
}

3.测试调用的代码

        //调用的代码
        String companyName = recruiterVo.getCompanyName();
        String recruiterPhone = recruiterVo.getRecruiterPhone();
        String recruiterName = recruiterVo.getRecruiterName();
        PageRequest request = PageRequest.of(page, size, Sort.by("updateTime"));
        Page<RecruiterRecordResult> resultSimplePage = recruiterRecordRepository.findRecord(companyName,recruiterPhone,recruiterName, request);
        return resultSimplePage;

二、jap自动封装对象方法二

1.接收的实体类代码

//省略set get 方法
public class TalentPoolResult implements Serializable {


    private  Integer id;

    private String talentPoolName;

    private String userName;

    private Integer gender;

    private java.util.Date birthDate;

    private Integer age;

    private String workExperienceYears;

    private String userPhone;

    private String educationLevel;

    private String expectedSalaryRang;

    private Integer applyJobCategoryId;

    private String applyJobCategoryName;

    private String schoolName;

    private String companyName;


    private Integer resumeId;

     //定义构造方法
    public TalentPoolResult(Integer id, String userName, Integer gender, Date birthDate, String workExperienceYears, String userPhone, String educationLevel, String expectedSalaryRang, Integer applyJobCategoryId, Integer resumeId) {
        this.id = id;
        this.userName = userName;
        this.gender = gender;
        this.birthDate = birthDate;
        this.workExperienceYears = workExperienceYears;
        this.userPhone = userPhone;
        this.educationLevel = educationLevel;
        this.expectedSalaryRang = expectedSalaryRang;
        this.applyJobCategoryId = applyJobCategoryId;
        this.resumeId = resumeId;
    }
  }

2.Service代码

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;

public Page<TalentPoolResult> findTalentPoolList(Resume resume, Integer recruiterUserId, int page, int size) {


        PageRequest request = PageRequest.of(page, size, Sort.by("updateTime").descending());

        Page<TalentPoolResult> talentPoolResultPage = talentPoolRepository.findTalentPool(recruiterUserId,request);


        return talentPoolResultPage;
    }

3.Repository

//报错的，注意空格，在代码里面每行后面都有个空格。把new 对象(参数1，参数2) 保持写在同一行不要分行

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

//报错的，注意空格，在代码里面每行后面都有个空格。把new 对象(参数1，参数2) 保持写在同一行不要分行
@Query(" select  new com.xsili.business.resume.model.TalentPoolResult(tsrr.id, tr.userName, tr.gender, tr.birthDate, tr.workExperienceYears, tr.userPhone, tr.educationLevel, tr.expectedSalaryRang, tr.applyJobCategoryId, tsrr.resumeId) " +
            "from " +
            "SendResumeRecord as tsrr " +
            "left join  " +
            "Resume as tr  " +
            "on  " +
            "tsrr.resumeId = tr.id " +
            "where  " +
            "tsrr.recruiterUserId = 174087 ")
    Page<TalentPoolResult> findTalentPool(Integer recruiterUserId, Pageable request);
    

在Specification构建使用mysql的内置函数

@Override
            public Predicate toPredicate(Root<Position> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

     if (position.getRecruitmentIsPoxy() != null) {
          predicateList.add(cb.equal(root.get("recruitmentIsPoxy").as(Boolean.class),position.getRecruitmentIsPoxy()));                          
                    
      }
      
      if (position.getAdminCheckStatus() != null) {
                    Path<Object> path = root.get("checkStatus");//定义查询的字段
                    CriteriaBuilder.In<Object> in = cb.in(path);
                    in.value(1);//存入值
                    in.value(3);//存入值
                    predicateList.add(in);
     }
     
     #使用调用mysql自带的函数
     #例子unix_timestamp(position0_.position_start)>1614252286582
     Expression<Long> function = cb.function("UNIX_TIMESTAMP", Long.class,root.get("positionStart"));
                predicateList.add(cb.greaterThan(function,new Date().getTime()));
           

          
}