PROPERTIES
DB 데이터
PROPERTIES
db.properties
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@13.124.190.205:6379:xe
user=gobooke
pw=gobooke
ddl.properties
DROP TABLE COMMENTS CASCADE CONSTRAINTS;
DROP TABLE BOOK CASCADE CONSTRAINTS;
DROP TABLE CHAT CASCADE CONSTRAINTS;
DROP TABLE RECOMMEND CASCADE CONSTRAINTS;
DROP TABLE PLACE CASCADE CONSTRAINTS;
DROP TABLE STUDY CASCADE CONSTRAINTS;
DROP TABLE GO_USER CASCADE CONSTRAINTS;
DROP TABLE REVIEW CASCADE CONSTRAINTS;
DROP TABLE STUDY_CATEGORY CASCADE CONSTRAINTS;
DROP TABLE WISHLIST CASCADE CONSTRAINTS;
DROP TABLE SCHEDULE CASCADE CONSTRAINTS;
DROP TABLE STUDY_REQUEST CASCADE CONSTRAINTS;
DROP TABLE BOOK_CATEGORY CASCADE CONSTRAINTS;
DROP TABLE REPORTS CASCADE CONSTRAINTS;
DROP TABLE NOTICE CASCADE CONSTRAINTS;
DROP TABLE PHOTO CASCADE CONSTRAINTS;
CREATE TABLE GO_USER
(
USER_SEQ NUMBER NOT NULL,
USER_ID VARCHAR2(50) UNIQUE NOT NULL,
USER_PWD VARCHAR2(100) NOT NULL,
USER_NICKNAME VARCHAR2(50) UNIQUE NOT NULL,
USER_GENDER VARCHAR2(1) CHECK (USER_GENDER IN ('M', 'F')),
USER_PHONE VARCHAR2(20) NULL,
USER_ADDRESS VARCHAR2(100) NULL,
USER_PROFILE VARCHAR2(100) NULL,
USER_INTRO VARCHAR2(500) NULL,
USER_TYPE VARCHAR2(1) CHECK (USER_TYPE IN ('0', '1')),
USER_EMAIL VARCHAR2(50) NOT NULL,
USER_CREATE_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
USER_DELETE_TIME TIMESTAMP NULL,
USER_SPEED NUMBER DEFAULT 0
);
CREATE TABLE REVIEW
(
REVIEW_SEQ NUMBER NOT NULL,
REVIEW_TITLE VARCHAR2(100) NOT NULL,
REVIEW_CONTENTS VARCHAR2(2000) NOT NULL,
REVIEW_CREATE_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
REVIEW_RATE NUMBER CHECK (REVIEW_RATE IN (0, 1, 2, 3, 4, 5)) NOT NULL,
REVIEW_DELETE_TIME TIMESTAMP NULL,
REVIEW_EDIT_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
REVIEW_IS_PUBLIC VARCHAR2(1) DEFAULT 'Y' CHECK (REVIEW_IS_PUBLIC IN ('Y', 'N')),
USER_SEQ NUMBER NOT NULL,
BOOK_SEQ NUMBER NOT NULL
);
CREATE TABLE NOTICE
(
NOTICE_SEQ NUMBER NOT NULL,
NOTICE_TITLE VARCHAR2(100) NULL,
NOTICE_CONTENTS VARCHAR2(2000) NULL,
NOTICE_ORDER NUMBER,
NOTICE_CREATE_TIME TIMESTAMP NULL,
NOTICE_EDIT_TIME TIMESTAMP NULL,
NOTICE_DELETE_TIME TIMESTAMP NULL
);
CREATE TABLE REPORTS
(
REP_SEQ NUMBER NOT NULL,
REP_REASON VARCHAR2(100) NULL,
REP_BOARD_SEQ NUMBER NULL,
REP_CREATE_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
USER_SEQ NUMBER NOT NULL
);
CREATE TABLE BOOK_CATEGORY
(
BC_CID NUMBER,
BC_DEPT NUMBER,
BC_CID_NAME VARCHAR2(100),
BC_COUNTRY VARCHAR2(20),
BC_DEPT1_CID NUMBER,
BC_DEPT1_NAME VARCHAR2(100),
BC_DEPT2_CID NUMBER,
BC_DEPT2_NAME VARCHAR2(100),
BC_DEPT3_CID NUMBER,
BC_DEPT3_NAME VARCHAR2(100),
BC_DEPT4_CID NUMBER,
BC_DEPT4_NAME VARCHAR2(100),
BC_DEPT5_CID NUMBER,
BC_DEPT5_NAME VARCHAR2(100)
);
CREATE TABLE PHOTO
(
PHOTO_SEQ NUMBER NOT NULL,
PHOTO_BOARD_SEQ NUMBER NOT NULL,
PHOTO_RENAMED_NAME VARCHAR2(100) DEFAULT 'gobookedefault.png' NOT NULL
);
CREATE TABLE COMMENTS
(
COMMENTS_SEQ NUMBER NOT NULL,
COMMENTS_CONTENTS VARCHAR2(2000),
COMMENTS_CREATE_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
COMMENTS_DELETE_TIME TIMESTAMP,
COMMENTS_EDIT_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
COMMENTS_PARENT_SEQ NUMBER DEFAULT 0,
COMMENTS_IS_PUBLIC VARCHAR2(1) DEFAULT 'Y' CHECK (COMMENTS_IS_PUBLIC IN ('Y', 'N')),
USER_SEQ NUMBER NOT NULL,
REVIEW_SEQ NUMBER NOT NULL
);
CREATE TABLE BOOK
(
BOOK_SEQ NUMBER NOT NULL,
BOOK_ID NUMBER NOT NULL,
BOOK_TITLE VARCHAR2(500) NULL,
BOOK_LINK VARCHAR2(1000) NULL,
BOOK_AUTHOR VARCHAR2(300) NULL,
BOOK_PUBDATE DATE NULL,
BOOK_DESCRIPTION VARCHAR2(1000) NULL,
BOOK_ISBN VARCHAR2(20) NULL,
BOOK_ISBN13 VARCHAR2(20) NULL,
BOOK_PRICESALES NUMBER NULL,
BOOK_PRICESTANDARD NUMBER NULL,
BOOK_MALLTYPE VARCHAR2(100) NULL,
BOOK_STOCKSTATUS VARCHAR2(100) NULL,
BOOK_MILEAGE NUMBER NULL,
BOOK_COVER VARCHAR2(1000) NULL,
BOOK_CATEGORYID VARCHAR2(100) NULL,
BOOK_CATEGORYNAME VARCHAR2(300) NULL,
BOOK_PUBLISHER VARCHAR2(300) NULL,
BOOK_SALESPOINT NUMBER NULL,
BOOK_ADULT VARCHAR2(5) DEFAULT 'N' NULL,
BOOK_FIXEDPRICE VARCHAR2(5) NULL,
BOOK_CUSTOMERREVIEWRANK NUMBER NULL,
BOOK_SERIESID VARCHAR2(100) NULL,
BOOK_SERIESLINK VARCHAR2(1000) NULL,
BOOK_SERIESNAME VARCHAR2(300) NULL,
BOOK_SUBINFO VARCHAR2(100) NULL
);
CREATE TABLE CHAT
(
CHAT_SEQ NUMBER NOT NULL,
CHAT_MESSAGE VARCHAR2(500) NOT NULL,
CHAT_CREATE_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
STUDY_SEQ NUMBER NOT NULL,
USER_SEQ NUMBER NOT NULL
);
CREATE TABLE RECOMMEND
(
REC_SEQ NUMBER NOT NULL,
REC_TYPE VARCHAR2(1) CHECK (REC_TYPE IN ('0', '1')),
REC_BOARD_SEQ NUMBER NULL,
USER_SEQ NUMBER NOT NULL
);
CREATE TABLE PLACE
(
PLACE_SEQ NUMBER NOT NULL,
PLACE_TITLE VARCHAR2(100) NOT NULL,
PLACE_CONTENTS VARCHAR2(2000) NOT NULL,
PLACE_CREATE_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
PLACE_DELETE_TIME TIMESTAMP NULL,
PLACE_PRICE NUMBER DEFAULT 0,
PLACE_ADDRESS VARCHAR2(100) NOT NULL,
PLACE_LATITUDE NUMBER NOT NULL,
PLACE_LONGITUDE NUMBER NOT NULL,
PLACE_IS_PUBLIC VARCHAR2(1) DEFAULT 'Y' CHECK (PLACE_IS_PUBLIC IN ('Y', 'N')),
USER_SEQ NUMBER NOT NULL
);
CREATE TABLE STUDY
(
STUDY_SEQ NUMBER NOT NULL,
STUDY_TITLE VARCHAR2(100) NOT NULL,
STUDY_CONTENT VARCHAR2(2000) NOT NULL,
STUDY_DATE TIMESTAMP,
STUDY_MEMBER_LIMIT NUMBER CHECK (STUDY_MEMBER_LIMIT BETWEEN 2 AND 12),
STUDY_CREATE_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
STUDY_EDIT_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
STUDY_IS_PUBLIC VARCHAR2(1) DEFAULT 'Y' CHECK (STUDY_IS_PUBLIC IN ('Y', 'N')),
STUDY_DELETE_TIME TIMESTAMP NULL,
STUDY_ADDRESS VARCHAR2(100),
STUDY_LATITUDE NUMBER NULL,
STUDY_LONGITUDE NUMBER NULL,
USER_SEQ NUMBER NOT NULL,
SC_SEQ NUMBER NOT NULL
);
CREATE TABLE STUDY_REQUEST
(
REQUEST_SEQ NUMBER NOT NULL,
REQUEST_MSG VARCHAR2(100) NULL,
REQUEST_CONFIRM VARCHAR2(1) DEFAULT 'N',
CHECK (REQUEST_CONFIRM IN ('Y', 'N', 'R')),
USER_SEQ NUMBER NOT NULL,
STUDY_SEQ NUMBER NOT NULL
);
CREATE TABLE SCHEDULE
(
SCHEDULE_SEQ NUMBER NOT NULL,
SCHEDULE_IS_CONFIRM VARCHAR2(1) DEFAULT 'N' CHECK (SCHEDULE_IS_CONFIRM IN ('Y', 'N', 'R')),
SCHEDULE_DELETE_TIME TIMESTAMP NULL,
SCHEDULE_CREATE_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
SCHEDULE_DATE DATE NOT NULL,
SCHEDULE_NOSHOW_YN VARCHAR2(1) DEFAULT 'N' CHECK (SCHEDULE_NOSHOW_YN IN ('Y', 'N','R')),
STUDY_SEQ NUMBER NOT NULL,
PLACE_SEQ NUMBER NOT NULL
);
CREATE TABLE WISHLIST
(
WL_SEQ NUMBER NOT NULL,
USER_SEQ NUMBER NOT NULL,
BOOK_SEQ NUMBER NOT NULL
);
CREATE TABLE STUDY_CATEGORY
(
SC_SEQ NUMBER NOT NULL,
SC_TITLE VARCHAR2(20) NULL
);
-- PRIMARY KEY
ALTER TABLE STUDY_CATEGORY
ADD CONSTRAINT PK_STUDY_CATEGORY PRIMARY KEY (SC_SEQ);
ALTER TABLE WISHLIST
ADD CONSTRAINT PK_WISHLIST PRIMARY KEY (WL_SEQ);
ALTER TABLE BOOK_CATEGORY
ADD CONSTRAINT PK_BOOK_CATEGORY PRIMARY KEY (BC_CID);
ALTER TABLE REPORTS
ADD CONSTRAINT PK_REPORTS PRIMARY KEY (REP_SEQ);
ALTER TABLE NOTICE
ADD CONSTRAINT PK_NOTICE PRIMARY KEY (NOTICE_SEQ);
ALTER TABLE SCHEDULE
ADD CONSTRAINT PK_PLACE_SCHEDULE PRIMARY KEY (SCHEDULE_SEQ);
ALTER TABLE STUDY_REQUEST
ADD CONSTRAINT PK_STUDY_REQUEST PRIMARY KEY (REQUEST_SEQ);
ALTER TABLE COMMENTS
ADD CONSTRAINT PK_COMMENTS PRIMARY KEY (COMMENTS_SEQ);
ALTER TABLE BOOK
ADD CONSTRAINT PK_BOOK PRIMARY KEY (BOOK_SEQ);
ALTER TABLE CHAT
ADD CONSTRAINT PK_CHAT PRIMARY KEY (CHAT_SEQ);
ALTER TABLE RECOMMEND
ADD CONSTRAINT PK_RECOMMEND PRIMARY KEY (REC_SEQ);
ALTER TABLE PLACE
ADD CONSTRAINT PK_PLACE PRIMARY KEY (PLACE_SEQ);
ALTER TABLE STUDY
ADD CONSTRAINT PK_STUDY PRIMARY KEY (STUDY_SEQ);
ALTER TABLE GO_USER
ADD CONSTRAINT PK_USER PRIMARY KEY (USER_SEQ);
ALTER TABLE REVIEW
ADD CONSTRAINT PK_REVIEW PRIMARY KEY (REVIEW_SEQ);
ALTER TABLE PHOTO
ADD CONSTRAINT PK_PHOTO PRIMARY KEY (PHOTO_SEQ);
-- FOREIGN KEY
ALTER TABLE WISHLIST
ADD CONSTRAINT FK_USER_TO_WISHLIST_1 FOREIGN KEY (USER_SEQ) REFERENCES GO_USER (USER_SEQ);
ALTER TABLE WISHLIST
ADD CONSTRAINT FK_BOOK_TO_WISHLIST_1 FOREIGN KEY (BOOK_SEQ) REFERENCES BOOK (BOOK_SEQ);
ALTER TABLE SCHEDULE
ADD CONSTRAINT FK_STUDY_TO_PLACE_RESERVATION_1 FOREIGN KEY (STUDY_SEQ) REFERENCES STUDY (STUDY_SEQ);
ALTER TABLE SCHEDULE
ADD CONSTRAINT FK_PLACE_TO_PLACE_RESERVATION_1 FOREIGN KEY (PLACE_SEQ) REFERENCES PLACE (PLACE_SEQ);
ALTER TABLE STUDY_REQUEST
ADD CONSTRAINT FK_USER_TO_STUDY_REQUEST_1 FOREIGN KEY (USER_SEQ) REFERENCES GO_USER (USER_SEQ);
ALTER TABLE STUDY_REQUEST
ADD CONSTRAINT FK_STUDY_TO_STUDY_REQUEST_1 FOREIGN KEY (STUDY_SEQ) REFERENCES STUDY (STUDY_SEQ);
ALTER TABLE COMMENTS
ADD CONSTRAINT FK_USER_TO_COMMENTS_1 FOREIGN KEY (USER_SEQ) REFERENCES GO_USER (USER_SEQ);
ALTER TABLE COMMENTS
ADD CONSTRAINT FK_REVIEW_TO_COMMENTS_1 FOREIGN KEY (REVIEW_SEQ) REFERENCES REVIEW (REVIEW_SEQ);
ALTER TABLE CHAT
ADD CONSTRAINT FK_USER_TO_CHAT_1 FOREIGN KEY (USER_SEQ) REFERENCES GO_USER (USER_SEQ);
ALTER TABLE CHAT
ADD CONSTRAINT FK_USER_TO_CHAT_2 FOREIGN KEY (STUDY_SEQ) REFERENCES STUDY (STUDY_SEQ);
ALTER TABLE PLACE
ADD CONSTRAINT FK_USER_TO_PLACE_1 FOREIGN KEY (USER_SEQ) REFERENCES GO_USER (USER_SEQ);
ALTER TABLE STUDY
ADD CONSTRAINT FK_USER_TO_STUDY_1 FOREIGN KEY (USER_SEQ) REFERENCES GO_USER (USER_SEQ);
ALTER TABLE STUDY
ADD CONSTRAINT FK_STUDY_CATEGORY_TO_STUDY_1 FOREIGN KEY (SC_SEQ) REFERENCES STUDY_CATEGORY (SC_SEQ);
ALTER TABLE RECOMMEND
ADD CONSTRAINT FK_USER_TO_RECOMMEND_1 FOREIGN KEY (USER_SEQ) REFERENCES GO_USER (USER_SEQ);
ALTER TABLE REPORTS
ADD CONSTRAINT FK_USER_TO_REPORTS_1 FOREIGN KEY (USER_SEQ) REFERENCES GO_USER (USER_SEQ);
ALTER TABLE REVIEW
ADD CONSTRAINT FK_USER_TO_REVIEW_1 FOREIGN KEY (USER_SEQ) REFERENCES GO_USER (USER_SEQ);
ALTER TABLE REVIEW
ADD CONSTRAINT FK_BOOK_TO_REVIEW_1 FOREIGN KEY (BOOK_SEQ) REFERENCES BOOK (BOOK_SEQ);
-- DELETE SEQUENCE
DROP SEQUENCE SEQ_SPEED;
DROP SEQUENCE SEQ_CHAT;
DROP SEQUENCE SEQ_SCHEDULE;
DROP SEQUENCE SEQ_BOOK;
DROP SEQUENCE SEQ_USER;
DROP SEQUENCE SEQ_BOARD;
DROP SEQUENCE SEQ_SC;
DROP SEQUENCE SEQ_WL;
DROP SEQUENCE SEQ_REQUEST;
DROP SEQUENCE SEQ_BOOK_CATEGORY;
DROP SEQUENCE SEQ_REP;
DROP SEQUENCE SEQ_NOTICE;
DROP SEQUENCE SEQ_REC;
DROP SEQUENCE SEQ_PHOTO;
-- CREATE SEQUENCE
CREATE SEQUENCE SEQ_SPEED;
CREATE SEQUENCE SEQ_CHAT;
CREATE SEQUENCE SEQ_SCHEDULE;
CREATE SEQUENCE SEQ_BOOK;
CREATE SEQUENCE SEQ_USER;
CREATE SEQUENCE SEQ_BOARD;
CREATE SEQUENCE SEQ_SC;
CREATE SEQUENCE SEQ_WL;
CREATE SEQUENCE SEQ_REQUEST;
CREATE SEQUENCE SEQ_BOOK_CATEGORY;
CREATE SEQUENCE SEQ_REP;
CREATE SEQUENCE SEQ_NOTICE;
CREATE SEQUENCE SEQ_REC;
CREATE SEQUENCE SEQ_PHOTO;
review-sql.properties
getAllReviews=\\
SELECT * FROM ( \\
SELECT ROWNUM AS RNUM, R.* FROM ( \\
SELECT \\
RV.REVIEW_SEQ, \\
RV.REVIEW_TITLE, \\
RV.REVIEW_CONTENTS, \\
RV.REVIEW_CREATE_TIME, \\
RV.REVIEW_RATE, \\
RV.REVIEW_EDIT_TIME, \\
BK.BOOK_TITLE, \\
BK.BOOK_COVER, \\
NVL(COUNT(RC.REC_SEQ), 0) AS RECOMMEND_COUNT, \\
(SELECT COUNT(*) \\
FROM COMMENTS C \\
WHERE C.REVIEW_SEQ = RV.REVIEW_SEQ AND C.COMMENTS_DELETE_TIME IS NULL) AS COMMENTS_COUNT \\
FROM REVIEW RV \\
LEFT JOIN RECOMMEND RC \\
ON RV.REVIEW_SEQ = RC.REC_BOARD_SEQ AND RC.REC_TYPE = '0' \\
JOIN BOOK BK \\
ON RV.BOOK_SEQ = BK.BOOK_SEQ \\
WHERE RV.REVIEW_DELETE_TIME IS NULL \\
AND RV.REVIEW_IS_PUBLIC = 'Y' \\
GROUP BY \\
RV.REVIEW_SEQ, RV.REVIEW_TITLE, RV.REVIEW_CONTENTS, \\
RV.REVIEW_CREATE_TIME, RV.REVIEW_RATE, RV.REVIEW_EDIT_TIME, \\
RV.REVIEW_IS_PUBLIC, RV.USER_SEQ, RV.BOOK_SEQ, \\
BK.BOOK_TITLE, BK.BOOK_AUTHOR, BK.BOOK_PUBLISHER, BK.BOOK_COVER \\
ORDER BY RV.REVIEW_CREATE_TIME DESC \\
) R \\
) \\
WHERE RNUM BETWEEN ? AND ?
getAllReviewsByRec=\\
SELECT * FROM ( \\
SELECT ROWNUM AS RNUM, R.* FROM ( \\
SELECT \\
RV.REVIEW_SEQ, \\
RV.REVIEW_TITLE, \\
RV.REVIEW_CONTENTS, \\
RV.REVIEW_CREATE_TIME, \\
RV.REVIEW_RATE, \\
RV.REVIEW_EDIT_TIME, \\
BK.BOOK_TITLE, \\
BK.BOOK_COVER, \\
(SELECT COUNT(*) \\
FROM COMMENTS C \\
WHERE C.REVIEW_SEQ = RV.REVIEW_SEQ AND C.COMMENTS_DELETE_TIME IS NULL) AS COMMENTS_COUNT, \\
NVL(COUNT(RC.REC_SEQ), 0) AS RECOMMEND_COUNT \\
FROM REVIEW RV \\
LEFT JOIN RECOMMEND RC \\
ON RV.REVIEW_SEQ = RC.REC_BOARD_SEQ AND RC.REC_TYPE = '0' \\
JOIN BOOK BK \\
ON RV.BOOK_SEQ = BK.BOOK_SEQ \\
WHERE RV.REVIEW_DELETE_TIME IS NULL \\
AND RV.REVIEW_IS_PUBLIC = 'Y' \\
GROUP BY \\
RV.REVIEW_SEQ, RV.REVIEW_TITLE, RV.REVIEW_CONTENTS, \\
RV.REVIEW_CREATE_TIME, RV.REVIEW_RATE, RV.REVIEW_EDIT_TIME, \\
RV.REVIEW_IS_PUBLIC, RV.USER_SEQ, RV.BOOK_SEQ, \\
BK.BOOK_TITLE, BK.BOOK_AUTHOR, BK.BOOK_PUBLISHER, BK.BOOK_COVER \\
ORDER BY RECOMMEND_COUNT DESC, RV.REVIEW_CREATE_TIME DESC \\
) R \\
) \\
WHERE RNUM BETWEEN ? AND ?
getBestReviews=\\
SELECT * FROM ( \\
SELECT ROWNUM AS RNUM, R.* FROM ( \\
SELECT \\
RV.REVIEW_SEQ, \\
RV.REVIEW_TITLE, \\
RV.REVIEW_CONTENTS, \\
RV.REVIEW_CREATE_TIME, \\
RV.REVIEW_RATE, \\
RV.REVIEW_EDIT_TIME, \\
BK.BOOK_TITLE, \\
BK.BOOK_AUTHOR, \\
BK.BOOK_PUBLISHER, \\
BK.BOOK_COVER, \\
(SELECT COUNT(*) \\
FROM REVIEW R2 \\
WHERE R2.BOOK_SEQ = RV.BOOK_SEQ AND R2.REVIEW_DELETE_TIME IS NULL) AS BOOK_REVIEW_COUNT, \\
(SELECT ROUND(AVG(R2.REVIEW_RATE), 2) \\
FROM REVIEW R2 \\
WHERE R2.BOOK_SEQ = RV.BOOK_SEQ AND R2.REVIEW_DELETE_TIME IS NULL) AS BOOK_AVG_RATE, \\
(SELECT COUNT(*) \\
FROM COMMENTS C \\
WHERE C.REVIEW_SEQ = RV.REVIEW_SEQ AND C.COMMENTS_DELETE_TIME IS NULL) AS COMMENTS_COUNT, \\
NVL(COUNT(RC.REC_SEQ), 0) AS RECOMMEND_COUNT \\
FROM REVIEW RV \\
LEFT JOIN RECOMMEND RC \\
ON RV.REVIEW_SEQ = RC.REC_BOARD_SEQ AND RC.REC_TYPE = '0' \\
JOIN BOOK BK \\
ON RV.BOOK_SEQ = BK.BOOK_SEQ \\
WHERE RV.REVIEW_DELETE_TIME IS NULL \\
AND RV.REVIEW_IS_PUBLIC = 'Y' \\
AND RV.REVIEW_CREATE_TIME >= TRUNC(SYSDATE, 'MM') \\
GROUP BY \\
RV.REVIEW_SEQ, RV.REVIEW_TITLE, RV.REVIEW_CONTENTS, \\
RV.REVIEW_CREATE_TIME, RV.REVIEW_RATE, RV.REVIEW_EDIT_TIME, \\
RV.REVIEW_IS_PUBLIC, RV.USER_SEQ, RV.BOOK_SEQ, \\
BK.BOOK_TITLE, BK.BOOK_AUTHOR, BK.BOOK_PUBLISHER, BK.BOOK_COVER \\
ORDER BY RECOMMEND_COUNT DESC, RV.REVIEW_CREATE_TIME DESC \\
) R \\
) \\
WHERE RNUM BETWEEN 1 AND 4
getAllReviewsByUser=\\
SELECT * FROM ( \\
SELECT ROWNUM AS RNUM, R.* FROM ( \\
SELECT \\
RV.REVIEW_SEQ, \\
RV.REVIEW_TITLE, \\
RV.REVIEW_CONTENTS, \\
RV.REVIEW_CREATE_TIME, \\
RV.REVIEW_RATE, \\
RV.REVIEW_EDIT_TIME, \\
BK.BOOK_TITLE, \\
BK.BOOK_COVER, \\
NVL(COUNT(RC.REC_SEQ), 0) AS RECOMMEND_COUNT, \\
(SELECT COUNT(*) \\
FROM COMMENTS C \\
WHERE C.REVIEW_SEQ = RV.REVIEW_SEQ AND C.COMMENTS_DELETE_TIME IS NULL) AS COMMENTS_COUNT \\
FROM REVIEW RV \\
LEFT JOIN RECOMMEND RC \\
ON RV.REVIEW_SEQ = RC.REC_BOARD_SEQ AND RC.REC_TYPE = '0' \\
JOIN BOOK BK \\
ON RV.BOOK_SEQ = BK.BOOK_SEQ \\
WHERE RV.USER_SEQ = ? \\
AND RV.REVIEW_DELETE_TIME IS NULL \\
AND RV.REVIEW_IS_PUBLIC = 'Y' \\
GROUP BY \\
RV.REVIEW_SEQ, RV.REVIEW_TITLE, RV.REVIEW_CONTENTS, \\
RV.REVIEW_CREATE_TIME, RV.REVIEW_RATE, RV.REVIEW_EDIT_TIME, \\
RV.REVIEW_IS_PUBLIC, RV.USER_SEQ, RV.BOOK_SEQ, \\
BK.BOOK_TITLE, BK.BOOK_AUTHOR, BK.BOOK_PUBLISHER, BK.BOOK_COVER \\
ORDER BY RV.REVIEW_CREATE_TIME DESC \\
) R \\
) \\
WHERE RNUM BETWEEN ? AND ?
getAllReviewsRecByUser=\\
SELECT * FROM ( \\
SELECT ROWNUM AS RNUM, R.* FROM ( \\
SELECT \\
RV.REVIEW_SEQ, \\
RV.REVIEW_TITLE, \\
RV.REVIEW_CONTENTS, \\
RV.REVIEW_CREATE_TIME, \\
RV.REVIEW_EDIT_TIME, \\
BK.BOOK_TITLE, \\
BK.BOOK_COVER, \\
RV.REVIEW_RATE, \\
(SELECT COUNT(*) FROM RECOMMEND R2 WHERE R2.REC_BOARD_SEQ = RV.REVIEW_SEQ AND R2.REC_TYPE = '0') AS RECOMMEND_COUNT, \\
(SELECT COUNT(*) \\
FROM COMMENTS C \\
WHERE C.REVIEW_SEQ = RV.REVIEW_SEQ AND C.COMMENTS_DELETE_TIME IS NULL) AS COMMENTS_COUNT \\
FROM RECOMMEND R \\
JOIN REVIEW RV ON R.REC_BOARD_SEQ = RV.REVIEW_SEQ \\
JOIN GO_USER U ON RV.USER_SEQ = U.USER_SEQ \\
JOIN BOOK BK ON RV.BOOK_SEQ = BK.BOOK_SEQ \\
WHERE R.USER_SEQ = ? \\
AND R.REC_TYPE = '0' \\
AND RV.REVIEW_IS_PUBLIC = 'Y' \\
AND RV.REVIEW_DELETE_TIME IS NULL \\
ORDER BY RV.REVIEW_CREATE_TIME DESC \\
) R \\
) WHERE RNUM BETWEEN ? AND ?
countReviewsRecByUser=\\
SELECT COUNT(*) \\
FROM RECOMMEND R \\
JOIN REVIEW RV ON R.REC_BOARD_SEQ = RV.REVIEW_SEQ \\
WHERE R.USER_SEQ = ? \\
AND R.REC_TYPE = '0' \\
AND RV.REVIEW_IS_PUBLIC = 'Y' \\
AND RV.REVIEW_DELETE_TIME IS NULL
countByUser=SELECT COUNT(*) FROM REVIEW WHERE USER_SEQ = ? AND REVIEW_DELETE_TIME IS NULL AND REVIEW_IS_PUBLIC = 'Y'
reviewCount= SELECT COUNT(*) FROM REVIEW
getReviewBySeq=\\
SELECT \\
RV.REVIEW_SEQ, \\
RV.REVIEW_TITLE, \\
RV.REVIEW_CONTENTS, \\
RV.REVIEW_CREATE_TIME, \\
RV.REVIEW_RATE, \\
RV.REVIEW_EDIT_TIME, \\
RV.USER_SEQ, \\
RV.BOOK_SEQ, \\
U.USER_NICKNAME, \\
B.BOOK_TITLE, \\
B.BOOK_AUTHOR, \\
B.BOOK_PUBLISHER, \\
B.BOOK_COVER, \\
B.BOOK_DESCRIPTION, \\
U.USER_NICKNAME, \\
U.USER_PROFILE, \\
U.USER_SPEED, \\
(SELECT COUNT(*) FROM REVIEW R2 WHERE R2.BOOK_SEQ = RV.BOOK_SEQ AND R2.REVIEW_DELETE_TIME IS NULL) AS BOOK_REVIEW_COUNT, \\
(SELECT ROUND(AVG(R2.REVIEW_RATE), 2) FROM REVIEW R2 WHERE R2.BOOK_SEQ = RV.BOOK_SEQ AND R2.REVIEW_DELETE_TIME IS NULL) AS BOOK_AVG_RATE, \\
(SELECT COUNT(*) FROM RECOMMEND R3 WHERE R3.REC_BOARD_SEQ = RV.REVIEW_SEQ AND R3.REC_TYPE = '0') AS RECOMMEND_COUNT, \\
(SELECT COUNT(*) FROM RECOMMEND R3 WHERE R3.REC_BOARD_SEQ = RV.REVIEW_SEQ AND R3.REC_TYPE = '1') AS NON_RECOMMEND_COUNT \\
FROM REVIEW RV \\
JOIN GO_USER U ON RV.USER_SEQ = U.USER_SEQ \\
JOIN BOOK B ON RV.BOOK_SEQ = B.BOOK_SEQ \\
WHERE RV.REVIEW_DELETE_TIME IS NULL \\
AND RV.REVIEW_IS_PUBLIC = 'Y' \\
AND RV.REVIEW_SEQ = ?
getReviewComments=\\
SELECT \\
LEVEL AS COMMENT_LEVEL, \\
C.COMMENTS_PARENT_SEQ, \\
C.COMMENTS_SEQ, \\
C.COMMENTS_CONTENTS, \\
C.COMMENTS_CREATE_TIME, \\
C.COMMENTS_EDIT_TIME, \\
U.USER_NICKNAME, \\
C.USER_SEQ, \\
(SELECT COUNT(*) FROM RECOMMEND R3 WHERE R3.REC_BOARD_SEQ = C.COMMENTS_SEQ AND R3.REC_TYPE = '0') AS RECOMMEND_COUNT, \\
(SELECT COUNT(*) FROM RECOMMEND R3 WHERE R3.REC_BOARD_SEQ = C.COMMENTS_SEQ AND R3.REC_TYPE = '1') AS NON_RECOMMEND_COUNT \\
FROM COMMENTS C \\
JOIN GO_USER U ON C.USER_SEQ = U.USER_SEQ \\
WHERE C.REVIEW_SEQ = ? \\
AND C.COMMENTS_DELETE_TIME IS NULL \\
AND C.COMMENTS_IS_PUBLIC = 'Y' \\
START WITH C.COMMENTS_PARENT_SEQ = 0 \\
CONNECT BY PRIOR C.COMMENTS_SEQ = C.COMMENTS_PARENT_SEQ \\
ORDER SIBLINGS BY C.COMMENTS_CREATE_TIME ASC
getAllCommentsByUser=\\
SELECT * FROM ( \\
SELECT ROWNUM AS RNUM, R.* FROM ( \\
SELECT \\
C.COMMENTS_SEQ, \\
C.COMMENTS_CONTENTS, \\
C.COMMENTS_CREATE_TIME, \\
C.COMMENTS_EDIT_TIME, \\
C.REVIEW_SEQ, \\
U.USER_NICKNAME, \\
C.USER_SEQ, \\
(SELECT REVIEW_TITLE FROM REVIEW R2 WHERE R2.REVIEW_SEQ = C.REVIEW_SEQ) AS REVIEW_TITLE, \\
(SELECT COUNT(*) FROM RECOMMEND R3 WHERE R3.REC_BOARD_SEQ = C.COMMENTS_SEQ AND R3.REC_TYPE = '0') AS RECOMMEND_COUNT, \\
(SELECT COUNT(*) FROM RECOMMEND R3 WHERE R3.REC_BOARD_SEQ = C.COMMENTS_SEQ AND R3.REC_TYPE = '1') AS NON_RECOMMEND_COUNT \\
FROM COMMENTS C \\
JOIN GO_USER U ON C.USER_SEQ = U.USER_SEQ \\
WHERE C.USER_SEQ = ? \\
AND COMMENTS_DELETE_TIME IS NULL \\
AND C.COMMENTS_IS_PUBLIC = 'Y' \\
ORDER BY COMMENTS_CREATE_TIME DESC \\
) R \\
) WHERE RNUM BETWEEN ? AND ?
getAllCommentsRecByUser=\\
SELECT * FROM ( \\
SELECT ROWNUM AS RNUM, R.* FROM ( \\
SELECT \\
C.COMMENTS_SEQ, \\
C.COMMENTS_CONTENTS, \\
C.COMMENTS_CREATE_TIME, \\
C.COMMENTS_EDIT_TIME, \\
C.REVIEW_SEQ, \\
U.USER_NICKNAME, \\
C.USER_SEQ, \\
(SELECT REVIEW_TITLE FROM REVIEW R2 WHERE R2.REVIEW_SEQ = C.REVIEW_SEQ) AS REVIEW_TITLE, \\
(SELECT COUNT(*) FROM RECOMMEND R3 WHERE R3.REC_BOARD_SEQ = C.COMMENTS_SEQ AND R3.REC_TYPE = '0') AS RECOMMEND_COUNT, \\
(SELECT COUNT(*) FROM RECOMMEND R3 WHERE R3.REC_BOARD_SEQ = C.COMMENTS_SEQ AND R3.REC_TYPE = '1') AS NON_RECOMMEND_COUNT \\
FROM RECOMMEND R \\
JOIN COMMENTS C ON R.REC_BOARD_SEQ = C.COMMENTS_SEQ \\
JOIN GO_USER U ON C.USER_SEQ = U.USER_SEQ \\
WHERE R.USER_SEQ = ? \\
AND R.REC_TYPE = '0' \\
AND C.COMMENTS_DELETE_TIME IS NULL \\
AND C.COMMENTS_IS_PUBLIC = 'Y' \\
ORDER BY COMMENTS_CREATE_TIME DESC \\
) R \\
) WHERE RNUM BETWEEN ? AND ?
countCommentsRecByUser=\\
SELECT COUNT(*) \\
FROM RECOMMEND R \\
JOIN COMMENTS C ON R.REC_BOARD_SEQ = C.COMMENTS_SEQ \\
WHERE R.USER_SEQ = ? \\
AND R.REC_TYPE = '0' \\
AND C.COMMENTS_IS_PUBLIC = 'Y' \\
AND C.COMMENTS_DELETE_TIME IS NULL
commentsCountByUser=\\
SELECT COUNT(*) FROM COMMENTS WHERE USER_SEQ = ? AND COMMENTS_DELETE_TIME IS NULL AND COMMENTS_IS_PUBLIC = 'Y'
insertComment=\\
INSERT INTO COMMENTS \\
(COMMENTS_SEQ, COMMENTS_CONTENTS, COMMENTS_CREATE_TIME, COMMENTS_EDIT_TIME,\\
COMMENTS_PARENT_SEQ, COMMENTS_IS_PUBLIC, USER_SEQ, REVIEW_SEQ) \\
VALUES (SEQ_BOARD.NEXTVAL, ?, SYSTIMESTAMP, SYSTIMESTAMP, ?, 'Y', ?, ?)
updateComment=UPDATE COMMENTS SET COMMENTS_CONTENTS = ?, \\
COMMENTS_EDIT_TIME = SYSTIMESTAMP WHERE COMMENTS_SEQ = ? \\
AND USER_SEQ = ? AND COMMENTS_DELETE_TIME IS NULL
deleteComment=UPDATE COMMENTS SET COMMENTS_DELETE_TIME = SYSTIMESTAMP \\
WHERE COMMENTS_SEQ = ? AND USER_SEQ = ? AND COMMENTS_DELETE_TIME IS NULL
searchBooks=SELECT * FROM BOOK WHERE BOOK_TITLE LIKE ? OR BOOK_AUTHOR LIKE ?
insertReview=INSERT INTO REVIEW (REVIEW_SEQ, REVIEW_TITLE, REVIEW_CONTENTS, REVIEW_RATE, \\
REVIEW_CREATE_TIME, USER_SEQ, BOOK_SEQ) VALUES (SEQ_BOARD.NEXTVAL, ?, ?, ?, SYSTIMESTAMP, ?, ?)
updateReview=UPDATE REVIEW SET REVIEW_TITLE = ?, REVIEW_CONTENTS = ?, REVIEW_RATE = ?, REVIEW_EDIT_TIME = SYSTIMESTAMP, BOOK_SEQ = ? \\
WHERE REVIEW_SEQ = ? AND USER_SEQ = ? AND REVIEW_DELETE_TIME IS NULL
deleteReview=UPDATE REVIEW SET REVIEW_DELETE_TIME = SYSTIMESTAMP WHERE REVIEW_SEQ = ? AND USER_SEQ = ? AND REVIEW_DELETE_TIME IS NULL
users-sql.properties
# 전체 회원 조회
findAllUsers=SELECT * FROM GO_USER WHERE USER_DELETE_TIME IS NOT NULL
# 로그인
searchById=SELECT * FROM GO_USER WHERE USER_ID=?
#닉네임 중복 확인
searchByNickName=SELECT * FROM GO_USER WHERE USER_NICKNAME=?
# SEQ로 회원 조회
findUserBySeq=SELECT * FROM GO_USER WHERE USER_SEQ = ?
# 회원 생성
insertUser=INSERT INTO GO_USER \\
(USER_SEQ, USER_ID, USER_PWD, USER_NICKNAME, USER_GENDER, USER_PHONE, USER_ADDRESS, USER_PROFILE ,USER_INTRO, USER_TYPE, USER_EMAIL, USER_CREATE_TIME) \\
VALUES (SEQ_USER.nextval, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,SYSTIMESTAMP)
# 회원 거북이 속도 UPDATE
updateUserSpeed=UPDATE GO_USER SET USER_SPEED = USER_SPEED + ? WHERE USER_SEQ = ?
# 회원 비밀번호 변경
changePwd = UPDATE GO_USER SET USER_PWD = ? WHERE USER_ID = ?
# 회원 프로필 수정
updateProfile = UPDATE GO_USER SET USER_NICKNAME = ?, USER_PWD = ?, USER_PHONE = ?, USER_PROFILE = ? WHERE USER_SEQ = ?
# 회원 삭제
deleteUser=UPDATE GO_USER SET USER_DELETE_TIME = SYSTIMESTAMP WHERE USER_SEQ = ?
# 회원 email로 id 조회
findUserIdByUserEmail=SELECT USER_ID FROM GO_USER WHERE USER_EMAIL = ? AND USER_DELETE_TIME IS NULL
# 회원 email, id로 조회
searchUserByIdAndEmail=SELECT 1 FROM GO_USER WHERE USER_ID = ? AND USER_EMAIL = ? AND USER_DELETE_TIME IS NULL
# 회원 email과 id로 password 조회
findUserPwdyUserEmail=SELECT USER_PWD FROM GO_USER WHERE USER_EMAIL = ? AND USER_ID = ?
# 회원 고북이 속도 조회
findUserSpeed=SELECT * FROM PLACE JOIN GOBOOKEE.GO_USER GU on PLACE.USER_SEQ = GU.USER_SEQ WHERE USER_SEQ = ? AND GOBOOKEE.GO_USER.USER_TYPE = '1'
# 회원(사장) 장소대여 목록 조회
findStores=SELECT * FROM PLACE JOIN GOBOOKEE.GO_USER U on PLACE.USER_SEQ = U.USER_SEQ WHERE U.USER_SEQ = ?
# 회원 리뷰 목록 조회
findReviews=SELECT * FROM REVIEW JOIN GOBOOKEE.GO_USER U on REVIEW.USER_SEQ = U.USER_SEQ WHERE U.USER_SEQ = ?
# 회원 댓글 목록 조회
findComments=SELECT * FROM COMMENTS JOIN GOBOOKEE.GO_USER U on COMMENTS.USER_SEQ = U.USER_SEQ WHERE U.USER_SEQ = ?
# 회원 스터디 글 목록 조회
findStudies=SELECT * FROM STUDY JOIN GOBOOKEE.GO_USER U on STUDY.USER_SEQ = U.USER_SEQ WHERE U.USER_SEQ = ?
getSpeedRanking=\\
SELECT * FROM ( \\
SELECT ROWNUM AS RNUM, R.* FROM ( \\
SELECT USER_SEQ, USER_NICKNAME, USER_PROFILE, USER_SPEED \\
FROM GO_USER \\
WHERE USER_DELETE_TIME IS NULL \\
ORDER BY USER_SPEED DESC \\
) R \\
) \\
WHERE RNUM BETWEEN ? AND ?
countSpeedRanking=\\
SELECT COUNT(*) \\
FROM GO_USER \\
WHERE USER_DELETE_TIME IS NULL
getUserRank=\\
SELECT RNUM FROM ( \\
SELECT USER_SEQ, ROWNUM AS RNUM \\
FROM ( \\
SELECT USER_SEQ \\
FROM GO_USER \\
WHERE USER_DELETE_TIME IS NULL \\
ORDER BY USER_SPEED DESC \\
) \\
) \\
WHERE USER_SEQ = ?
study-sql.properties
# 전체 스터디 최신 업로드순으로 조회
getStudiesByTime= SELECT * FROM ( \\
SELECT ROWNUM AS RNUM, N.* FROM ( \\
SELECT \\
S.STUDY_SEQ, \\
S.STUDY_TITLE, \\
S.STUDY_DATE, \\
S.STUDY_MEMBER_LIMIT, \\
S.STUDY_ADDRESS, \\
C.SC_TITLE, \\
(SELECT COUNT(*) \\
FROM STUDY_REQUEST R \\
WHERE R.STUDY_SEQ = S.STUDY_SEQ AND R.REQUEST_CONFIRM = 'Y') AS CONFIRMED_COUNT, \\
(SELECT P.PHOTO_RENAMED_NAME \\
FROM PHOTO P \\
WHERE P.PHOTO_BOARD_SEQ = S.STUDY_SEQ AND ROWNUM = 1) AS PHOTO_NAME, \\
(SELECT COUNT(*) \\
FROM RECOMMEND REC \\
WHERE REC.REC_BOARD_SEQ = S.STUDY_SEQ AND REC.REC_TYPE = '0') AS LIKE_COUNT, \\
(SELECT COUNT(*) \\
FROM RECOMMEND REC \\
WHERE REC.REC_BOARD_SEQ = S.STUDY_SEQ AND REC.REC_TYPE = '1') AS DISLIKE_COUNT \\
FROM STUDY S \\
JOIN STUDY_CATEGORY C ON S.SC_SEQ = C.SC_SEQ \\
WHERE S.STUDY_IS_PUBLIC = 'Y' \\
ORDER BY S.STUDY_CREATE_TIME DESC \\
) N \\
) \\
WHERE RNUM BETWEEN ? AND ?
# 전체 스터디 좋아요 순으로 조회
getStudiesByRec = SELECT * FROM ( \\
SELECT ROWNUM AS RNUM, T.* FROM ( \\
SELECT \\
S.STUDY_SEQ, \\
S.STUDY_TITLE, \\
S.STUDY_DATE, \\
S.STUDY_MEMBER_LIMIT, \\
S.STUDY_ADDRESS, \\
C.SC_TITLE, \\
(SELECT COUNT(*) FROM STUDY_REQUEST R WHERE R.STUDY_SEQ = S.STUDY_SEQ AND R.REQUEST_CONFIRM = 'Y') AS CONFIRMED_COUNT, \\
(SELECT P.PHOTO_RENAMED_NAME FROM PHOTO P WHERE P.PHOTO_BOARD_SEQ = S.STUDY_SEQ AND ROWNUM = 1) AS PHOTO_NAME, \\
(SELECT COUNT(*) FROM RECOMMEND REC WHERE REC.REC_BOARD_SEQ = S.STUDY_SEQ AND REC.REC_TYPE = '0') AS LIKE_COUNT, \\
(SELECT COUNT(*) FROM RECOMMEND REC WHERE REC.REC_BOARD_SEQ = S.STUDY_SEQ AND REC.REC_TYPE = '1') AS DISLIKE_COUNT \\
FROM STUDY S \\
JOIN STUDY_CATEGORY C ON S.SC_SEQ = C.SC_SEQ \\
WHERE S.STUDY_IS_PUBLIC = 'Y' \\
ORDER BY \\
(SELECT COUNT(*) FROM RECOMMEND REC WHERE REC.REC_BOARD_SEQ = S.STUDY_SEQ AND REC.REC_TYPE = '0') DESC, \\
S.STUDY_DATE DESC \\
) T \\
) \\
WHERE RNUM BETWEEN ? AND ?
# 전체 스터디 갯수 조회
allStudyCount = SELECT COUNT(*) AS TOTAL_STUDY_COUNT \\
FROM STUDY \\
WHERE STUDY_IS_PUBLIC = 'Y' \\
AND STUDY_DATE >= SYSDATE
# 호스트 유저 시퀀스로 스터디 리스트 조회
getStudyListByHostUserSeq=SELECT STUDY_SEQ, STUDY_TITLE FROM STUDY WHERE USER_SEQ = ? AND STUDY_DELETE_TIME IS NULL AND STUDY_IS_PUBLIC = 'Y' AND STUDY_DATE IS NULL
# 새로 등록할 스터디 번호 조회
getNextBoardSeq = SELECT SEQ_BOARD.NEXTVAL FROM DUAL
# 새 스터디 입력
insertStudy = INSERT INTO STUDY \\
(STUDY_SEQ, STUDY_TITLE, STUDY_CONTENT, STUDY_DATE, STUDY_MEMBER_LIMIT, STUDY_CREATE_TIME, \\
STUDY_ADDRESS, STUDY_LATITUDE, STUDY_LONGITUDE, SC_SEQ, USER_SEQ) \\
VALUES (?, ?, ?, ?, ?, SYSTIMESTAMP, ?, ?, ?, (SELECT SC_SEQ FROM STUDY_CATEGORY WHERE SC_TITLE = ?), ?)
# 스터디 상세페이지
studyView = SELECT S.STUDY_SEQ, S.USER_SEQ, S.STUDY_TITLE, TO_CHAR(S.STUDY_DATE, 'YYYY-MM-DD') \\
AS STUDY_DATE, S.STUDY_CONTENT, S.STUDY_MEMBER_LIMIT, S.STUDY_ADDRESS, S.STUDY_LATITUDE, \\
S.STUDY_LONGITUDE, U.USER_NICKNAME, U.USER_SPEED, U.USER_PROFILE, \\
C.SC_TITLE AS STUDY_CATEGORY, P.PHOTO_RENAMED_NAME AS PHOTO_NAME, \\
(SELECT COUNT(*) FROM STUDY_REQUEST R WHERE R.STUDY_SEQ = S.STUDY_SEQ AND R.REQUEST_CONFIRM = 'Y') AS CONFIRMED_COUNT, \\
(SELECT COUNT(*) FROM RECOMMEND REC WHERE REC.REC_BOARD_SEQ = S.STUDY_SEQ AND REC.REC_TYPE = '0') AS LIKE_COUNT, \\
(SELECT COUNT(*) FROM RECOMMEND REC WHERE REC.REC_BOARD_SEQ = S.STUDY_SEQ AND REC.REC_TYPE = '1') AS DISLIKE_COUNT \\
FROM STUDY S JOIN GO_USER U ON S.USER_SEQ = U.USER_SEQ JOIN STUDY_CATEGORY C ON S.SC_SEQ = C.SC_SEQ LEFT JOIN \\
(SELECT PHOTO_BOARD_SEQ, PHOTO_RENAMED_NAME FROM (SELECT PHOTO_BOARD_SEQ, PHOTO_RENAMED_NAME, ROW_NUMBER() OVER \\
(PARTITION BY PHOTO_BOARD_SEQ ORDER BY PHOTO_SEQ) AS RN FROM PHOTO) WHERE RN = 1) P ON S.STUDY_SEQ = P.PHOTO_BOARD_SEQ WHERE S.STUDY_SEQ = ?
# 스터디 신청자목록
studyViewUsers = SELECT U.USER_NICKNAME, U.USER_PROFILE, U.USER_SPEED FROM STUDY_REQUEST R JOIN GO_USER U ON R.USER_SEQ = U.USER_SEQ WHERE R.STUDY_SEQ = ? AND R.REQUEST_CONFIRM = 'Y'
# 스터디 신청 승인 목록(Y)
studyConfirmedUsers = SELECT U.USER_SEQ, U.USER_NICKNAME, U.USER_PROFILE, U.USER_SPEED \\
FROM STUDY_REQUEST R JOIN GO_USER U ON R.USER_SEQ = U.USER_SEQ WHERE R.STUDY_SEQ = ? AND R.REQUEST_CONFIRM = 'Y'
# 스터디 신청 미승인 목록(N,R)
studyNotConfirmedUsers=SELECT R.USER_SEQ, R.REQUEST_CONFIRM \\
FROM STUDY_REQUEST R \\
WHERE R.STUDY_SEQ = ? \\
AND R.REQUEST_CONFIRM != 'Y'
# 스터디 신청
insertStudyRequest = INSERT INTO STUDY_REQUEST (REQUEST_SEQ, STUDY_SEQ, USER_SEQ, REQUEST_CONFIRM, REQUEST_MSG) \\
VALUES (SEQ_REQUEST.NEXTVAL, ?, ?, 'N', ?)
# 스터디 신청목록
studyRequests = SELECT R.STUDY_SEQ, \\
S.STUDY_MEMBER_LIMIT, \\
R.USER_SEQ, \\
U.USER_PROFILE, \\
U.USER_NICKNAME, \\
U.USER_SPEED, \\
R.REQUEST_CONFIRM, \\
R.REQUEST_MSG, \\
S.USER_SEQ AS HOST_SEQ \\
FROM STUDY_REQUEST R \\
JOIN GO_USER U ON R.USER_SEQ = U.USER_SEQ \\
JOIN STUDY S ON R.STUDY_SEQ = S.STUDY_SEQ \\
WHERE R.STUDY_SEQ = ?
#스터디 승인/거절
updateRequestConfirm = UPDATE STUDY_REQUEST \\
SET REQUEST_CONFIRM = ? \\
WHERE STUDY_SEQ = ? AND USER_SEQ = ?
#스터디 삭제
deleteStudy = UPDATE STUDY SET STUDY_DELETE_TIME = SYSDATE WHERE STUDY_SEQ = ?
place-sql.properties
createPlace=INSERT INTO PLACE(PLACE_SEQ, PLACE_TITLE, PLACE_CONTENTS, PLACE_CREATE_TIME, PLACE_DELETE_TIME, PLACE_PRICE,\\
PLACE_ADDRESS, PLACE_LATITUDE, PLACE_LONGITUDE, PLACE_IS_PUBLIC, USER_SEQ) \\
VALUES (?, ?, ?, DEFAULT, NULL, DEFAULT, ?, ?, ?, DEFAULT, ?)
getNextBoardSeq=SELECT SEQ_BOARD.NEXTVAL FROM DUAL
getPlaceCount=SELECT COUNT(*) FROM PLACE WHERE PLACE_IS_PUBLIC = 'Y' AND PLACE_DELETE_TIME IS NULL
getPlaceList=SELECT * FROM ( \\
SELECT ROWNUM AS RNUM, R.* FROM ( \\
SELECT P.PLACE_SEQ, P.PLACE_TITLE, P.PLACE_CONTENTS, P.PLACE_ADDRESS, \\
(SELECT COUNT(*) FROM RECOMMEND R2 WHERE R2.REC_BOARD_SEQ = P.PLACE_SEQ AND R2.REC_TYPE = '0') AS RECOMMEND_COUNT, \\
(SELECT COUNT(*) FROM RECOMMEND R2 WHERE R2.REC_BOARD_SEQ = P.PLACE_SEQ AND R2.REC_TYPE = '1') AS NON_RECOMMEND_COUNT \\
FROM PLACE P \\
WHERE P.PLACE_IS_PUBLIC = 'Y' AND P.PLACE_DELETE_TIME IS NULL \\
ORDER BY P.PLACE_CREATE_TIME DESC \\
) R \\
) WHERE RNUM BETWEEN ? AND ?
getPlaceListByRec=SELECT * \\
FROM (SELECT ROWNUM AS RNUM, R.* \\
FROM (SELECT P.PLACE_SEQ, P.PLACE_TITLE, P.PLACE_CONTENTS, P.PLACE_ADDRESS, U.USER_NICKNAME AS WRITER_NICKNAME, \\
(SELECT COUNT(*) FROM RECOMMEND R2 WHERE R2.REC_BOARD_SEQ = P.PLACE_SEQ AND R2.REC_TYPE = '0') AS RECOMMEND_COUNT, \\
(SELECT COUNT(*) FROM RECOMMEND R2 WHERE R2.REC_BOARD_SEQ = P.PLACE_SEQ AND R2.REC_TYPE = '1') AS NON_RECOMMEND_COUNT \\
FROM PLACE P \\
LEFT JOIN GO_USER U ON P.USER_SEQ = U.USER_SEQ \\
WHERE P.PLACE_IS_PUBLIC = 'Y' AND P.PLACE_DELETE_TIME IS NULL \\
GROUP BY P.PLACE_SEQ, P.PLACE_TITLE, P.PLACE_CONTENTS, P.PLACE_ADDRESS, U.USER_NICKNAME \\
ORDER BY RECOMMEND_COUNT DESC) R) \\
WHERE RNUM BETWEEN ? AND ?
getPlaceBySeq=SELECT P.USER_SEQ, P.PLACE_SEQ, P.PLACE_TITLE, P.PLACE_CONTENTS, P.PLACE_ADDRESS, P.PLACE_LATITUDE, P.PLACE_LONGITUDE, U.USER_NICKNAME, U.USER_SPEED, U.USER_PROFILE, \\
(SELECT COUNT(*) FROM RECOMMEND R2 WHERE R2.REC_BOARD_SEQ = P.PLACE_SEQ AND R2.REC_TYPE = '0') AS RECOMMEND_COUNT, \\
(SELECT COUNT(*) FROM RECOMMEND R2 WHERE R2.REC_BOARD_SEQ = P.PLACE_SEQ AND R2.REC_TYPE = '1') AS NON_RECOMMEND_COUNT \\
FROM PLACE P \\
LEFT JOIN GO_USER U ON P.USER_SEQ = U.USER_SEQ \\
WHERE P.PLACE_SEQ = ? AND P.PLACE_IS_PUBLIC = 'Y' AND P.PLACE_DELETE_TIME IS NULL
deletePlace=UPDATE PLACE SET PLACE_DELETE_TIME = SYSTIMESTAMP WHERE PLACE_SEQ = ?
updatePlace=UPDATE PLACE SET PLACE_TITLE = ?, PLACE_CONTENTS = ?, PLACE_ADDRESS = ?, PLACE_LATITUDE = ?, PLACE_LONGITUDE = ? WHERE PLACE_SEQ = ?
getPlaceAddressBySeq=SELECT PLACE_ADDRESS, PLACE_LATITUDE, PLACE_LONGITUDE FROM PLACE WHERE PLACE_SEQ = ?
getAllPlaceByUser=SELECT * FROM ( \\
SELECT ROWNUM AS RNUM, R.* FROM ( \\
SELECT P.PLACE_SEQ, P.PLACE_TITLE, P.PLACE_CONTENTS, P.PLACE_ADDRESS, U.USER_NICKNAME AS WRITER_NICKNAME, \\
(SELECT COUNT(*) FROM RECOMMEND R2 WHERE R2.REC_BOARD_SEQ = P.PLACE_SEQ AND R2.REC_TYPE = '0') AS RECOMMEND_COUNT, \\
(SELECT COUNT(*) FROM RECOMMEND R2 WHERE R2.REC_BOARD_SEQ = P.PLACE_SEQ AND R2.REC_TYPE = '1') AS NON_RECOMMEND_COUNT \\
FROM PLACE P \\
LEFT JOIN GO_USER U ON P.USER_SEQ = U.USER_SEQ \\
WHERE P.PLACE_IS_PUBLIC = 'Y' AND P.PLACE_DELETE_TIME IS NULL \\
AND P.USER_SEQ = ? \\
GROUP BY P.PLACE_SEQ, P.PLACE_TITLE, P.PLACE_CONTENTS, P.PLACE_ADDRESS, U.USER_NICKNAME, P.PLACE_CREATE_TIME \\
ORDER BY P.PLACE_CREATE_TIME DESC \\
) R \\
) WHERE RNUM BETWEEN ? AND ?
placeCountByUser=\\
SELECT COUNT(*) FROM PLACE WHERE USER_SEQ = ? AND PLACE_DELETE_TIME IS NULL AND PLACE_IS_PUBLIC = 'Y'
getAllPlacesRecByUser=\\
SELECT * FROM ( \\
SELECT ROWNUM AS RNUM, R.* FROM ( \\
SELECT P.PLACE_SEQ, P.PLACE_TITLE, P.PLACE_CONTENTS, P.PLACE_ADDRESS, U.USER_NICKNAME AS WRITER_NICKNAME, \\
(SELECT COUNT(*) FROM RECOMMEND R2 WHERE R2.REC_BOARD_SEQ = P.PLACE_SEQ AND R2.REC_TYPE = '0') AS RECOMMEND_COUNT, \\
(SELECT COUNT(*) FROM RECOMMEND R2 WHERE R2.REC_BOARD_SEQ = P.PLACE_SEQ AND R2.REC_TYPE = '1') AS NON_RECOMMEND_COUNT \\
FROM RECOMMEND R \\
JOIN PLACE P ON R.REC_BOARD_SEQ = P.PLACE_SEQ \\
JOIN GO_USER U ON P.USER_SEQ = U.USER_SEQ \\
WHERE R.USER_SEQ = ? \\
AND R.REC_TYPE = '0' \\
AND P.PLACE_IS_PUBLIC = 'Y' \\
AND P.PLACE_DELETE_TIME IS NULL \\
ORDER BY P.PLACE_CREATE_TIME DESC \\
) R \\
) WHERE RNUM BETWEEN ? AND ?
placeCountRecByUser=\\
SELECT COUNT(*) \\
FROM RECOMMEND R \\
JOIN PLACE P ON R.REC_BOARD_SEQ = P.PLACE_SEQ \\
WHERE R.USER_SEQ = ? \\
AND R.REC_TYPE = '0' \\
AND P.PLACE_IS_PUBLIC = 'Y' \\
AND P.PLACE_DELETE_TIME IS NULL
book-sql.properties
insertBook=INSERT INTO BOOK VALUES (SEQ_BOOK.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
getBookListPaging=SELECT * FROM ( \\
SELECT ROWNUM AS RNUM, n.* \\
FROM ( \\
SELECT B.*, \\
NVL(R.REVIEW_COUNT, 0) AS REVIEW_COUNT, \\
NVL(R.REVIEW_AVG, 0) AS REVIEW_RATE_AVG, \\
NVL(W.USER_SEQ,0) AS WISH_CHECK \\
FROM BOOK B \\
LEFT JOIN ( \\
SELECT BOOK_SEQ, \\
COUNT(*) AS REVIEW_COUNT, \\
AVG(REVIEW_RATE) AS REVIEW_AVG \\
FROM REVIEW \\
WHERE REVIEW_IS_PUBLIC = 'Y' \\
AND REVIEW_DELETE_TIME IS NULL \\
GROUP BY BOOK_SEQ \\
) R ON B.BOOK_SEQ = R.BOOK_SEQ \\
LEFT JOIN WISHLIST W ON B.BOOK_SEQ = W.BOOK_SEQ AND USER_SEQ = ? \\
) n) \\
WHERE RNUM BETWEEN ? AND ?
getBookCount=SELECT count(*) FROM BOOK
getBookDetailBySeq=\\
SELECT B.*, \\
NVL(R.REVIEW_COUNT, 0) AS REVIEW_COUNT, \\
NVL(R.REVIEW_AVG, 0) AS REVIEW_RATE_AVG, \\
NVL(W.USER_SEQ, 0) AS WISH_CHECK \\
FROM BOOK B \\
LEFT JOIN WISHLIST W ON B.BOOK_SEQ = W.BOOK_SEQ AND W.USER_SEQ = ? \\
LEFT JOIN ( \\
SELECT BOOK_SEQ, \\
COUNT(*) AS REVIEW_COUNT, \\
AVG(REVIEW_RATE) AS REVIEW_AVG \\
FROM REVIEW \\
WHERE REVIEW_IS_PUBLIC = 'Y' \\
AND REVIEW_DELETE_TIME IS NULL \\
GROUP BY BOOK_SEQ \\
) R ON B.BOOK_SEQ = R.BOOK_SEQ \\
WHERE B.BOOK_SEQ = ?
getBookDetailByID=SELECT * FROM BOOK WHERE BOOK_ID = ?
getReviewByBookSeq=\\
SELECT * \\
FROM (SELECT ROWNUM AS RNUM, N.* \\
FROM (SELECT R.REVIEW_SEQ, \\
R.REVIEW_TITLE, \\
R.REVIEW_CREATE_TIME, \\
R.REVIEW_CONTENTS, \\
NVL(RECOMMEND_COUNT, 0) AS RECOMMEND_COUNT, \\
NVL(NON_RECOMMEND_COUNT, 0) AS NON_RECOMMEND_COUNT, \\
U.USER_PROFILE, \\
U.USER_NICKNAME, \\
REC2.REC_TYPE \\
FROM REVIEW R \\
LEFT JOIN GO_USER U ON R.USER_SEQ = U.USER_SEQ \\
LEFT JOIN (SELECT REC_BOARD_SEQ, \\
SUM(CASE WHEN REC_TYPE = 1 THEN 1 ELSE 0 END) AS RECOMMEND_COUNT, \\
SUM(CASE WHEN REC_TYPE = 0 THEN 1 ELSE 0 END) AS NON_RECOMMEND_COUNT \\
FROM RECOMMEND \\
GROUP BY REC_BOARD_SEQ) REC ON REC.REC_BOARD_SEQ = R.REVIEW_SEQ \\
LEFT JOIN RECOMMEND REC2 ON R.REVIEW_SEQ = REC2.REC_BOARD_SEQ AND REC2.USER_SEQ = ? \\
WHERE R.BOOK_SEQ = ? \\
AND R.REVIEW_DELETE_TIME IS NULL \\
AND R.REVIEW_IS_PUBLIC = 'Y' \\
ORDER BY ${orderColumn} ) \\
N) \\
WHERE RNUM BETWEEN ? AND ?
getReviewByBookSeqCount=SELECT COUNT(*) FROM REVIEW WHERE REVIEW_DELETE_TIME IS NULL AND REVIEW_IS_PUBLIC = 'Y' AND BOOK_SEQ = ?
getCategoryList=SELECT * FROM BOOK_CATEGORY WHERE ${whereColumn}
getBookForReview=\\
SELECT \\
BOOK_TITLE, \\
BOOK_AUTHOR, \\
BOOK_PUBLISHER, \\
BOOK_COVER, \\
BOOK_DESCRIPTION, \\
BOOK_PUBDATE \\
FROM BOOK \\
WHERE BOOK_SEQ = ?
wishCheck=INSERT INTO WISHLIST VALUES(SEQ_WL.nextval, ? , ? )
wishUncheck=DELETE FROM WISHLIST WHERE USER_SEQ = ? AND BOOK_SEQ = ?
bookWishCountByUser=SELECT COUNT(*) FROM WISHLIST WHERE USER_SEQ = ?
getWishListByUserSeq=SELECT * FROM(SELECT ROWNUM AS RNUM,B.* FROM ( \\
select B.* \\
from BOOK B \\
JOIN WISHLIST W ON B.BOOK_SEQ = W.BOOK_SEQ \\
JOIN GO_USER U ON U.USER_SEQ = W.USER_SEQ \\
WHERE U.USER_SEQ = ? \\
) B) WHERE RNUM BETWEEN ? AND ?
getWishCountByUserSeq=SELECT COUNT(*) \\
FROM BOOK B \\
JOIN WISHLIST W ON B.BOOK_SEQ = W.BOOK_SEQ \\
JOIN GO_USER U ON U.USER_SEQ = W.USER_SEQ \\
WHERE U.USER_SEQ = ?
schedule-sql.properties