해커톤 프로젝트 - repository : 스프링 데이터 JPA 관련 끄적임

동적으로 DB에서 데이터를 뽑아내야 할 경우 - 공공서비스예약

• 공공서비스 예약 관련

@Repository
public interface PublicServiceReservationRepositoryCustom {

    List<PublicServiceReservation> findByFiltered(List<String> areaNM, List<String> reserveType, List<String> maxClassNM,
                                                  List<String> minClassNM, List<String> svcStatNM, List<String> payAtNM, String serviceName);

}

인터페이스를 만들어 놓고

@RequiredArgsConstructor
public class PublicServiceReservationRepositoryCustomImpl implements PublicServiceReservationRepositoryCustom {

   private final EntityManager em;

   @Override
   public List<PublicServiceReservation> findByFiltered(List<String> areaNM, List<String> reserveType, List<String> maxClassNM,
                                                        List<String> minClassNM, List<String> svcStatNM, List<String> payAtNM, String svcNM) {

       String query = "SELECT p FROM PublicServiceReservation p where ";

       List<String> whereClause = new ArrayList<>();
       if(checkParamList(areaNM)){
           whereClause.add("p.areaNM in :areaNM");
       }
       if(checkParamList(reserveType)){
           whereClause.add("p.reserveType in :reserveType");
       }
       if(checkParamList(maxClassNM)){
           whereClause.add("p.maxClassNM in :maxClassNM");
       }
       if(checkParamList(minClassNM)){
           whereClause.add("p.minClassNM in :minClassNM");
       }
       if(checkParamList(svcStatNM)){
           whereClause.add("p.svcStatNM in :svcStatNM");
       }
       if(checkParamList(payAtNM)){
           whereClause.add("p.payAtNM in :payAtNM");
       }
       if (checkString(svcNM)) {
           whereClause.add("p.svcNM LIKE :svcNM");
       }

       if(checkParamList(whereClause)){
           query += String.join(" and ", whereClause);
           query += " ORDER BY p.rcptenddt ASC";   //조회할 때 마감일자 순으로 오름차순으로 보여주기 위해.
       }

       TypedQuery<PublicServiceReservation> typedQuery
               = em.createQuery(query, PublicServiceReservation.class);
       if(checkParamList(areaNM)){
           typedQuery.setParameter("areaNM", areaNM);
       }
       if(checkParamList(reserveType)){
           typedQuery.setParameter("reserveType", reserveType);
       }
       if(checkParamList(maxClassNM)){
           typedQuery.setParameter("maxClassNM", maxClassNM);
       }
       if(checkParamList(minClassNM)){
           typedQuery.setParameter("minClassNM", minClassNM);
       }
       if(checkParamList(svcStatNM)){
           typedQuery.setParameter("svcStatNM", svcStatNM);
       }
       if(checkParamList(payAtNM)){
           typedQuery.setParameter("payAtNM", payAtNM);
       }
       if (checkString(svcNM)) {
           typedQuery.setParameter("svcNM", "%" + svcNM + "%");
       }

//        int offset = pageable.getPageNumber() * pageable.getPageSize();
//        int limit = pageable.getPageSize();

       return typedQuery.getResultList();
       //        return typedQuery.setFirstResult(offset)
//                .setMaxResults(limit)
//                .getResultList();
   }

   private <T> boolean checkParamList(List<T> param) {
       return param == null ? false : param.isEmpty() || param.size() == 0 ? false : true;
   }

   private boolean checkString(String serviceName) {
       return serviceName == null ? false : serviceName.isEmpty() ? false : true;
   }

}

이런 식으로 jpql을 활용하여 조건들을 만든다.

• 그리고 JpaRepository와 함께 extends를 하면 된다.

public interface PublicServiceReservationRepository extends JpaRepository<PublicServiceReservation, Long>, PublicServiceReservationRepositoryCustom {

    @Query("SELECT p.serviceId FROM PublicServiceReservation p")
    List<String> findAllServiceId();

    @Query("SELECT DISTINCT p.serviceId FROM PublicServiceReservation p")
    Set<String> findAllDistinctServiceId();

    //페이징 처리 + 가가운 마감일자로 정렬
    Page<PublicServiceReservation> findByAreaNMOrReserveTypeOrMaxClassNMOrMinClassNMOrSvcStatNMOrPayAtNMOrderByRcptenddtAsc(
            String areaNM, String reserveType, String maxClassNM, String minClassNM, String svcStatNM, String payAtNM,
            Pageable pageable
    );

    List<PublicServiceReservation> findByAreaNMOrReserveTypeOrMaxClassNMOrMinClassNMOrSvcStatNMOrPayAtNMOrderByRcptenddtAsc(
            String areaNM, String reserveType, String maxClassNM, String minClassNM, String svcStatNM, String payAtNM
    );

