[국비 73일차 TIL] 전자정부 프레임워크 스프링 notice write login DB

전자정부 프레임워크 스프링 프로젝트

indexMapper.xml

if(date_format(current_timestamp(),'%Y-%m-%d') = date_format(b.board_date,'%Y-%m-%d'), 
      date_format(b.board_date,'%h:%i'),date_format(b.board_date,'%m-%d')) AS board_date,

	<select id="freeboard" resultType="BoardDTO">
		SELECT mtno, mno, mtdel, mtread, mtcate, mttitle, mtip
		if(date_format(current_timestamp(),'%Y-%m-%d') = date_format(b.board_date,'%Y-%m-%d'), 
			date_format(b.board_date,'%h:%i'),date_format(b.board_date,'%m-%d')) AS mtdate,
		FROM multiboard
		WHERE mtcate=1 AND mtdel=1
		ORDER BY mtno DESC
		LIMIT 0, 10
	</select>

multiboard view 만들기

CREATE VIEW multiboardview AS SELECT mtno, mttitle,
      mtcontent, if(DATE_FORMAT(now(), '%Y-%m-%d')=DATE_FORMAT(mtdate, '%Y-%m-%d'), 
      DATE_FORMAT(mtdate, '%H-%i'), DATE_FORMAT(mtdate, '%m-%d')) AS mtdate, 
      mtip, mtdel, mtread, mtcate, (SELECT mname FROM member WHERE multiboard.mno=member.mno) AS mname
      FROM multiboard WHERE mtdel =1 ORDER BY mtno DESC

board.html

    <!-- 2024-03-08 div로 테이블 만들기 -->
    
    <div class="table">
    	<div class="row" th:each="row : ${board}">
    		<div class ="col-1" th:text="${row.mtno}"></div>
    		<div class ="col-6 text-start">[[${row.mttitle}]]</div>
    		<div class ="col-2" th:text="${row.mname}"></div>
    		<div class ="col-2" th:text="${row.mtdate}"></div>
    		<div class ="col-1" th:text="${row.mtcount}"></div>
    	</div>
    </div>

IndexController.java

@RequestParam("cate") int cate
freeboard의 변수에 cate 추가

	// 2024-03-08
	@GetMapping("/freeboard")
	public String freeboard(@RequestParam(value="cate", defaultValue="1") int cate, Model model) {
		List<BoardDTO> board = indexService.freeboard(cate);
		model.addAttribute("board", board);
		return "board";
	}
	
	@GetMapping("/notice")
	public String notice(@RequestParam(value="cate", defaultValue = "2") int cate, Model model) {
		List<BoardDTO> board = indexService.freeboard(cate);
		model.addAttribute("board", board);
		return "notice";
	}

 

-> freeboard 생성 (서비스, dao)

IndexService

	public List<BoardDTO> freeboard(int cate) {
		return indexDAO.freeboard(cate);
	}

IndexDAO

	List<BoardDTO> freeboard(int cate);

indexMapper.xml

id="freeboard"
WHERE mtcate=#{cate}

	<select id="freeboard" resultType="BoardDTO" parameterType="int">
		SELECT mtno, mttitle, mno, mtdate, mtip, mtdel, mtread, mtcate
		FROM multiboardview 
		WHERE mtcate=#{cate} AND mtdel=1
		LIMIT 0,10
	</select>

	<select id="detail" parameterType="int" resultType="BoardDTO">
		SELECT mtno, mttitle, mtcontent, mname, mtdate, mtip, mtdel, mtread, mtcate
		FROM multiboardview
		WHERE mtno=#{no}
	</select>

 

menu.html

					<ul class="navbar-nav ms-auto me-4 my-3 my-lg-0">
						<li class="nav-item"><a class="nav-link me-lg-3" href="/freeboard?cate=1">Freeboard</a></li>
						<li class="nav-item"><a class="nav-link me-lg-3" href="/notice?cate=2">Notice</a></li>
						<li class="nav-item"><a class="nav-link me-lg-3" href="/notice?cate=3">Notice3</a></li>
						<li class="nav-item"><a class="nav-link me-lg-3" href="/notice?cate=4">Notice4</a></li>
						<li class="nav-item"><a class="nav-link me-lg-3" href="/notice?cate=5">Notice5</a></li>
						<li class="nav-item"><a class="nav-link me-lg-3" href="/notice?cate=6">Notice6</a></li>
						<li class="nav-item"><a class="nav-link me-lg-3" href="/notice?cate=7">Notice7</a></li>
					</ul>

