개발자로가는길

카페 24에서 자동으로 워드프레스를 설치하는 방법

1. 웹호스팅 옵션 중에 10G광호스팅 FullSSD을 선택, 사이트의 용량에 맞게 신청하기 클릭

2. 관리자 정보 설정 > 다음

3.  서버 환경설정 > 서버환경 PHP 버전 설정 (PHP 7.0이상 설정 권장)

프로그램 자동설치 체크

4. 결제완료

5. [나의 서비스관리] > [호스팅 관리] > [계정 관리]의 [프로그램 자동설치]

[설치프로그램선택] > 워드프레스 선택 > 설치하기 > DB비밀번호 입력 (약 3분 소요)

6. 관리자 페이지 접속 : http://계정명.cafe24.com/wp-admin 입력

계정은 호스팅 아이디, 호스팅 FTP/DB 비밀번호와 동일하게 세팅

7. 설치완료

1. SQL 

1) 수정시 boardDate를 수정시간으로 update

UPDATE board SET boardTitle = '제목변경 ', boardContent='내용변경 ', boardDate=now() WHERE boardNo=보드번호

2. View

1) WebContent - view - board - viewBoard.jsp 링크 추가

<a href="http://localhost:8080/webstudy-board/DispatcherServlet?command=updateBoardPage&boardNo=${boardInfo.boardDTO.boardNo}">수정</a>

2) WebContent - view - board - updateBoard.jsp 추가

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <div class="card"> <form method="post" action="DispatcherServlet"> <input type="hidden" name="command" value="updateBoard"> <input type="hidden" name="boardNo" value="${boardInfo.boardDTO.boardNo}"> <table> <tr> <td>글번호</td><td>${boardInfo.boardDTO.boardNo}"</td> <td>글제목</td><td><input type="text" name="boardTitle" value="${boardInfo.boardDTO.boardTitle}" > </td> </tr> <tr> <td>글쓴이</td><td>${boardInfo.boardDTO.clientId}</td> <td>이메일</td><td>${boardInfo.clientDTO.clientEmail}</td> </tr> <tr> <td>작성일</td><td>${boardInfo.boardDTO.boardDate}</td> <td>조회수</td><td>${boardInfo.boardDTO.boardCount}</td> </tr> <tr> <td colspan="4"><textarea name="boardContent" cols="50" rows="13">${boardInfo.boardDTO.boardContent}</textarea></td> </tr> </table> <a href="http://localhost:8080/webstudy-board/DispatcherServlet?command=boardList">목록으로</a> <input type="submit" value="수정"> </form> </div>

3. Model

1) src - model.BoardDAO.java에 updateBoard 메소드 추가

//4. 글수정 public void updateBoard(int boardNo, String boardTitle, String boardContent) throws SQLException { Connection con=null; PreparedStatement pstmt=null; try { con=getConnection(); String sql="UPDATE board SET boardTitle = ?, boardContent=?, boardDate=now() WHERE boardNo=?"; pstmt=con.prepareStatement(sql); pstmt.setString(1, boardTitle); pstmt.setString(2, boardContent); pstmt.setInt(3, boardNo); pstmt.execute(); }finally { closeAll(pstmt, con); } }

2) test - test.TestUpdateBoard.java 추가

package test; import model.BoardDAO; public class TestUpdateBoard { public static void main(String[] args) { int boardNo=13; String boardTitle="수정테스트"; String boardContent="수정 테스트 되나여?"; try { BoardDAO.getInstance().updateBoard(boardNo, boardTitle, boardContent); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } }

4. Controller

1) src - controller.HandlerMapping.java에 updateBoardPage, updateBoard 추가 

else if(command.equals("updateBoardPage")) // 글수정 페이지 이동 c=new UpdateBoardPageController(); else if(command.equals("updateBoard")) // 글수정 처리 c=new UpdateBoardController();

2) src - controller.UpdateBoardPageController.java 추가

package controller; import java.util.HashMap; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import model.BoardDAO; public class UpdateBoardPageController implements Controller { @Override public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception { HttpSession session = request.getSession(false); if(session==null||session.getAttribute("clientDTO")==null){ return "redirect:index.jsp"; } int boardNo=Integer.parseInt(request.getParameter("boardNo")); HashMap<String, Object> boardInfo= BoardDAO.getInstance().viewBoard(boardNo); request.setAttribute("boardInfo", boardInfo); request.setAttribute("url", "/view/board/updateBoard.jsp"); return "/view/template/home.jsp"; } }

