JPA - JPQL(Java Persistence Query Language)

💡 SQL을 추상화한 JPQL

JPA : ORM(Object-Relational Mapping) 프레임워크

JPQL(Java Persistence Query Language)

JPA는 SQL을 추상화한 JPQL이라는 객체 지향 쿼리 언어를 제공합니다.

따라서 테이블을 대상으로 쿼리 하는 것이 아닌 엔티티 객체를 대상으로 쿼리합니다.

JPQL은 SQL을 추상화했기 때문에 특정 데이터베이스 SQL에 의존하지 않는 장점이 있습니다.

JPQL은 SQL과 문법이 유사하며, SELECT, FROM, WHERE, GROUP BY, HAVING, JOIN을 지원합니다.

---

💡 쿼리메소드를 사용한 쿼리문 만들기

✅ TODO 1) 전체 조회 + 정렬(내림차순)

📂 DeptRepository.java

package com.example.jpacustomexam.repository;

import com.example.jpacustomexam.model.Dept;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * packageName : com.example.jpacustomexam.repository
 * fileName : DeptRepository
 * author : GGG
 * date : 2023-10-17
 * description : 부서 레포지토리 (기본 CRUD 함수)
 * 요약 :
 * <p>
 * ===========================================================
 * DATE            AUTHOR             NOTE
 * —————————————————————————————
 * 2023-10-17         GGG          최초 생성
 */
@Repository
public interface DeptRepository extends JpaRepository<Dept, Integer> {
    // 개발자가 직접 SQL 작성 하는 기능(JPQL) : 1) 쿼리메소드
    //                                      2) @Query 쓰는 방법
    // todo: 1) 전체조회 + 정렬(내림차순)
    List<Dept> findAllByOrderByDnoDesc();
}

📂 DeptService.java

package com.example.jpacustomexam.service;

import com.example.jpacustomexam.model.Dept;
import com.example.jpacustomexam.repository.DeptRepository;
import lombok.Setter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * packageName : com.example.jpacustomexam.service
 * fileName : DeptService
 * author : GGG
 * date : 2023-10-17
 * description : 부서 서비스
 * 요약 :
 * <p>
 * ===========================================================
 * DATE            AUTHOR             NOTE
 * —————————————————————————————
 * 2023-10-17         GGG          최초 생성
 */
@Service
public class DeptService {
    @Autowired
    DeptRepository deptRepository;

    /** 전체조회 + 정렬(dno 내림차순) */
    // todo) 쿼리메소드를 사용한 함수
    public List<Dept> findAllByOrderByDnoDesc(){
        List<Dept> list = deptRepository.findAllByOrderByDnoDesc();
        return list;
    }
}

 

📂 DeptController.java

package com.example.jpacustomexam.controller;

import com.example.jpacustomexam.model.Dept;
import com.example.jpacustomexam.service.DeptService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * packageName : com.example.jpacustomexam.controller
 * fileName : DeptController
 * author : GGG
 * date : 2023-10-17
 * description : 부서 컨트롤러 (리액트 용)
 * 요약 :
 * <p>
 * ===========================================================
 * DATE            AUTHOR             NOTE
 * —————————————————————————————
 * 2023-10-17         GGG          최초 생성
 */
@Slf4j
@RestController
public class DeptController {
    @Autowired
    DeptService deptService;    // DI

    /** 전체 조회 + 정렬(dno 내림차순) */
    @GetMapping("/dept/desc")
    public ResponseEntity<Object> getDeptAllDesc(){
        try {
            // todo : 전체 조회 + 정렬(dno 내림차순)함수 호출
            List<Dept> list = deptService.findAllByOrderByDnoDesc();
            if (list.isEmpty() == false) {
//                성공
                return new ResponseEntity<>(list, HttpStatus.OK);
            } else {
//                데이터 없음
                return new ResponseEntity<>(HttpStatus.NO_CONTENT);
            }
        } catch (Exception e) {
            log.debug(e.getMessage());
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }
}

 

✔ 서버 재시작 후 API 테스트 

부서번호가 내림차순으로 정렬된 것을 알 수 있습니다.

1) 쿼리메소드 : 함수이름으로 sql문 생성

