Creating Queries Using the JPQL (Java Persistence Query Language)

CandidateProfileRepository.java

package repository;

@Repository
public interface CandidateProfileRepository
    extends BaseRepository<CandidateProfile, Long>, CandidateProfileRepositoryCustom {

}

CandidateProfileRepositoryCustom.java

package repository;

public interface CandidateProfileRepositoryCustom {

  List<FindCandidateProfilesByFullNameAndJobIdAndStage> findCandidateProfilesByFullNameAndJobIdAndStage(String fullName, Long jobId,
      Integer stage);
}

CandidateProfileRepositoryImpl.java

package repository.impl;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;

import repository.CandidateProfileRepositoryCustom;
import dto.FindCandidateProfilesByFullNameAndJobIdAndStage;

public class CandidateProfileRepositoryImpl implements CandidateProfileRepositoryCustom {

  @PersistenceContext
  private EntityManager em;

  @Override
  public List<FindCandidateProfilesByFullNameAndJobIdAndStage> findCandidateProfilesByFullNameAndJobIdAndStage(
      String fullName, Long jobId, Integer stage) {
    String sql =
        "SELECT new dto.FindCandidateProfilesByFullNameAndJobIdAndStage( "
            + "c.id "
            + ", c.fullName "
            + ", c.resumeUrl "
            + ", j.jobTitle ) "
            + "FROM CandidatePipeline cp "
            + "LEFT JOIN cp.job j "
            + "RIGHT JOIN cp.candidate c "
            + "WHERE c.deleteFlag = false "
            + "AND LOWER(c.fullName) LIKE LOWER(CONCAT('%', IFNULL(:fullName, c.fullName), '%')) ";

    // Query for JobId
    if (jobId == null) {
      // Just get is null
      sql += "AND cp.job.id IS NULL ";
    } else if (jobId != -1) {
      // Get get equal value
      sql += "AND cp.job.id = :jobId ";
    }

    // Query for stage
    if (stage == null) {
      // Just get is null
      sql += "AND cp.stage IS NULL ";
    } else if (stage != -1) {
      // Get get equal value
      sql += "AND cp.stage =:stage ";
    }

    sql += "ORDER BY c.fullName";

    TypedQuery<FindCandidateProfilesByFullNameAndJobIdAndStage> query = em.createQuery(sql
        , FindCandidateProfilesByFullNameAndJobIdAndStage.class);

    query.setParameter("fullName", fullName);

    if (jobId != null && jobId != -1) {
      query.setParameter("jobId", jobId);
    }

    if (stage != null && stage != -1) {
      query.setParameter("stage", stage);
    }

    return query.getResultList();
  }

}

JobRepositoryImpl.java

package repository.impl;

public class JobRepositoryImpl implements JobRepositoryCustom {

	@PersistenceContext
	private EntityManager em;

	@Override
	public Page<JobHiringPipelineDTO> getJobsHiringPipeline(Integer status, String jobTitle,
			String createDate, Long locationId, Long companyId, String situationType, Pageable pageable) {
		String sql =
				"SELECT new dto.JobHiringPipelineDTO( "
						+ "j.id"
						+ ", j.jobTitle"
						+ ", j.zipCode"
						+ ", l.name"
						+ ", j.situationType"
						+ ", j.createDate"
						+ ", COUNT(CASE WHEN cp.stage = " + JobStageConstants.PHONE_SCREEN + " THEN 1 END) "
						+ ", COUNT(CASE WHEN cp.stage = " + JobStageConstants.SENT_TO_CLIENT + " THEN 1 END) "
						+ ", COUNT(CASE WHEN cp.stage = " + JobStageConstants.INTERVIEW + " THEN 1 END) "
						+ ", COUNT(CASE WHEN cp.stage = " + JobStageConstants.OFFER + " THEN 1 END) "
						+ ", COUNT(CASE WHEN cp.stage = " + JobStageConstants.HIRED + " THEN 1 END) "
						+ ", CONCAT('#', j.id) "
						+ ", CONCAT('#', j.id) "
						+ ", CONCAT('#', j.id) "
						+ ", c.companyName "
						+ ", c.id "
						+ ") "
						+ "FROM CandidatePipeline cp "
						+ "RIGHT JOIN cp.job j "
						+ "LEFT JOIN j.company c "
						+ "LEFT JOIN j.country l "
						+ "WHERE j.deleteFlag = false "
						+ "AND j.status = IFNULL(:status, j.status) "
						+ "AND j.situationType = IFNULL(NULLIF(:situationType, ''), j.situationType) "
						+ "AND c.id = IFNULL(:companyId, c.id) "
						+ "AND UPPER(j.jobTitle) LIKE CONCAT('%', UPPER(IFNULL(:jobTitle, '')), '%') "
						+ "AND DATEDIFF(CURRENT_DATE(), DATE_FORMAT(j.createDate, '%Y-%m-%d')) <= IFNULL(NULLIF(:createDate, ''), " + Long.MAX_VALUE + ") ";

		// Query for JobId
		if (locationId == null) {
			// Just get is null
			sql += "AND l.id IS NULL ";
		} else if (locationId != -1) {
			// Get get equal value
			sql += "AND l.id = :locationId ";
		}

		sql += "GROUP BY j.id, j.jobTitle, l.name, j.situationType, j.createDate";

		TypedQuery<JobHiringPipelineDTO> query = em.createQuery(sql
				, JobHiringPipelineDTO.class);

		query.setParameter("status", status);
		query.setParameter("jobTitle", jobTitle);
		query.setParameter("createDate", createDate);
		query.setParameter("companyId", companyId);
		query.setParameter("situationType", situationType);

		if (locationId != null && locationId != -1) {
			query.setParameter("locationId", locationId);
		}

		// Pageable
		List<JobHiringPipelineDTO> resultList = query.getResultList();
		long total = resultList.size();
		query.setFirstResult(pageable.getOffset());
		query.setMaxResults(pageable.getPageSize());

		return new PageImpl<>(query.getResultList(), pageable, total);
	}
}