3) src - controller.UpdateBoardController 추가

package controller; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import model.BoardDAO; public class UpdateBoardController implements Controller { @Override public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception { HttpSession session = request.getSession(false); if(session==null||session.getAttribute("clientDTO")==null){ return "redirect:index.jsp"; } int boardNo=Integer.parseInt(request.getParameter("boardNo")); String boardTitle=request.getParameter("boardTitle"); String boardContent=request.getParameter("boardContent"); BoardDAO.getInstance().updateBoard(boardNo,boardTitle,boardContent); return "redirect:DispatcherServlet?command=viewBoard&boardNo="+boardNo; } }

[참고]

mysql 현재시간 update https://m.blog.naver.com/PostView.nhn?blogId=teastful&logNo=140110768553&proxyReferer=https%3A%2F%2Fwww.google.com%2F

1. SQL

1) auto increment 값 받아오기

SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'table name' AND table_schema = DATABASE( ) ;

2. View

1)  WebContent - view - board- boardList.jsp 링크 수정

<a href="DispatcherServlet?command=createBoardPage">글쓰기</a>

2)  WebContent - view - board- createBoard.jsp 추가

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <div class="card"> <form action="DispatcherServlet" method="post"> <input type="hidden" name="command" value="createBoard"> <table> <tr> <td>작성자</td><td><input type="text" name ="clientName" value="${sessionScope.clientDTO.clientName}" readonly="readonly"></td> </tr> <tr> <td>글제목</td><td><input type="text" name="boardTitle"></td> </tr> <tr> <td>글쓰기</td><td><textarea name="boardContent" cols="50" rows="13"></textarea></td> </tr> <tr> <td colspan="2"><input type="submit" value="작성 완료"></td> </tr> </table> </form> </div>

3. Model

1) src - model.BoardDAO.java에 createBoard 메소드 추가

//3. 글쓰기 @SuppressWarnings("resource") public int createBoard(String boardTitle, String boardContent, String clientId) throws SQLException { Connection con=null; PreparedStatement pstmt=null; ResultSet rs=null; int boardNo=0; try { con=getConnection(); String sql="insert into board(boardTitle, boardContent, clientId) values (?,?,?)"; pstmt=con.prepareStatement(sql); pstmt.setString(1,boardTitle); pstmt.setString(2,boardContent); pstmt.setString(3,clientId); pstmt.execute(); pstmt.close(); pstmt=con.prepareStatement("SELECT AUTO_INCREMENT-1 FROM information_schema.tables WHERE table_name = 'board' AND table_schema = DATABASE()"); rs=pstmt.executeQuery(); while(rs.next()) boardNo=rs.getInt(1); }finally{ closeAll(rs, pstmt, con); } return boardNo; }

2) src - test.TestCreateBoard.java 추가

4. Controller

1) src - controller.HandlerMapping.java에 createBoard 추가

else if(command.equals("createBoard")) // 글쓰기 처리 c=new CreateBoardController();

2) src - controller.CreateBoardController.java 추가

package controller; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import model.BoardDAO; import model.ClientDTO; public class CreateBoardController implements Controller { @Override public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception { HttpSession session = request.getSession(false); if(session==null||session.getAttribute("clientDTO")==null){ return "redirect:index.jsp"; } ClientDTO clientDTO=(ClientDTO)session.getAttribute("clientDTO"); String boardTitle=request.getParameter("boardTitle"); String boardContent=request.getParameter("boardContent"); String clientId=clientDTO.getClientId(); int boardNo=BoardDAO.getInstance().createBoard(boardTitle,boardContent,clientId); return "redirect:DispatcherServlet?command=ViewBoard&boardNo="+boardNo; } }

[참고]

auto increment값 받아오기 https://wkdgusdn3.tistory.com/entry/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%EB%8B%A4%EC%9D%8C-autoincrement-%EA%B0%92-%EB%B0%9B%EC%95%84%EC%98%A4%EA%B8%B0

1. sql

1) Inner join으로 게시글과 사용자 정보 join

select b.boardNo,b.boardTitle,b.boardContent,b.clientId,b.boardDate,b.boardCount,c.clientName,c.clientEmail from board as b join client as c on b.clientId = c.clientId where boardNo='1'

2. View 

1) WebContent - view - board- boardList.jsp 링크 수정