예) findAllByOrderByDnoDesc

findAllByOrderByDnoDesc
findAll	전체조회
OrderBy	정렬
Dno	대상컬럼명
Desc	내림차순

 

💡 JPA의 쿼리메소드 샘플은 다음과 같습니다.

출처:https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods         

Distinct	findDistinctByLastnameAndFirstname	select distinct …​ where x.lastname = ?1 and x.firstname = ?2
And	findByLastnameAndFirstname	… where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	… where x.lastname = ?1 or x.firstname = ?2
Is, Equals	findByFirstname,findByFirstnameIs,findByFirstnameEquals	… where x.firstname = ?1
Between	findByStartDateBetween	… where x.startDate between ?1 and ?2
LessThan	findByAgeLessThan	… where x.age < ?1
LessThanEqual	findByAgeLessThanEqual	… where x.age <= ?1
GreaterThan	findByAgeGreaterThan	… where x.age > ?1
GreaterThanEqual	findByAgeGreaterThanEqual	… where x.age >= ?1
After	findByStartDateAfter	… where x.startDate > ?1
Before	findByStartDateBefore	… where x.startDate < ?1
IsNull, Null	findByAge(Is)Null	… where x.age is null
IsNotNull, NotNull	findByAge(Is)NotNull	… where x.age not null
Like	findByFirstnameLike	… where x.firstname like ?1
NotLike	findByFirstnameNotLike	… where x.firstname not like ?1
StartingWith	findByFirstnameStartingWith	… where x.firstname like ?1 (parameter bound with appended %)
EndingWith	findByFirstnameEndingWith	… where x.firstname like ?1 (parameter bound with prepended %)
Containing	findByFirstnameContaining	… where x.firstname like ?1 (parameter bound wrapped in %)
OrderBy	findByAgeOrderByLastnameDesc	… where x.age = ?1 order by x.lastname desc
Not	findByLastnameNot	… where x.lastname <> ?1
In	findByAgeIn(Collection<Age> ages)	… where x.age in ?1
NotIn	findByAgeNotIn(Collection<Age> ages)	… where x.age not in ?1
True	findByActiveTrue()	… where x.active = true
False	findByActiveFalse()	… where x.active = false
IgnoreCase	findByFirstnameIgnoreCase	… where UPPER(x.firstname) = UPPER(?1)

✅ TODO 2) 전체 조회 + 정렬(내림차순) - 부서명으로 내림차순, 부서번호 오름차순

📂 DeptRepository.java 추가

    // todo: 2) 전체조회 + 정렬(부서명 내림차순)
    List<Dept> findAllByOrderByDnameDesc();

    // todo: 3) 전체조회 + 정렬(부서번호 오름차순) : Asc 생략가능
    List<Dept> findAllByOrderByDno();
}

📂 DeptService.java 추가

/** 전체조회 + 정렬(dname 내림차순) : 쿼리메소드 */
public List<Dept> findAllByOrderByDnameDesc(){
    List<Dept> list = deptRepository.findAllByOrderByDnameDesc();
    return list;
}

/** 전체조회 + 정렬(dno 오름차순) : 쿼리메소드 */
public List<Dept> findAllByOrderByDno(){
    List<Dept> list = deptRepository.findAllByOrderByDno();
    return list;
}

📂 DeptController.java 추가

 /** 전체 조회 + 정렬(dname 내림차순) */
    @GetMapping("/dept/dname/desc")
    public ResponseEntity<Object> findAllByOrderByDnameDesc(){
        try {
            // todo : 전체 조회 + 정렬(dno 내림차순)함수 호출
            List<Dept> list = deptService.findAllByOrderByDnameDesc();
            if (list.isEmpty() == false) {
//                성공
                return new ResponseEntity<>(list, HttpStatus.OK);
            } else {
//                데이터 없음
                return new ResponseEntity<>(HttpStatus.NO_CONTENT);
            }
        } catch (Exception e) {
            log.debug(e.getMessage());
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }
    /** 전체 조회 + 정렬(dno 오름차순) */
    @GetMapping("/dept/dno/asc")
    public ResponseEntity<Object> findAllByOrderByDno(){
        try {
            // todo : 전체 조회 + 정렬(dno 내림차순)함수 호출
            List<Dept> list = deptService.findAllByOrderByDno();
            if (list.isEmpty() == false) {
//                성공
                return new ResponseEntity<>(list, HttpStatus.OK);
            } else {
//                데이터 없음
                return new ResponseEntity<>(HttpStatus.NO_CONTENT);
            }
        } catch (Exception e) {
            log.debug(e.getMessage());
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }

✔ 서버 재시작 후 API 테스트 

1) 전체조회 - 부서이름으로 내림차순

2) 전체조회 - 부서번호 오름차순