IndexController

매핑할 주소와 defaultValue의 숫자만 바꿔주면 다른 게시판이 됨.

	@GetMapping("/notice")
	public String notice(@RequestParam(value="cate", defaultValue = "2") int cate, Model model) {
		List<BoardDTO> board = indexService.freeboard(cate);
		model.addAttribute("board", board);
		return "notice";
	}

notice.html

board.html과 같아서 복사해서 만들어 줌. 

	<div class="text-center">
		<div class="container mt-5">
			<h1>공지사항</h1>

			<span> [[${#lists.size(board)}]]개의 글이 있습니다.</span>
			<div th:if="${#lists.size(board) le 0}">
				<h2>출력할 데이터가 없습니다.</h2>
				<h3>관리자에게 문의하세요.</h3>
			</div>

			<div th:unless="${#lists.size(board) le 0}">
				<!-- 2024-03-08  -->
				<div class="table table-hover">
					<div class="row" th:each="row : ${board}">
						<div class="col-1" th:text="${row.mtno}"></div>
						<div class="col-6 text-start">[[${row.mttitle}]]</div>
						<div class="col-2" th:text="${row.mname}"></div>
						<div class="col-2" th:text="${row.mtdate}"></div>
						<div class="col-1" th:text="${row.mtcount}"></div>
					</div>
				</div>
			</div>
		</div>

    <!-- 2024-03-08  -->
    <div class="table">
    	<div class="row" th:each="row : ${board}">
    		<div class ="col-1" th:text="${row.mtno}"></div>
    		<div class ="col-6 text-start">[[${row.mttitle}]]</div>
    		<div class ="col-2" th:text="${row.mname}"></div>
    		<div class ="col-2" th:text="${row.mtdate}"></div>
    		<div class ="col-1" th:text="${row.mtcount}"></div>
    	</div>
    </div>
</div>

board.html

 <button type="button" th:with="cate=${board[0].mtcate}" class="btn btn-primary" onclick="location.href='/write?cate=${board[0].mtcate}'">글쓰기</button>>

---

글쓰기 write

IndexController.java

	@GetMapping("/write")
	public String write() {
		return "write";
	}

write.html

	<div class="text-center">
		<div class="container mt-5">
			<br>
			<br>
			<h1>글쓰기 write</h1>

			들어온 카테고리 : [[${param.cate}]]

			<form class="container" action="/write" method="post">
				<div class="form-group col-md-6 d-flex align-items-center justify-content-center">
					<input type="text" id="title" name="title" class="form-control" placeholder="제목" aria-label="제목">
				</div>
				<div class="form-group col-md-6 d-flex align-items-center justify-content-center">
					<textarea id="content" name="content" class="form-control" aria-label="With textarea" style="height: 300px;"></textarea>
				</div>
				<div class="form-group col-md-12">
					<button type="submit" class="btn btn-dark">글쓰기</button>
				</div>
			</form>
			
			<input type="hidden" name="mtcate" th:value="${param.cate}">

indexMapper

	<select id="detail" parameterType="int" resultType="BoardDTO">
		SELECT mtno, mttitle, mtcontent, mname, mtdate, mtip, mtdel, mtread, mtcate
		FROM multiboardview
		WHERE mtno=#{no}
	</select>
	
	<insert id="write" parameterType="Map">
		INSERT INTO multiboard (mttitle, mtcontent, mtip, mtcate, mno) 
		VALUES (#{title], #{content}, #{ip}, #{cate}, (SELECT mno FROM member WHERE mid=#{mid}))
	</insert>

IndexController

	@PostMapping("/write")
	public String write(@RequestParam Map<String, Object> map, HttpServletRequest request) {
		String ip = util.getIP();
		int result = indexService.write(map);
		System.out.println(result);
		
		return "write";
	}

 

-> write() 생성 (서비스,dao)

IndexService

	public int write(Map<String, Object> map) {
		// DB에 있는 mid, ip 추가하기
		map.put("mid", "test");
		map.put("mtcate", map.get("cate"));
		map.put("ip", util.getIP());
		
		return indexDAO.write(map);
	}

IndexDAO

	int write(Map<String, Object> map);

---

로그인

Util.java

package com.example.web.util;

import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpSession;

@Component
public class Util {
	public HttpServletRequest req() {
		ServletRequestAttributes sra = (ServletRequestAttributes) RequestContextHolder.currentRequestAttributes();
		HttpServletRequest request = sra.getRequest();
		return request;
	}

	public HttpSession getSession() {
		HttpSession session = req().getSession();
		return session;
	}

	public int str2Int(String str) {
		try {
			return Integer.parseInt(str);
		} catch (Exception e) {
			return 0;
		}
	}
	
	public int str2Int(Object obj) {
		return str2Int(String.valueOf(obj));
	}

	// ip
	public String getIP() {
		HttpServletRequest request = req();
		String ip = request.getHeader("X-FORWARDED-FOR");
		if (ip == null) {
			ip = request.getHeader("Proxy-Client-IP");
		}
		if (ip == null) {
			ip = request.getHeader("WL-Proxy-Client-IP");
		}
		if (ip == null) {
			ip = request.getHeader("HTTP_CLIENT_IP");
		}
		if (ip == null) {
			ip = request.getHeader("HTTP_X_FORWARDED_FOR");
		}
		if (ip == null) {
			ip = request.getRemoteAddr();
		}
		return ip;
	}
}

MemberController

@Controller
public class MemberController {
	
	@GetMapping("/login") // 화면만 보여줌
	public String login() {
		return "login";
	}
	
	@PostMapping("/login") // 실제 로그인 작업
	public String login2(@RequestParam Map<String, Object> map) {
		System.out.println(map);

		return "login";
	}
}

login.html

<body>
	<th:block th:insert="~{menu.html :: menu}" />
	
		<aside class="text-center">
		<div class="container px-5">
			<div>
				<form action="/login" method="post">
					<input type="text" name="id">
					<input type="password" name="pw">
					<button type="submit">Login</button>
				</form>
			</div>
		</div>
	</aside>

memberMapper.xml

<mapper namespace="com.example.web.dao.MemberDAO">
	<select id="login" parameterType="Map" resultType="Map">
		SELECT COUNT(*) as count, mname 
		FROM member
		WHERE mid=#{id} AND #{pw}=AES_DECRYPT(UNHEX(mpw), 'Daiso')
	</select>
</mapper>

MemberController.java

	@PostMapping("/login") // 실제 로그인 작업
	public String login2(@RequestParam Map<String, Object> map) {
		System.out.println(map);
		
		Map<String, Object> result = memberService.login(map);
		System.out.println(result);
		if(util.str2Int((String)result.get("count")) == 1) {
			// 정상 로그인 -> 세션 -> board 이동
			HttpSession session = util.getSession();
			session.setAttribute("mid", map.get("id"));
			session.setAttribute("mname", map.get("mname"));
			return "redirect:/freeboard";
		} else {
			// 로그인 불가
			return "redirect:/login";
		}
	}
}

menu.html

로그인, 마이페이지, 로그아웃
로그인 한 사람만 마이페이지 보이도록

						<th:block th:if="${sessionScope.mid ne null }">
							<li class="nav-item"><a class="nav-link me-lg-3" href="/myInfo">[[${session.mname}]]</a></li>
							<li class="nav-item"><a class="nav-link me-lg-3" href="/logout">LOGOUT</a></li>
						</th:block>
						<th:block th:unless="${sessionScope.mid ne null }">
							<li class="nav-item"><a class="nav-link me-lg-3" href="/login">LOGIN</a></li>
						</th:block>

mariaDB (multiboard)

CREATE TABLE `multiboard` (
	`mtno` INT(11) NOT NULL AUTO_INCREMENT,
	`mttitle` VARCHAR(255) NOT NULL COLLATE 'utf8mb4_general_ci',
	`mtcontent` LONGTEXT NOT NULL COLLATE 'utf8mb4_general_ci',
	`mno` INT(11) NULL DEFAULT NULL,
	`mtdate` DATETIME NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
	`mtdel` INT(1) NULL DEFAULT '1',
	`mtread` INT(11) NULL DEFAULT '1',
	`mtcate` INT(1) NULL DEFAULT '1',
	`mid` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`mname` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`mtip` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	PRIMARY KEY (`mtno`) USING BTREE,
	INDEX `FK_board_member` (`mno`) USING BTREE,
	CONSTRAINT `FK_multiboard_member` FOREIGN KEY (`mno`) REFERENCES `member` (`mno`) ON UPDATE NO ACTION ON DELETE NO ACTION
);

---

테스트하기

WebApplicationTest.java

@SpringBootTest
class WebApplicationTests {

	@Autowired
	MemberService memberService;
	
	@DisplayName("로그인 카운트 값 확인")
	@Test
	void contextLoads() {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("id", "test");
		map.put("pw", "01234567");
		
		Map<String, Object> result = memberService.login(map);
		
		// 결과값이 1로 나오니? 비교할 값
		// assertEquals(1, Integer.parseInt(String.valueOf(result.get("count"))));
		
		int num = 100;
		assertEquals(100, num);
	}
}

 

테스트 실행할 때는 JUnit Test 로 실행하기.

---

IndexController.java

	@PostMapping("/postDel")
	public String postDel(@RequestParam("no") int no) {
		System.out.println(no);
		return "redirect:/freeboard";
	}

detail.html

<!DOCTYPE html>
<html lang="ko" xmlns:th="http://thymeleaf.org">
    <head>
        <th:block th:insert="~{menu.html :: head}"></th:block>
        <script type="text/javascript">
      function update(no){
    	  Swal.fire({
    		  title: "수정합니까?",
    		  text: "해당 내용을 수정합니다.",
    		  icon: "warning",
    		  showCancelButton: true,
    		  confirmButtonColor: "#3085d6",
    		  cancelButtonColor: "#d33",
    		  confirmButtonText: "수정"
    		}).then((result) => {
    		  if (result.isConfirmed) {
    		    Swal.fire({
    		      title: "수정을 선택했습니다.",
    		      text: "수정합니다.",
    		      icon: "success"
    		    });
    		  }
    		});
      }
        
      function del(no){
    	  Swal.fire({
    		  title: "삭제합니까?",
    		  text: "해당 내용을 삭제합니다. 복구가 불가능합니다.",
    		  icon: "warning",
    		  showCancelButton: true,
    		  confirmButtonColor: "#3085d6",
    		  cancelButtonColor: "#d33",
    		  confirmButtonText: "삭제"
    		}).then((result) => {
    		  if (result.isConfirmed) {
    		    //Swal.fire({title: "삭제를 선택했습니다.",text: "삭제합니다.",icon: "success"});
    		    //location.href="/postDel?no="+no;
    		    let form = document.createElement('form');
    		    form.setAttribute('method','post');
    		    form.setAttribute('action','/postDel');
    		    
    		    let input = document.createElement('input');
    		    input.setAttribute('type','hidden');
    		    input.setAttribute('name','no');
    		    input.setAttribute('value', no);
    		    
    		    form.appendChild(input);
    		    document.body.appendChild(form);
    		    form.submit();
    		  }
    		});
      }
      
        </script>
    </head>
    <body id="page-top">
        <!-- Navigation-->
        <th:block th:insert="~{menu.html :: menu}"></th:block>
        <!-- Mashead header-->
        
        <!-- Quote/testimonial aside-->
        <aside class="text-center">
            <div class="container px-5">
                      	
			<div class="p-3 mt-5 mb-2 rounded" style="background-color: #FAFAFA">
				<div class="border-bottom">
					<h3 th:text="${detail.mttitle}"></h3>
				</div>
				<div class="border-bottom" style="background-color: #c0c0c0">
					<div class="row">
						<div class="col-6 text-start">
						[[${detail.mname }]]님 
						<i class="bi bi-pencil-fill" th:id="${detail.mtno }" onclick="update(this.id)"></i> 
						<i class="bi bi-trash2-fill" th:id="${detail.mtno }" onclick="del(this.id)"></i>
						</div>
						<div class="col-6 row text-end">
							<div class="col-7" th:text="${detail.mtread }"></div>
							<div class="col-5" th:text="${detail.mtip }"></div>
						</div>
					</div>
				</div>
				<div class="mt-3 text-start" th:utext="${detail.mtcontent }" style="min-height: 300px; height: auto"></div>
			</div>
			
			<button type="button" class="btn">게시판으로</button>
            </div>
        </aside>
        <!-- App features section-->
        
        
        <!-- Footer-->
        <th:block th:insert="~{menu.html :: footer}"></th:block>
        <!-- Feedback Modal-->
        <!-- Bootstrap core JS-->
        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
        <!-- Core theme JS-->
        <script src="js/scripts.js"></script>
        <!-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *-->
        <!-- * *                               SB Forms JS                               * *-->
        <!-- * * Activate your form at https://startbootstrap.com/solution/contact-forms * *-->
        <!-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *-->
        <script src="https://cdn.startbootstrap.com/sb-forms-latest.js"></script>
    </body>
</html>