[1단계] 마이 셀파 웹 앱 3일차. 프로젝트 DB 구축 및 기능 별 쿼리 작성

*본 글은 필자가 진행한 프로젝트 '마이 셀파'의 작업 일기입니다. 재밌게 봐주세요. 

마이 셀파 리부트 1단계 Spring MVC를 활용한 웹 앱

3일차 작업 프로젝트 DB 구축 및 기능 별 쿼리 작성

오늘은 MySQL DB로 프로젝트에 사용할 DB, TABLE 구축, 데이터 생성 그리고 프로젝트에 쓰일 쿼리문들을 대략적으로 정의하는 작업을 진행하였습니다. SQL을 알고 있다고 가정하기 때문에 잘 모르신다면 저의 DB편의 SQL 포스팅을 보고 오셔도 좋습니다! 참고로 저는 MySQL 워크벤치라는 프로그램을 사용합니다. SQL 유저 생성과 로그인에 대한 부분은 생략하겠습니다. 이제부터 시작하겠습니다.

DB 구축

먼저 MySQL DB에 로그인 후 사용할 DB myselpa를 만들었습니다. 그 후 그 use DB 명령문을 실행하였습니다.

create database myselpa;

use myselpa;

다 아시겠지만 use 명령문으로 사용할 DB를 지정하지 않으면 향후 데이터 조회, 삽입, 삭제 등의 작업을 수행할 수 없습니다. 자 이제 테이블을 만들어보겠습니다. 제가 설계 작업 단계에서 3개의 DB 테이블을 다음과 같이 정의해 두었습니다.

이번에 DB를 구축하면서 조금 바뀌었는데 다음의 테이블들이 그들입니다.

테이블명: user

설명 : 시스템에 접속하는 유저의 정보를 담고 있는 테이블입니다.

field	type	NOT NULL	PRIMARY KEY	FOREIGN KEY	부가 속성	설명
id	varchar	O	O			유저의 아이디입니다. 유저 테이블의 기본키로 후에 예약 테이블의 외래키로 지정됩니다.
pw	varchar	O				유저의 비밀번호입니다.
phone	varchar	O				유저의 전화번호입니다.
car_no	varhcar	O				유저의 차량 번호입니다.
is_master	boolean	O			DEFAULT false	유저가 일반 사용자인지 마스터 사용자인지 여부를 나타내는 필드입니다. 디폴트 값은 false입니다.

SQL 코드

create table user (

id varchar(50) not null,

pw varchar(50) not null,

phone varchar(50) not null,

carno varchar(50) not null,

is_master boolean not null default false

PRIMARY KEY (id)

);

테이블명 : parking_lot

설명 : 시스템에 저장되어 있는 주차장들의 정보입니다.

field	type	NOT NULL	PRIMARY KEY	FOREIGN KEY	부가 속성	설명
no	int	O	O		AUTO_INCREMENT	주차장의 번호입니다. 주차장 테이블의 기본키이며 자동으로 번호가 올라갑니다. 후에 예약 테이블의 외래키로 지정됩니다.
name	varchar	O				주차장의 이름입니다. [지역 주차장 이름] 형식으로 저장됩니다. ex) 용산 달
sectorCnt	int	O			DEFAULT 4	주차장의 구역의 개수입니다. 기본으로 유저가 한 주차장에 주차할 수 있는 공간은 4개입니다.

SQL 코드

create table parking_lot(

no int not null AUTO_INCREMENT,

name varchar(50) not null,

sectorCnt int not null default 4,

PRIMARY KEY(no)

);

테이블명 : reservation

field	type	NOT NULL	PRIMARY KEY	FOREIGN KEY	부가 속성	설명
no	int	O	O		AUTO_INCREMENT	예약 번호입니다. 기본키이며 오토인크리먼트 속성을 지녔습니다.
user_id	varchar	O		O	REFERENCE : user(id)  CASCADE : UPDATE, DELETE	예약한 유저의 아이디입니다. user 테이블의 id를 외래키로 가져옵니다. 삭제와 갱신시 자동으로 CASCADE합니다.
lot_no	int	O		O	REFERENCE : parking_lot(no)  CASCADE : UPDATE, DELETE	예약한 주차장의 번호입니다. parking_lot 테이블의 no를 외래키로 가져옵니다. 마찬가지로 삭제와 갱신시 자동으로 CASCADE합니다.
sector	int	O				유저가 선택한 구역의 번호입니다. 당연히 주차장이 가지고 있는 sectorCnt를 넘을 수 없습니다.

SQL 코드