<td><a href="DispatcherServlet?command=viewBoard&boardNo=${boardDTO.boardNo}">${boardDTO.boardTitle}</a></td>

2) WebContent - view - board- viewBoard.jsp 추가

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <div class="card"> <table> <tr> <td>글번호</td><td>${boardInfo.boardDTO.boardNo}</td> <td>글제목</td><td>${boardInfo.boardDTO.boardTitle} </td> </tr> <tr> <td>글쓴이</td><td>${boardInfo.boardDTO.clientId}</td> <td>이메일</td><td>${boardInfo.clientDTO.clientEmail}</td> </tr> <tr> <td>작성일</td><td>${boardInfo.boardDTO.boardDate}</td> <td>조회수</td><td>${boardInfo.boardDTO.boardCount}</td> </tr> <tr> <td colspan="4">${boardInfo.boardDTO.boardContent}</td> </tr> </table> <a href="http://localhost:8080/webstudy-board/DispatcherServlet?command=boardList">목록으로</a> <c:if test="${sessionScope.clientDTO.clientId== boardInfo.boardDTO.clientId}"> <a href="#">수정</a> <a href="#">삭제</a> </c:if> </div>

2. Model

1) src - model.BoardDAO.java에 viewBoard 메소드 추가

//2. 글상세 public HashMap<String, Object> viewBoard(int boardNo) throws SQLException { HashMap<String, Object> boardInfo=new HashMap<>(); Connection con=null; PreparedStatement pstmt=null; ResultSet rs=null; try { con=getConnection(); StringBuilder sql = new StringBuilder(); sql.append("select b.boardNo,b.boardTitle,b.boardContent,b.clientId,b.boardDate,b.boardCount,c.clientEmail "); sql.append("from board as b "); sql.append("join client as c "); sql.append("on b.clientId = c.clientId "); sql.append("where boardNo=?"); pstmt=con.prepareStatement(sql.toString()); pstmt.setInt(1, boardNo); rs=pstmt.executeQuery(); while(rs.next()) { BoardDTO boardDTO=new BoardDTO(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getInt(6)); boardInfo.put("boardDTO", boardDTO); ClientDTO clientDTO=new ClientDTO(rs.getString(4), null,null, rs.getString(7), null, null); boardInfo.put("clientDTO", clientDTO); } }finally { closeAll(rs, pstmt, con); } return boardInfo; }

3. Controller

1) src - controller.HandlerMapping.java에 viewBoard 추가

else if(command.equals("viewBoard")) // 글상세 페이지 이동 c=new ViewBoardController();

2) src - controller.ViewBoardController.java 추가

package controller; import java.util.HashMap; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import model.BoardDAO; public class ViewBoardController implements Controller { @Override public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception { HttpSession session = request.getSession(false); if(session==null||session.getAttribute("clientDTO")==null){ return "redirect:index.jsp"; } int boardNo=Integer.parseInt(request.getParameter("boardNo")); HashMap<String, Object> boardInfo= BoardDAO.getInstance().viewBoard(boardNo); request.setAttribute("boardInfo", boardInfo); request.setAttribute("url", "/view/board/viewBoard.jsp"); return "/view/template/home.jsp"; } }

[참고] 

Inner Join https://futurists.tistory.com/17

1. DB 수정

1) board 테이블에 deleteBoard 컬럼 추가

-- 컬럼 추가 - deleteBoard는 Not null, default는 N(삭제 시 Y로 바꾼다) alter table board add deleteBoard varchar(4) not null default 'N'

2) row number select 테스트

select row_number() over(order by boardNo asc) as rowNum,boardTitle,boardContent,clientId, boardDate, boardCount from board where deleteBoard='N' order by rowNum desc;

2. Template

1) WebContent - view - template - sidebar.jsp 수정

<a href="${pageContext.request.contextPath}/DispatcherServlet?command=boardList">게시판 보기</a>

3. View

1) WebContent - view - board - boardList.jsp  추가

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <div class="cardRight "> <a href="#">글쓰기</a> </div> <div class="card"> <table> <tr> <th>번호</th> <th>제목</th> <th>작성자ID</th> <th>작성일자</th> <th>조회수</th> </tr> <c:forEach var="boardDTO" items="${requestScope.boardList}"> <tr> <td>${boardDTO.boardRowNo}</td> <td><a href="#">${boardDTO.boardTitle}</a></td> <td>${boardDTO.clientId}</td> <td>${boardDTO.boardDate}</td> <td>${boardDTO.boardCount}</td> </tr> </c:forEach> </table> </div>

