이안의 평일코딩

Spring 17일차 - SQL PROCEDURE 게시판 본문

Back-end/Spring

Spring 17일차 - SQL PROCEDURE 게시판

이안92 2020. 11. 19. 22:04
반응형

2020.11.19~20(목~금)

PROCEDURE, FUNCTION => 재사용을 위해!

 

  SQL  

-- 게시판 테이블

1
2
3
4
5
6
7
8
9
10
11
12
-- 게시판
CREATE TABLE project_board(
    no NUMBER,
    name VARCHAR2(34) CONSTRAINT pb_name_nn NOT NULL,
    subject VARCHAR2(1000) CONSTRAINT pb_sub_nn NOT NULL,
    content CLOB CONSTRAINT pb_cont_nn NOT NULL,
    pwd VARCHAR2(10) CONSTRAINT pb_pwd_nn NOT NULL,
    regdate DATE DEFAULT SYSDATE,
    hit NUMBER DEFAULT 0,
    CONSTRAINT pb_no_pk PRIMARY KEY(no)
);
 
 cs

 

-- 입력 PROCEDURE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 입력
CREATE OR REPLACE PROCEDRUE projectBoardInsert(
    pName project_board.name%TYPE,
    pSubject project_board.subject%TYPE,
    pContent project_board.content%TYPE,
    pPwd project_board.pwd%TYPE,
)
IS
BEGIN
    INSERT INTO project_board(no,name,subject,content,pwd)
    VALUES((SELECT NVL(MAX(no)+1,1) FROM project_board),
            pName,pSubject,pContent,pPwd);
    COMMIT;
END;
/
 
 cs

 

-- 목록 PROCEDURE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 목록
CREATE OR REPLACE PROCEDURE projectBoardListData(
    pStart NUMBER,
    pEnd NUMBER,
    pResult OUT SYS_REFCURSOR, -- 결과값, 값을 받는 변수는 OUT을 붙임
)
IS
BEGIN
    OPEN pResult FOR
     SELECT no,subject,name,regdate,hit,num
     FROM (SELECT no,subject,name,regdate,hit,rownum as num
     FROM (SELECT no,subject,name,regdate,hit
     FROM project_board ORDER BY no DESC))
     WHERE num BETWEEN pStart AND pEnd;
END;
/
 cs

 

-- 상세보기 PROCEDURE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 상세보기
CREATE OR REPLACE PROCEDURE projectBoardDetailData(
    pNo project_board.no%TYPE,
    pResult OUT SYS_REFCURSOR
)
IS
BEGIN
    -- 조회수 증가
    UPDATE project_board SET
    hit=hit+1
    WHERE no=pNo;
    COMMIT;
    -- 상세보기 내용
    OPEN pResult FOR
     SELECT no,name,subject,content,regdate,hit
     FROM project_board
     WHERE no=pNo;
END;
/
 cs

 

