여기 단원은 참고하기
환경설정
- spring security는 로그인 할때만 하기
- 한글 설정 : UTF-8
- build.gradle
- application.properties
- log4jdbc.log 에서 이름 수정
쿼리메소드와 @Query는 같다. 그 두 가지의 방법을 알아보는 시간
1. 쿼리메소드 : 함수명으로 sql을 작성하는 방법
entity - exam01 - Dept
속성 만들기
@Entity
@Table(name="TB_DEPT")
@SequenceGenerator(
name = "SQ_DEPT_GENERATOR"
, sequenceName = "SQ_DEPT"
, initialValue = 1
, allocationSize = 1)
@Getter
@Setter
@ToString
@NoArgsConstructor
@AllArgsConstructor
@DynamicInsert
@DynamicUpdate
public class Dept extends BaseTimeEntity {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE
, generator = "SQ_DEPT_GENERATOR")
private Integer dno; // 기본키, 시퀀스
private String dname;
private String loc;
}
DeptRepository
ex) findAllByOrderByDnoDesc
- findAll : 전체조회 : find(select) + All(*)
- OrderBy : 정렬
- Dno : 속성(필드) = 대상컬럼명
- Desc : 내림차순 / Asc : 오름차순
- 단어와 단어 사이는 대문자로 작성(대소문자 구분)
1) 전체 조회 + 정렬(내림차순)
JpaRepository 에서 상속받아서, List 배열로 findAllByOrderByDnoDesc라는 함수를 만듬
이 함수의 리턴값이 < > 로 들어가는 것
@Repository
public interface DeptRepository extends JpaRepository<Dept, Integer> {
// TODO: (참고) 쿼리메소드 == @Query
// TODO: 1) 전체 조회 + 정렬(내림차순)
List<Dept> findAllByOrderByDnoDesc();
}
함수명에서 오타나면 에러뜸!
findAllByOrderByDnoDesc
DeptSerivce
@Service
public class DeptService {
@Autowired
DeptRepository deptRepository;
// TODO: 쿼리메소드 예제
public List<Dept> findAllByOrderByDnoDesc(){
List<Dept> list = deptRepository.findAllByOrderByDnoDesc();
return list;
}
}
DeptController
@Slf4j
@Controller
@RequestMapping("/exam01")
public class DeptController {
@Autowired
DeptService deptService;
// TODO: 쿼리메소드 예제
// 1) 전체 조회 + 정렬(내림차순)
@GetMapping("/dept/desc")
public String getDeptAllDesc(Model model){
// DB 전체 조회 + 정렬 함수 실행
List<Dept> list = deptService.findAllByOrderByDnoDesc();
model.addAttribute("list", list);
return "exam01/dept/query-method/dept_all_desc.jsp";
}
}
exam01 - dept - query-method - dept_all_desc.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<jsp:include page="../../../common/header.jsp"/>
<div class="container">
<%-- 테이블 반복문--%>
<%-- todo: 테이블 반복문 시작--%>
<table class="table">
<tbody>
<c:forEach var="data" items="${list}">
<tr>
<td>${data.dno}</td>
<td>${data.dname}</td>
<td>${data.loc}</td>
</tr>
</c:forEach>
</tbody>
</table>
<%-- todo: 테이블 반복문 끝--%>
</div>
<jsp:include page="../../../common/footer.jsp"/>
</body>
</html>
결과값 : http://localhost:8000/exam01/dept/desc
2. @Query : sql 직접 작성하는 방법 (쿼리 = sql)
1) 오라클 쿼리
DeptRepository
위의 쿼리메소드를 오라클 쿼리로 바꾼것
sql-developer에서 작성하고 ;를 빼고 복사
dname 에 포함되어 있는 단어로 검색
@Query(value = "SELECT D.* FROM TB_DEPT D\n" +
"WHERE D.DNAME LIKE '%' || :dname || '%'"
,nativeQuery = true)
List<Dept> selectByDname(@Param("dname") String dname);
DeptService
// 1) dname like 검색 : 오라클 sql
public List<Dept> selectByDname(String dname) {
List<Dept> list
= deptRepository.selectByDname(dname);
return list;
DeptController
@GetMapping("/dept/desc/dname")
public String getDeptDnameDesc(Model model){
List<Dept> list = deptService.findAllByOrderByDnameDesc();
model.addAttribute("list", list);
return "exam01/dept/query-method/dept_dname_desc.jsp";
}
dept_dname_desc.jsp
반복문 복사
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<jsp:include page="../../../common/header.jsp"/>
<div class="container">
<%-- 테이블 반복문--%>
<%-- todo: 테이블 반복문 시작--%>
<table class="table">
<tbody>
<c:forEach var="data" items="${list}">
<tr>
<td>${data.dno}</td>
<td>${data.dname}</td>
<td>${data.loc}</td>
</tr>
</c:forEach>
</tbody>
</table>
<%-- todo: 테이블 반복문 끝--%>
</div>
<jsp:include page="../../../common/footer.jsp"/>
</body>
</html>2) 객체 쿼리
DeptRepository
1. nativeQuery = true 조건 없애기
2. 테이블 명 대신 -> 자바의 entity명으로 바꾸기 : entity는 대소문자 구분함
3. 컬럼명은 성명으로 바꾸기 -> D.*에서 *는 없어짐(속성이니까 *를 쓰지 않음. 쓰면 에러남)
대신 엔티티명의 별명만 작성
dname으로 우리가 입력할 값이 있으니까, 매개변수를 괄호안에 적어야 함
@Param 으로 안적으면, 스프링이 이게 매개변수인지 인식을 못하기 떄문에
적어줘야 스프링이 container에 올릴 수 있음
@Query(value = "SELECT D FROM Dept D\n" +
"WHERE D.dname LIKE '%' || :dname || '%'")
List<Dept> selectByDname2(@Param("dname") String dname);
DeptService
우리가 매개변수로 적는 dname이 있어서 괄호안에 적어야 함
매개변수로 들어가는 값이 없으면 괄호안에 비워놔도 됨
// 2) dname like 검색 : 객체 sql(JPQL)
public List<Dept> selectByDname2(String dname) {
List<Dept> list
= deptRepository.selectByDname2(dname);
return list;
}
DeptController
@GetMapping("/dept/dname/containing/{dname}")
public String getDeptDnameContaining(Model model, @PathVariable String dname){
List<Dept> list2 = deptService.selectByDname2(dname);
model.addAttribute("list2", list2);
return "exam01/dept/query-method/dept_containing_desc.jsp";
}
dept_dname_desc.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<jsp:include page="../../../common/header.jsp"/>
<div class="container">
<%-- 테이블 반복문--%>
<%-- todo: 테이블 반복문 시작--%>
<table class="table">
<tbody>
<c:forEach var="data" items="${list}">
<tr>
<td>${data.dno}</td>
<td>${data.dname}</td>
<td>${data.loc}</td>
</tr>
</c:forEach>
</tbody>
</table>
<%-- todo: 테이블 반복문 끝--%>
</div>
<jsp:include page="../../../common/footer.jsp"/>
</body>
</html>
연습문제
EmpRepository
@Repository
public interface EmpRepository extends JpaRepository<Emp, Integer> {
// 연습 : 1) @Query 사용 : 오라클 sql 사용
// Emp 테이블에서 salary 내림차순, ename 오름차순으로 정렬해서 전체조회하세요
@Query(value="SELECT E.* FROM TB_EMP E\n" +
"ORDER BY E.SALARY DESC, E.ENAME ASC"
, nativeQuery = true)
List<Emp> selectBySalaryEname();
// 연습 : 2) @Query 사용 : 오라클 sql 사용
// commission 이 null 이고
//// salary 값보다 같거나 큰 사원 정보를 모두 출력하세요.
@Query(value="SELECT E.* FROM TB_EMP E\n" +
"WHERE COMMISSION IS NULL\n" +
"AND SALARY >= :salary"
, nativeQuery = true)
List<Emp> selectByCommissionSalary(@Param("salary") Integer salary);
EmpService
@Service
public class EmpService {
@Autowired
EmpRepository empRepository;
// 연습 1)
public List<Emp> selectBySalaryEname(){
List<Emp> list = empRepository.selectBySalaryEname();
return list;
}
public List<Emp> selectByCommissionSalary(Integer salary){
List<Emp> list = empRepository.selectByCommissionSalary(salary);
return list;
}
EmpController
@Slf4j
@Controller
@RequestMapping("/exam01")
public class EmpController {
@Autowired
EmpService empService;
@GetMapping("/emp/salary/ename")
public String selectBySalaryEname(Model model){
List<Emp> list = empService.selectBySalaryEname();
model.addAttribute("list", list);
return "exam01/emp/query/salary_ename.jsp";
}
@GetMapping("/emp/null/salary/{salary}")
public String selectByCommissionSalary(Model model, @PathVariable Integer salary){
List<Emp> list = empService.selectByCommissionSalary(salary);
model.addAttribute("list", list);
return "exam01/emp/query/null_salary.jsp";
}
jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<jsp:include page="../../../common/header.jsp"/>
<div class="container">
<%-- 테이블 반복문--%>
<%-- todo: 테이블 반복문 시작--%>
<table class="table">
<tbody>
<c:forEach var="data" items="${list}">
<tr>
<td>${data.eno}</td>
<td>${data.ename}</td>
<td>${data.job}</td>
<td>${data.manager}</td>
<td>${data.hiredate}</td>
<td>${data.salary}</td>
<td>${data.commission}</td>
<td>${data.dno}</td>
</tr>
</c:forEach>
</tbody>
</table>
<%-- todo: 테이블 반복문 끝--%>
</div>
<jsp:include page="../../../common/footer.jsp"/>
</body>
</html>Dto
entity 에 담으려면 sql 결과와 entity의 속성명과 컬럼명이 같아야 한다.
근데 그룹함수를 쓰면 같을 수 없다.
그래서 이런 이름을 가진 클래스나 인터페이스를 만들어야 함 = Dto
방법 1) 인터페이스로 만드는 법 ★
- 오라클 쿼리를 사용하면 무조건 인터페이스 Dto를 사용해야 함
= 인터페이스 형태로 사용해야 함
방법 2) 클래스로 만드는 법
- 잘 쓰지는 않는데 나중에 배울 예정
sql-developer
SUM(DNO) 등등 컬럼이 TB_DEPT 테이블 컬럼에 없음!
AS sumVar 처럼 별명을 쓰지 않으면 에러가 남. 대신 별명은 우리가 정하면
SELECT SUM(DNO) AS sumVar
, AVG(DNO) AS avgVar
, MIN(DNO) AS minVar
, MAX(DNO) AS maxVar
FROM TB_DEPT;
model - dto - DeptGroupDto : 오라클 sql 의 결과를 담을 DTO 인터페이스
1. 그룹 함수 결과로 : sumVar, avgVar, maxVar, minVar
=> Getter 함수 형태로 만들어야 함
=> getSumVar, getAvgVar, getMaxVar, getMinVar
2. DTO 디자인 패턴
1) sql 결과와 entity 클래스의 속성명이 다를경우 : 테이블의 컬럼명과 entity의 속성명이 다를 때
2) sql 결과는 같으나 일부 속성(필드) 을 화면에 표시하기 싫을 때(보안 목적)
=> entity 에 결과를 담았음
=> (참고) 일부 회사에서는 무조건 DTO 클래스 만들어서 사용하도록 권고
public interface DeptGroupDto {
// getSumVar(), getAvgVar(), getMaxVar(), getMinVar()
Integer getSumVar();
Integer getAvgVar();
Integer getMaxVar();
Integer getMinVar();
}
우리가 값을 입력하면, JPA에서 자동으로 자식클래스를 만들어서 결과를 보내줌
DeptRepository
// 예제 3) 부서테이블의 부서번호를 sum, avg, max, min 값을
// 출력하는 함수를 작성하세요.
// 단, nativeQuery = true 사용하세요
@Query(value="SELECT SUM(DNO) AS sumVar\n" +
" , AVG(DNO) AS avgVar\n" +
" , MIN(DNO) AS minVar\n" +
" , MAX(DNO) AS maxVar\n" +
"FROM TB_DEPT" , nativeQuery = true)
List<DeptGroupDto> selectByGroupFunc();
DeptService
DeptRepository와 DeptController를 연결
public List<DeptGroupDto> selectByGroupFunc() {
List<DeptGroupDto> list
= deptRepository.selectByGroupFunc();
return list;
}
DeptController
매개변수 없음
@GetMapping("/dept/groupfunc")
public String selectByGroupFunc(
Model model
) {
// DB 서비스 함수 실행
List<DeptGroupDto> list
= deptService.selectByGroupFunc();
model.addAttribute("list", list);
return "exam01/dept/query/dept_group.jsp";
}
dept_group.jsp
<%--
Created by IntelliJ IDEA.
User: GGG
Date: 2024-03-29
Time: 오전 10:37
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<%--머리말 --%>
<jsp:include page="../../../common/header.jsp"/>
<%--본문 --%>
<div class="container">
<%-- 테이블 반복문--%>
<%-- todo: 테이블 반복문 시작--%>
<table class="table">
<tbody>
<c:forEach var="data" items="${list}">
<tr>
<td>${data.dno}</td>
<td>${data.job}</td>
<td>${data.salary}</td>
</tr>
</c:forEach>
</tbody>
</table>
<%-- todo: 테이블 반복문 끝--%>
</div>
<%--꼬리말 --%>
<jsp:include page="../../../common/footer.jsp"/>
</body>
</html>
연습문제 : dno, job 별 월급여의 합을 출력하는 함수를 작성하세요.(tb_emp)
// nativeQuery = true 사용,
// dto : EmpGroupDto
별명은 어떻게 붙여도 상관 없음
SELECT DNO, JOB, SUM(SALARY) AS salary
FROM TB_EMP E
GROUP BY DNO, JOB;
EmpGroupDto
컬럼들을 전부 get을 붙여서 Getter함수의 형태로 만들어놓아야 함
public interface EmpGroupDto {
// DNO, JOB, SUM(SALARY) AS salary
Integer getDno();
String getJob();
Integer getSalary();
}
EmpRepository
@Query(value = "SELECT DNO, JOB, SUM(SALARY) AS salary\n" +
"FROM TB_EMP\n" +
"GROUP BY DNO, JOB",
nativeQuery = true)
List<EmpGroupDto> selectGroupDnoJob();
}
EmpService
public List<EmpGroupDto> selectGroupDnoJob() {
List<EmpGroupDto> list
= empRepository.selectGroupDnoJob();
return list;
}
EmpController
@GetMapping("/emp/group")
public String selectGroupDnoJob(
Model model) {
// Db 서비스 함수 실행
List<EmpGroupDto> list
= empService.selectGroupDnoJob();
// jsp 전송
model.addAttribute("list", list);
return "exam01/emp/query/emp_group.jsp";
}
emp_group.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<%--머리말 --%>
<jsp:include page="../../../common/header.jsp"/>
<%--본문 --%>
<div class="container">
<%-- 테이블 반복문--%>
<%-- todo: 테이블 반복문 시작--%>
<table class="table">
<tbody>
<c:forEach var="data" items="${list}">
<tr>
<td>${data.dno}</td>
<td>${data.job}</td>
<td>${data.salary}</td>
</tr>
</c:forEach>
</tbody>
</table>
<%-- todo: 테이블 반복문 끝--%>
</div>
<%--꼬리말 --%>
<jsp:include page="../../../common/footer.jsp"/>
</body>
</html>
- 쿼리
1. 쿼리 메소드
2. @Query
1) 오라클 쿼리
2) 객체 쿼리
- DTO
'SpringBoot' 카테고리의 다른 글
| #66 [Vue] Node.js 설치, axios library 설치 (0) | 2024.04.01 |
|---|---|
| #66 [JPA] 오라클 쿼리로 조인 / JPA 어노테이션으로 조인(단방향, 양방향) (0) | 2024.04.01 |
| #64 [JPA] 회원가입, 로그인 / 로그아웃 / 관리자 버튼 변경 (0) | 2024.03.28 |
| #64 [JPA] 회원가입 (0) | 2024.03.28 |
| #64 [JPA] 로그아웃 (#3) (0) | 2024.03.28 |