4. Model

1) src - model.BoardDTO.java 수정

package model; public class BoardDTO { private int boardRowNo; private int boardNo; private String boardTitle; private String boardContent; private String clientId; private String boardDate; private int boardCount; public BoardDTO() { super(); } public BoardDTO(int boardNo, String boardTitle, String boardContent, String clientId, String boardDate, int boardCount) { super(); this.boardNo = boardNo; this.boardTitle = boardTitle; this.boardContent = boardContent; this.clientId = clientId; this.boardDate = boardDate; this.boardCount = boardCount; } public BoardDTO(int boardRowNo, int boardNo, String boardTitle, String boardContent, String clientId, String boardDate, int boardCount) { super(); this.boardRowNo = boardRowNo; this.boardNo = boardNo; this.boardTitle = boardTitle; this.boardContent = boardContent; this.clientId = clientId; this.boardDate = boardDate; this.boardCount = boardCount; } public int getBoardRowNo() { return boardRowNo; } public void setBoardRowNo(int boardRowNo) { this.boardRowNo = boardRowNo; } public int getBoardNo() { return boardNo; } public void setBoardNo(int boardNo) { this.boardNo = boardNo; } public String getBoardTitle() { return boardTitle; } public void setBoardTitle(String boardTitle) { this.boardTitle = boardTitle; } public String getBoardContent() { return boardContent; } public void setBoardContent(String boardContent) { this.boardContent = boardContent; } public String getClientId() { return clientId; } public void setClientId(String clientId) { this.clientId = clientId; } public String getBoardDate() { return boardDate; } public void setBoardDate(String boardDate) { this.boardDate = boardDate; } public int getBoardCount() { return boardCount; } public void setBoardCount(int boardCount) { this.boardCount = boardCount; } @Override public String toString() { StringBuilder builder = new StringBuilder(); builder.append("BoardDTO [boardRowNo="); builder.append(boardRowNo); builder.append(", boardNo="); builder.append(boardNo); builder.append(", boardTitle="); builder.append(boardTitle); builder.append(", boardContent="); builder.append(boardContent); builder.append(", clientId="); builder.append(clientId); builder.append(", boardDate="); builder.append(boardDate); builder.append(", boardCount="); builder.append(boardCount); builder.append("]"); return builder.toString(); } }

2) src - model.BoardDAO.java에 getBoardList 메소드 추가

//1. board 전체 리스트 가져오기 public ArrayList<BoardDTO> getBoardList() throws SQLException { Connection con=null; PreparedStatement pstmt=null; ResultSet rs=null; ArrayList<BoardDTO> boardList= new ArrayList<BoardDTO>(); try { con=getConnection(); StringBuilder sql = new StringBuilder(); sql.append("select row_number() over(order by boardNo asc) as rowNum,boardNo,boardTitle,boardContent,clientId, boardDate,boardCount "); sql.append("from board "); sql.append("where deleteBoard='N' "); sql.append("order by rowNum desc"); pstmt=con.prepareStatement(sql.toString()); rs=pstmt.executeQuery(); while(rs.next()) { BoardDTO boardDTO=new BoardDTO(rs.getInt(1),rs.getInt(2),rs.getString(3),null,rs.getString(5),rs.getString(6),rs.getInt(7)); boardList.add(boardDTO); } }finally { closeAll(rs, pstmt, con); } return boardList; }

3) src - test.TestGetBoardList 추가

package test; import java.util.ArrayList; import model.BoardDAO; import model.BoardDTO; public class TestGetBoardList { public static void main(String[] args) { try { ArrayList<BoardDTO> boardList=BoardDAO.getInstance().getBoardList(); System.out.println(boardList); } catch (Exception e) { e.printStackTrace(); } } }

5. Controller

1) src - controller.HandlerMapping.java에 boardList추가

else if(command.equals("boardList")) // 글목록 페이지 이동 c=new BoardListController();

2) src - controller.BoardListController.java 추가

package controller; import java.util.ArrayList; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import model.BoardDAO; import model.BoardDTO; public class BoardListController implements Controller { @Override public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception { HttpSession session = request.getSession(false); if(session==null||session.getAttribute("clientDTO")==null){ return "redirect:index.jsp"; } ArrayList<BoardDTO> boardList=BoardDAO.getInstance().getBoardList(); request.setAttribute("boardList", boardList); request.setAttribute("url", "/view/board/boardList.jsp"); return "/view/template/home.jsp"; } }