    List<PublicServiceReservation> findByAreaNMInAndReserveTypeInAndMaxClassNMInAndMinClassNMInAndSvcStatNMInAndPayAtNMInOrderByRcptenddtAsc(
            List<String> areaNM, List<String> reserveType, List<String> maxClassNM,
            List<String> minClassNM, List<String> svcStatNM, List<String> payAtNM
    );
    List<PublicServiceReservation> findBySvcNMContainingOrderByRcptenddtAsc(String serviceName);

    List<PublicServiceReservation> findAllByOrderByRcptenddtAsc();

    Page<PublicServiceReservation> findAll(Pageable pageable);

    // SELECT *FROM PublicServiceReservation WHERE areaNM in :areaNM;
    Page<PublicServiceReservation> findByAreaNMInOrderByRcptenddt(List<String> areaNM, Pageable pageable);

}

노인구직 관련 동적 데이터 뽑아내기

@Repository
public interface EmploymentRepositoryCustom {

    // 유료/무료 + 마감/접수중 + 교육명(검색어) 로 필터 적용
    List<Employment> findByFiltered(String registCost, String applyState, String subject);

}

• 구현하면

@RequiredArgsConstructor
@Slf4j
public class EmploymentRepositoryCustomImpl implements EmploymentRepositoryCustom{

    private final EntityManager em;

    @Override
    public List<Employment> findByFiltered(String registCost, String applyState, String subject) {

        String query = "SELECT e FROM Employment e where ";
        List<String> whereClause = new ArrayList<>();

        if (checkString(registCost)) {
            whereClause.add("e.registCost = :registCost");
        }
        if (checkString(applyState)) {
            whereClause.add("e.applyState = :applyState");
        }
        if (checkString(subject)) {
            whereClause.add("e.subject LIKE :subject");
        }

        if (checkParamList(whereClause)) {
            query += String.join(" and ", whereClause);
        }
        log.info("query = {}", query);

        TypedQuery<Employment> typedQuery = em.createQuery(query, Employment.class);
        if (checkString(registCost)) {
            typedQuery.setParameter("registCost",registCost);
        }
        if (checkString(applyState)) {
            typedQuery.setParameter("applyState", applyState);
        }
        if (checkString(subject)) {
            typedQuery.setParameter("subject", "%" + subject + "%");
        }
        return typedQuery.getResultList();
    }

    private <T> boolean checkParamList(List<T> param) {
        return param == null ? false : param.isEmpty() || param.size() == 0 ? false : true;
    }

    private boolean checkString(String param) {
        return param == null ? false : param.isEmpty() ? false : true;
    }
}

• 해당 내용들을 EmploymentRepository에서 함께 extends 하면 된다.

public interface EmploymentRepository extends JpaRepository<Employment, Long>, EmploymentRepositoryCustom {

    @Query("SELECT DISTINCT e.subject FROM Employment e")
    Set<String> findAllDistinctSubject();

    List<Employment> findAllByOrderByApplicationEndDateAsc();

    List<Employment> findByRegistCostAndApplyState(String registCost, String applyState);

    List<Employment> findByRegistCost(String registCost);

    List<Employment> findByApplyState(String applyState);
    List<Employment> findBySubjectContaining(String subject);

}

새누리 노인 공고 관련

@Repository
public interface SenuriServiceDetailRepositoryCustom {

    List<SenuriServiceDetailCheck> findFiltered(List<String> area, List<String> jobPosition, List<String> status, String wantedTitle);

    List<SenuriServiceDetailCheck> findFilteredInterestArea(List<String> area);
}

• 구현하면

@RequiredArgsConstructor
@Slf4j
public class SenuriServiceDetailRepositoryCustomImpl implements SenuriServiceDetailRepositoryCustom{

    private final EntityManager em;

