구디아카데미/JAVA
1101 [Sakila] staffOne
COCO_develop
2024. 11. 1. 17:49
Staff 관련 데이터 모델
쿼리
SELECT
a.address_id
, a.address
, a.address2
, a.district
, a.postal_code
, a.phone
, ci.city
, co.country
FROM address a INNER JOIN city ci
ON a.city_id = ci.city_id
INNER JOIN country co
ON ci.country_id = co.country_idcountry, city를 알기위한 것
SELECT s.*,t1.*
FROM store s INNER JOIN (SELECT
a.address_id addressId
, a.address address
, a.address2
, a.district district
, a.postal_code postalCode
, a.phone phone
, ci.city city
, co.country country
FROM address a INNER JOIN city ci
ON a.city_id = ci.city_id
INNER JOIN country co
ON ci.country_id = co.country_id) t1
ON s.address_id = t1.addressIdstore 상세 주소
SELECT *
FROM
staff st INNER JOIN (SELECT s.*,t1.*
FROM store s INNER JOIN (SELECT
a.address_id addressId
, a.address address
, a.address2
, a.district district
, a.postal_code postalCode
, a.phone phone
, ci.city city
, co.country country
FROM address a INNER JOIN city ci
ON a.city_id = ci.city_id
INNER JOIN country co
ON ci.country_id = co.country_id) t1
ON s.address_id = t1.addressId) t2
ON st.store_id = t2.store_idstaff 담당 매니저(상사?)
SELECT t2.*, ms.username
FROM
staff ms INNER JOIN (SELECT s.*,t1.*
FROM store s INNER JOIN (SELECT
a.address_id addressId
, a.address address
, a.address2
, a.district district
, a.postal_code postalCode
, a.phone phone
, ci.city city
, co.country country
FROM address a INNER JOIN city ci
ON a.city_id = ci.city_id
INNER JOIN country co
ON ci.country_id = co.country_id) t1
ON s.address_id = t1.addressId) t2
ON ms.staff_id = t2.manager_staff_idms = manager staff
매니저 정보 조회
SELECT *
FROM
staff s INNER JOIN
(SELECT t2.*, ms.username mgrUsername
FROM
staff ms INNER JOIN (SELECT s.*,t1.*
FROM store s INNER JOIN (SELECT
a.address_id addressId
, a.address address
, a.address2
, a.district district
, a.postal_code postalCode
, a.phone phone
, ci.city city
, co.country country
FROM address a INNER JOIN city ci
ON a.city_id = ci.city_id
INNER JOIN country co
ON ci.country_id = co.country_id) t1
ON s.address_id = t1.addressId) t2
ON ms.staff_id = t2.manager_staff_id) t3
ON s.store_id = t3.store_id
SELECT t4.staff_id staffId
, t4.first_name firstName
, t4.last_name lastName
, t5.address staffAddress
, t5.address2 staffAddress2
, t5.district staffDistrict
, t5.postalCode staffPostCode
, t5.phone staffPhone
, t5.city staffCity
, t5.country staffCountry
, t4.store_id storeId
, t4.manager_staff_id managerStaffId
, t4.manager_name managerName
, t4.address storeAddress
, t4.address2 storeAddress2
, t4.district storeDistrict
, t4.postal_code storePostCode
, t4.phone storePhone
, t4.city storeCity
, t4.country storeCountry
FROM (SELECT s.staff_id
, s.first_name
, s.last_name
, s.address_id
, s.email
, s.username
, s.store_id
, t3.manager_staff_id
, t3.manager_name
, t3.address
, t3.address2
, t3.district
, t3.postal_code
, t3.phone
, t3.city
, t3.country
FROM staff s
INNER JOIN (SELECT t2.store_id
, t2.manager_staff_id
, ms.username manager_name
, t2.address
, t2.address2
, t2.district
, t2.postal_code
, t2.phone
, t2.city
, t2.country
FROM staff ms
INNER JOIN (SELECT s.store_id store_id
, s.manager_staff_id
, s.address_id
, t1.address
, t1.address2
, t1.district
, t1.postal_code
, t1.phone
, t1.city
, t1.country
FROM store s
INNER JOIN (SELECT
a.address_id
, a.address
, a.address2
, a.district
, a.postal_code
, a.phone
, ci.city
, co.country
FROM address a INNER JOIN city ci
ON a.city_id = ci.city_id
INNER JOIN country co
ON ci.country_id = co.country_id) t1
ON s.address_id = t1.address_id) t2
ON ms.staff_id = t2.manager_staff_id) t3
ON s.store_id = t3.store_id) t4
INNER JOIN (SELECT
a.address_id addressId
, a.address address
, a.address2
, a.district district
, a.postal_code postalCode
, a.phone phone
, ci.city city
, co.country country
FROM address a INNER JOIN city ci
ON a.city_id = ci.city_id
INNER JOIN country co
ON ci.country_id = co.country_id) t5
ON t4.address_id=t5.addressId
WHERE t4.staff_id=1;Chat GPT
특정 직원(staff_id = 1)의 정보와 해당 직원이 속한 매장(store) 및 매니저(manager)의 세부 정보를 조회하는 쿼리입니다. 쿼리의 구조를 분석해 보면 아래와 같은 계층적인 데이터를 가져오도록 구성되어 있습니다:
- 직원 정보 조회:
- t4 서브쿼리에서 직원의 기본 정보(이름, address_id, store_id 등)와 직원이 속한 매장과 매니저 정보까지 포함하여 가져옵니다.
- 매장 정보와 매니저 정보 결합:
- t3와 t2 서브쿼리를 통해 매장의 store_id, manager_staff_id, 매니저 이름 등을 조회하여 t4에 결합합니다.
- 주소 정보 결합:
- t1 서브쿼리에서 주소(address 테이블)와 관련된 시(city)와 국가(country) 정보를 결합하여 t2와 함께 사용합니다.
- 직원의 개인 주소 정보와 매장 주소 정보 조회:
- t5 서브쿼리는 직원의 개인 주소 정보(address_id)와 연결된 시(city) 및 국가(country) 정보를 조회합니다.
- 최종 결합 및 필터링:
- 모든 정보를 staff_id=1 조건에 따라 필터링하여 특정 직원의 정보를 가져옵니다.
이 쿼리는 복잡한 구조의 테이블을 사용하여 하나의 SQL 쿼리로 다양한 정보를 결합하는 예시로, 특정 직원의 업무 관련 상세 정보를 한 번에 조회할 수 있는 방식입니다.
StaffMapper.xml (login부분은 생략)
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.sakila.mapper.StaffMapper">
<select id="selectStaffOne" parameterType="int" resultType="map">
<!-- join 시 resultType은 java.util.Map -->
SELECT t4.staff_id staffId
, t4.first_name firstName
, t4.last_name lastName
, t5.address staffAddress
, t5.address2 staffAddress2
, t5.district staffDistrict
, t5.postalCode staffPostCode
, t5.phone staffPhone
, t5.city staffCity
, t5.country staffCountry
, t4.store_id storeId
, t4.manager_staff_id managerStaffId
, t4.manager_name managerName
, t4.address storeAddress
, t4.address2 storeAddress2
, t4.district storeDistrict
, t4.postal_code storePostCode
, t4.phone storePhone
, t4.city storeCity
, t4.country storeCountry
FROM (SELECT s.staff_id
, s.first_name
, s.last_name
, s.address_id
, s.email
, s.username
, s.store_id
, t3.manager_staff_id
, t3.manager_name
, t3.address
, t3.address2
, t3.district
, t3.postal_code
, t3.phone
, t3.city
, t3.country
FROM staff s
INNER JOIN (SELECT t2.store_id
, t2.manager_staff_id
, ms.username manager_name
, t2.address
, t2.address2
, t2.district
, t2.postal_code
, t2.phone
, t2.city
, t2.country
FROM staff ms
INNER JOIN (SELECT s.store_id store_id
, s.manager_staff_id
, s.address_id
, t1.address
, t1.address2
, t1.district
, t1.postal_code
, t1.phone
, t1.city
, t1.country
FROM store s
INNER JOIN (SELECT a.address_id
, a.address
, a.address2
, a.district
, a.postal_code
, a.phone
, ci.city
, co.country
FROM address a INNER JOIN city ci
ON a.city_id = ci.city_id
INNER JOIN country co
ON ci.country_id = co.country_id) t1
ON s.address_id = t1.address_id) t2
ON ms.staff_id = t2.manager_staff_id) t3
ON s.store_id = t3.store_id) t4
INNER JOIN (SELECT
a.address_id addressId
, a.address address
, a.address2
, a.district district
, a.postal_code postalCode
, a.phone phone
, ci.city city
, co.country country
FROM address a INNER JOIN city ci
ON a.city_id = ci.city_id
INNER JOIN country co
ON ci.country_id = co.country_id) t5
ON t4.address_id=t5.addressId
WHERE t4.staff_id = #{staffId};
</select>
</mapper>
StaffMapper.java
@Mapper
public interface StaffMapper {
// /on/staffOne.jsp
Map<String, Object> selectStaffOne(int staffId);
// /off/login.jsp
Staff login(Staff staff);
}여러 값을 받기 위해 Map형태로
StaffController.java
@Slf4j
@Controller
public class StaffController {
@Autowired StaffMapper staffMapper;
@GetMapping("/on/staffOne")
public String staffOne(HttpSession session,Model model) {
int staffId = ((Staff)session.getAttribute("loginStaff")).getStaffId();
Map<String, Object> staff = staffMapper.selectStaffOne(staffId);
model.addAttribute("staff",staff);
log.debug(staff.toString());
return "on/staffOne";
}
}
View -> staffOne.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html>
<html>
<head>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://code.jquery.com/jquery-3.7.1.js" integrity="sha256-eKhayi8LEQwp4NKxN+CfCh+3qOVUtJn3QNZ0TciWLP4=" crossorigin="anonymous"></script>
<meta charset="UTF-8">
<title></title>
</head>
<body class="container-flud">
<div class="row">
<div class="col-sm-2">
<!-- leftMune.jsp include -->
<c:import url="/WEB-INF/view/on/inc/leftMenu.jsp"></c:import>
</div>
<div class="col-sm-10">
<!-- main content -->
<h2>Staff One</h2>
<div>
${staff}
</div>
</div>
</div>
</body>
</html>
