-- 회원
CREATE TABLE `MEMBER` (
`USERNAME` VARCHAR(200) NOT NULL,
`PASSWORD` VARCHAR(200) DEFAULT NULL,
`NAME` VARCHAR(200) DEFAULT NULL,
`GENDER` VARCHAR(10),
`BIRTH` DATE DEFAULT NULL,
`EMAIL` VARCHAR(200) DEFAULT NULL,
`PHONE` VARCHAR(200) DEFAULT NULL,
`POST` VARCHAR(200) DEFAULT NULL,
`ADDRESS1` VARCHAR(200) DEFAULT NULL,
`ADDRESS2` VARCHAR(200) DEFAULT NULL,
`REGDATE` DATE DEFAULT NULL,
`ENABLED` BIT(1) DEFAULT 0,
`LOANSTATUS` BIT(1) DEFAULT 0,
`OVMYRCOUNT` int DEFAULT NULL,
`ACCOUNTNONLOCKED` BIT(1) DEFAULT 1,
CONSTRAINT `MEMBER_ID_PK` PRIMARY KEY (`USERNAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ENABLED(탈퇴시 계정 비활성화)
1(true) = 활성화
0(false) = 비활성화
ACCOUNTNONLOCKED(연체시 계정 잠김)
1(true) = 잠기지않음
0(false) = 잠김
INSERT INTO MEMBER
VALUES('member','3434','asdf','여','2022-11-11','[email protected]','01012341234','1234','qwer','qwer',now(),1,0,0,1);
INSERT INTO MEMBER
VALUES('user1','202212-11','zxcv','남',20221123,'[email protected]','010123415555','3424','asdf','asdf',now(),1,0,0,1);
INSERT INTO MEMBER
VALUES('admin','3434','asdf','여', '2022-01-11','[email protected]','01012341234','1234','qwer','qwer',now(),1,0,0,1);
-- 등급
CREATE TABLE `ROLE`(
`ROLENUM` INT NOT NULL AUTO_INCREMENT,
`ROLENAME` VARCHAR(200) DEFAULT NULL,
CONSTRAINT `ROLE_NUM_PK` PRIMARY KEY (`ROLENUM`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO ROLE VALUES(NULL, 'ROLE_ADMIN');
INSERT INTO ROLE VALUES(NULL, 'ROLE_MEMBER');
-- 회원 등급
CREATE TABLE `MEMBER_ROLE`(
`ROLENUM` INT NOT NULL,
`USERNAME` VARCHAR(200) NOT NULL,
CONSTRAINT `MR_ID_FK` FOREIGN KEY(`USERNAME`) REFERENCES `MEMBER`(`USERNAME`),
CONSTRAINT `MR_NUM_FK` FOREIGN KEY(`ROLENUM`) REFERENCES `ROLE`(`ROLENUM`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO MEMBER_ROLE VALUES (1,'admin')
CREATE TABLE BOARD(
BOARDNUM INT NOT NULL AUTO_INCREMENT,
TITLE VARCHAR(500) NOT NULL,
CONTENTS VARCHAR(500) NOT NULL,
WRITER VARCHAR(10) NOT NULL,
REGDATE DATE NOT NULL,
HIT INT(11),
CONSTRAINT BOARD_NUM_PK PRIMARY KEY (BOARDNUM)
)
CREATE TABLE BOARDFILE (
FILENUM INT NOT NULL AUTO_INCREMENT,
BOARDNUM INT,
FILENAME VARCHAR (500),
ORINAME VARCHAR (500),
CONSTRAINT BOARDFILE_NUM_PK PRIMARY KEY(FILENUM),
CONSTRAINT BOARDFILE_BN_FK FOREIGN KEY (BOARDNUM) REFERENCES BOARD(BOARDNUM)
)
create table QNA(
QNANUM INT NOT NULL AUTO_INCREMENT,
TITLE VARCHAR(500) NOT NULL,
CONTENTS VARCHAR(500) NOT NULL,
CONSTRAINT QNA_NUM_PK PRIMARY KEY(QNANUM)
)
create table WISHBOARD(
WISHNUM INT NOT NULL AUTO_INCREMENT,
TITLE VARCHAR(500) NOT NULL,
CONTENTS VARCHAR(500) NOT NULL,
WRITER VARCHAR(500) NOT NULL,
REGDATE DATE NOT NULL,
STATUS BIT(1) NOT NULL,
SECRET BIT(1) NOT NULL,
CONSTRAINT WISHBOARD_NUM_PK PRIMARY KEY(WISHNUM)
)
===================안다은====================
7. 도서관안내 - 도서관예절, 휴관일(달력), 이용시간, 대여규칙, 편의시설
9. 도서관소식 - 공지사항, 건의게시판, 자유게시판, 자료실
마7) 나의 건의게시판 글-목록
<마이페이지>
마0) 마이페이지 인덱스
마1) 회원가입 - 정보수정, 탈퇴
마2) 로그인, 아이디찾기, 로그아웃
(세큐리티, 카카오, 구글, 네이버)
| URL | /member/login |
|---|---|
| Param | |
| Method | GET, POST |
| JSP | /WEB-INF/views/member/login |
ID, PW, 아이디 기억하기
(기본/ social) 로그인, 로그아웃 → /로 이동
- 로그인 시, 마이페이지, 로그아웃 / 로그아웃 시, 로그인으로 페이지 이동
social- kakao, google API
social/기본 회원탈퇴 처리
| URL | /member/join |
|---|---|
| Param | |
| Method | GET,POST |
| JSP | /WEB-INF/views/member/join |
1)회원가입 페이지