create table reservation(

no int not null AUTO_INCREMENT,

user_id varchar(50) NOT NULL UNIQUE,

lot_no int NOT NULL,

sector int not null,

PRIMARY KEY(no),

FOREIGN KEY(user_id) REFERENCES user(id) ON UPDATE CASCADE ON DELETE CASCADE,

FOREIGN KEY(lot_no) REFERENCES parking_lot(no) ON UPDATE CASCADE ON DELETE CASCADE,

);

이렇게 해서 제가 생각해두었던 테이블을 모두 만들었습니다. 이제 테스트를 위해 데이터를 삽입하겠습니다.

 

데이터 삽입

제가 테스트를 위해 생각한 데이터들은 다음과 같습니다.

유저

id	pw	phone	carno	is_master
gurumee1	1	master	master	true
gurumee2	2	01000000002	00가0002	false
gurumee3	3	01000000003	00가0003	false
gurumee4	4	01000000004	00가0004	false
gurumee5	5	01000000005	00가0005	false
gurumee6	6	01000000006	00가0006	false
gurumee7	7	01000000007	00가0007	false
gurumee8	8	01000000008	00가0008	false
gurumee9	9	01000000009	00가0009	false
gurumee10	10	01000000010	00가0010	false

여기서 gurumee1 사용자는 마스터 사용자입니다. 그래서 데이터를 생성 시에 is_master를 true로 손수 지정해주어야 합니다.

SQL 코드

INSERT INTO user VALUES('gurumee1', '1', 'master', 'master', true);

나머지들은 is_master 속성을 건들일 필요는 없습니다. 왜냐하면 DEFAULT 값을 false로 지정해 두었기 때문입니다. 따라서 쿼리문은 다음과 같습니다.

SQL 코드 

INSERT INTO user(id, pw, phone, carno) VALUES('gurumee2', '2', '01000000002', '00가0002');

INSERT INTO user(id, pw, phone, carno) VALUES('gurumee3', '3', '01000000003', '00가0003');

INSERT INTO user(id, pw, phone, carno) VALUES('gurumee4', '4', '01000000004', '00가0004');

INSERT INTO user(id, pw, phone, carno) VALUES('gurumee5', '5', '01000000005', '00가0005');

INSERT INTO user(id, pw, phone, carno) VALUES('gurumee6', '6', '01000000006', '00가0006');

INSERT INTO user(id, pw, phone, carno) VALUES('gurumee7', '7', '01000000007', '00가0007');

INSERT INTO user(id, pw, phone, carno) VALUES('gurumee8', '8', '01000000008', '00가0008');

INSERT INTO user(id, pw, phone, carno) VALUES('gurumee9', '9', '01000000009', '00가0009');

INSERT INTO user(id, pw, phone, carno) VALUES('gurumee10', '10', '01000000010', '00가0010');

여기서 이 삽입 코드는 유저의 회원가입 때 쓰일 쿼리문이 될 예정입니다. 잘 기억해 두세요!

참고!

위의 삽입 코드는 2가지 방식으로 쓰였습니다. 첫 번째 마스터를 만든 쿼리문은 VALUES에 모든 Field의 값을 넣어주었고 두 번째 일반 유저를 만든 쿼리문은 id, pw, phone, carno 자신이 원하는 속성을 지정한 후 그에 맞는 값을 넣어주었습니다.

주차장

no	name	sectorCnt
1	용산 해	4
2	일산 달	4
3	영등포 지구	4
4	서울 우주	4

주차장은 자동으로 지정되는 필드가 2개 있습니다. no, sectorCnt. 전체 주차 구역의 수를 나타내는 sectorCnt는 지정을 해주어도 좋으나 그냥 테스트를 쉽게 하기 위해 4로 통일하였습니다. 따라서 쿼리문은 name만 지정해주면 됩니다.

SQL 코드

INSERT INTO parking_lot(name) VALUES('용산 해');

INSERT INTO parking_lot(name) VALUES('일산 달');

INSERT INTO parking_lot(name) VALUES('영등포 지구');

INSERT INTO parking_lot(name) VALUES('서울 우주');

예약

no	user_id	lot_no	sector
1	gurumee10	1	1
2	gurumee9	1	2
3	gurumee8	1	3
4	gurumee7	1	4
5	gurumee6	2	1
6	gurumee5	2	3
7	gurumee4	3	4
8	gurume3	4	2

no는 자동으로 생성되기 때문에 user_id, lot_no, sector만 지정해주면 됩니다.

SQL 코드