[참고]

row_number() 사용 방법 https://www.fromdual.com/mariadb-10-2-window-function-examples

auto_increment 사용 방법 https://mariadb.com/kb/en/library/documentation/columns-storage-engines-and-plugins/data-types/auto_increment/

mariadb 테이블 수정 https://knoow.tistory.com/90

mariadb 테이블 수정2 http://blog.naver.com/PostView.nhn?blogId=imf4&logNo=220779978513&parentCategoryNo=&categoryNo=26&viewDate=&isShowPopularPosts=false&from=postView

mariadb 테이블 수정3 http://tcpschool.com/mysql/mysql_constraint_default

1. Template

1) WebContent - view - template - sidebar.jsp 수정

<p><a href="${pageContext.request.contextPath}/DispatcherServlet?command=deletePage">회원탈퇴</a></p>

2. View

1) WebContent - view - member - delete.jsp 추가

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <div class="card"> <form action="DispatcherServlet" method="post"> <input type="hidden" name="command" value="delete"> <h3>회원탈퇴</h3> <h4>아이디 패스워드를 입력하세요</h4> <table> <tr> <td>아이디</td> <td><input type="text" name="clientId" value="${sessionScope.clientDTO.clientId}" readonly="readonly"></td> </tr> <tr> <td>비밀번호</td> <td><input type="password" name="clientPassword" ></td> </tr> <tr rowspan="2"> <td><input type="submit" value="탈퇴"/></td> </tr> </table> </form> </div>

2) WebContent - view - member - delete_result.jsp 추가

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <script> alert("탈퇴되셨습니다."); location.href="http://localhost:8080/webstudy-board/"; </script>

1) WebContent - view - member - checkClient_fail.jsp 추가

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <script> alert("패스워드가 맞지 않습니다."); location.href="http://localhost:8080/webstudy-board/"; </script>

3. Model

1) src - model.ClientDAO.java에  checkClient 메소드 추가

//5. 회원 아이디 패스워드 체크 public int checkClient(String clientId, String clientPassword) throws SQLException { Connection con = null; PreparedStatement pstmt = null; ResultSet rs=null; int result = 0; try { con = getConnection(); String sql = "select count(*) from client where clientId=? and clientPassword=?"; pstmt = con.prepareStatement(sql); pstmt.setString(1, clientId); pstmt.setString(2, clientPassword); rs=pstmt.executeQuery(); if(rs.next()) result=rs.getInt(1); } catch (Exception e) { e.printStackTrace(); } finally { closeAll(rs,pstmt, con); } return result; }

2) src - test.TestCheckClient.java 추가

package test; import java.sql.SQLException; import model.ClientDAO; public class TestCheckClient { public static void main(String[] args) { String clientId="suman"; String clientPassword="sm1233"; try { int checkClient = ClientDAO.getInstance().checkClient(clientId,clientPassword); System.out.println(checkClient); } catch (SQLException e) { e.printStackTrace(); } } }

3) src - model.ClientDAO.java에  deleteMember 메소드 추가

4) src - test.TestDeleteMember.java 추가

4. Controller

1) src - controller.HandlerMapping.java 에 deletePage,delete 추가

else if(command.equals("deletePage")) // 회원탈퇴 페이지 이동 c=new DeletePageController(); else if(command.equals("delete")) // 회원탈퇴 처리 c = new DeleteController();

2) src - controller.DeletePageController.java 추가

package controller; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public class DeletePageController implements Controller { @Override public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception { request.setAttribute("url", "/view/member/delete.jsp"); return "/view/template/home.jsp"; } }

3) src - controller.DeleteController.java 추가

package controller; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import model.ClientDAO; public class DeleteController implements Controller { @Override public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception { HttpSession session = request.getSession(); if(session==null||session.getAttribute("clientDTO")==null){ return "redirect:index.jsp"; } String clientId=request.getParameter("clientId"); String clientPassword=request.getParameter("clientPassword"); int checkClient = ClientDAO.getInstance().checkClient(clientId,clientPassword); if(checkClient==0) { return "redirect:view/member/checkClient_fail.jsp"; }else { session.invalidate(); ClientDAO.getInstance().deleteMember(clientId); return "redirect:view/member/delete_result.jsp"; } } }