✅ TODO 3) 전체조회 + dname(부서명) like 검색 + dname 내림차순 조회

📂 DeptRepository.java 추가

// todo: 4) dname like 검색(DnameContaining)+ dname 내림차순 조회
List<Dept> findAllByDnameContainingOrderByDnameDesc(String dname);

📂 DeptService.java 추가

/** dname(부서명) like 검색 + dname 내림차순 조회 */
public List<Dept> findAllByDnameContainingOrderByDnameDesc(String dname){
    List<Dept> list 
            = deptRepository.findAllByDnameContainingOrderByDnameDesc(dname);
    return list;
}

📂 DeptController.java 추가

 /** 전체조회 + dname(부서명) like 검색 + dname 내림차순 조회 */
    @GetMapping("/dept/dname/containing/desc/{dname}")
    public ResponseEntity<Object> findAllByDnameContainingOrderByDnameDesc(
            @PathVariable String dname
    ){
        try {
            // todo : 전체 조회 + 정렬(dno 내림차순)함수 호출
            List<Dept> list
                    = deptService
                    .findAllByDnameContainingOrderByDnameDesc(dname);
            if (list.isEmpty() == false) {
//                성공
                return new ResponseEntity<>(list, HttpStatus.OK);
            } else {
//                데이터 없음
                return new ResponseEntity<>(HttpStatus.NO_CONTENT);
            }
        } catch (Exception e) {
            log.debug(e.getMessage());
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }

✔ 서버 재시작 후 API 테스트 

1) 전체조회 - 부서명 like 검색 + dname 내림차순 조회

✅ TODO 4) 쿼리메소드 응용 연습문제

// todo) 응용연습
//    todo: 응용 연습문제 
//    todo: 연습 4) EMP 테이블에서 Job 이 manager 이고
//              매개변수로 부서번호(dno)를 받는 함수를 작성하세요.

//    todo: 연습 5) Emp 테이블에서 salary 가 1000 ~ 1500 사이의 값을 같는
//     사원을 조회하려고 합니다.  함수를 작성해 주세요

📂 EmpRepository.java

 

List<Emp> findAllByJobAndDno(String job, int dno);

List<Emp> findAllBySalaryBetween(int first, int last);

📂 EmpService.java

//    todo: 연습 4) EMP 테이블에서 Job 이 manager 이고
//              매개변수로 부서번호(dno)를 받는 함수를 작성하세요.
public List<Emp> findAllByJobAndDno(String job, int dno){
    List<Emp> list = empRepository.findAllByJobAndDno(job, dno);
    return list;
}

//    todo: 연습 5) Emp 테이블에서 salary 가 1000 ~ 1500 사이의 값을 같는
//     사원을 조회하려고 합니다.  함수를 작성해 주세요
public List<Emp> findAllBySalaryBetween(int first, int last){
    List<Emp> list = empRepository.findAllBySalaryBetween(first, last);
    return list;
}