INSERT INTO reservation(user_id, lot_no, sector) VALUES('gurumee10', 1, 1);

INSERT INTO reservation(user_id, lot_no, sector) VALUES('gurumee9', 1, 2);

INSERT INTO reservation(user_id, lot_no, sector) VALUES('gurumee8', 1, 3);

INSERT INTO reservation(user_id, lot_no, sector) VALUES('gurumee7', 1, 4);

INSERT INTO reservation(user_id, lot_no, sector) VALUES('gurumee6', 2, 1);

INSERT INTO reservation(user_id, lot_no, sector) VALUES('gurumee5', 2, 3);

INSERT INTO reservation(user_id, lot_no, sector) VALUES('gurumee4', 3, 4);

INSERT INTO reservation(user_id, lot_no, sector) VALUES('gurumee3', 4, 2);

자 이제 모든 테스트를 위한 모든 데이터를 만들어 두었습니다. 각각의 DB를 조회해보면 결과는 다음과 같습니다.

참고!

reservation 테이블은 반드시 마지막에 데이터를 삽입해두어야 합니다. 왜냐하면 user와 parking_lot의 필드를 외래키로 삼고 있기 때문에 참조하고 있는 이들의 데이터가 정의되어있지 않다면 SQL내에서 에러가 뜨기 때문입니다.

user

parking_lot

reservation

저는 왜 2번부터 시작하냐면 쿼리문을 실행 시에 잘 못 작성해서 그렇습니다. 별로 중요하지 않으니 넘어가 주세요! 이제 각 화면을 보면서 이 기능 때 쓰일 쿼리문들을 정의해보도록 하겠습니다. 

기능 별 쿼리 작성

먼저 로그인 화면입니다.

로그인 버튼을 누르면 데이터 베이스에서 id와 pw가 맞는 유저가 있는지 결과를 반환해주어야 합니다. 따라서 쿼리문은 다음과 같습니다.

SQL 코드

select * from user where id="ID 타이핑 문자열" AND pw="PW 타이핑 문자열";

뭐 그 외에는 없을 것 같습니다. 바로 회원가입으로 넘어가죠.

자 이 화면에서는 두 개를 생각해볼 수 있겠습니다. 먼저 유저 테이블의 아이디는 PRIMARY KEY입니다. 따라서 중복 값을 저장할 수 없기 때문에 아이디를 입력하면 중복이 되는지 안되는지 알려줄 수 있어야 합니다.

SQL 코드

select * from user where id="ID 입력 값"

그리고 등록 버튼을 누르면 해당 입력 값들을 DB에 보내주어 유저 데이터를 생성해 주어야 합니다.

SQL 코드

INSERT INTO user(id, pw, phone, carno) VALUES('ID 입력값', 'PW 입력값', '전화번호 입력값', '차량번호 입력값'); 

이제 유저 화면으로 넘어가죠.

유저 정보 화면은 주차장에 자신의 차가 주차 되어있는지 주차 되어있다면 어디에 있는지 알려줄 수 있어야 합니다. 간단하게 유저의 아이디, 주차장 이름, 주차 섹터를 나타내보도록 하죠. 

SQL 코드

select user_id, name, sector

from reservation as r inner join parking_lot as p

on r.lot_no=p.no

where user_id="로그인 화면에서 넘겨준 ID값";

이 쿼리문의 실행 결과는 다음과 같습니다.(ex) user_id="gurumee3")

만약 이 결과가 NULL이라면 설계에서 정의한대로 다음의 화면이 뜨게 하면 됩니다.

출차 버튼을 누르면 유저가 예약한 데이터가 삭제되어야 합니다. 따라서 다음 쿼리를 정의해줄 수 있겠습니다. 

SQL 코드

delete from reservation where user_id="로그인 화면에서 넘겨준 ID 값";

이제 주차장 검색 화면으로 가보도록 하죠.

검색 화면에서는 주차장의 정보들이 표시되어야 합니다. 화면과 같이 주차장 별로 이름과 주차 채워진 수, 주차 전체 수를 나타내야겠죠? 처음에는 검색 데이터가 없을테니 모든 주차장의 결과가 나와야 합니다. 그렇다면 다음과 같이 쿼리문을 작성해야 합니다.

SQL 코드

select lot_no as "주차장 번호", name as "주차장", COUNT(lot_no) as "주차 구역 이용 고객", sectorCnt as "전체 주차 구역 수"

from reservation as r inner join parking_lot as p

on r.lot_no=p.no

group by p.no;

이 쿼리를 실행해보면 다음과 같은 결과가 나옵니다.