    /**
     * 지역구, 채용공고형태, 공고상태, 검색어로 필터링 적용
     */
    @Override
    public List<SenuriServiceDetailCheck> findFiltered(List<String> area, List<String> jobPosition, List<String> status, String wantedTitle) {

        String query = "SELECT s FROM SenuriServiceDetailCheck s where ";
        List<String> whereClause = new ArrayList<>();

//        if (checkParamList(area)) {
//            List<String> likeConditions = area.stream()
//                    .map(areaName -> "s.plDetAddr LIKE :plDetAddr")
//                    .collect(Collectors.toList());
//            whereClause.add("(" + String.join(" OR ", likeConditions) + ")");
//        }
        if (checkParamList(area)) {
            List<String> likeConditions = area.stream()
                    .map(areaName -> "s.plDetAddr LIKE :plDetAddr_" + areaName)
                    .collect(Collectors.toList());
            // -> 이렇게 해야지만 파라미터 이름을 구별할 수 있음 :plDetAddr 이렇게만 쓰면 하이버네이트는 하나의 파라미터로 인식해서
            // 아무리 리스트에 4개의 값이 있어도 하나의 파라미터에만 값을 넣게 돼 !! name을 구분해줘야지 제대로 넣을 수 있음 !
            whereClause.add("(" + String.join(" OR ", likeConditions) + ")");
        }
        if (checkParamList(jobPosition)) {
            whereClause.add("s.emplymShpNm in :emplymShpNm");
        }
        if (checkParamList(status)) {
            whereClause.add("s.deadline in :deadline");
        }
        if (checkSearchWord(wantedTitle)) {
            whereClause.add("s.wantedTitle LIKE :wantedTitle");
        }
        if (checkParamList(whereClause)) {
            query += String.join(" and ", whereClause);
        }
        LocalDate today = LocalDate.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");
        String formattedToday = today.format(formatter);
        log.info("formating = {}", formattedToday);

        query += " and s.toAcptDd >= :formattedToday";
        query += " GROUP BY s.jobId";
        query += " ORDER BY s.toAcptDd ASC";  //종료접수일 순으로 오름차순 진행.

        log.info("query = {} ", query);

        TypedQuery<SenuriServiceDetailCheck> typedQuery
                = em.createQuery(query, SenuriServiceDetailCheck.class);


//        if (checkParamList(area)) {
//            area.forEach(areaName -> typedQuery.setParameter("plDetAddr", "%" + areaName + "%"));
//        }
        if (checkParamList(area)) {
            area.forEach(areaName -> typedQuery.setParameter("plDetAddr_" + areaName, "%" + areaName + "%"));
        }
        if (checkParamList(jobPosition)) {
            typedQuery.setParameter("emplymShpNm", jobPosition);
        }
        if (checkParamList(status)) {
            typedQuery.setParameter("deadline", status);
        }
        if (checkSearchWord(wantedTitle)) {
            typedQuery.setParameter("wantedTitle", "%" + wantedTitle + "%");
        }
        typedQuery.setParameter("formattedToday", formattedToday);

        return typedQuery.getResultList();
    }

    @Override
    public List<SenuriServiceDetailCheck> findFilteredInterestArea(List<String> area) {
        String query = "SELECT s FROM SenuriServiceDetailCheck s where ";
        List<String> whereClause = new ArrayList<>();

        if (checkParamList(area)) {
            List<String> likeConditions = area.stream()
                    .map(areaName -> "s.plDetAddr LIKE :plDetAddr_" + areaName)
                    .collect(Collectors.toList());
            // -> 이렇게 해야지만 파라미터 이름을 구별할 수 있음 :plDetAddr 이렇게만 쓰면 하이버네이트는 하나의 파라미터로 인식해서
            // 아무리 리스트에 4개의 값이 있어도 하나의 파라미터에만 값을 넣게 돼 !! name을 구분해줘야지 제대로 넣을 수 있음 !
            whereClause.add("(" + String.join(" OR ", likeConditions) + ")");
        }
        if (checkParamList(whereClause)) {
            query += String.join(" and ", whereClause);
        }
        LocalDate today = LocalDate.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");
        String formattedToday = today.format(formatter);
        log.info("formating = {}", formattedToday);

        query += " and s.toAcptDd >= :formattedToday";
        query += " GROUP BY s.jobId";
        query += " ORDER BY s.toAcptDd ASC";
        log.info("query = {} ", query);

        TypedQuery<SenuriServiceDetailCheck> typedQuery
                = em.createQuery(query, SenuriServiceDetailCheck.class);
        if (checkParamList(area)) {
            area.forEach(areaName -> typedQuery.setParameter("plDetAddr_" + areaName, "%" + areaName + "%"));
        }
        typedQuery.setParameter("formattedToday", formattedToday);

        return typedQuery.getResultList();
    }

    private <T> boolean checkParamList(List<T> param) {
        return param == null ? false : param.isEmpty() || param.size() == 0 ? false : true;
//        return param != null && !param.isEmpty();
    }

    private boolean checkSearchWord(String param) {  //검색어 필터링
        return param == null ? false : param.isEmpty() ? false : true;
//        return param != null && !param.isEmpty();
    }
}

• 해당 동적 내용을 함꼐 extends

public interface SenuriServiceDetailRepository extends JpaRepository<SenuriServiceDetailCheck, Long>, SenuriServiceDetailRepositoryCustom {

    @Query("SELECT DISTINCT s FROM SenuriServiceDetailCheck s WHERE s.toAcptDd >= :today AND s.plDetAddr LIKE %:city% GROUP BY s.jobId ORDER BY s.toAcptDd ASC")
    List<SenuriServiceDetailCheck> findAfterTodayAndCityOrderByToAcptDdAsc(@Param("today") String today, @Param("city") String city);

    @Query("SELECT s FROM SenuriServiceDetailCheck s WHERE s.createDy >= :today AND s.plDetAddr LIKE %:city% GROUP BY s.jobId ORDER BY s.toAcptDd ASC")
    List<SenuriServiceDetailCheck> findCreateDayAfterTodayAndCityOrderByToAcptDdAsc(String today, String city);
}