📂 EmpController.java

  /** 연습 4) : 쿼리메소드 */
    @GetMapping("/emp/dno/{dno}")
    public ResponseEntity<Object> findAllByEnameContaining(
            @PathVariable int dno
    ) {
        try {
//         전체 조회 + 정렬(dno 내림차순) 호출
            List<Emp> list
                    = empService.findAllByJobAndDno("MANAGER", dno);

            if (list.isEmpty() == false) {
//                성공
                return new ResponseEntity<>(list, HttpStatus.OK);
            } else {
//                데이터 없음
                return new ResponseEntity<>(HttpStatus.NO_CONTENT);
            }

        } catch (Exception e){
            log.debug(e.getMessage());
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }

    /** 연습5 */

    @GetMapping("emp/salary/{first}/{last}")
    public ResponseEntity<Object> findAllBySalaryBetween(@PathVariable int first, @PathVariable int last){
        try {
            List<Emp> list
                    = empService.findAllBySalaryBetween(first, last);
            if(list.isEmpty() == false){
                return new ResponseEntity<>(list, HttpStatus.OK);
            } else {
                return new ResponseEntity<>(HttpStatus.NO_CONTENT);
            }

        } catch (Exception e) {
            log.debug(e.getMessage());
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }

✔ 서버 재시작 후 API 테스트 

연습 4)

 

연습 5)

[
  {
    "insertTime": "2023-10-17 06:38:45",
    "updateTime": null,
    "eno": 7521,
    "ename": "WARD",
    "job": "SALESMAN",
    "manager": 7698,
    "hiredate": "1981-02-22 00:00:00",
    "salary": 1250,
    "commission": 500,
    "dno": 30
  },
  {
    "insertTime": "2023-10-17 06:38:45",
    "updateTime": null,
    "eno": 7654,
    "ename": "MARTIN",
    "job": "SALESMAN",
    "manager": 7698,
    "hiredate": "1981-09-28 00:00:00",
    "salary": 1250,
    "commission": 1400,
    "dno": 30
  },
  {
    "insertTime": "2023-10-17 06:38:45",
    "updateTime": null,
    "eno": 7844,
    "ename": "TURNER",
    "job": "SALESMAN",
    "manager": 7698,
    "hiredate": "1981-09-08 00:00:00",
    "salary": 1500,
    "commission": 0,
    "dno": 30
  },
  {
    "insertTime": "2023-10-17 06:38:45",
    "updateTime": null,
    "eno": 7876,
    "ename": "ADAMS",
    "job": "CLERK",
    "manager": 7788,
    "hiredate": "1987-07-13 00:00:00",
    "salary": 1100,
    "commission": null,
    "dno": 20
  },
  {
    "insertTime": "2023-10-17 06:38:45",
    "updateTime": null,
    "eno": 7934,
    "ename": "MILLER",
    "job": "CLERK",
    "manager": 7782,
    "hiredate": "1982-01-23 00:00:00",
    "salary": 1300,
    "commission": null,
    "dno": 10
  }
]
응답 파일이 저장되었습니다.
> 2023-10-17T153953.200.json

Response code: 200; Time: 25ms (25 ms); Content length: 933 bytes (933 B)

💡 @Query 이용한 쿼리문 작성

데이터베이스에서 값을 가져올 때는 위의 코드처럼 메서드의 이름만으로 쿼리 메서드를 생성할 수 도 있습니다.

이번에는 @Query 어노테이션을 사용해 직접 JPQL을 작성해봅시다.

 

📂 DeptRepository.java

    //    ----------------------------------------
    //    @Query 예제
    //    1) 오라클 쿼리
    //    2) 객체 쿼리
    //    ----------------------------------------
    //    todo 1) ename like 검색
    @Query(value = "SELECT TD.* FROM TB_EMP TD WHERE TD.ENAME LIKE '%' || :dname || '%'", nativeQuery = true)
    List<Emp> selectByEname(@Param("ename") String ename);

위 코드를 다르게 작성하는 방법(JDK 버전에 따라 사용 불가능할 수도 있음)

//    todo: 1-1) 위의 코드를 다르게 코딩
    @Query(value = "SELECT TD.* FROM TB_DEPT TD WHERE TD.DNAME LIKE '%'||:dname||'%'",
            nativeQuery = true)
    List<Dept> selectByDname(String dname);

//    todo: 1-2) 위의 코드를 다르게 코딩 : 참고
@Query(value = "SELECT TD.* FROM TB_DEPT TD WHERE TD.DNAME LIKE '%'|| ?1 ||'%'",
        nativeQuery = true)