-- 수정폼 (원래글 데이터 가져오기) PROCEDURE

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 수정
CREATE OR REPLACE PROCEDURE projectBoardUpdateData(
    pNo project_board.no%TYPE,
    pResult OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN pResult FOR
        SELECT no,name,subject,content
        FROM project_board
        WHERE no=pNo;
END;
/
 cs

 

-- 실제수정 PROCEDURE (비밀번호 같을 때 수정)

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
-- 실제 수정
CREATE OR REPLACE PROCEDURE projectBoardUpdate(
    pNo project_board.no%TYPE,
    pName project_board.name%TYPE,
    pSubject project_board.subject%TYPE,
    pContent project_board.content%TYPE,
    pPwd project_board.pwd%TYPE,
    pResult OUT project_board.name%TYPE
)
IS
    vPwd project_board.pwd%TYPE;
BEGIN
    SELECT pwd INTO vPwd
    FROM project_board
    WHERE no=pNo;
 
    IF(vPwd=pPwd) THEN
     pResult:='true';
     UPDATE project_board SET
     name=pName,subject=pSubject,content=pContent
     WHERE no=pNo;
     COMMIT;
    ELSE
     pResult:='false';
    END IF;
END;
/
 cs

 

-- 삭제 PROCEDURE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 삭제
CREATE OR REPLACE PROCEDURE projectBoardDelete(
    pNo project_board.no%TYPE,
    pPwd project_board.pwd%TYPE,
    pResult OUT project_board.name%TYPE
)
IS
    vPwd project_board.pwd%TYPE; --변수선언
BEGIN
    SELECT pwd INTO vPwd -- pPwd는 사용자가 보낸 비밀번호
    FROM project_board -- pwd는 project_board의 db안에 저장되어있는 비밀번호
    WHERE no=pNo;
 
    IF(vPwd=pPwd) THEN
     pResult:='true';
     DELETE FROM project_board
     WHERE no=pNo;
     COMMIT;
    ELSE
     pResult:='false';
    END IF;
END;
/
 cs

 

-- 총페이지 FUNCTION (매개변수가 없으면 () 필요없음)

1
2
3
4
5
6
7
8
9
10
-- 총페이지
CREATE OR REPLACE FUNCTION boardTotalPage RETURN NUMBER
IS
    pTotal NUMBER;
BEGIN
    SELECT CEIL(COUNT(*)/10.0) INTO pTotal
    FROM project_board;
    RETURN pTotal;
END;
/
 cs

 

-- 댓글 테이블

1
2
3
4
5
6
7
8
9
10
-- 댓글
CREATE TABLE project_reply(
    no NUMBER, -- 고유번호(수정, 삭제), 자동증가번호
    type NUMBER, -- 영화, 맛집, 게시판 별 모든 댓글 가능
    cno NUMBER, -- 참조번호
    id VARCHAR2(20) CONSTRAINT pr_id_nn NOT NULL,
    name VARCHAR2(34) CONSTRAINT pr_name_nn NOT NULL,
    msg CLOB CONSTRAINT pr_msg_nn NOT NULL,
    regdate DATE DEFAULT SYSDATE
);
 cs

type: 레시피(1), 맛집(2), 게시판(3) => JDBC(X), MyBatis

 

-- 댓글목록 PROCEDURE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 목록
CREATE OR REPLACE PROCEDURE replyListData(
    pType project_reply.type%TYPE,
    pCno project_reply.cno%TYPE,
    pStart NUMBER,
    pEnd NUMBER,
    pResult OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN pResult FOR
    SELECT no,type,cno,id,name,msg,TO_CHAR(regdate,'YYYY-MM-DD HH24:MI:SS'),num
    FROM (SELECT no,type,cno,id,name,msg,regdate,rownum as num
    FROM (SELECT no,type,cno,id,name,msg,regdate
    FROM project_reply WHERE type=pType AND cno=pCno ORDER BY no DESC))
    WHERE num BETWEEN pStart AND pEnd;
END;
/
 cs

 

-- 댓글수정 PROCEDURE

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 수정
CREATE OR REPLACE PROCEDURE replyUpdate(
    pNo project_reply.no%TYPE,
    pMsg project_reply.msg%TYPE
)
IS
BEGIN
    UPDATE project_reply SET
    msg=pMsg
    WHERE no=pNo;
    COMMIT;
END;
/
 cs

 

-- 댓글삭제 PROCEDURE

1
2
3
4
5
6
7
8
9
10
11
-- 삭제
CREATE OR REPLACE PROCEDURE replyDelete(
    pNo project_reply.no%TYPE
)
IS
BEGIN
    DELETE FROM project_reply
    WHERE no=pNo;
    COMMIT;
END;
/
 cs

 

-- 댓글추가 PROCEDURE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--추가
CREATE OR REPLACE PROCEDURE replyInsert(
    pType project_reply.type%TYPE,
    pCno project_reply.cno%TYPE,
    pId project_reply.id%TYPE,
    pName project_reply.name%TYPE,
    pMsg project_reply.msg%TYPE,
)
IS
    vNo project_reply.no%TYPE,
BEGIN
    SELECT NVL(MAX(no)+1,1) INTO vNo --vNo가 최대값 가져옴
    FROM project_reply;
    INSERT INTO project_reply(no, type, cno, id, name, msg)
    VALUES(vNo, pType, pCno, pId, pName, pMsg)
    COMMIT;
END;
/
 cs

 

  Spring  

 

src/main/java

com.sist.board.dao

BoardVO

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
package com.sist.board.dao;
import java.util.*;
public class BoardVO {
    private int no,hit;
    private String name,subject,content,pwd;
    private Date regdate;
    public int getNo() {
        return no;
    }
    public void setNo(int no) {
        this.no = no;
    }
    public int getHit() {
        return hit;
    }
    public void setHit(int hit) {
        this.hit = hit;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getSubject() {
        return subject;
    }
    public void setSubject(String subject) {
        this.subject = subject;
    }
    public String getContent() {
        return content;
    }
    public void setContent(String content) {
        this.content = content;
    }
    public String getPwd() {
        return pwd;
    }
    public void setPwd(String pwd) {
        this.pwd = pwd;
    }
    public Date getRegdate() {
        return regdate;
    }
    public void setRegdate(Date regdate) {
        this.regdate = regdate;
    }
    
}
 
 cs

 

ReplyVO

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
package com.sist.board.dao;
 
import java.util.*;
 
public class ReplyVO {
    private int no;
    private int type;
    private int cno;
    private String id;
    private String name;
    private String msg;
    private Date regdate;
    private String dbday;
    public int getNo() {
        return no;
    }
    public void setNo(int no) {
        this.no = no;
    }
    public int getType() {
        return type;
    }
    public void setType(int type) {
        this.type = type;
    }
    public int getCno() {
        return cno;
    }
    public void setCno(int cno) {
        this.cno = cno;
    }
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getMsg() {
        return msg;
    }
    public void setMsg(String msg) {
        this.msg = msg;
    }
    public Date getRegdate() {
        return regdate;
    }
    public void setRegdate(Date regdate) {
        this.regdate = regdate;
    }
    public String getDbday() {
        return dbday;
    }
    public void setDbday(String dbday) {
        this.dbday = dbday;
    }
    
}
 
 cs

 

MerberVO

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
32
package com.sist.board.dao;
 
public class MemberVO {
    private String id;
    private String pwd;
    private String name;
    private String message;
    public String getMessage() {
        return message;
    }
    public void setMessage(String message) {
        this.message = message;
    }
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getPwd() {
        return pwd;
    }
    public void setPwd(String pwd) {
        this.pwd = pwd;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }    
}
 cs

 

DBConnection

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
package com.sist.board.dao;
 
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
 
public class DBConnection {
    private Connection conn;
    private CallableStatement cs;
    private String driver,url,username,password;
    public DBConnection(String driver, String url, String username, String password) {
        this.driver=driver;
        this.url=url;
        this.username=username;
        this.password=password;
        
        try {
            Class.forName(driver);
        }catch(Exception ex) {}
    }
    
    public void getConnection() {
        try {
            conn=DriverManager.getConnection(url,username,password);
        }catch(Exception ex) {}
    }
    
    public void disConnection() {
        try {
            if(conn!=null) conn.close();
            if(cs!=null) cs.close();
        }catch(Exception ex) {}
    }
    
    // Source - Getters/Setters로 conn, cs생성
    
    public Connection getConn() {
        return conn;
    }
    public void setConn(Connection conn) {
        this.conn = conn;
    }
    public CallableStatement getCs() {
        return cs;
    }
    public void setCs(CallableStatement cs) {
        this.cs = cs;
    }
    
}
 
 cs

 

BoardDAO (DBAspect에서 AOP썼으므로 주석부분 생략)

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
package com.sist.board.dao;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
 
import oracle.jdbc.OracleTypes;
 
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;
 
@Repository
public class BoardDAO {
    @Autowired
    private DBConnection dbConn;
    private CallableStatement cs; //PROCEDURE, FUNCTION
    private PreparedStatement ps; //SQL
    public List<BoardVO> boardListData(int page){
        System.out.println("BoardDAO:"+dbConn);
        List<BoardVO> list=new ArrayList<BoardVO>();
        // dbConn.getConnection()
        
        
        //함수 호출
        /*
         * CREATE OR REPLACE PROCEDURE projectBoardListData(
                pStart NUMBER,
                pEnd NUMBER,
                pResult OUT SYS_REFCURSOR,
            )
            OPEN pResult FOR
              SELECT no,subject,name,regdate,hit,num
         */
        try { // CALL => CallableStatement
            String sql="{CALL projectBoardListData(?,?,?)}";
            cs=dbConn.getConn().prepareCall(sql);
            int rowSize=10;
            int start=(rowSize*page)-(rowSize-1);
            int end=rowSize*page;
            cs.setInt(1, start);
            cs.setInt(2, end);
            cs.registerOutParameter(3, OracleTypes.CURSOR); //ojdbc14.jar필요
            // 실행
            cs.executeQuery();
            // 데이터 받기
            ResultSet rs=(ResultSet)cs.getObject(3);
            while(rs.next()) {
                BoardVO vo=new BoardVO();
                vo.setNo(rs.getInt(1));
                vo.setSubject(rs.getString(2));
                vo.setName(rs.getString(3));
                vo.setRegdate(rs.getDate(4));
                vo.setHit(rs.getInt(5));
                list.add(vo);
            }
        }catch(Exception ex) {}
        // dbConn.disConnection()
        return list;
    }
    /*
     * CREATE OR REPLACE PROCEDRUE projectBoardInsert(
            pName project_board.name%TYPE,
            pSubject project_board.subject%TYPE,
            pContent project_board.content%TYPE,
            pPwd project_board.pwd%TYPE,
        )
     */
    public void boardInsert(BoardVO vo) {
        try {
            String sql="{CALL projectBoardInsert(?,?,?,?)}";
            cs=dbConn.getConn().prepareCall(sql);
            cs.setString(1, vo.getName());
            cs.setString(2, vo.getSubject());
            cs.setString(3, vo.getContent());
            cs.setString(4, vo.getPwd());
            cs.executeQuery();
        }catch(Exception ex) {}
    }
    /*
     CREATE OR REPLACE PROCEDURE projectBoardDetailData(
        pNo project_board.no%TYPE,
        pResult OUT SYS_REFCURSOR
        
        OPEN pResult FOR
          SELECT no,name,subject,content,regdate,hit
     */
    public BoardVO boardDetailData(int no) {
        BoardVO vo=new BoardVO();
        // getConnection() => @Before
        try {
            String sql="{CALL projectBoardDetailData(?,?)}";
            cs=dbConn.getConn().prepareCall(sql);
            cs.setInt(1, no);
            cs.registerOutParameter(2, OracleTypes.CURSOR); // 커서로 한번에 받아옴
            cs.executeQuery();
             // 데이터 받기
            ResultSet rs=(ResultSet)cs.getObject(2);
            rs.next();
            vo.setNo(rs.getInt(1));
            vo.setName(rs.getString(2));
            vo.setSubject(rs.getString(3));
            vo.setContent(rs.getString(4));
            vo.setRegdate(rs.getDate(5));
            vo.setHit(rs.getInt(6));
            rs.close();
        }catch(Exception ex) {}
        // disConnection() => @After
        return vo;
    }
    /*
     수정 데이터 읽기
     CREATE OR REPLACE PROCEDURE projectBoardUpdateData(
        pNo project_board.no%TYPE,
        pResult OUT SYS_REFCURSOR
 
        OPEN pResult FOR
         SELECT no,name,subject,content
     */
    public BoardVO boardUpdateData(int no) {
        BoardVO vo=new BoardVO();
        // getConnection() => @Before
        try {
            String sql="{CALL projectBoardUpdateData(?,?)}";
            // DBAspect와 DAO의 dbConn이 동일해야함 @Autowired
            cs=dbConn.getConn().prepareCall(sql);
            // 실행 요청 ?에 값을 채운다
            cs.setInt(1, no);
            // OUT => 저장 공간을 만들어 준다
            cs.registerOutParameter(2, OracleTypes.CURSOR);
            cs.executeQuery();
            // 저장공간에서 값을 가지고 온다
            ResultSet rs=(ResultSet)cs.getObject(2); //더 작기때문에 형변환
            rs.next();
            vo.setNo(rs.getInt(1));
            vo.setName(rs.getString(2));
            vo.setSubject(rs.getString(3));
            vo.setContent(rs.getString(4));
            rs.close();
            // Client => DAO(바로연결X)
            // Client <==> Model <==> DAO
        }catch(Exception ex) {/*@AfterThrowing*/} // AOP에서 처리 어떤 오류가 났는지
        // disConnection() => @After
        return vo; // @AfterReturning
    }
    
 
    /*
     CREATE OR REPLACE PROCEDURE projectBoardUpdate(
        pNo project_board.no%TYPE,
        pName project_board.name%TYPE,
        pSubject project_board.subject%TYPE,
        pContent project_board.content%TYPE,
        pPwd project_board.pwd%TYPE,
        pResult OUT project_board.name%TYPE
    )
    IS
        vPwd project_board.pwd%TYPE;
    BEGIN
        SELECT pwd INTO vPwd
        FROM project_board
        WHERE no=pNo;
    
        IF(vPwd=pPwd) THEN
         pResult:='true';
         UPDATE project_board SET
         name=pName,subject=pSubject,content=pContent
         WHERE no=pNo;
         COMMIT;
        ELSE
         pResult:='false';
        END IF;
    END;
     */
    public boolean boardUpdate(BoardVO vo) {
        boolean bCheck=false;
        try {
            String sql="{CALLprojectBoardUpdate(?,?,?,?,?,?)}";
            // 전송 => 오라클
            cs=dbConn.getConn().prepareCall(sql);
            cs.setInt(1, vo.getNo());
            cs.setString(2, vo.getName());
            cs.setString(3, vo.getSubject());
            cs.setString(4, vo.getContent());
            cs.setString(5, vo.getPwd());
            cs.registerOutParameter(6, OracleTypes.VARCHAR);
            cs.executeQuery();
            String result=cs.getString(6);
            // Cursor => 자바에서는 존재X => ResultSet
            bCheck=Boolean.parseBoolean(result); // Boolean형으로 바꿈
        }catch(Exception ex) {}
        return bCheck;
    }
    /*
     CREATE OR REPLACE PROCEDURE projectBoardDelete(
        pNo project_board.no%TYPE,
        pPwd project_board.pwd%TYPE,
        pResult OUT project_board.name%TYPE
    )
    IS
        vPwd project_board.pwd%TYPE; --변수선언
    BEGIN
        SELECT pwd INTO vPwd -- pPwd는 사용자가 보낸 비밀번호
        FROM project_board -- pwd는 project_board의 db안에 저장되어있는 비밀번호
        WHERE no=pNo;
     
        IF(vPwd=pPwd) THEN
         pResult:='true';
         DELETE FROM project_board
         WHERE no=pNo;
         COMMIT;
        ELSE
         pResult:='false';
        END IF;
    END;
    /
 
     */
    public boolean boardDelete(int no, String pwd) {
        boolean bCheck=false;
        try {
            String sql="{CALL projectBoardDelete(?,?,?)}";
            cs=dbConn.getConn().prepareCall(sql);
            cs.setInt(1, no);
            cs.setString(2, pwd);
            cs.registerOutParameter(3, OracleTypes.VARCHAR);
            cs.executeQuery();
            String result=cs.getString(3);
            bCheck=Boolean.parseBoolean(result);
        }catch(Exception ex) {}
        return bCheck;
    }
    public int boardTotalPage() {
        int total=0;
        try { //FUNCTION은 결과값 하나만 가져오기 때문에 SELECT문을 사용한다 => PreparedStatement
            String sql="SELECT boardTotalPage() FROM project_board";
            ps=dbConn.getConn().prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            rs.next();
            total=rs.getInt(1);
            rs.close();
            ps.close();
            /*
             * PROCEDURE => {CALL pro_name()}
             * FUNCTION => SELECT func_name()
             */
        }catch(Exception ex) {}
        return total;
    }
    
    // 댓글
    /*
     * CREATE OR REPLACE PROCEDURE replyListData(
            pType project_reply.type%TYPE,
            pCno project_reply.cno%TYPE,
            pStart NUMBER,
            pEnd NUMBER,
            pResult OUT SYS_REFCURSOR
        )
        IS
        BEGIN
            OPEN pResult FOR
            SELECT no,type,cno,id,name,msg,TO_CHAR(regdate,'YYYY-MM-DD HH24:MI:SS'),num
     */
                        //board~로 시작하지 않아서 AOP적용이 안됨
                        //=>DBAspect에서 reply~도 추가해줌
    public List<ReplyVO> replyListData(int type,int cno,int page){
        List<ReplyVO> list=new ArrayList<ReplyVO>();
        try {
            String sql="{CALL replyListData(?,?,?,?,?)}";
            cs=dbConn.getConn().prepareCall(sql);
            cs.setInt(1, type);
            cs.setInt(2, cno);
            int rowSize=5;
            int start=(rowSize*page)-(rowSize-1);
            int end=rowSize*page;
            cs.setInt(3, start);
            cs.setInt(4, end);
            cs.registerOutParameter(5, OracleTypes.CURSOR);
            cs.executeQuery();
            ResultSet rs=(ResultSet)cs.getObject(5);
            while(rs.next()) {
                ReplyVO vo=new ReplyVO();
                vo.setNo(rs.getInt(1));
                vo.setType(rs.getInt(2));
                vo.setCno(rs.getInt(3));
                vo.setId(rs.getString(4));
                vo.setName(rs.getString(5));
                vo.setMsg(rs.getString(6));
                vo.setDbday(rs.getString(7));
                list.add(vo);
            }
            rs.close();
        }catch(Exception ex) {}
        return list;
    }
    /*
     * CREATE OR REPLACE PROCEDURE replyInsert(
        pType project_reply.type%TYPE,
        pCno project_reply.cno%TYPE,
        pId project_reply.id%TYPE,
        pName project_reply.name%TYPE,
        pMsg project_reply.msg%TYPE,
     */
    public void replyInsert(ReplyVO vo) {
        try {
            String sql="{CALL replyInsert(?,?,?,?,?}";
            cs=dbConn.getConn().prepareCall(sql);
            cs.setInt(1, vo.getType());
            cs.setInt(2, vo.getCno());
            cs.setString(3, vo.getId());
            cs.setString(4, vo.getName());
            cs.setString(5, vo.getMsg());
            cs.executeQuery();
        }catch(Exception ex) {}
    }
    /* 댓글수정
     * CREATE OR REPLACE PROCEDURE replyUpdate(
        pNo project_reply.no%TYPE,
        pMsg project_reply.msg%TYPE
     */
    public void replyUpdate(int no, String msg) {
        try {
            String sql="{CALL replyUpdate(?,?)}";
            cs=dbConn.getConn().prepareCall(sql);
            cs.setInt(1, no);
            cs.setString(2, msg);
            cs.executeQuery();
        }catch(Exception ex) {}
    }
    /*
     * 댓글삭제
        CREATE OR REPLACE PROCEDURE replyDelete(
            pNo project_reply.no%TYPE
     */
    public void replyDelete(int no) {
        try {
            String sql="{CALL replyDelete(?)}";
            cs=dbConn.getConn().prepareCall(sql);
            cs.setInt(1, no);
            cs.executeQuery();
        }catch(Exception ex) {}
    }
    // 로그인
    public MemberVO memberLogin(String id,String pwd) {
        MemberVO vo=new MemberVO();
        try {
            dbConn.getConnection();
            String sql="SELECT COUNT(*) FROM member "
                    +"WHERE id=?";
            ps=dbConn.getConn().prepareStatement(sql);
            ps.setString(1, id);
            ResultSet rs=ps.executeQuery();
            rs.next();
            int count=rs.getInt(1);
            rs.close();
            if(count==0) { //ID가 없는 상태
                vo.setMessage("NOID");
            }else { //ID가 존재하는 상태
                sql="SELECT pwd,name FROM member "
                    +"WHERE id=?";
                ps=dbConn.getConn().prepareStatement(sql);
                ps.setString(1, id);
                rs=ps.executeQuery();
                rs.next();
                String db_pwd=rs.getString(1);
                String name=rs.getString(2);
                rs.close();
                
                if(db_pwd.equals(pwd)) { //로그인
                    vo.setId(id);
                    vo.setName(name);
                    vo.setMessage("OK");
                }else { //비밀번호가 틀린 경우
                    vo.setMessage("NOPWD");
                }
            }
        }catch(Exception ex) {
            System.out.println(ex.getMessage());
        }finally {
            dbConn.disConnection();
        }
        return vo;
    }
}
 
 cs

 

DAO에서 OracleTypes.CURSOR을 쓰기위해 pom.xml에 ojdbc14.jar을 추가하고

webapp/WEB-INF/lib폴더에 ojdbc14.jar을 포함한다

1
2
3
4
5
6
7
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc14</artifactId>
    <version>10.2.0.4.0</version>
    <scope>system</scope>
    <systemPath>${project.basedir}/src/main/webapp/WEB-INF/lib/ojdbc14.jar</systemPath>
</dependency>
 cs

 

DAO와 Aspect에 DBConnection은 같은 주소!

=> 싱글톤 방식이기 때문에(하나를 고정시켜놓고 공유하는 방식)

=> Autowired로 주소값 같아야하기 때문에 DBConnection 동일해야함

obj는 @AfterReturning으로 vo를 return한 주소

com.sist.common

DBAspect

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
32
33
34
35
36
37
38
39
40
41
package com.sist.common;
 
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.AfterReturning;
import org.aspectj.lang.annotation.AfterThrowing;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
 
import com.sist.board.dao.DBConnection;
 
@Aspect
// application-*.xml에
// <aop:aspectj-autoproxy></aop:aspectj-autoproxy> 추가
@Component
public class DBAspect {
    @Autowired
    private DBConnection dbCon;
                                        //board로 시작하는 모든 메소드에 적용
    @Before("execution(* com.sist.board.dao.BoardDAO.board*(..))||execution(* com.sist.board.dao.BoardDAO.reply*(..))")
    public void before() {
        System.out.println("DBAspect:"+dbCon);
        dbCon.getConnection();
    }
    @After("execution(* com.sist.board.dao.BoardDAO.board*(..))||execution(* com.sist.board.dao.BoardDAO.reply*(..))")
    public void after() {
        dbCon.disConnection();
    }
    // catch절(에러잡기) 수행하는 Annotation
    @AfterThrowing(value="execution(* com.sist.board.dao.BoardDAO.board*(..))||execution(* com.sist.board.dao.BoardDAO.reply*(..))",throwing="ex")
    public void afterThrowing(Throwable ex) {
        ex.printStackTrace();
    }
    // return형이 어떻게 되었는지 확인 / null이면 값이 안들어온것
    @AfterReturning(value="execution(* com.sist.board.dao.BoardDAO.board*(..))||execution(* com.sist.board.dao.BoardDAO.reply*(..))",returning="obj")
    public void afterReturning(Object obj) {
        System.out.println("obj="+obj);
    }
}
 
 cs

AOP가 적용되면 아래와 같이 왼쪽에 화살표가 생김!

 

com.sist.web

BoardController

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
package com.sist.web;
import java.util.*;
 
import javax.servlet.http.HttpSession;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
 
import com.sist.board.dao.*;
 
@Controller
// forward, sendRedirect => 파일명 => 전송 (<script>사용할 수 없다) => jsp에서
// => @RestController을 사용해야함
@RequestMapping("board/") // board/ 경로명 중복 뺄수있게끔
public class BoardController {
    @Autowired
    private BoardDAO dao;
    // 목록
    @RequestMapping("list.do")
    public String board_list(String page,Model model) {
        if(page==null)
            page="1";
        int curpage=Integer.parseInt(page);
        List<BoardVO> list=dao.boardListData(curpage);
        int totalpage=dao.boardTotalPage();
        model.addAttribute("curpage", curpage);
        model.addAttribute("totalpage", totalpage);
        model.addAttribute("list", list);
        return "board/list";
    }
    // 글쓰기
    @RequestMapping("insert.do")
    public String board_insert() {
        return "board/insert";
    }
    // 글쓰기실행
    @RequestMapping("insert_ok.do")
    public String board_insert_ok(BoardVO vo) {
        dao.boardInsert(vo);
        return "redirect:../board/list.do";
    }
    // 상세보기
    @RequestMapping("detail.do")
    public String board_detailData(int no, String page, Model model) {
        if(page==null)
            page="1";
        int curpage=Integer.parseInt(page);
        BoardVO vo=dao.boardDetailData(no);
        List<ReplyVO> list=dao.replyListData(3, no, curpage); //type : 3
        model.addAttribute("vo", vo);
        model.addAttribute("list", list);
        return "board/detail";
    }
    // 수정
    @RequestMapping("update.do")
    public String board_update(int no, Model model) {
        BoardVO vo=dao.boardUpdateData(no);
        model.addAttribute("vo", vo);
        return "board/update";
    }
    // 수정실행
    @RequestMapping("update_ok.do")
    public String board_update_ok(BoardVO vo, Model model) {
        // DAO연동
        boolean bCheck=dao.boardUpdate(vo);
        model.addAttribute("bCheck", bCheck);
        model.addAttribute("no", vo.getNo()); //데이터값을 동시에 넘길수없기때문에
                                    //리스트가 아니라 상세보기로 넘어감 수정=>디테일
                                    //디테일로 넘어가기 위해서 no가 필요
        return "board/update_ok"; //ajax
        //RestController쓰면 안만들어도되지만 구글(크롬)에서만 가능 explored에서는 안됨
    }
    // 삭제
    @RequestMapping("delete.do")
    public String board_delete(int no, Model model) {
        model.addAttribute("no", no);
        return "board/delete";
    }
    // 삭제실행
    @RequestMapping("delete_ok.do")
    public String board_delete_ok(int no, String pwd, Model model) {
        boolean bCheck=dao.boardDelete(no, pwd);
        model.addAttribute("bCheck", bCheck);
        return "board/delete_ok";
    }
    // 로그인
    @RequestMapping("login.do")
    public String board_login() {
        return "board/login";
    }
    
    @RequestMapping("login_ok.do")
    public String board_login_ok(String id, String pwd, Model model, HttpSession session) {
        MemberVO vo=dao.memberLogin(id, pwd);
        if(vo.getMessage().equals("OK")) {
            session.setAttribute("id", id);
            session.setAttribute("name", vo.getName());
        }
        model.addAttribute("msg", vo.getMessage());
        return "board/login_ok";
    }
    @RequestMapping("reply_insert.do")
    public String replyInsert(ReplyVO vo, HttpSession session) {
        vo.setId((String)session.getAttribute("id"));
        vo.setName((String)session.getAttribute("name"));
        vo.setType(3);
        dao.replyInsert(vo);
        return "redirect:detail.do?no="+vo.getCno();
    }
    @RequestMapping("reply_delete.do")
    public String reply_delete(int no, int cno) {
        dao.replyDelete(no);
        return "redirect:detail.do?no="+cno;
    }
    @RequestMapping("reply_update.do")
    public String reply_update(int no, int cno, String msg) {
        dao.replyUpdate(no, msg);
        return "redirect:detail.do?no="+cno;
    }
}
 
 cs

 

WEB-INF/config

application-context.xml

c : 생성자(Constructor) DI

p : Setter DI

 

ASPECT사용하려면 application-context.xml에 aop넣어줘야함!!

1
<aop:aspectj-autoproxy></aop:aspectj-autoproxy>
 cs
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
32
33
34
35
36
37
38
39
40
41
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:p="http://www.springframework.org/schema/p"
    xmlns:c="http://www.springframework.org/schema/c"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd">
     <aop:aspectj-autoproxy></aop:aspectj-autoproxy>
     <context:component-scan base-package="com.sist.*"/>
     <bean id="dbcon"
         class="com.sist.board.dao.DBConnection"
         c:driver="oracle.jdbc.driver.OracleDriver"
        c:url="jdbc:oracle:thin:@211.238.142.181:1521:XE"
        c:username="hr"
        c:password="happy"
     />
     <bean id="ds" class="org.apache.commons.dbcp.BasicDataSource"
       p:driverClassName="oracle.jdbc.driver.OracleDriver"
       p:url="jdbc:oracle:thin:@211.238.142.181:1521:XE"
       p:username="hr"
       p:password="happy"
       p:maxActive="20"
       p:maxIdle="10"
       p:maxWait="-1"
     />
     <bean id="ssf" class="org.mybatis.spring.SqlSessionFactoryBean"
       p:dataSource-ref="ds"
     />
     <bean id="mapper" class="org.mybatis.spring.mapper.MapperFactoryBean"
       p:sqlSessionFactory-ref="ssf"
       p:mapperInterface="com.sist.dao.EmpMapper"
     />
     <bean id="viewResolver"
         class="org.springframework.web.servlet.view.InternalResourceViewResolver"
         p:prefix="/"
         p:suffix=".jsp"
      />
</beans>
 cs

 

 

src/main/webapp/board 생성 (jsp파일은 그대로 써도됨)

list.jsp

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%-- SimpleDateFormat --%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<style type="text/css">
.row {
   margin: 0px auto;
   width:900px;
}
h1 {
    text-align: center;
}
</style>
</head>
<body>
   <div class="container">
     <div class="row">
       <h1>스프링 게시판(Procedure이용)</h1>
       <table class="table">
         <tr>
           <td>
             <a href="insert.do" class="btn btn-sm btn-primary">새글</a>
           </td>
         </tr>
       </table>
       <table class="table table-striped">
         <tr class="danger">
           <th class="text-center" width=10%>번호</th>
           <th class="text-center" width=45%>제목</th>
           <th class="text-center" width=15%>이름</th>
           <th class="text-center" width=20%>작성일</th>
           <th class="text-center" width=10%>조회수</th>
         </tr>
         <%-- 데이터 출력 위치 --%>
         <%--
               for(BoardVO vo:request.getAttribute("list"))
               => request.setAttribute("list",list);
               ${list}=request.getAttribute("list")
          --%>
         <c:forEach var="vo" items="${list }">
           <tr>
               <td class="text-center" width=10%>${vo.no }</td>
               <td class="text-left" width=45%>
                 <a href="detail.do?no=${vo.no }">${vo.subject }</a>
               </td>
               <td class="text-center" width=15%>${vo.name }</td>
               <td class="text-center" width=20%>
                 <fmt:formatDate value="${vo.regdate }" pattern="yyyy-MM-dd"/>
               </td>
               <td class="text-center" width=10%>${vo.hit }</td>
           </tr>
         </c:forEach>
       </table>
       <table class="table">
         <td class="text-center">
          <a href="#" class="btn btn-sm btn-primary">이전</a>
          ${curpage } page / ${totalpage } pages
          <a href="#" class="btn btn-sm btn-primary">다음</a>
         </td>
       </table>
     </div>
   </div>
</body>
</html>
 cs

 

insert.jsp

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<style type="text/css">
.row {
   margin: 0px auto;
   width:700px;
}
h1 {
   text-align: center;
}
</style>
 
</head>
<body>
  <div class="container">
    <div class="row">
     <h1>글쓰기</h1>
     <form method=post action="insert_ok.do">
     <table class="table">
       <tr>
        <th class="text-right danger" width=15%>이름</th>
        <td width=85%>
          <input type=text name=name size=15>
        </td>
       </tr>
       
       <tr>
        <th class="text-right danger" width=15%>제목</th>
        <td width=85%>
          <input type=text name=subject size=50>
        </td>
       </tr>
       
       <tr>
        <th class="text-right danger" width=15%>내용</th>
        <td width=85%>
          <textarea rows="8" cols="55" name=content></textarea>
        </td>
       </tr>
       <tr>
        <th class="text-right danger" width=15%>비밀번호</th>
        <td width=85%>
          <input type="password" name=pwd size=10>
        </td>
       </tr>
       
       <tr>
        <td colspan="2" class="text-center">
          <input type=submit value="글쓰기">
          <input type=button value="취소" onclick="javascript:history.back()">
        </td>
       </tr>
     </table>
     </form>
    </div>
  </div>
</body>
</html>
 cs

 

detail.jsp

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<style type="text/css">
.row {
   margin: 0px auto;
   width:1000px;
}
h1 {
   text-align: center;
}
</style>
<script type="text/javascript" src="http://code.jquery.com/jquery.js"></script>
<script type="text/javascript">
let i=0;
$(function(){
    $('.upBtn').click(function(){
        $('.update').hide(); //전체 열린창 다 닫기
        let no=$(this).attr("data-no");
        if(i==0)
        {
            $(this).text("취소"); //해당되는 창 열기
            $('#reply_up'+no).show(); //하나씩 열때
            i=1;
        }
        else
        {
            $(this).text("수정");
            $('#reply_up'+no).hide(); //하나씩 닫을때
            i=0;
        }
    });
});
</script>
</head>
<body>
   <div class="container">
     <div class="row">
      <h1>내용보기</h1>
      <table class="table table-striped">
        <tr>
          <th width=20% class="text-center danger">번호</th>
          <td width=30% class="text-center">${vo.no }</td>
          <th width=20% class="text-center danger">작성일</th>
          <td width=30% class="text-center"> 
            <fmt:formatDate value="${vo.regdate }" pattern="yyyy-MM-dd"/>
          </td>
        </tr>
        <tr>
          <th width=20% class="text-center danger">이름</th>
          <td width=30% class="text-center">${vo.name }</td>
          <th width=20% class="text-center danger">조회수</th>
          <td width=30% class="text-center">${vo.hit }</td>
        </tr>
        <tr>
          <th width=20% class="text-center danger">제목</th>
          <td colspan="3" class="text-left">${vo.subject }</td>
        </tr>
        
        <tr>
          <td colspan="4" class="text-left" valign="top" height=200>
            ${vo.content }
          </td>
        </tr>
        <tr>
          <td colspan="4" class="text-right">
            <a href="update.do?no=${vo.no }" class="btn btn-sm btn-success">수정</a>
            <a href="delete.do?no=${vo.no }" class="btn btn-sm btn-info">삭제</a>
            <a href="list.do" class="btn btn-sm btn-warning">목록</a>
          </td>
        </tr>
      </table>
     </div>
     <div class="row">
       <%-- 분석 결과 출력  --%>
       <div class="col-sm-6">
       
        <table class="table">
         <tr>
           <td>
            <c:forEach var="rvo" items="${list }">
             <table class="table">
              <tr>
                <td class="text-left">
                               ◐${rvo.name }(${rvo.dbday})
                </td>
                <td class="text-right">
                  <c:if test="${sessionScope.id==rvo.id }">
                                                          <!-- 클릭했을때 javascript -->
                    <span class="btn btn-xs btn-success upBtn" data-no="${rvo.no }">수정</span>
                    <a href="reply_delete.do?no=${rvo.no }&cno=${vo.no}" class="btn btn-xs btn-info">삭제</a>
                                              <!-- 댓글번호,      게시물번호 -->
                  </c:if>
                </td>
              </tr>
              <tr>
                <td colspan="2">
                  <pre style="white-space: pre-line;border:none;background-color: white">${rvo.msg }</pre>
                </td>
              </tr>
                          <!-- javascript 숨겼다가 수정버튼 눌리면 뜨게 -->
              <tr id="reply_up${rvo.no }" class="update" style="display:none">
                  <td colspan="2">
                    <form method=post action="reply_update.do">
                    <input type=hidden name=cno value=${vo.no }>
                    <input type=hidden name=no value=${rvo.no }>
                    <textarea rows="3" cols="45" style="float: left" name=msg>${rvo.msg }</textarea>
                    <input type=submit value="댓글수정"
                      class="btn btn-sm btn-danger" style="height:65px;float: left">
                    </form>
                  </td>
                 </tr>
             </table>
            </c:forEach>
           </td>
         </tr>
         <tr>
          <td>
            <form method=post action="reply_insert.do">
            <input type=hidden name=cno value=${vo.no }>
            <textarea rows="3" cols="45" style="float: left" name=msg></textarea>
            <input type=submit value="댓글쓰기"
              class="btn btn-sm btn-danger" style="height:65px;float: left">
            </form>
          </td>
         </tr>
        </table>
       </div>
       <div class="col-sm-6"></div>
     </div>
   </div>
</body>
</html>
 cs

 

update.jsp

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<style type="text/css">
.row {
   margin: 0px auto;
   width:700px;
}
h1 {
   text-align: center;
}
</style>
 
</head>
<body>
  <div class="container">
    <div class="row">
     <h1>수정하기</h1>
     <form method=post action="update_ok.do">
     <table class="table">
       <tr>
        <th class="text-right danger" width=15%>이름</th>
        <td width=85%>
          <input type=text name=name size=15 value="${vo.name }">
          <input type=hidden name=no value="${vo.no }">
        </td>
       </tr>
       
       <tr>
        <th class="text-right danger" width=15%>제목</th>
        <td width=85%>
          <input type=text name=subject size=50 value="${vo.subject }">
        </td>
       </tr>
       
       <tr>
        <th class="text-right danger" width=15%>내용</th>
        <td width=85%>
          <textarea rows="8" cols="55" name=content>${vo.content }</textarea>
        </td>
       </tr>
       <tr>
        <th class="text-right danger" width=15%>비밀번호</th>
        <td width=85%>
          <input type="password" name=pwd size=10>
        </td>
       </tr>
       
       <tr>
        <td colspan="2" class="text-center">
          <input type=submit value="수정">
          <input type=button value="취소" onclick="javascript:history.back()">
        </td>
       </tr>
     </table>
     </form>
    </div>
  </div>
</body>
</html>
 cs

 

update_ok.jsp (스크립트는 Controller에서 못쓰기 때문에 jsp에서 써야함!) => @RestController도 사용하면 가능

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<c:choose>
  <c:when test="${bCheck==true }">
    <c:redirect url="detail.do?no=${no }"/>
  </c:when>
  <c:otherwise>
    <script>
     alert("비밀번호가 틀립니다!!");
     history.back();
    </script>
  </c:otherwise>
</c:choose>
 cs

 

delete.jsp

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
32
33
34
35
36
37
38
39
40
41
42
43
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<style type="text/css">
.row {
   margin: 0px auto;
   width:350px;
}
h1 {
   text-align: center;
}
</style>
</head>
<body>
   <div class="container">
    <div class="row">
      <h1>삭제하기</h1>
      <form action="delete_ok.do" method="post">
      <table class="table">
       <tr>
         <td>
          비밀번호:<input type=password name=pwd size=15 class="input-sm">
            <input type=hidden name=no value="${no }">
         </td>
       </tr>
       <tr>
         <td class="text-center">
           <input type=submit value="삭제">
           <input type=button value="취소" onclick="javascript:history.back()">
         </td>
       </tr>
      </table>
      </form>
    </div>
   </div>
</body>
</html>
 
 cs

 

delete_ok.jsp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<c:choose>
  <c:when test="${bCheck==true }">
    <c:redirect url="list.do"/>
  </c:when>
  <c:otherwise>
    <script>
      alert("비밀번호가 틀립니다!!");
      history.back();
    </script>
  </c:otherwise>
</c:choose>
 cs

 

login.jsp

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<style type="text/css">
.row {
   margin: 0px auto;
   width:350px;
}
h1 {
   text-align: center;
}
</style>
</head>
<body>
   <div class="container">
    <div class="row">
      <h1>로그인</h1>
      <form action="login_ok.do" method="post">
      <table class="table">
       <tr>
         <td width=25% class="text-right">ID</td>
         <td width=75%>
           <input type=text name=id size=15>
         </td>
       </tr>
       <tr>
         <td width=25% class="text-right">PW</td>
         <td width=75%>
           <input type=password name=pwd size=15>
         </td>
       </tr>
       <tr>
         <td class="text-center" colspan="2">
           <input type=submit value="로그인">
           <input type=button value="취소" onclick="javascript:history.back()">
         </td>
       </tr>
      </table>
      </form>
    </div>
   </div>
</body>
</html>
 cs

 

login_ok.jsp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<c:choose>
  <c:when test="${msg=='NOID' }">
   <script>
    alert("아이디가 존재하지 않습니다");
    history.back();
   </script>
  </c:when>
  <c:when test="${msg=='NOPWD' }">
    <script>
     alert("비밀번호가 틀립니다");
     history.back();
    </script>
  </c:when>
  <c:otherwise>
    <c:redirect url="list.do"/>
  </c:otherwise>
</c:choose>
 cs

 

반응형

'Back-end > Spring' 카테고리의 다른 글

Spring 16일차 - Transaction 게시판 만들기 / 쿠키  (1) 2020.11.18
Spring 15일차 - AOP  (0) 2020.11.17
Spring 14일차 - Tiles  (1) 2020.11.16
Spring 13일차 - Mybatis연동, JSON  (0) 2020.11.13
Spring 12일차 - PL/SQL Procedure 게시판 만들기  (0) 2020.11.12
'Back-end/Spring' Related Articles more
Comments