Notice
Recent Posts
Recent Comments
Link
| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | ||||||
| 2 | 3 | 4 | 5 | 6 | 7 | 8 |
| 9 | 10 | 11 | 12 | 13 | 14 | 15 |
| 16 | 17 | 18 | 19 | 20 | 21 | 22 |
| 23 | 24 | 25 | 26 | 27 | 28 | 29 |
| 30 | 31 |
Tags
- 코딩
- CSS
- HTML
- Firebase
- Spring Security
- 배포
- jsp
- ChatGPT
- JavaScript
- github
- 깃허브
- restapi
- jQuery
- java
- bootstrap
- error
- 오블완
- Eclipse
- 스파르타코딩클럽
- vscode
- spring
- 자바
- myBatis
- AJAX
- SQLD
- 이클립스
- 기업설명회
- 티스토리챌린지
- SQL
- 웹개발
Archives
- Today
- Total
푸들푸들
1101 [Sakila] staffOne 본문
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>
'구디아카데미 > JAVA' 카테고리의 다른 글
| Github 잔디 (4) | 2024.11.02 |
|---|---|
| Spring 설치 (1) | 2024.11.02 |
| 1101 [Sakila]로그아웃, 유효성 검사 (3) | 2024.11.01 |
| 1031 [Sakila] Spring 로그인 (1) | 2024.11.01 |
| 1031 Github Token 발급, Commit, Push, 내려받기 (0) | 2024.10.31 |
'구디아카데미/JAVA' Related Articles
more