List<Dept> selectByDname(String dname);

✅ SQL에서 쿼리문 결과가 올바른지 확인

📂 DeptService.java

/** 전체조회 + dname like 검색 : @Query */
public List<Dept> selectByDname(String dname) {
    List<Dept> list
            = deptRepository.selectByDname(dname);

    return list;
}

📂 DeptController.java

 /** 전체조회 + dname like 검색 : @Query */
    @GetMapping("/dept/dname/{dname}")
    public ResponseEntity<Object> selectByDname(
            @PathVariable String dname
    ) {
        try {
//         전체 조회 + 정렬(dno 오름차순) 호출
            List<Dept> list
                    = deptService.selectByDname(dname);

            if (list.isEmpty() == false) {
//                성공
                return new ResponseEntity<>(list, HttpStatus.OK);
            } else {
//                데이터 없음
                return new ResponseEntity<>(HttpStatus.NO_CONTENT);
            }

        } catch (Exception e){
            log.debug(e.getMessage());
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }

✅ 실행결과

부서명 중에 'S'가 들어가는 부서명 검색하기

GET http://localhost:8000/dept/dname/S

HTTP/1.1 200 
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 18 Oct 2023 00:11:16 GMT
Keep-Alive: timeout=60
Connection: keep-alive

[
  {
    "insertTime": "2023-10-18 00:11:06",
    "updateTime": null,
    "dno": 20,
    "dname": "RESEARCH",
    "loc": "DALLAS"
  },
  {
    "insertTime": "2023-10-18 00:11:06",
    "updateTime": null,
    "dno": 30,
    "dname": "SALES",
    "loc": "CHICAGO"
  },
  {
    "insertTime": "2023-10-18 00:11:06",
    "updateTime": null,
    "dno": 40,
    "dname": "OPERATIONS",
    "loc": "BOSTON"
  }
]
응답 파일이 저장되었습니다.
> 2023-10-18T091116.200.json

Response code: 200; Time: 277ms (277 ms); Content length: 295 bytes (295 B)

✅ 객체쿼리를 이용한 쿼리문 작성

//    todo: 2) dname like 검색 (객체 쿼리)
    // 객체 쿼리 만드는 방법 : 테이블 -> 클래스명
    //                      컬럼명 -> 속성명
    //                      * -> 사용하지 않음, 클래스명의 별칭을 붙여서 사용
    //                      (대소문자를 구분함, nativeQuery=true(생략))
    @Query(value = "SELECT TD FROM Dept TD WHERE TD.dname LIKE '%'||:dname||'%'")
    List<Dept> selectByDname(@Param("dname") String dname);

 

✅ TODO) 부서 테이블에서 부서명(dname)과 위치(loc)를 매개변수로 받아 조회하는 함수 만들기

📂 DeptRepository.java

@Query(value = "SELECT D.* FROM TB_DEPT D " +
        "WHERE D.DNAME = :dname " +
        "AND   D.LOC = :loc ", nativeQuery = true)
List<Dept> selectByDnameAndLoc(@Param("dname") String dname,@Param("loc") String loc);

📂 DeptService.java

/** 전체조회 + dname like 검색 : @Query */
public List<Dept> selectByDnameAndLoc(String dname, String loc) {
    List<Dept> list
            = deptRepository.selectByDnameAndLoc(dname, loc);

    return list;
}

📂 DeptController.java

    /** 전체조회 + dname like 검색 : @Query */
    @GetMapping("/dept/dname/{dname}/loc/{loc}")
    public ResponseEntity<Object> selectByDnameAndLoc(
            @PathVariable String dname,
            @PathVariable String loc
    ) {
        try {
//         전체 조회 + 정렬(dno 오름차순) 호출
            List<Dept> list
                    = deptService.selectByDnameAndLoc(dname, loc);

            if (list.isEmpty() == false) {
//                성공
                return new ResponseEntity<>(list, HttpStatus.OK);
            } else {
//                데이터 없음
                return new ResponseEntity<>(HttpStatus.NO_CONTENT);
            }

        } catch (Exception e){
            log.debug(e.getMessage());
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }