MEMBER

-- 회원
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')

board

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) 로그인, 아이디찾기, 로그아웃
(세큐리티, 카카오, 구글, 네이버)

회원관리

  1. 로그인 페이지
URL /member/login
Param
Method GET, POST
JSP /WEB-INF/views/member/login
  1. 로그인
  1. 회원가입
URL /member/join
Param
Method GET,POST
JSP /WEB-INF/views/member/join

1)회원가입 페이지