계획한 대로 주차장의 이름, 이용고객의 수 전체 구역의 수를 나타내주었습니다. 이 쿼리를 조금만 응용하면 검색 문자열에 따른 주차장 별 예약 정보 결과를 나타낼 수 있습니다. 

참고!

여기서 inner join으로 예약과 주차장 테이블을 결합시킨 후 주차장 번호 별로 그룹화 시켜주었습니다. 주차장 이름과 구역의 수는 그룹화에 제약이 없지만 이용 고객을 나타내기 위해서는 COUNT를 통해서 주차장의 이용 고객 수를 집계해주어야 합니다. 다음에는 사용자가 주차장을 검색했을 때입니다. 

SQL 코드

select lot_no as "주차장 번호", name as "주차장", COUNT(lot_no) as "주차 구역 이용 고객", sectorCnt as "전체 주차 구역 수"

from reservation as r inner join parking_lot as p

on r.lot_no=p.no

group by p.no

having p.name like "%검색 문자열%";

검색문자열에 해를 대입했을 때 쿼리의 결과는 다음과 같습니다.

참고!

HAVING 구는 GROUP BY 구에 조건 절이라고 생각하면 됩니다. SQL은 정귶표현식 말고도 간단하게 like %를 이용해서 문자열을 검색할 수 있습니다. 위의 예에서 검색 문자열에 해라는 문자열을 입력헀다면 문자열 중간에 '해'라는 문자열을 가지고 있는 데이터들을 DB에서 추출해줍니다. 

이제 예약 화면의 쿼리문을 정의해보도록 하겠습니다.

이 화면은 이전에 검색 화면에서 우리가 찾은 최소 주차장 번호의 정보가 입력되어야 합니다. 그 후 다음 쿼리를 실행하면 이 주차장에 예약된 섹터 정보를 알 수 있겠죠?

SQL 코드

select lot_no, sector

from reservation

where lot_no="검색 화면에서 넘겨준 주차장 번호";

그 후 빈 공간을 클릭하면 예약을 할 수 있어야 합니다. 여태까지 로그인 -> 유저 정보 -> 검색 을 거치면서 넘겨받은 정보는 최소한 로그인 한 아이디, 그리고 주차장 번호가 있으니 이렇게 쿼리를 작성하면 예약을 할 수 있겠습니다.

INSERT INTO reservation(user_id, lot_no, sector)

VALUES('로그인 화면에서 넘겨 받은 ID', '검색 화면에서 넘겨 받은 주차장 번호', '현재 화면에서 클릭한 섹터 번호');

이제 일반 유저가 사용하는 쿼리문을 거의 다 작성한 것 같습니다. 이제 마스터 유저의 쿼리를 짜보도록 하죠.

화면 설계에 따르면 마스터는 로그인 시 바로 검색 페이지로 이동합니다. 다만, 마스터는 어떤 주차장에 누구 유저가 어떤 구역에 주차를 예약했는지 알려주어야 합니다. 여기서 추가적으로 자동차 차량 번호까지 알려주도록 만들겠습니다. 쿼리는 다음과 같습니다.

SQL 코드

select u.id, u.carno, p.name, r.sector

from user as u inner join reservation as r

on u.id = r.user_id, parking_lot as p

where r.lot_no=p.no

쿼리의 결과는 다음과 같습니다.

이제 검색 결과에 따라 이 정보들을 변경시켜보겠습니다. 이번에도 LIKE % 를 이용해보죠.

select u.id, u.carno, p.name, r.sector

from user as u inner join reservation as r

on u.id = r.user_id, parking_lot as p

where p.name like '%검색 문자열%';

이제 마지막으로 마스터 상세 정보 페이지의 쿼리문들을 작성해보겠습니다.

이 화면에서는 주차장 별 이용 고객의 정보가 필요랍니다. 이전 화면에서 주차장의 모든 고객의 결과를 반환했던 쿼리를 응용해보죠. 여기서는 이전 화면에서 주차장 번호의 정보를 넘겨주어야 합니다. 따라서 쿼리는 다음과 같습니다.

SQL 코드

select u.id, u.carno, p.name, r.sector

from user as u inner join reservation as r

on u.id = r.user_id AND r.lot_no=1, parking_lot as p

where r.lot_no = p.no;

결과는 다음과 같습니다.

이렇게 해서 오늘의 작업을 모두 마쳤습니다. 내일은 Spring boot 어플리케이션에서 엔티티 클래스 정의와 Spring JDBC의 테스트를 진행해보도록 하겠습니다.